反射实体类拼接SQL语句

实体类基类：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Reflection;
  5 using System.Text;
  6 using System.Threading.Tasks;
  7 
  8 namespace Common
  9 {
 10     /// <summary>
 11     /// 实体类基类
 12     /// </summary>
 13     [Serializable]
 14     public abstract class EntityBase
 15     {
 16         /// <summary>
 17         /// 获取主键
 18         /// </summary>
 19         /// <returns></returns>
 20         public abstract string GetPrimaryKey();
 21         /// <summary>
 22         /// 获取INSERT语句
 23         /// </summary>
 24         /// <returns></returns>
 25         public string GetInsertSql()
 26         {
 27             try
 28             {
 29                 Type t = this.GetType();
 30                 string tableName = t.Name,pKey=this.GetPrimaryKey(),fields=string.Empty,values=string.Empty,temp=null;
 31                 foreach (PropertyInfo pi in t.GetProperties())
 32                 {
 33                     if (!pi.CanWrite) continue;
 34                     if (pi.Name.Equals(pKey))
 35                     {
 36                         continue;
 37                     }
 38                     temp = GetByTypeStr(pi);
 39                     fields += pi.Name + ",";
 40                     values += temp + ",";
 41                 }
 42                 return string.Format("Insert into {0}({1}) Values({2})", tableName, fields.TrimEnd(','), values.TrimEnd(','));
 43             }
 44             catch
 45             {
 46                 throw;
 47             }
 48         }
 49         /// <summary>
 50         /// 获取UPDATE语句
 51         /// </summary>
 52         /// <returns></returns>
 53         public string GetUpdateSql()
 54         {
 55             try
 56             {
 57                 Type t = this.GetType();
 58                 PropertyInfo[] pInfos = t.GetProperties();
 59                 string tableName = t.Name, pKey = this.GetPrimaryKey(), str_fields=string.Empty;
 60                 int keyIndex = -1;
 61                 for (int i = 0; i < pInfos.Length; i++)
 62                 {
 63                     if (pInfos[i].Name.Equals(this.GetPrimaryKey()))
 64                     {
 65                         keyIndex = i;
 66                         continue;
 67                     }
 68                     str_fields += pInfos[i].Name + " = " + GetByTypeStr(pInfos[i]) + ",";
 69                 }
 70                 return string.Format("Update {0} Set {1} Where {2} = {3}", tableName, str_fields.TrimEnd(','),this.GetPrimaryKey(), GetByTypeStr(pInfos[keyIndex]));
 71             }
 72             catch
 73             {
 74                 throw;
 75             }
 76         }
 77         /// <summary>
 78         /// 根据数据类型反射字段值
 79         /// </summary>
 80         /// <param name="pInfo">公共属性</param>
 81         /// <returns></returns>
 82         private string GetByTypeStr(PropertyInfo pInfo)
 83         {
 84             try
 85             {
 86                 string result_str = string.Empty;
 87                 Type t = pInfo.PropertyType;
 88                 object obj = pInfo.GetValue(this, null);
 89                 bool valueNull = StringUtil.isNullOrBlank(obj);
 90                 if (t == typeof(string))
 91                 {
 92                     result_str = valueNull ? "null" : "'" + obj.ToString().Replace("--","") + "'";
 93                 }
 94                 else if (t == typeof(System.Decimal) || t == typeof(System.Int16) || t == typeof(System.Int32) || t == typeof(System.Int64))
 95                 {
 96                     result_str = t.Name == "Nullable`1"&& valueNull ? "null" : obj.ToString();
 97                     //if ()
 98                     //{
 99 
100                     //}
101                     //else
102                     //{
103                     //    result_str = valueNull ? "0" : obj.ToString();
104                     //}                    
105                 }
106                 else if(t==typeof(DateTime)||t.Name== "Nullable`1")
107                 {
108                     if (valueNull||DateTime.MinValue.Equals(obj)|| t.Name == "Nullable`1")
109                     {
110                         result_str = "null";
111                     }
112                     else
113                     {
114                         result_str = "'"+obj.ToString().Replace("年", "-").Replace("月", "-").Replace("日", "")+"'";
115                     }
116                 }
117                 return result_str;
118             }
119             catch
120             {
121                 throw;
122             }
123         }
124     }
125 }

实体类：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Threading.Tasks;
 6 using Common;
 7 
 8 namespace Model
 9 {
10     public class MainModel:EntityBase
11     {
12         public decimal id { get; set; }
13         public string title { get; set; }
14         public string contents { get; set; }
15         public string type { get; set; }
16         public DateTime? date { get; set; }
17         public string people { get; set; }
18         public string picurl { get; set; }
19         /// <summary>
20         /// 设置主键
21         /// </summary>
22         /// <returns></returns>
23         public override string GetPrimaryKey()
24         {
25             return "id";
26         }
27     }
28 }

调用：

1             Model.MainModel model = new Model.MainModel();
2             model.title = context.Request.Form["txtTitle"];
3             model.people = context.Request.Form["txtName"];
4             model.contents = context.Request.Form["txtContent"];
5             string resSql = model.GetInsertSql();