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

　　遇到一个场景，新旧系统使用维护类似的表结构和规范，需要将新的数据迁移到旧的系统中，当时采取的方案就是在新系统开发，将数据生成新增/更新的sql语句，在导入到旧库中，所以简单做了一个符合当时场景的动态生成SQL语句的实现。

　　主要考虑到问题：

　　1.解决无自增外键生成INSERT SQL脚本和UPDATE-INSERT SQL脚本的实现

　　2.解决有一个自增外键生成INSERT SQL脚本和UPDATE-INSERT 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         /// <summary>
 15         /// 模式
 16         /// </summary>
 17         public string Schema { get; set; }
 18         
 19         public PostgreSqlBuilder() { }
 20         public PostgreSqlBuilder(string schema)
 21         {
 22             this.Schema = schema;
 23         }
 24 
 25         /// <summary>
 26         /// 批量生成INSERT SQL脚本-无自增外键
 27         /// </summary>
 28         /// <typeparam name="T">目标对象类型</typeparam>
 29         /// <param name="collection">目标对象集合</param>
 30         /// <param name="key">目标对象主键名</param>
 31         /// <returns></returns>
 32         public string BatchInsertSql<T>(IEnumerable<T> 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         /// <summary>
 42         /// 批量生成INSERT SQL脚本-有一个自增外键；
 43         /// 需先生成主键表sql脚本插入成功后，再使用该方法；
 44         /// 要求参数constraint为主外键表共有字段；
 45         /// </summary>
 46         /// <typeparam name="T">目标对象类型</typeparam>
 47         /// <typeparam name="TOuter">外键关联对象类型</typeparam>
 48         /// <param name="collection">目标对象集合</param>
 49         /// <param name="outKey">目标对象外键名</param>
 50         /// <param name="constraint">外键关联对象唯一约束属性名(非主键)</param>
 51         /// <returns>INSERT SQL脚本</returns>
 52         public string BatchInsertSqlWithOuterKeyId<T,TOuter>(IEnumerable<T> 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<T,TOuter>(obj, outKey, constraint));
 59             }
 60             return sbBatch.ToString();
 61         }
 62         /// <summary>
 63         /// 批量生成UPDATE-INSERT SQL脚本
 64         /// </summary>
 65         /// <typeparam name="T">目标对象类型</typeparam>
 66         /// <param name="collection">目标对象集合</param>
 67         /// <param name="key">目标对象主键名</param>
 68         /// <param name="conflicts">冲突</param>
 69         /// <returns></returns>
 70         public string BatchUpSertSql<T>(IEnumerable<T> 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         /// <summary>
 83         /// 生成INSERT SQL脚本-无自增外键
 84         /// </summary>
 85         /// <typeparam name="T">目标对象类型</typeparam>
 86         /// <param name="targetObj">目标对象</param>
 87         /// <param name="targetObjKey">目标对象主键名</param>
 88         /// <returns>INSERT SQL脚本</returns>
 89         public string InsertSqlNoIncForeignKey<T>(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         /// <summary>
130         /// 生成INSERT SQL脚本-无自增外键
131         /// </summary>
132         /// <typeparam name="T">目标对象类型</typeparam>
133         /// <param name="targetObj">目标对象</param>
134         /// <param name="targetObjKeyExpr">目标对象属性表达式-主键</param>
135         /// <returns>INSERT SQL脚本</returns>
136         public string InsertSqlNoIncForeignKey<T, TK>(T targetObj, Expression<Func<T, TK>> 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         /// <summary>
185         /// 生成INSERT SQL脚本-有一个自增外键；
186         /// 需先生成主键表sql脚本插入成功后，再使用该方法；
187         /// 要求参数constraint为主外键表共有字段；
188         /// </summary>
189         /// <typeparam name="T">目标对象类型</typeparam>
190         /// <typeparam name="TForeign">外键关联对象类型</typeparam>
191         /// <param name="targetObj">目标对象</param>
192         /// <param name="foreignKey">目标对象外键名</param>
193         /// <param name="constraint">外键关联对象唯一约束属性名(非主键)</param>
194         /// <param name="targetObjKey">目标对象主键名</param>
195         /// <param name="foreignObjKey">外键关联对象主键名</param>
196         /// <returns>INSERT SQL脚本</returns>
197         public string InsertSqlWithOuterKeyId<T, TForeign>(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                 .Where(p => IsFundamental(p.PropertyType));
203             var sbColumn = new StringBuilder(100);
204             var sbValue = new StringBuilder(200);
205             sbColumn.Append("INSERT INTO " + tableName + " (");
206             sbValue.Append(" VALUES (");
207             foreach (var propertyInfo in propertyInfos)
208             {
209                 //自增主键(默认int类型),过滤
210                 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue;
211                 sbColumn.Append($" {GetFieldName(propertyInfo)}");
212                 sbColumn.Append(" ,");
213                 //获取属性值
214                 if (propertyInfo.Name.Equals(foreignKey))
215                 {
216                     sbValue.Append(
217                         @$" (SELECT {ObjectTableNameConvention(foreignObjKey)} FROM {GetTableName(typeof(TForeign))} WHERE {ObjectTableNameConvention(constraint)}='{typeof(T).GetProperty(constraint)?.GetValue(targetObj)}'),");
218                     continue;
219                 }
220 
221                 var setSqlValueAttributes =
222                     propertyInfo.GetCustomAttributes(typeof(SetPostgreSqlValueAttribute), false);
223                 if (setSqlValueAttributes.Length > 0)
224                 {
225                     var setSqlValueAttribute = setSqlValueAttributes[0] as SetPostgreSqlValueAttribute;
226                     sbValue.Append($"{setSqlValueAttribute?.Value} ,");
227                 }
228                 else if (propertyInfo.GetValue(targetObj) == null)
229                 {
230                     sbValue.Append(" null,");
231                 }
232                 else
233                 {
234                     sbValue.Append(" '");
235                     sbValue.Append($"{propertyInfo.GetValue(targetObj)}");
236                     sbValue.Append("' ,");
237                 }
238             }
239             sbColumn.Replace(',', ')', sbColumn.Length - 1, 1);
240             sbValue.Replace(',', ')', sbValue.Length - 1, 1);
241             sbColumn.Append(sbValue).Append(';');
242             return sbColumn.ToString();
243         }
244         /// <summary>
245         /// 生成UPDATE-INSERT SQL脚本
246         /// </summary>
247         /// <typeparam name="T">目标对象类型</typeparam>
248         /// <param name="targetObj">目标对象</param>
249         /// <param name="targetObjKey">目标对象主键名</param>
250         /// <param name="conflicts">冲突</param>
251         /// <returns>UPDATE-INSERT SQL脚本</returns>
252         public string UpSertSql<T>(T targetObj, string targetObjKey = "Id", params string[] conflicts) where T : class
253         {
254             var type = typeof(T);
255             var tableName = GetTableName(typeof(T));
256             var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
257                 .Where(p => IsFundamental(p.PropertyType));
258             var sbColumn = new StringBuilder(100);
259             var sbValue = new StringBuilder(200);
260             var sbConflict = new StringBuilder(100);
261             sbColumn.Append("INSERT INTO " + tableName + " (");
262             sbValue.Append(" VALUES (");
263             sbConflict.Append(" ON CONFLICT(");
264             foreach (var conflict in conflicts)
265             {
266                 if (!propertyInfos.Select(p => p.Name).Contains(conflict))
267                 {
268                     throw new ArgumentNullException($"{typeof(T).Name}不存在字段名{conflict}");
269                 }
270                 sbConflict.Append($" {ObjectTableNameConvention(conflict)},");
271             }
272             sbConflict.Replace(',', ')', sbConflict.Length - 1, 1).Append(" DO UPDATE SET");
273             foreach (var propertyInfo in propertyInfos)
274             {
275                 //自增主键(默认int类型),过滤
276                 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue;
277                 sbColumn.Append($" {GetFieldName(propertyInfo)}");
278                 sbColumn.Append(" ,");
279                 //获取属性值
280                 var value = string.Empty;
281                 var setSqlValueAttributes =
282                     propertyInfo.GetCustomAttributes(typeof(SetPostgreSqlValueAttribute), false);
283                 if (setSqlValueAttributes.Length > 0)
284                 {
285                     if (setSqlValueAttributes[0] is SetPostgreSqlValueAttribute setSqlValueAttribute)
286                     {
287                         value = $"{setSqlValueAttribute.Value} ,";
288                         sbValue.Append(value);
289                     }
290                 }
291                 else if (propertyInfo.GetValue(targetObj) == null)
292                 {
293                     value = " null,";
294                     sbValue.Append(value);
295                 }
296                 else
297                 {
298                     value = " '" + $"{propertyInfo.GetValue(targetObj)}" + "' ,";
299                     sbValue.Append(value);
300                 }
301                 sbConflict.Append($" {GetFieldName(propertyInfo)}={value}");
302             }
303             sbColumn.Replace(',', ')', sbColumn.Length - 1, 1);
304             sbValue.Replace(',', ')', sbValue.Length - 1, 1);
305             sbConflict.Remove(sbConflict.Length - 1, 1);
306             sbColumn.Append(sbValue).Append(sbConflict).Append(';');
307             return sbColumn.ToString();
308         }
309 
310         #region 内部方法
311 
312         /// <summary>
313         /// 获取表名
314         /// </summary>
315         /// <param name="type">类型对象</param>
316         /// <returns>表名</returns>
317         protected string GetTableName(Type type)
318         {
319             var className = type.Name;
320             return string.IsNullOrWhiteSpace(this.Schema)
321                 ? ObjectTableNameConvention(className)
322                 : ObjectTableNameConvention(this.Schema) + "." + ObjectTableNameConvention(className);
323         }
324 
325         /// <summary>
326         /// 获取表字段名
327         /// </summary>
328         /// <param name="propertyInfo">对象属性信息</param>
329         /// <returns>表字段名</returns>
330         protected string GetFieldName(PropertyInfo propertyInfo)
331         {
332             return ObjectTableNameConvention(propertyInfo.Name);
333         }
334 
335         /// <summary>
336         /// 对象-表 名称转换
337         /// </summary>
338         /// <param name="objectName">对象中名称</param>
339         /// <returns>表中名称</returns>
340         protected string ObjectTableNameConvention(string objectName)
341         {
342             var pattern =
343                 new Regex(@"[A-Z]{2,}(?=[A-Z][a-z]+[0-9]*|\b)|[A-Z]?[a-z]+[0-9]*|[A-Z]|[0-9]+");
344             var snakeCaseName = objectName == null
345                    ? null
346                    : string
347                        .Join("_", pattern.Matches(objectName).Cast<Match>().Select(m => m.Value))
348                        .ToLower();
349             return snakeCaseName;
350         }
351 
352         /// <summary>
353         /// 是否基础类型(值类型，枚举，字符串及可空类型)
354         /// </summary>
355         /// <param name="type"></param>
356         /// <returns></returns>
357         protected bool IsFundamental(Type type)
358         {
359             return type.IsPrimitive || type.IsEnum || type == typeof(string) || type == typeof(DateTime) ||
360                    (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>));
361         } 
362 
363         #endregion
364 
365     }
366 }

