缘由

原来公司有个实习生因为遇到大量数据入库,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

/// <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);

代码如下

拼接批量入库Sql

/// <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.


随意游世