• 批量插入SQL(适用于数据量十万级以上的快速大量插入)


     1 static public class LinqToDataTable
     2     {
     3         static public DataTable ToDataTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
     4         {
     5             DataTable dtReturn = new DataTable();
     6             PropertyInfo[] oProps = null;
     7             foreach (T rec in varlist)
     8             {
     9                 // 使用反射获取属性名,创建表,只有第一次,其他的才会跟随
    10                 if (oProps == null)
    11                 {
    12 
    13                     oProps = ((Type)rec.GetType()).GetProperties();
    14 
    15                     foreach (PropertyInfo pi in oProps)
    16                     {
    17 
    18                         Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
    19                         {
    20 
    21                             colType = colType.GetGenericArguments()[0];
    22 
    23                         }
    24 
    25                         dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
    26 
    27                     }
    28 
    29                 }
    30 
    31                 DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
    32                 {
    33 
    34                     dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
    35 
    36                 }
    37 
    38                 dtReturn.Rows.Add(dr);
    39 
    40             }
    41 
    42             return (dtReturn);
    43 
    44         }
    45 
    46         public delegate object[] CreateRowDelegate<T>(T t);
    47     }
    1         public static void bulkinsert<T>(string conn, string tbname, IEnumerable<T> query)
    2         {
    3             DateTime dtnow = DateTime.Now;
    4             SQLHelper sqlhelper = new SQLHelper();
    5             DataTable dt = query.ToDataTable<T>(rec => new object[] { query });
    6 
    7             sqlhelper.ExecuteSqlBulkCopy(conn, tbname, dt);
    8             TraceLog.PrintLn("{1}正在结束插入数据耗时{0}ms,总共{2}条", (DateTime.Now - dtnow).TotalMilliseconds, tbname,dt.Rows.Count);
    9         }
     1         /// <summary>
     2         /// 批量插入
     3         /// </summary>
     4         /// <param name="lstdata"></param>
     5         public void InsertMBT(List<inf_mobileDDVerificationALLDMBT> lstdata)
     6         {
     7             try
     8             {
     9                 SqlBulkCopyHelper.bulkinsert<inf_mobileDDVerificationALLDMBT>(Properties.Settings.Default.smsMarketdataConnectionString, "inf_mobileDDVerificationALLDMBT", lstdata);
    10                 lstdata.Clear();
    11             }
    12             catch (Exception err)
    13             {
    14 
    15             }
    16             finally
    17             {
    18                 lstdata.Clear();
    19             }
    20         }
    限定目的,能使人生变得简洁。
  • 相关阅读:
    每日总结
    每日总结
    每日总结
    每日总结
    每日总结
    每日总结
    每日博客
    每日博客
    结对作业之全国疫情统计可视化地图
    每日博客
  • 原文地址:https://www.cnblogs.com/lx07/p/9254047.html
Copyright © 2020-2023  润新知