View Code

　　基本功能已经实现，后面会又碰到一种新问题，就是存在生成空字段默认值的问题：即当时生成sql是全属性生成，空值为null而不是空值就直接过滤不参加生成sql语句。为了达到这一目的，改造的方法就是在反射的时候获取具有默认值的字段，能在元数据加点标记用特性来实现（上面的代码是已经改造后的）。特性定义如下：

 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 }

View Code

　　定义的枚举：　

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 
 5 namespace PostgreSqlBuilder
 6 {
 7     public enum SqlValueType
 8     {
 9         /// <summary>
10         /// slq常量
11         /// </summary>
12         Constant = 0,
13         /// <summary>
14         /// sql方法
15         /// </summary>
16         Function = 1,
17         /// <summary>
18         /// sql关键字
19         /// </summary>
20         KeyWord = 2
21     }
22 }

View Code

以上就是完整地代码实现，测试一下（实例用的是新增无自增外键sql语句生成）：

 1 using PostgreSqlBuilder;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Linq;
 5 using System.Text;
 6 using System.Threading.Tasks;
 7 
 8 namespace Dtos
 9 {
10     public class Person
11     {
12         public int Id { get; set; }
13         public string Name { get; set; }
14         public int? Age { get; set; }
15 
16         [SetPostgreSqlValue(SqlValueType.Function,"now()")]
17         public DateTime? DateTime { get; set; }
18 
19         public List<Pet> Pets { get; set; } = new List<Pet>();
20 
21         public override string ToString()
22         {
23             return $"name:{Name},age:{Age},dateTime:{DateTime},pets:{string.Join(';',Pets)}";
24         }
25     }
26 }

View Code

 1 using Dtos;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Linq;
 5 
 6 namespace PostgreSqlBuilder.ConsoleTest
 7 
 8 {
 9     class Program
10     {
11         static void Main(string[] args)
12         {
13 
14             Person person = new (){ Age = 0, Name = "张三"};
15            
16             PostgreSqlBuilder sqlHelper = new ("scale");
17             var sql1 = sqlHelper.InsertSqlNoIncForeignKey(person, p => p.Id);
18             
19             Console.WriteLine(sql1);
20             Console.ReadKey();
21         }
22     }
23 }