反射实体类拼接SQL语句
时间：2021-07-01 10:21:17
帮助过：9人阅读
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();
反射实体类拼接SQL语句
标签：space isnull key dex orb ima contex catch override
