----------- Windows Phone 7手机开发、.Net培训、期待与您交流! ------------
1 //使用SqlBulkCopy大批量导入数据 2 private void btnBulk_Click(object sender, RoutedEventArgs e) 3 { 4 string connStr = ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString; 5 OpenFileDialog ofd = new OpenFileDialog(); 6 ofd.Filter = "文本文件|*.txt"; 7 if (ofd.ShowDialog() == false) 8 { 9 return; 10 } 11 string[] lines = 12 File.ReadLines(ofd.FileName, Encoding.Default).ToArray(); 13 //构造一个Datatable存储将要批量导入的数据 14 DataTable table = new DataTable(); 15 table.Columns.Add("StartTelNum"); 16 table.Columns.Add("City"); 17 table.Columns.Add("TelType"); 18 for (int i = 1; i < lines.Count(); i++) 19 { 20 string line = lines; 21 string[] strs = line.Split('\t');//去掉两边的双引号:"北京市" 22 string startTelNum = strs[0]; 23 string city = strs[1]; 24 city = city.Trim('"'); 25 string telType = strs[2]; 26 telType = telType.Trim('"'); 27 DataRow dr = table.NewRow(); 28 dr["StartTelNum"] = startTelNum; 29 dr["City"] = city; 30 dr["TelType"] = telType; 31 table.Rows.Add(dr); 32 } 33 //实际的开发可能会遇到数据大批量插入数据的问题,若是一条条的循环导入效率非常低下,使用SqlBulkCopy大批量导入数据。 34 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr)) 35 { 36 //设置要批量写入的表 37 bulkCopy.DestinationTableName = "T_TelNum"; 38 //自定义的datatable和数据库的字段进行对应 39 bulkCopy.ColumnMappings.Add("StartTelNum","StartTelNum"); 40 bulkCopy.ColumnMappings.Add("City", "TelArea"); 41 bulkCopy.ColumnMappings.Add("TelType", "TelType"); 42 //自定义的datatable和数据库的字段进行对应 43 bulkCopy.WriteToServer(table); 44 } 45 } 46 47 //一条一条的循环导入数据 48 private void btnImport_Click(object sender, RoutedEventArgs e) 49 { 50 OpenFileDialog ofd = new OpenFileDialog(); 51 ofd.Filter = "文本文件|*.txt"; 52 if (ofd.ShowDialog() == false) 53 { 54 return; 55 } 56 string[] lines = 57 File.ReadLines(ofd.FileName, Encoding.Default).ToArray(); 58 //跳过第一行表头 59 for (int i = 1; i < lines.Count(); i++) 60 { 61 string line = lines[i]; 62 string[] strs = line.Split('\t'); 63 string startTelNum = strs[0]; 64 string city = strs[1]; 65 city = city.Trim('"'); 66 string telType = strs[2]; 67 telType = telType.Trim('"'); 68 69 SqlHelper.ExecuteNonQuery("insert into T_TelNum(StartTelNum,TelType,TelArea) values(@StartTelNum,@TelType,@TelArea)", 70 new SqlParameter("@StartTelNum", startTelNum), 71 new SqlParameter("@TelType", telType), 72 new SqlParameter("@TelArea", city)); 73 } 74 MessageBox.Show("导入成功"); 75 } 76