C#用SqlBulkCopy批量导入数据库操作
下面是要批量导入如下格式的文本数据到数据库的演示：
手机归属地信息：
"号段" "所属地区" "号码类型" "区号" 1300000 "北京市" "联通" "010" 1300001 "江苏省常州市" "联通" "0519" 1300002 "安徽省巢湖市" "联通" "0565" 1300006 "江苏省南京市" "联通" "025" 1300008 "湖北省武汉市" "联通" "027" 1300010 "北京市" "联通" "010" 1300011 "北京市" "联通" "010" 1300012 "天津市" "联通" "022" 1300013 "天津市" "联通" "022" 1300014 "天津市" "联通" "022" 1300015 "山东省淄博市" "联通" "0533" 1300016 "山东省烟台市" "联通" "0535" 1300017 "山东省济南市" "联通" "0531"
普通的遍历数据的方式插入数据库：
1 private void InsertNumInfo(BackgroundWorker bk) 2 { 3 int SucNum = 0; 4 int Total = 0; 5 string connStr = ConfigurationManager.ConnectionStrings["DBMobileTest"].ConnectionString; 6 DateTime start = DateTime.Now; 7 8 using (SqlConnection conn = new SqlConnection(connStr)) 9 { 10 conn.Open(); 11 using (SqlCommand cmd = conn.CreateCommand()) 12 {
//第一种导入数据库的方式 13 //string[] numInfos = File.ReadLines(@"D:\New Folder\WinformGames\WPF项目\手机号码归属地\手机号段归属地数据库20120814.txt", Encoding.Default).ToArray<String>(); 14 //Total = numInfos.Count(); 15 //for (int i = 1; i < (Total); i++) 16 // { 17 // string curInfo = numInfos[i]; 18 // string[] infos = curInfo.Split('\t'); 19 // string StartTelNum = infos[0]; 20 // string TelType = infos[1].Trim('\"'); 21 // string TelArea = infos[2].Trim('\"'); 22 // string AreaNum = infos[3].Trim('\"'); 23 24 // cmd.CommandText = "insert into T_TelNumInfo(StartTelNum,TelType,TelArea,AreaNum) values('" + StartTelNum + "','" + TelType + "','" + TelArea + "','" + AreaNum + "')"; 25 // SucNum += cmd.ExecuteNonQuery(); 26 27 28 // bk.ReportProgress(SucNum,Total-1); 29 // //textBlock1.Text = "(" + SucNum + "," + Total + ")"; 30 // } 31 //第二种导入数据库的方式 32 StreamReader sr = new StreamReader(@"D:\New Folder\WinformGames\WPF项目\手机号码归属地\手机号段归属地数据库20120814.txt",Encoding.Default); 33 string line=null; 34 while ((line=sr.ReadLine())!=null) 35 { 36 string[] infos = line.Split('\t'); 37 string StartTelNum = infos[0]; 38 string TelType = infos[1].Trim('\"'); 39 string TelArea = infos[2].Trim('\"'); 40 string AreaNum = infos[3].Trim('\"'); 41 cmd.CommandText = "insert into T_TelNumInfo(StartTelNum,TelType,TelArea,AreaNum) values('" + StartTelNum + "','" + TelType + "','" + TelArea + "','" + AreaNum + "')"; 42 SucNum += cmd.ExecuteNonQuery(); 43 bk.ReportProgress(SucNum, Total - 1); 44 //textBlock1.Text = "(" + SucNum + "," + Total + ")"; 45 } 46 sr.Close(); 47 48 49 50 51 } 52 } 53 TimeSpan span=DateTime.Now-start; 54 MessageBox.Show("数据总条数：" + Total + ",执行成功：" + SucNum + ",总耗时：" + span); 55 }
下面是C#的内置的批量查询（SqlBulkCopy ）的一种方式：
1 void InsertBySQLbulk() 2 { 3 DateTime start = DateTime.Now; 4 DataTable dt = new DataTable(); 5 dt.Columns.Add("PID"); 6 dt.Columns.Add("PStartTelNum"); 7 dt.Columns.Add("PTelType"); 8 dt.Columns.Add("PTelArea"); 9 dt.Columns.Add("PAreaNum"); 10 StreamReader sr = new StreamReader(@"D:\New Folder\WinformGames\WPF项目\手机号码归属地\手机号段归属地数据库20120814.txt", Encoding.Default); 11 string line=null; 12 13 while ((line=sr.ReadLine())!=null) 14 { 15 string[] infos = line.Split('\t'); 16 string PStartTelNum = infos[0]; 17 string PTelType = infos[1].Trim('\"'); 18 string PTelArea = infos[2].Trim('\"'); 19 string PAreaNum = infos[3].Trim('\"'); 20 DataRow row= dt.NewRow(); 21 row["PStartTelNum"] = PStartTelNum; 22 row["PTelType"] = PTelType; 23 row["PTelArea"] = PTelArea; 24 row["PAreaNum"] = PAreaNum; 25 dt.Rows.Add(row); 26 } 27 int count = dt.Rows.Count; 28 sr.Close(); 29 30 using(SqlBulkCopy bulCopy=new SqlBulkCopy( ConfigurationManager.ConnectionStrings["DBMobileTest"].ConnectionString)){ 31 bulCopy.DestinationTableName = "T_TelNumInfo"; 32 bulCopy.ColumnMappings.Add("PStartTelNum", "StartTelNum"); 33 bulCopy.ColumnMappings.Add("PTelType", "TelType"); 34 bulCopy.ColumnMappings.Add("PTelArea", "TelArea"); 35 bulCopy.ColumnMappings.Add("PAreaNum", "AreaNum"); 36 bulCopy.WriteToServer(dt); 37 } 38 39 TimeSpan span = DateTime.Now - start; 40 MessageBox.Show("耗时："+span.TotalSeconds); 41 }
测评：我用机房的破电脑测试发现，用第一种普通的查询方式耗时40+分钟，用第二种批量查询的方式，耗时只需要13s+,由此可见其效率之高。不过
据说太大的数据可能会失败，这个问题还不清楚。
