Oracle的OracleBulkCopy不支援交易處理

標籤：

在進行OracleBulkCopy批量資料匯入的過程中使用事務後拋出了異常，

沒使用事務時可以正確大量匯入，

ORA-12154：無法解析指定的連接字串，

但是TNS配置肯定是沒有錯的，

難道是ConnectionString不是這樣寫了，執行事務時發生了變化，

還有：

//暫時發現oracle的bulkCopy不支援交易處理

using (OracleBulkCopy bulkCopy = new OracleBulkCopy(conn,OracleBulkCopyOptions.UseInternalTransaction))

如果不支援事務，這個OracleBulkCopyOptions.UseInternalTransaction怎麼解釋。

  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 }

錯誤碼：OracleBulkCopy進行交易處理的

 

Oracle的OracleBulkCopy不支援交易處理