OracleBulkCopy的批量数据导入
时间：2021-07-01 10:21:17
帮助过：2人阅读
private void button1_Click(
object sender, EventArgs e)
2 {
3 OpenFileDialog afd =
new OpenFileDialog();
4 if (afd.ShowDialog()!=
System.Windows.Forms.DialogResult.OK)
5 {
6 return;
7 }
8 string fileName =
afd.FileName;
9 if (Path.GetExtension(fileName)!=
".csv")
10 {
11 MessageBox.Show(
"文件名后缀必须是.csv");
12 return;
13 }
14 DataTable table =
new DataTable();
15 using(Stream stream=
new FileStream(fileName,FileMode.Open,FileAccess.Read))
16 using(StreamReader sr=
new StreamReader(stream,Encoding.Default))
17 {
18 string firstline =
sr.ReadLine();
19 string line;
20
21
22 #region 把创建的列加入表的列集合中
23 //Id
24 DataColumn column =
new DataColumn();
25 column.ColumnName =
"Id";
26 column.DataType = System.Type.GetType(
"System.Int32");
27 table.Columns.Add(column);
28 //MobileNumber
29 column =
new DataColumn();
30 column.ColumnName =
"MobileNumber";
31 column.DataType = System.Type.GetType(
"System.String");
32 column.MaxLength =
10;
33 table.Columns.Add(column);
34 //MobileArea
35 column =
new DataColumn();
36 column.ColumnName =
"MobileArea";
37 column.DataType = System.Type.GetType(
"System.String");
38 column.MaxLength =
30;
39 table.Columns.Add(column);
40 //MobileType
41 column =
new DataColumn();
42 column.ColumnName =
"MobileType";
43 column.DataType = System.Type.GetType(
"System.String");
44 column.MaxLength =
20;
45 table.Columns.Add(column);
46 //AreaCode
47 column =
new DataColumn();
48 column.ColumnName =
"AreaCode";
49 column.DataType = System.Type.GetType(
"System.String");
50 column.MaxLength =
10;
51 table.Columns.Add(column);
52 //PostCode
53 column =
new DataColumn();
54 column.ColumnName =
"PostCode";
55 column.DataType = System.Type.GetType(
"System.String");
56 column.MaxLength =
8;
57 table.Columns.Add(column);
58 #endregion
59
60 while ((line = sr.ReadLine())!=
null)
61 {
62 string[] mobileMsg = line.Split(
new char[] {
‘,‘ }, StringSplitOptions.RemoveEmptyEntries);
63 int Id = Convert.ToInt32(mobileMsg[
0]);
64 string MobileNumber = mobileMsg[
1].Trim(
‘"‘);
65 string MobileArea = mobileMsg[
2].Trim(
‘"‘);
66 string MobileType = mobileMsg[
3].Trim(
‘"‘);
67 string AreaCode = mobileMsg[
4].Trim(
‘"‘);
68 string PostCode = mobileMsg[
5].Trim(
‘"‘);
69 //创建行，把行加入表的行集合中
70 DataRow row =
table.NewRow();
71 row[
"Id"] =
Id;
72 row[
"MobileNumber"] =
MobileNumber;
73 row[
"MobileArea"] =
MobileArea;
74 row[
"MobileType"] =
MobileType;
75 row[
"AreaCode"] =
AreaCode;
76 row[
"PostCode"] =
PostCode;
77 table.Rows.Add(row);
78 }
79 }
80
81 int i =
table.Rows.Count;
82 Stopwatch sw =
new Stopwatch();
83 sw.Start();
84 using (OracleBulkCopy bulkCopy =
new OracleBulkCopy(OracleHelper.CreateConnection()))
85 {
86 bulkCopy.DestinationTableName =
"T_MOBILE";
87 foreach (DataColumn column
in table.Columns)
88 {
89 bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
//源列名->目标列名
90 }
91 try //如果发生异常就回滚
92 {
93 bulkCopy.WriteToServer(table);
//把table表写入数据库表中
94 // tx.Commit();
95 }
96 catch (Exception ex)
97 {
98 // tx.Rollback();
99 throw new Exception(ex.Message);
100 }
101 }
102 sw.Stop();
103 MessageBox.Show(
"耗时：" + sw.ElapsedMilliseconds +
"秒");
104 // }
105 }
OracleBulkCopy的批量数据导入
标签：
