根據上篇的內容，簡單測試了三種方式的對比，需要使用的，請自行根據需求優化。。。
上篇文字網址：http://www.rzrgm.cn/ggll611928/p/17897005.html
三種方式：
1、常規方式，通過sql語句循環執行
2、使用OracleBulkCopy
3、使用ArrayBind
后面：增加了擴展，使用存儲過程+ ArrayBind
具體步驟如下：
1、創建測試表
CREATE TABLE T_TEST ( ID NUMBER(9) not null, NAME NVARCHAR2(30) not null, AGE NUMBER(2), CREATEDATE DATE, REMARK NVARCHAR2(50) ); COMMENT ON COLUMN T_TEST.ID IS '測試編號'; COMMENT ON COLUMN T_TEST.NAME IS '測試姓名'; COMMENT ON COLUMN T_TEST.AGE IS '創建時間'; COMMENT ON COLUMN T_TEST.CREATEDATE IS '測試年齡'; COMMENT ON COLUMN T_TEST.REMARK IS '測試描述'; ALTER TABLE T_TEST ADD CONSTRAINT PK_T_TEST PRIMARY KEY (ID);
2、創建數據源
使用DataTable，模擬數據源
/// <summary> /// 獲取測試數據源 /// </summary> /// <returns></returns> public DataTable GetTestTable(int type) { //創建數據源 DataTable dt = new DataTable("t_test"); dt.Columns.Add("id", typeof(int)); dt.Columns.Add("name", typeof(string)); dt.Columns.Add("age", typeof(int)); dt.Columns.Add("createdate", typeof(DateTime)); int i, k; if (type == 1) { i = 1; k = 100000; } else if (type == 2) { i = 100001; k = 200000; } else { i = 200001; k = 300000; } //添加數據到 DataTable for (; i <= k; i++) { DataRow row = dt.NewRow(); row["id"] = i; row["name"] = i+ "-" + i; row["age"] = 18; row["createdate"] = DateTime.Now; dt.Rows.Add(row); } return dt; }
3、編寫三種方式
3.1 方式一：常規方式
/// <summary> /// 批量處理插入數據，使用常規方式 /// </summary> /// <param name="dt">數據源</param> /// <returns></returns> public int Insert(DataTable dt) { int count = 0; string conString = orcHelper.GetConn(); using (OracleConnection conn = new OracleConnection(conString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (OracleTransaction transaction = conn.BeginTransaction()) { using (OracleCommand cmd = conn.CreateCommand()) { cmd.Transaction = transaction; string sql = @"insert into t_test(id, name, age, createdate) values(:id, :name, :age, :createdate)"; foreach (DataRow dw in dt.Rows) { OracleParameter[] parametersList = new OracleParameter[] { new OracleParameter(":id", int.Parse(dw["id"].ToString())), new OracleParameter(":name", dw["name"].ToString()), new OracleParameter(":age",int.Parse(dw["age"].ToString())), new OracleParameter(":createdate",DateTime.Parse(dw["createdate"].ToString())), }; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); cmd.Parameters.AddRange(parametersList); try { count = cmd.ExecuteNonQuery(); if (count < 0) { transaction.Rollback(); return count; } } catch (Exception) { transaction.Rollback(); return count; } } transaction.Commit(); return count; } } } }
3.2 方式二：使用OracleBulkCopy
/// <summary> /// 批量處理插入數據，使用OracleBulkCopy /// </summary> /// <param name="dt">數據源</param> public bool InsertOracleBulkCopy(DataTable dt) { string conString = orcHelper.GetConn(); using (OracleConnection conn = new OracleConnection(conString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (OracleTransaction transaction = conn.BeginTransaction()) { //創建 OracleBulkCopy 對象，并指定數據庫連接信息 using (OracleBulkCopy bulkCopy = new OracleBulkCopy(conn)) { //數據庫表名稱 bulkCopy.DestinationTableName = dt.TableName; //指定批量插入的行數 bulkCopy.BatchSize = dt.Rows.Count; //指定 DataTable 和數據表的列名映射關系 for (int i = 0; i < dt.Columns.Count; i++) { bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } try { //將數據源添加到 OracleBulkCopy 對象中 bulkCopy.WriteToServer(dt); transaction.Commit(); return true; } catch (Exception) { transaction.Rollback(); return false; } } } } }
3.3 方式三：使用ArrayBind
/// <summary> /// 批量處理插入數據，使用ArrayBind /// <param name="dt">數據源</param> /// </summary> public int InsertArrayBind(DataTable dt) { string conString = orcHelper.GetConn(); using (OracleConnection conn = new OracleConnection(conString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (OracleTransaction transaction = conn.BeginTransaction()) { int recordCount = dt.Rows.Count, i = 0, count = 0; using (OracleCommand cmd = conn.CreateCommand()) { cmd.Transaction = transaction; cmd.CommandText = "insert into t_test(id, name, age, createdate) values(:id, :name, :age, :createdate)"; //指定單次需要處理的條數 cmd.ArrayBindCount = recordCount; int[] p_col1 = new int[recordCount]; string[] p_col2 = new string[recordCount]; int[] p_col3 = new int[recordCount]; DateTime[] p_col4 = new DateTime[recordCount]; cmd.Parameters.Add(new OracleParameter("id", OracleDbType.Int32, p_col1, ParameterDirection.Input)); cmd.Parameters.Add(new OracleParameter("name", OracleDbType.Varchar2, p_col2, ParameterDirection.Input)); cmd.Parameters.Add(new OracleParameter("age", OracleDbType.Int32, p_col3, ParameterDirection.Input)); cmd.Parameters.Add(new OracleParameter("createdate", OracleDbType.Date, p_col4, ParameterDirection.Input)); foreach (DataRow dr in dt.Rows) { p_col1[i] = Convert.ToInt32(dr["id"].ToString()); p_col2[i] = dr["name"].ToString(); p_col3[i] = Convert.ToInt32(dr["age"].ToString()); p_col4[i] = Convert.ToDateTime(dr["createdate"].ToString()); i++; } try { count = cmd.ExecuteNonQuery(); if (count > 0) { transaction.Commit(); } } catch (Exception) { transaction.Rollback(); } return count; } } } }
4、調用三種方式，進行測試
只需調用下面的方法，即可測試三種方式的對比效果
/// <summary> /// 測試批量插入 /// </summary> private void TestInsert() { int count; long totalTime; string message = "", result; var sw = new Stopwatch(); #region 1、使用常規方式進行批量添加數據 DataTable dataTable = sysService.GetTestTable(1); message += "三種方式對比，每種循環的總記錄數：" + dataTable.Rows.Count + "\n"; sw.Start(); count = sysService.Insert(dataTable); sw.Stop(); totalTime = sw.ElapsedMilliseconds; result = count > 0 ? "成功" : "失敗"; message += "\n方式一：常規方式--" + result + "， 使用總時間：" + totalTime ; #endregion #region 2、使用OracleBulkCopy進行批量添加數據 dataTable = sysService.GetTestTable(2); sw.Start(); bool isOk = sysService.InsertOracleBulkCopy(dataTable); sw.Stop(); totalTime = sw.ElapsedMilliseconds; result = isOk ? "成功" : "失敗"; message += "\n方式二：OracleBulkCopy--" + result + "， 使用總時間：" + totalTime ; #endregion #region 3、使用ArrayBind進行批量添加數據 dataTable = sysService.GetTestTable(3); sw.Start(); count = sysService.InsertArrayBind(dataTable); sw.Stop(); totalTime = sw.ElapsedMilliseconds; result = count>0 ? "成功" : "失敗"; message += "\n方式三：ArrayBind--" + result + "， 使用總時間：" + totalTime; #endregion MessageBox.Show(message, "GrowlMsg"); }
5、運行結果
執行完后，數據庫中該表的總條數
重點注意：數據的完整性及合理性，如主鍵數據必須唯一，日期必須是日期格式等。
6、擴展：存儲過程
使用存儲過程 + ArrayBind，批量修改數據
6.1 創建測試用的存儲過程
主要實現修改數據
CREATE OR REPLACE PROCEDURE p_test_yy( p_Id IN t_test.ID%TYPE, p_Name IN t_test.NAME%type, p_ErrCode OUT NUMBER, p_ErrText OUT VARCHAR2 ) AS v_cnt NUMBER; begin select count(*) into v_cnt from t_test where id = p_Id; if v_cnt is null or v_cnt = 0 then p_ErrCode := -1; p_ErrText :='編號：'||p_Id||'-信息不存在！'; return; end if; -- 模擬錯誤數據 if p_Id = 10 or p_Id = 11 then p_ErrCode := -1; p_ErrText :='編號：'||p_Id||'-重復！'; return; end if; update t_test set createdate=sysdate where id = p_Id; p_ErrCode := p_Id; p_ErrText := '編號：'||p_Id||'-修改日期成功！'; commit; exception when others then p_ErrCode := -ABS(sqlcode); P_ErrText := '發生錯誤，錯誤信息為：'||SUBSTR(SQLERRM, 1, 200); rollback; end p_test_yy;
6.2 重要代碼
/// <summary> /// 批量處理數據，使用存儲過程 + ArrayBind /// <param name="dt">數據源</param> /// </summary> public int InsertArrayBindProcedure(DataTable dt) { string conString = orcHelper.GetConn(); using (OracleConnection conn = new OracleConnection(conString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (OracleTransaction transaction = conn.BeginTransaction()) { int recordCount = dt.Rows.Count, count = 0; using (OracleCommand cmd = conn.CreateCommand()) { cmd.Transaction = transaction; cmd.CommandText = "p_test_yy"; cmd.BindByName = true; cmd.CommandType = CommandType.StoredProcedure; //指定單次需要處理的條數 cmd.ArrayBindCount = recordCount; var columnId = dt.AsEnumerable().Select(row => row.Field<object>("id")).ToArray(); var columnName = dt.AsEnumerable().Select(row => row.Field<object>("name")).ToArray(); cmd.Parameters.Add(new OracleParameter("p_Id", OracleDbType.Int32, columnId, ParameterDirection.Input)); cmd.Parameters.Add(new OracleParameter("p_Name", OracleDbType.Varchar2, columnName, ParameterDirection.Input)); cmd.Parameters.Add( new OracleParameter("p_ErrCode", OracleDbType.Int32, ParameterDirection.Output) { ArrayBindSize = new int[] { recordCount } }); cmd.Parameters.Add( new OracleParameter("p_ErrText", OracleDbType.Varchar2, ParameterDirection.Output) { ArrayBindSize = new int[] { recordCount } }); try { count = cmd.ExecuteNonQuery(); //正常獲取p_ErrCode的全部數據 object objCode = cmd.Parameters["p_ErrCode"].Value; OracleDecimal[] oDecimalCodes = (OracleDecimal[]) objCode; //只能獲取p_ErrText的第一條數據 object objText = cmd.Parameters["p_ErrText"].Value; OracleString[] oDecimalTexts = (OracleString[])objText; int codeCount = oDecimalCodes.AsEnumerable().ToList().Where(t => t < 1).Count(); if (codeCount > 0) { count = 0; transaction.Rollback(); return count; } count = oDecimalCodes.Count(); transaction.Commit(); } catch (Exception) { transaction.Rollback(); } return count; } } } }
6.3 調用
其中GetTestTable，在上面右介紹，此處不再說明
/// <summary> /// 測試存儲過程批量處理數據 /// </summary> private void TestProcedure() { int count; long totalTime; string message = "", result; var sw = new Stopwatch(); DataTable dataTable = sysService.GetTestTable(1); message += "循環的總記錄數：" + dataTable.Rows.Count + "\n"; sw.Start(); count = sysService.InsertArrayBindProcedure(dataTable); sw.Stop(); totalTime = sw.ElapsedMilliseconds; result = count > 0 ? "成功" : "失敗"; message += "\n測試存儲過程批量處理數據--" + result + "， 使用總時間：" + totalTime; MessageBox.Show(message, "GrowlMsg"); }
6.4 調試
根據下圖，可以看到
存儲過程返回的第一個參數p_ErrCode，可以正常獲取全部數據
但是，第二個參數p_ErrText，只能獲取第一條的數據
原因目前還沒弄清除，有需求的自己研究。。。
故需要使用存儲過程批量處理數據時，執行前一定確定數據的完整性，這樣批量執行就不會出錯！！！
浙公網安備 33010602011771號