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 Program 10 { 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 try 40 { 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条数据消耗时间：
