.net 使用SqlBulkCopy极速插入数据到 SQL Server

刚进公司时，领导说物流水系统有一个问题：发送水票速度很慢，每发送一次就得等10多分钟，问我有没有解决方法，时间原因一直没去研究。

今天早上reader 上收到cnblogs的订阅里看到一个关于SQL语句快速插入的文章，提到SqlBulkCopy，感觉不错，按他的测试SqlBulkCopy要比普通插入快近30倍，

按这个来算，我们那个发水票的时间就会由 10分钟-->20秒，这可太神奇了。

于是乎，下demo，测试，改成自己一般使用的方法测试，NND，还真可以说是极速。

在此贴上我的Demo:UploadFiles//SqlBulkCopy.rar

view plaincopy to clipboardprint?
  1. using System;  
  2. using System.Diagnostics;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using Microsoft.ApplicationBlocks.Data;  
  6. using System.Text;  
  7.  
  8. namespace ConsoleAppInsertTest  
  9. {  
  10.     class Program  
  11.     {  
  12.         staticint count = 100000;           //插入的条数  
  13.         staticvoid Main(string[] args)  
  14.         {  
  15.             long runTime = 0;  
  16.  
  17.             SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");  
  18.             runTime = SqlBulkCopyInsert();  
  19.             Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", runTime, count));  
  20.  
  21.             SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");  
  22.             runTime = TransactionInsert2();  
  23.             Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --使用事务插入数据，分组执行", runTime, count));  
  24.  
  25.             SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");  
  26.             runTime = TransactionInsert1();  
  27.             Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --先存到string再一次性执行", runTime, count));  
  28.  
  29.             SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");  
  30.             runTime = TransactionInsert();  
  31.             Console.WriteLine(string.Format("       使用事务插入{1}条数据所用的时间是{0}毫秒  --在事务里一条一条执行", runTime, count));  
  32.  
  33.  
  34.             SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");  
  35.             runTime = CommonInsert1();  
  36.             Console.WriteLine(string.Format("       普通方式插入{1}条数据所用的时间是{0}毫秒 --保持SqlConnection", runTime, count));  
  37.  
  38.             SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");  
  39.             runTime = CommonInsert();  
  40.             Console.WriteLine(string.Format("       普通方式插入{1}条数据所用的时间是{0}毫秒  --一条一条插入", runTime, count));  
  41.  
  42.             //使用SqlBulkCopy插入100000条数据所用的时间是1301毫秒  
  43.             //       使用事务插入100000条数据所用的时间是5811毫秒  --使用事务插入数据，分组执行  
  44.             //       使用事务插入100000条数据所用的时间是23315毫秒  --先存到string再一次性执行  
  45.             //       使用事务插入100000条数据所用的时间是15756毫秒  --在事务里一条一条执行  
  46.             //       普通方式插入100000条数据所用的时间是50287毫秒 --保持SqlConnection  
  47.             //       普通方式插入100000条数据所用的时间是49693毫秒  --一条一条插入  
  48.  
  49.             //使用SqlBulkCopy插入100000条数据所用的时间是1257毫秒  
  50.             //       使用事务插入100000条数据所用的时间是5870毫秒  --使用事务插入数据，分组执行  
  51.             //       使用事务插入100000条数据所用的时间是25062毫秒  --先存到string再一次性执行  
  52.             //       使用事务插入100000条数据所用的时间是16943毫秒  --在事务里一条一条执行  
  53.             //       普通方式插入100000条数据所用的时间是55764毫秒 --保持SqlConnection  
  54.             //       普通方式插入100000条数据所用的时间是58620毫秒  --一条一条插入  
  55.  
  56.             //使用SqlBulkCopy插入100000条数据所用的时间是1409毫秒  
  57.             //       使用事务插入100000条数据所用的时间是6041毫秒  --使用事务插入数据，分组执行  
  58.             //       使用事务插入100000条数据所用的时间是23330毫秒  --先存到string再一次性执行  
  59.             //       使用事务插入100000条数据所用的时间是15313毫秒  --在事务里一条一条执行  
  60.             //       普通方式插入100000条数据所用的时间是50665毫秒 --保持SqlConnection  
  61.             //       普通方式插入100000条数据所用的时间是50775毫秒  --一条一条插入  
  62.  
  63.             //通过三次测试分析发现，SqlBulkCopy速度是最快的，其次是SQL语句组合分组后执行，然后是SQL事务一条一条执行，然后是其它的  
  64.             //速度比是1:5:15:50,推荐前两种方法  
  65.  
  66.             Console.ReadLine();  
  67.  
  68.         }  
  69.  
  70.         /// <summary>  
  71.         /// 使用普通插入数据  
  72.         /// </summary>  
  73.         /// <returns></returns>  
  74.         privatestaticlong CommonInsert()  
  75.         {  
  76.             Stopwatch stopwatch = new Stopwatch();  
  77.             stopwatch.Start();  
  78.             for (int i = 0; i < count; i++)  
  79.             {  
  80.                 SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");  
  81.             }  
  82.             stopwatch.Stop();  
  83.             return stopwatch.ElapsedMilliseconds;  
  84.         }  
  85.  
  86.  
  87.         /// <summary>  
  88.         /// 使用普通插入数据,保持SqlConnection  
  89.         /// </summary>  
  90.         /// <returns></returns>  
  91.         privatestaticlong CommonInsert1()  
  92.         {  
  93.             Stopwatch stopwatch = new Stopwatch();  
  94.             stopwatch.Start();  
  95.             SqlConnection sqlconn = new SqlConnection(SqlHelper.SqlConnection);  
  96.             for (int i = 0; i < count; i++)  
  97.             {  
  98.                 SqlHelper.ExecuteNonQuery(sqlconn, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");  
  99.             }  
  100.             stopwatch.Stop();  
  101.             return stopwatch.ElapsedMilliseconds;  
  102.         }  
  103.  
  104.  
  105.         /// <summary>  
  106.         /// 使用事务插入数据  
  107.         /// </summary>  
  108.         /// <returns></returns>  
  109.         privatestaticlong TransactionInsert()  
  110.         {  
  111.             Stopwatch stopwatch = new Stopwatch();  
  112.             stopwatch.Start();  
  113.  
  114.             using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))  
  115.             {  
  116.                 ConnNow.Open();  
  117.                 using (SqlTransaction trans = ConnNow.BeginTransaction())  
  118.                 {  
  119.                     try 
  120.                     {  
  121.                         for (int i = 0; i < count; i++)  
  122.                         {  
  123.                             SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");  
  124.                         }  
  125.                         trans.Commit();  
  126.                     }  
  127.                     catch (Exception ex)  
  128.                     {  
  129.                         trans.Rollback();  
  130.                     }  
  131.                 }  
  132.             }  
  133.  
  134.             stopwatch.Stop();  
  135.             return stopwatch.ElapsedMilliseconds;  
  136.         }  
  137.  
  138.         /// <summary>  
  139.         /// 使用事务插入数据,先存到string再一次性执行  
  140.         /// </summary>  
  141.         /// <returns></returns>  
  142.         privatestaticlong TransactionInsert1()  
  143.         {  
  144.             Stopwatch stopwatch = new Stopwatch();  
  145.             stopwatch.Start();  
  146.             StringBuilder sb = new StringBuilder();  
  147.             for (int i = 0; i < count; i++)  
  148.             {  
  149.                 sb.AppendFormat("insert into passport(PassportKey) values('{0}');", Guid.NewGuid());  
  150.             }  
  151.             using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))  
  152.             {  
  153.                 ConnNow.Open();  
  154.                 using (SqlTransaction trans = ConnNow.BeginTransaction())  
  155.                 {  
  156.                     try 
  157.                     {  
  158.                         SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());  
  159.                         for (int i = 0; i < count; i++)  
  160.                         {  
  161.                             SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");  
  162.                         }  
  163.                         trans.Commit();  
  164.                     }  
  165.                     catch (Exception ex)  
  166.                     {  
  167.                         trans.Rollback();  
  168.                     }  
  169.                 }  
  170.             }  
  171.  
  172.             stopwatch.Stop();  
  173.             return stopwatch.ElapsedMilliseconds;  
  174.         }  
  175.  
  176.         /// <summary>  
  177.         /// 使用事务插入数据，分组执行-- 感谢 闫道民 提供  
  178.         /// </summary>  
  179.         /// <returns></returns>  
  180.         privatestaticlong TransactionInsert2()  
  181.         {  
  182.             Stopwatch stopwatch = new Stopwatch();  
  183.             stopwatch.Start();  
  184.             using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))  
  185.             {  
  186.                 ConnNow.Open();  
  187.                 string tmp = string.Empty; ;  
  188.                 int itmp = 0;  
  189.                 using (SqlTransaction trans = ConnNow.BeginTransaction())  
  190.                 {  
  191.                     try 
  192.                     {  
  193.                         for (int i = 0; i < count; i++)  
  194.                         {  
  195.                             itmp++;  
  196.                             tmp = tmp + "insert into passport(PassportKey) values('" + Guid.NewGuid() + "');";  
  197.                             if (itmp == 180)  
  198.                             {  
  199.                                 itmp = 0;  
  200.                                 SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);  
  201.                                 tmp = string.Empty;  
  202.                             }  
  203.                         }  
  204.                         SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);  
  205.                         trans.Commit();  
  206.                     }  
  207.                     catch (Exception ex)  
  208.                     {  
  209.                         trans.Rollback();  
  210.                     }  
  211.                 }  
  212.             }  
  213.  
  214.             stopwatch.Stop();  
  215.             return stopwatch.ElapsedMilliseconds;  
  216.         }  
  217.  
  218.         /// <summary>  
  219.         /// 使用SqlBulkCopy方式插入数据  
  220.         /// </summary>  
  221.         /// <returns></returns>  
  222.         privatestaticlong SqlBulkCopyInsert()  
  223.         {  
  224.             Stopwatch stopwatch = new Stopwatch();  
  225.             stopwatch.Start();  
  226.  
  227.             DataTable dataTable = GetTableSchema();  
  228.             for (int i = 0; i < count; i++)  
  229.             {  
  230.                 DataRow dataRow = dataTable.NewRow();  
  231.                 dataRow[2] = Guid.NewGuid();  
  232.                 dataTable.Rows.Add(dataRow);  
  233.             }  
  234.  
  235.             //Console.WriteLine(stopwatch.ElapsedMilliseconds);//初始化数据时间  
  236.  
  237.             SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlHelper.SqlConnection);  
  238.             sqlBulkCopy.DestinationTableName = "Passport";  
  239.  
  240.             if (dataTable != null && dataTable.Rows.Count != 0)  
  241.             {  
  242.                 sqlBulkCopy.WriteToServer(dataTable);  
  243.             }  
  244.             sqlBulkCopy.Close();  
  245.  
  246.  
  247.             stopwatch.Stop();  
  248.             return stopwatch.ElapsedMilliseconds;  
  249.         }  
  250.  
  251.  
  252.         privatestatic DataTable GetTableSchema()  
  253.         {  
  254.             return SqlHelper.ExecuteDataset(SqlHelper.SqlConnection, CommandType.Text, "select * from Passport where 1=2").Tables[0];  
  255.         }  
  256.  
  257.     }  
  258. }  




 转自：http://www.yongfa365.com/Item/SqlBulkCopy.html



        

	      

	    

	  

	  

	    
      
        【推广】
        免费学中医，健康全家人
      
	  

    

    

          原文地址：https://www.cnblogs.com/cuihongyu3503319/p/2768665.html