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
