缘由
原来公司有个实习生因为遇到大量数据入库，ORM对其没有比较好得优化支持，他就实现了一套用 += 组装原生sql入库，每条数据都有一个INSERT INTO
实习生嘛,也就算了,指正下就好,而用 += 就必须要改了,要换成StringBuilder 这个就另一个问题了
后来我也遇到需要批量的原生SQL了,而他那个方法写得过于死,参数都直接写到String里面了,不能用到其他表或地方
所以我自己写了一套传入 List 和指定字段的方式生产批量插入SQL
在我到了另一家公司又需要到了这个函数,因此记录一下
主要思路：
1.传入List，业务不需要关系字段如何赋值
2.通过Expression指定列
3.表名肯定是要显式指定，有些项目数据库实体并没有基础Class继承，无法指定约束，如果你那边有，可以使用基础类对其进行约束，表名直接取类名称即可
4.以免有部分字段参数全部都一致，或不能为null而用来赋默认值
使用例子
var sql= SqlHelper.GetBatchInsertSqlStr(list, x => new { x.Id,x.Name}, "tabelName");
拼接Sql代码如下
拼接批量入库Sql
/// <summary>
/// 拼接批量入库Sql。
/// 例子:(list, x => new{ x.Name}, "table")
/// 性能测试：List非for入库5s，原生sql入库50ms，数据库特殊类型需要调整该函数
/// 额外：关于Append使用char对性能有一定影响，未进行详细测试
/// </summary>
/// <typeparam name="T">数据List</typeparam>
/// <typeparam name="TR">拼接临时变量类型</typeparam>
/// <param name="entitys">List</param>
/// <param name="expressions">临时变量</param>
/// <param name="tableName">表明</param>
/// <param name="extends">额外参数 可空</param>
/// <returns></returns>
public static string GetBatchInsertSqlStr<T, TR>(List<T> entitys, Expression<Func<T, TR>> expressions, string tableName, Dictionary<string, string> extends = null) where T : class
{
if (entitys.Count <= 0)
throw new Exception("传入数据源数量为0！");
var sb = new StringBuilder();
var inputType = typeof(T);
var expres = expressions.Body.Type;
//需要传入new {x.Name}的匿名类型格式才有字段内容。否则使用Expression[]实现功能
// 改为使用方法约束
//if (expres.IsClass == false)
// throw new Exception("传入参数不是Class！");
var extendKeys = new string[0];
if (extends != null)
extendKeys = extends.Keys.ToArray();
var properties = expres.GetProperties();
//sql表字段拼接
sb.Append("INSERT INTO ");
sb.Append(tableName);
sb.Append('(');
foreach (var propertie in properties)
{
sb.Append("[" + propertie.Name + "]");//mysql用`
sb.Append(',');
}
if (extendKeys.Length > 0)
foreach (var key in extendKeys)
{
sb.Append(key);
sb.Append(',');
}
sb.Remove(sb.Length - 1, 1);
sb.Append(')');
sb.Append(" VALUES ");
//sql字段值拼接
try
{
foreach (var item in entitys)
{
sb.Append('(');
foreach (var propertie in properties)
{
var type = inputType.GetProperty(propertie.Name).GetValue(item).GetType();
var val = inputType.GetProperty(propertie.Name).GetValue(item);
//sql server目前只发现这个字符有转义问题
if (val.ToString().Contains("'"))
val = (object)val.ToString().Replace("'", "''");
//mysql
//if (val.ToString().Contains(@"\"))
// val = (object)val.ToString().Replace(@"\", @"\\");
if (type.FullName == "System.Boolean")//原生mysql入库布尔为b'0'格式，无法使用int格式入库。后续特殊类型需要在此处添加类型处理
{
if (bool.Parse(val.ToString()))
sb.Append("\'1\'");//mysql sb.Append("b\'1\'");
else
sb.Append("\'0\'");//mysql sb.Append("b\'0\'");
}
else
{
sb.Append('\'');
sb.Append(val);
sb.Append('\'');
}
sb.Append(',');
}
if (extendKeys.Length > 0)
foreach (var key in extendKeys)
{
sb.Append('\'');
sb.Append(extends[key]);
sb.Append('\'');
sb.Append(',');
}
sb.Remove(sb.Length - 1, 1);
sb.Append(')');
sb.Append(',');
}
sb.Remove(sb.Length - 1, 1);
}
catch (Exception e)//获取值因名称由外部引用点出，一般都不会报错，以防万一用于定位
{
throw new Exception($"组装Sql异常！原因：{e.Message}");
}
return sb.ToString();
}
Sql Server使用 SqlBulkCopy 批量入库
使用方式：
SqlHelper.BatchInsert(30000, list, x => new
{
x.Price,
x.GoodsId,
}, nameof(Table), conn, transaction);
代码如下
SqlBulkCopy 批量入库
/// <summary>
/// 批量入库(没有执行数量返回)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TR"></typeparam>
/// <param name="pageSize"></param>
/// <param name="entitys"></param>
/// <param name="expressions"></param>
/// <param name="isqlCnt"></param>
/// <param name="tran"></param>
public static void BatchInsert<T, TR>(int pageSize, IEnumerable<T> entitys, Expression<Func<T, TR>> expressions, string tableName, IDbConnection isqlCnt, IDbTransaction tran)
{
foreach (var item in GetListPage(pageSize, entitys))
AddForDataTable(ToDataTable(item, expressions), tableName, isqlCnt, tran);
}
/// <summary>
/// 将DateTable入库
/// </summary>
/// <param name="dataTable"></param>
/// <param name="tableName"></param>
/// <param name="isqlCnt"></param>
/// <param name="tran"></param>
private static void AddForDataTable(DataTable dataTable, string tableName, IDbConnection isqlCnt, IDbTransaction tran)
{
var sqlCnt = (SqlConnection)isqlCnt;
var externalTransaction = (SqlTransaction)tran
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlCnt, SqlBulkCopyOptions.KeepIdentity, externalTransaction: externalTransaction))
{
bulkCopy.DestinationTableName = tableName;//要插入的表的表名
bulkCopy.BatchSize = dataTable.Rows.Count
foreach (DataColumn item in dataTable.Columns)
if (item.ColumnName.ToLower() != "id")
bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName)
bulkCopy.WriteToServer(dataTable);
}
}
/// <summary>
/// 检查是否可空
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
private static bool IsNullable(Type t) => !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
/// <summary>
/// 获取类型
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
private static Type GetCoreType(Type t)
{
if (t != null && IsNullable(t))
{
if (!t.IsValueType)
return t;
else
return Nullable.GetUnderlyingType(t);
}
else
{
return t;
}
}
/// <summary>
/// List转DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TR"></typeparam>
/// <param name="items"></param>
/// <param name="expressions"></param>
/// <returns></returns>
public static DataTable ToDataTable<T, TR>(IEnumerable<T> items, Expression<Func<T, TR>> expressions)
{
var tb = new DataTable(typeof(T).Name)
var props = typeof(TR).GetProperties(BindingFlags.Public | BindingFlags.Instance);
var inputType = typeof(T)
foreach (var prop in props)
{
var ColumnName = prop.Name
var inputProp = inputType.GetProperty(prop.Name);
var inputPropAttr = inputProp.CustomAttributes.FirstOrDefault(x => x.AttributeType.Name == "ColumnAttribute");
var attrArgs = inputPropAttr?.NamedArguments ?? new List<CustomAttributeNamedArgument>();
if (attrArgs.Any(x => x.MemberName == "ColumnName"))
ColumnName = attrArgs.FirstOrDefault(x => x.MemberName == "ColumnName").TypedValue.Value.ToString()
tb.Columns.Add(ColumnName, GetCoreType(prop.PropertyType));
}
foreach (T item in items)
{
var values = new object[props.Length]
for (int i = 0; i < props.Length; i++)
values[i] = inputType.GetProperty(props[i].Name).GetValue(item)
tb.Rows.Add(values);
}
return tb;
}
Q.E.D.