c#通过反射将object类生成sql语句源码

一.行为类的创建

   public class ModeCommandAttribute:Attribute
   {  
       //表名指定插入与更新规则，原名称加__加关键字update，inset，all，name
       //ignor不进行更新或者插入
       //updateKey更新依据
       //name新的名称
       public string update = "";
       public string insert = "";
       public string all = "";
       public string name = "";
   }

 

二.object类的代码转换

 /// <summary>
        /// 获取插入语句（oracle）
        /// </summary>
        /// <param name="bs"></param>
        /// <returns></returns>
        public static string getInsertSql(this Object bs)
        {
            Dictionary<FieldInfo, string> FIKeyDic = new Dictionary<FieldInfo, string>();
            string key = "";
            //通过反射获取bs内部的所有公共字段。
            FieldInfo[] FIArray = bs.GetType().GetFields(); 
            //sql语句拼装
            StringBuilder sb = new StringBuilder("insert into ");
            //bs类中必须包含公共字段table_name用来指示插入或者更新的表名
            sb.Append(bs.GetType().GetField("table_name").GetValue(bs));
            sb.Append(" (");
            foreach (FieldInfo FI in FIArray)
            {
                ModeCommand required = FI.GetCustomAttribute<ModeCommand>();
                string fiName = FI.Name;
                //获取公用字段行为（目前有修改名称，更新键，忽略三种功能）
                if (required != null)
                {
                    key = getInsertKeyWord(required, ref fiName);
                }
                //忽略功能的使用
                if (key == "ignor")
                { continue; }
                //记录相对应的功能和公用字段
                FIKeyDic.Add(FI, key);
                sb.Append(fiName);
                sb.Append(",");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(") values (");
            foreach (var FIKey in FIKeyDic)
            {
                FieldInfo FI = FIKey.Key;
                key = FIKey.Value;
                //忽略功能的使用
                if (key == "ignor")
                { continue; }
                //获取数据
                var value = FI.GetValue(bs);
          
                if (value != null)
                {
                    //获取数据类型
                    string type = value.GetType().ToString();
                    switch (type)
                    {
                        case "System.Double":
                        case "System.Int32":
                            sb.Append(value);
                            break;
                        case "System.DateTime":
                            sb.Append("TO_DATE('");
                            sb.Append(((DateTime)value).ToString("yyyy-MM-dd HH:mm"));
                            sb.Append("','yyyy-mm-dd HH24:mi')");
                            break;
                        default:
                        case "System.String":
                            sb.Append("'");
                            sb.Append(value);
                            sb.Append("'");
                            break;

                    }
                }
                else
                {
                    sb.Append("null");
                }
                sb.Append(",");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(") ");
            return sb.ToString();
        }
        /// <summary>
        /// 获取更新语句（oracle）
        /// </summary>
        /// <param name="bs"></param>
        /// <returns></returns>
        public static string getUpdateSql(this Object bs)
        {
            List<FieldInfo> updateKeyList = new List<FieldInfo>();
            string key = "";//关键字目前仅有ignor与key
            FieldInfo[] FIArray = bs.GetType().GetFields();
            StringBuilder sb = new StringBuilder("update ");
            sb.Append(bs.GetType().GetField("table_name").GetValue(bs));
            sb.Append(" set ");
            foreach (FieldInfo FI in FIArray)
            {
                ModeCommand required = FI.GetCustomAttribute<ModeCommand>();
                string fiName = FI.Name;
                key = getInsertKeyWord(required, ref fiName, "update");

                if (key == "ignor")
                { continue; }
                if (key == "updateKey")
                { updateKeyList.Add(FI); continue; }
                var value = FI.GetValue(bs);
                if (value == null)
                { continue; }
                sb.Append(fiName);
                sb.Append("=");
               
                string type = value.GetType().ToString();
                switch (type)
                {
                    default:
                    case "System.Double":
                    case "System.Int32":
                        sb.Append(value);
                        break;
                    case "System.String":
                        sb.Append("'");
                        sb.Append(value);
                        sb.Append("'");
                        break;
                    case "System.DateTime":
                        sb.Append("TO_DATE('");
                        sb.Append(((DateTime)value).ToString("yyyy-MM-dd HH:mm"));
                        sb.Append("','yyyy-mm-dd HH24:mi')");
                        break;

                }
                sb.Append(",");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(" where ");
            foreach (FieldInfo FI in updateKeyList)
            {
                sb.Append(FI.Name);
                sb.Append("=");
                var value = FI.GetValue(bs);
                string type = value.GetType().ToString();
                switch (type)
                {
                    default:
                    case "System.Double":
                    case "System.Int32":
                        sb.Append(value);
                        break;
                    case "System.String":
                        sb.Append("'");
                        sb.Append(value);
                        sb.Append("'");
                        break;
                    case "System.DateTime":
                        sb.Append("TO_DATE('");
                        sb.Append(((DateTime)value).ToString("yyyy-MM-dd HH:mm"));
                        sb.Append("','yyyy-mm-dd HH24:mi')");
                        break;

                }
                sb.Append(" and ");
            }
            sb.Remove(sb.Length - 5,4);
            return sb.ToString();
        }
     
        public static string getInsertKeyWord(ModeCommand required,ref string fiName, string inOrUp = "insert")
        {
            string KeyWord = "";
            if (inOrUp == "insert")
            {
                KeyWord = required.insert;
            }
            else if (inOrUp == "update")
            {
                KeyWord = required.update;
            }
            if (KeyWord == "")
            {
                KeyWord = required.all;
            }
            if (required.name != "")
            { fiName = required.name; }
            return KeyWord;
        }

三.转换代码的使用

1.类的创建

public class BASETest
   {

       //表名
       [ModeCommandAttribute(all = "ignor")]
       public string table_name = "test";

       public string Table_name
       {
           get { return table_name; }
           set { table_name = value; }
       }
       [ModeCommandAttribute(update = "updateKey" )]
       public DateTime full_date = DateTime.Now;

       public DateTime Full_date
       {
           get { return full_date; }
           set { full_date = value; }
       }
       [ModeCommandAttribute(insert = "ignor" )]
       public double? use_flux = null;

       public double? Use_flux
       {
           get { return use_flux; }
           set { use_flux = value; }
       }

       public double? three_days_avg = null;
       public double? Three_days_avg
       {
           get { return three_days_avg; }
           set { three_days_avg = value; }
       }
       public int fm_id = 5000;
       public int Fm_id
       {
           get { return fm_id; }
           set { fm_id = value; }
       }
   }

2.代码的调用

            BASETest bt = new BASETest();
            Console.WriteLine("插入语句："+ bt.getInsertSql());
            Console.WriteLine("更新语句："+bt.getUpdateSql());

3.效果展示

 

（目前这段代码有个已知的缺陷，无法将非null的字段更新成为null）