关于动态生成SQL语句的简单实现

　　代码如下：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.IO;
  4 using System.Linq;
  5 using System.Linq.Expressions;
  6 using System.Reflection;
  7 using System.Text;
  8 using System.Text.RegularExpressions;
  9 
 10 namespace PostgreSqlBuilder
 11 {
 12     public class PostgreSqlBuilder
 13     {
 14         /// 

 15         /// 模式
 16         /// 
 17         public string Schema { get; set; }
 18         
 19         public PostgreSqlBuilder() { }
 20         public PostgreSqlBuilder(string schema)
 21         {
 22             this.Schema = schema;
 23         }
 24 
 25         /// 

 26         /// 批量生成INSERT SQL脚本-无自增外键
 27         /// 
 28         /// 目标对象类型
 29         /// 目标对象集合
 30         /// 目标对象主键名
 31         /// 
 32         public string BatchInsertSql(IEnumerable collection, string key = "Id") where T : class
 33         {
 34             var sbBatch = new StringBuilder();
 35             foreach (var obj in collection)
 36             {
 37                 sbBatch.AppendLine(InsertSqlNoIncForeignKey(obj, key));
 38             }
 39             return sbBatch.ToString();
 40         }
 41         /// 

 42         /// 批量生成INSERT SQL脚本-有一个自增外键；
 43         /// 需先生成主键表sql脚本插入成功后，再使用该方法；
 44         /// 要求参数constraint为主外键表共有字段；
 45         /// 
 46         /// 目标对象类型
 47         /// 外键关联对象类型
 48         /// 目标对象集合
 49         /// 目标对象外键名
 50         /// 外键关联对象唯一约束属性名(非主键)
 51         /// INSERT SQL脚本
 52         public string BatchInsertSqlWithOuterKeyId(IEnumerable collection
 53             , string outKey, string constraint) where T : class where TOuter : class
 54         {
 55             var sbBatch = new StringBuilder();
 56             foreach (var obj in collection)
 57             {
 58                 sbBatch.AppendLine(InsertSqlWithOuterKeyId(obj, outKey, constraint));
 59             }
 60             return sbBatch.ToString();
 61         }
 62         /// 

 63         /// 批量生成UPDATE-INSERT SQL脚本
 64         /// 
 65         /// 目标对象类型
 66         /// 目标对象集合
 67         /// 目标对象主键名
 68         /// 冲突
 69         /// 
 70         public string BatchUpSertSql(IEnumerable collection, string key = "Id", params string[] conflicts) where T : class
 71         {
 72             var sbBatch = new StringBuilder();
 73             foreach (var obj in collection)
 74             {
 75                 sbBatch.AppendLine(UpSertSql(obj, key, conflicts));
 76             }
 77             return sbBatch.ToString();
 78         }
 79 
 80         #region 生成INSERT SQL脚本-无自增外键
 81 
 82         /// 

 83         /// 生成INSERT SQL脚本-无自增外键
 84         /// 
 85         /// 目标对象类型
 86         /// 目标对象
 87         /// 目标对象主键名
 88         /// INSERT SQL脚本
 89         public string InsertSqlNoIncForeignKey(T targetObj, string targetObjKey = "Id") where T : class
 90         {
 91             var type = typeof(T);
 92             var tableName = GetTableName(type);
 93             var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
 94                 .Where(p => IsFundamental(p.PropertyType));
 95             var sbColumn = new StringBuilder(100);
 96             var sbValue = new StringBuilder(200);
 97             sbColumn.Append("INSERT INTO " + tableName + " (");
 98             sbValue.Append(" VALUES (");
 99             foreach (var propertyInfo in propertyInfos)
100             {
101                 //自增主键(默认int类型),过滤
102                 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue;
103                 sbColumn.Append($" {GetFieldName(propertyInfo)}");
104                 sbColumn.Append(" ,");
105                 //获取属性值
106                 var attribute =
107                     propertyInfo.GetCustomAttribute(typeof(SetPostgreSqlValueAttribute), false);
108                 var propertyValue = propertyInfo.GetValue(targetObj);
109                 if (attribute != null)
110                 {
111                     if (attribute is SetPostgreSqlValueAttribute setSqlValueAttribute)
112                         sbValue.Append($"{setSqlValueAttribute.Value} ,");
113                 }
114                 else if (propertyValue == null)
115                     sbValue.Append(" null,");
116                 else
117                 {
118                     sbValue.Append(" '");
119                     sbValue.Append($"{propertyValue?.ToString()}");
120                     sbValue.Append("' ,");
121                 }
122             }
123             sbColumn.Replace(',', ')', sbColumn.Length - 1, 1);
124             sbValue.Replace(',', ')', sbValue.Length - 1, 1);
125             sbColumn.Append(sbValue).Append(';');
126             return sbColumn.ToString();
127         }
128 
129         /// 

130         /// 生成INSERT SQL脚本-无自增外键
131         /// 
132         /// 目标对象类型
133         /// 目标对象
134         /// 目标对象属性表达式-主键
135         /// INSERT SQL脚本
136         public string InsertSqlNoIncForeignKey(T targetObj, Expression> targetObjKeyExpr) where T : class
137         {
138             var type = typeof(T);
139             var tableName = GetTableName(type);
140             var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
141                 .Where(p => IsFundamental(p.PropertyType));
142 
143             if (targetObjKeyExpr.Body is not MemberExpression body)
144                 throw new ArgumentException($"'targetObjKeyExpr'不是MemberExpression, 表达式：{targetObjKeyExpr}");
145             var keyPropertyInfo = (PropertyInfo)body.Member;
146 
147             var sbColumn = new StringBuilder(100);
148             var sbValue = new StringBuilder(200);
149             sbColumn.Append("INSERT INTO " + tableName + " (");
150             sbValue.Append(" VALUES (");
151             foreach (var propertyInfo in propertyInfos)
152             {
153                 //自增主键(默认int类型),过滤
154                 if (propertyInfo.Name.Equals(keyPropertyInfo.Name) &&
155                     keyPropertyInfo.PropertyType == typeof(int)) continue;
156                 sbColumn.Append($" {GetFieldName(propertyInfo)}");
157                 sbColumn.Append(" ,");
158                 //获取属性值
159                 var attribute =
160                     propertyInfo.GetCustomAttribute(typeof(SetPostgreSqlValueAttribute), false);
161                 var propertyValue = propertyInfo.GetValue(targetObj);
162                 if (attribute != null)
163                 {
164                     if (attribute is SetPostgreSqlValueAttribute setSqlValueAttribute)
165                         sbValue.Append($"{setSqlValueAttribute.Value} ,");
166                 }
167                 else if (propertyValue == null)
168                     sbValue.Append(" null,");
169                 else
170                 {
171                     sbValue.Append(" '");
172                     sbValue.Append($"{propertyValue?.ToString()}");
173                     sbValue.Append("' ,");
174                 }
175             }
176             sbColumn.Replace(',', ')', sbColumn.Length - 1, 1);
177             sbValue.Replace(',', ')', sbValue.Length - 1, 1);
178             sbColumn.Append(sbValue).Append(';');
179             return sbColumn.ToString();
180         } 
181 
182         #endregion
183 
184         /// 

185         /// 生成INSERT SQL脚本-有一个自增外键；
186         /// 需先生成主键表sql脚本插入成功后，再使用该方法；
187         /// 要求参数constraint为主外键表共有字段；
188         /// 
189         /// 目标对象类型
190         /// 外键关联对象类型
191         /// 目标对象
192         /// 目标对象外键名
193         /// 外键关联对象唯一约束属性名(非主键)
194         /// 目标对象主键名
195         /// 外键关联对象主键名
196         /// INSERT SQL脚本
197         public string InsertSqlWithOuterKeyId(T targetObj, string foreignKey, string constraint, string targetObjKey = "Id", string foreignObjKey = "Id") where T : class where TForeign : class
198         {
199             var type = typeof(T);
200             var tableName = GetTableName(typeof(T));
201             var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
202             var sbColumn = new StringBuilder(100);
203             var sbValue = new StringBuilder(200);
204             sbColumn.Append("INSERT INTO " + tableName + " (");
205             sbValue.Append(" VALUES (");
206             foreach (var propertyInfo in propertyInfos)
207             {
208                 //自增主键(默认int类型),过滤
209                 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue;
210                 sbColumn.Append($" {GetFieldName(propertyInfo)}");
211                 sbColumn.Append(" ,");
212                 //获取属性值
213                 if (propertyInfo.Name.Equals(foreignKey))
214                 {
215                     sbValue.Append(
216                         @$" (SELECT {ObjectTableNameConvention(foreignObjKey)} FROM {GetTableName(typeof(TForeign))} WHERE {ObjectTableNameConvention(constraint)}='{typeof(T).GetProperty(constraint)?.GetValue(targetObj)}'),");
217                     continue;
218                 }
219 
220                 var setSqlValueAttributes =
221                     propertyInfo.GetCustomAttributes(typeof(SetPostgreSqlValueAttribute), false);
222                 if (setSqlValueAttributes.Length > 0)
223                 {
224                     var setSqlValueAttribute = setSqlValueAttributes[0] as SetPostgreSqlValueAttribute;
225                     sbValue.Append($"{setSqlValueAttribute?.Value} ,");
226                 }
227                 else if (propertyInfo.GetValue(targetObj) == null)
228                 {
229                     sbValue.Append(" null,");
230                 }
231                 else
232                 {
233                     sbValue.Append(" '");
234                     sbValue.Append($"{propertyInfo.GetValue(targetObj)}");
235                     sbValue.Append("' ,");
236                 }
237             }
238             sbColumn.Replace(',', ')', sbColumn.Length - 1, 1);
239             sbValue.Replace(',', ')', sbValue.Length - 1, 1);
240             sbColumn.Append(sbValue).Append(';');
241             return sbColumn.ToString();
242         }
243         /// 

244         /// 生成UPDATE-INSERT SQL脚本
245         /// 
246         /// 目标对象类型
247         /// 目标对象
248         /// 目标对象主键名
249         /// 冲突
250         /// UPDATE-INSERT SQL脚本
251         public string UpSertSql(T targetObj, string targetObjKey = "Id", params string[] conflicts) where T : class
252         {
253             var type = typeof(T);
254             var tableName = GetTableName(typeof(T));
255             var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
256             var sbColumn = new StringBuilder(100);
257             var sbValue = new StringBuilder(200);
258             var sbConflict = new StringBuilder(100);
259             sbColumn.Append("INSERT INTO " + tableName + " (");
260             sbValue.Append(" VALUES (");
261             sbConflict.Append(" ON CONFLICT(");
262             foreach (var conflict in conflicts)
263             {
264                 if (!propertyInfos.Select(p => p.Name).Contains(conflict))
265                 {
266                     throw new ArgumentNullException($"{typeof(T).Name}不存在字段名{conflict}");
267                 }
268                 sbConflict.Append($" {ObjectTableNameConvention(conflict)},");
269             }
270             sbConflict.Replace(',', ')', sbConflict.Length - 1, 1).Append(" DO UPDATE SET");
271             foreach (var propertyInfo in propertyInfos)
272             {
273                 //自增主键(默认int类型),过滤
274                 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue;
275                 sbColumn.Append($" {GetFieldName(propertyInfo)}");
276                 sbColumn.Append(" ,");
277                 //获取属性值
278                 var value = string.Empty;
279                 var setSqlValueAttributes =
280                     propertyInfo.GetCustomAttributes(typeof(SetPostgreSqlValueAttribute), false);
281                 if (setSqlValueAttributes.Length > 0)
282                 {
283                     if (setSqlValueAttributes[0] is SetPostgreSqlValueAttribute setSqlValueAttribute)
284                     {
285                         value = $"{setSqlValueAttribute.Value} ,";
286                         sbValue.Append(value);
287                     }
288                 }
289                 else if (propertyInfo.GetValue(targetObj) == null)
290                 {
291                     value = " null,";
292                     sbValue.Append(value);
293                 }
294                 else
295                 {
296                     value = " '" + $"{propertyInfo.GetValue(targetObj)}" + "' ,";
297                     sbValue.Append(value);
298                 }
299                 sbConflict.Append($" {GetFieldName(propertyInfo)}={value}");
300             }
301             sbColumn.Replace(',', ')', sbColumn.Length - 1, 1);
302             sbValue.Replace(',', ')', sbValue.Length - 1, 1);
303             sbConflict.Remove(sbConflict.Length - 1, 1);
304             sbColumn.Append(sbValue).Append(sbConflict).Append(';');
305             return sbColumn.ToString();
306         }
307 
308         #region 内部方法
309 
310         /// 

311         /// 获取表名
312         /// 
313         /// 类型对象
314         /// 表名
315         protected string GetTableName(Type type)
316         {
317             var className = type.Name;
318             return string.IsNullOrWhiteSpace(this.Schema)
319                 ? ObjectTableNameConvention(className)
320                 : ObjectTableNameConvention(this.Schema) + "." + ObjectTableNameConvention(className);
321         }
322 
323         /// 

324         /// 获取表字段名
325         /// 
326         /// 对象属性信息
327         /// 表字段名
328         protected string GetFieldName(PropertyInfo propertyInfo)
329         {
330             return ObjectTableNameConvention(propertyInfo.Name);
331         }
332 
333         /// 

334         /// 对象-表 名称转换
335         /// 
336         /// 对象中名称
337         /// 表中名称
338         protected string ObjectTableNameConvention(string objectName)
339         {
340             var pattern =
341                 new Regex(@"[A-Z]{2,}(?=[A-Z][a-z]+[0-9]*|\b)|[A-Z]?[a-z]+[0-9]*|[A-Z]|[0-9]+");
342             var snakeCaseName = objectName == null
343                    ? null
344                    : string
345                        .Join("_", pattern.Matches(objectName).Cast().Select(m => m.Value))
346                        .ToLower();
347             return snakeCaseName;
348         }
349 
350         /// 

351         /// 是否基础类型(值类型，枚举，字符串)
352         /// 
353         /// 
354         /// 
355         protected bool IsFundamental(Type type)
356         {
357             return type.IsPrimitive || type.IsEnum || type == typeof(string) || type == typeof(DateTime);
358         } 
359 
360         #endregion
361 
362     }
363 }

　　测试下性能和结果：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 
 5 namespace PostgreSqlBuilder
 6 {
 7     [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = false)]
 8     public class SetPostgreSqlValueAttribute : Attribute
 9     {
10         private readonly SqlValueType _type;
11         private readonly string _value;
12 
13         public SetPostgreSqlValueAttribute(SqlValueType type, string value)
14         {
15             _type = type;
16             _value = value;
17         }
18         public string Value
19         {
20             get
21             {
22                 return _type switch
23                 {
24                     SqlValueType.Constant => "'" + _value + "'",
25                     SqlValueType.Function or SqlValueType.KeyWord => _value,
26                     _ => throw new NotSupportedException($"_type值错误{_type}"),
27                 };
28             }
29         }
30     }
31 }

　　结果：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 
 5 namespace PostgreSqlBuilder
 6 {
 7     public enum SqlValueType
 8     {
 9         /// 

10         /// slq常量
11         /// 
12         Constant = 0,
13         /// 

14         /// sql方法
15         /// 
16         Function = 1,
17         /// 

18         /// sql关键字
19         /// 
20         KeyWord = 2
21     }
22 }