Oracle的OracleBulkCopy不支持事务处理

  1 private void button2_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 (OracleConnection conn = new OracleConnection())//连接
 85 {
 86 conn.ConnectionString = @"Data Source=(DESCRIPTION =
 87 (ADDRESS_LIST =
 88 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))
 89 )
 90 (CONNECT_DATA =
 91 (SERVER = DEDICATED)
 92 (SERVICE_NAME = orcl.192.168.1.117)
 93 ))
 94 );User Id=scott;Password=abcd5226584;";
 95 conn.Open();
 96 using (OracleTransaction tx = conn.BeginTransaction())//启动事务
 97 using (OracleBulkCopy bulkCopy = new OracleBulkCopy(conn,OracleBulkCopyOptions.UseInternalTransaction))
 98 {
 99 bulkCopy.DestinationTableName = "T_MOBILE";
100 foreach (DataColumn column in table.Columns)
101 {
102 bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);//源列名->目标列名
103 }
104 try //如果发生异常就回滚
105 {
106 bulkCopy.WriteToServer(table);//把table表写入数据库表中
107 tx.Commit();
108 }
109 catch (Exception ex)
110 {
111 tx.Rollback();
112 throw new Exception(ex.Message);
113 }
114 }
115 sw.Stop();
116 MessageBox.Show("耗时：" + sw.ElapsedMilliseconds + "秒");
117 }
118 }
119 }