使用SqlBulkCopy類批量複製大資料

標籤：

 1 using System; 2 using System.Configuration; 3 using System.Data; 4 using System.Data.SqlClient; 5 using System.Diagnostics; 6  7 namespace SqlBulkCopy1 8 { 9     class Program10     {11         static void Main(string[] args)12         {13             Stopwatch sw = new Stopwatch();14             for (int multiply = 0; multiply < 10; multiply++)15             {16                 DataTable dt = GetTableSchema();17                 for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)18                 {19                     DataRow r = dt.NewRow();20                     r[0] = count;21                     r[1] = string.Format("username-{0}", count * multiply);22                     r[2] = string.Format("userpwd-{0}", count * multiply);23                     r[3] = string.Format("address-{0}", count * multiply);24                     dt.Rows.Add(r);25                 }26                 sw.Start();27                 BulkToDB(dt);28                 sw.Stop();29                 Console.WriteLine(string.Format("已消耗時間{0}毫秒", sw.ElapsedMilliseconds)); 30             }31             Console.ReadLine(); 32         }33         public static void BulkToDB(DataTable dt)34         { 35             SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString);36             SqlBulkCopy bulkCopy = new SqlBulkCopy(con);37             bulkCopy.DestinationTableName = "USERINFO";38             bulkCopy.BatchSize = dt.Rows.Count;39             try40             {41                 con.Open();42                 if (dt != null && dt.Rows.Count != 0)43                     bulkCopy.WriteToServer(dt);44             }45             catch (Exception)46             {47                 48                 throw;49             }50         }51 52         public static DataTable GetTableSchema()53         {54             DataTable dt = new DataTable();55             dt.Columns.AddRange(new DataColumn[] { 56             new DataColumn("id",typeof(int)),57             new DataColumn("username",typeof(string)),58             new DataColumn("userpwd",typeof(string)),59             new DataColumn("address",typeof(string))60             });61             return dt;62         }63     }64 }

100W條資料消耗時間：

使用SqlBulkCopy類批量複製大資料