新增/更新的sql语句,在导入到旧库中,所以简单做了一个符合当时场景的动态生成SQL语句的实现。
主要考虑到问题:
无自增外键生成INSERT SQL脚本和UPDATE-INSERT SQL脚本的实现
有一个自增外键生成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
特性来实现(上面的代码是已经改造后的)。特性定义如下:
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 }
View Code
测试后果: