Oracle大数据库批量导入，引用Oracle.DataAccess

来源：互联网发布：linux查看打开文件数编辑：程序博客网时间：2024/05/22 10:40

网上找了好久都还没有找到，于是小严自己结合经验写了一个多用型方法，还可以进行优化

首先：Oracle 必须引用DataAccess，才能支持大数据批量导入

其次：一定要注意表中字段的类型，与你定义的类型要一致

      public static void InsertBatchData(DataTable dt, string ps_user, string Bill_id)
        {
            ResultData result = new ResultData();

            //这里引用数据库连接字符串
            using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(OracleHelper.InstanceGSCM.GetDBString("GSCMDB")))
            {
                Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand();
                conn.Open();
                command.Connection = conn;
                //到此为止，还都是我们熟悉的代码，下面就要开始喽
                //这个参数需要指定每次批插入的记录数
                command.ArrayBindCount = dt.Rows.Count;

                //这里就是写要插入数据库的插入语句（如何写都知道）
                command.CommandText = " INSERT INTO XX( ID,BILL_ID,RESULT_ITEM_ID,RESULT_SUP_ID,ITEM_UUID, ITEM_NO,ITEM_NAME,SUPPLIER_NO,SUPPLIER_NAME,ASSIGNQUANTITY,ASSIGNSHARE,PRICE_TAX_NONE, CURRENCY,EXCHANGERATE_USD,REMARK,CREATED_BY,CREATED_DATE,LAST_UPDATED_BY,LAST_UPDATED_DATE,ENABLED_FLAG,CARGO_PERIOD,FORECASTQUANTITY)values( :ID,:BILL_ID,:RESULT_ITEM_ID,:RESULT_SUP_ID,:ITEM_UUID,:ITEM_NO,:ITEM_NAME,:SUPPLIER_NO,:SUPPLIER_NAME,:ASSIGNQUANTITY,:ASSIGNSHARE,:PRICE_TAX_NONE, :CURRENCY,:EXCHANGERATE_USD,:REMARK,:CREATED_BY,:CREATED_DATE,:LAST_UPDATED_BY,:LAST_UPDATED_DATE,:ENABLED_FLAG,:CARGO_PERIOD,:FORECASTQUANTITY)";
                //下面定义几个数组（与表中字段一致）,必须与数据库命名规范一样
                string[] ID = new string[dt.Rows.Count];
                string[] BILL_ID = new string[dt.Rows.Count];
                string[] RESULT_ITEM_ID = new string[dt.Rows.Count];
                string[] RESULT_SUP_ID = new string[dt.Rows.Count];
                string[] ITEM_UUID = new string[dt.Rows.Count];
                string[] ITEM_NO = new string[dt.Rows.Count];
                string[] ITEM_NAME = new string[dt.Rows.Count];
                string[] SUPPLIER_NO = new string[dt.Rows.Count];
                string[] SUPPLIER_NAME = new string[dt.Rows.Count];
                string[] ASSIGNQUANTITY = new string[dt.Rows.Count];
                string[] ASSIGNSHARE = new string[dt.Rows.Count];
                string[] PRICE_TAX_NONE = new string[dt.Rows.Count];
                string[] CURRENCY = new string[dt.Rows.Count];
                string[] EXCHANGERATE_USD = new string[dt.Rows.Count];
                string[] REMARK = new string[dt.Rows.Count];
                string[] CREATED_BY = new string[dt.Rows.Count];
                DateTime[] CREATED_DATE = new DateTime[dt.Rows.Count];
                string[] LAST_UPDATED_BY = new string[dt.Rows.Count];
                DateTime[] LAST_UPDATED_DATE = new DateTime[dt.Rows.Count];
                int[] ENABLED_FLAG = new int[dt.Rows.Count];
                string[] CARGO_PERIOD = new string[dt.Rows.Count];
                string[] FORECASTQUANTITY = new string[dt.Rows.Count];
                // 为了传递参数,顺序一一对应好，才会解析成功
                OracleParameter deptNoParam = new OracleParameter("ID",OracleDbType.Varchar2);
                deptNoParam.Direction = ParameterDirection.Input;
                deptNoParam.Value = ID;
                command.Parameters.Add(deptNoParam);
                OracleParameter deptNameParam1 = new OracleParameter("BILL_ID",OracleDbType.Varchar2);
                deptNameParam1.Direction = ParameterDirection.Input;
                deptNameParam1.Value = BILL_ID;
                command.Parameters.Add(deptNameParam1);
                OracleParameter deptNameParam2 = new OracleParameter("RESULT_ITEM_ID", OracleDbType.Varchar2);
                deptNameParam2.Direction = ParameterDirection.Input;
                deptNameParam2.Value = RESULT_ITEM_ID;
                command.Parameters.Add(deptNameParam2);
                OracleParameter deptNameParam3 = new OracleParameter("RESULT_SUP_ID", OracleDbType.Varchar2);
                deptNameParam3.Direction = ParameterDirection.Input;
                deptNameParam3.Value = RESULT_SUP_ID;
                command.Parameters.Add(deptNameParam3);
                OracleParameter deptNameParam4 = new OracleParameter("ITEM_UUID", OracleDbType.Varchar2);
                deptNameParam4.Direction = ParameterDirection.Input;
                deptNameParam4.Value = ITEM_UUID;
                command.Parameters.Add(deptNameParam4);
                OracleParameter deptNameParam5 = new OracleParameter("ITEM_NO", OracleDbType.Varchar2);
                deptNameParam5.Direction = ParameterDirection.Input;
                deptNameParam5.Value = ITEM_NO;
                command.Parameters.Add(deptNameParam5);
                OracleParameter deptNameParam6 = new OracleParameter("ITEM_NAME", OracleDbType.Varchar2);
                deptNameParam6.Direction = ParameterDirection.Input;
                deptNameParam6.Value=ITEM_NAME;
                command.Parameters.Add(deptNameParam6);
                OracleParameter deptNameParam7 = new OracleParameter("SUPPLIER_NO", OracleDbType.Varchar2);
                deptNameParam7.Direction = ParameterDirection.Input;
                deptNameParam7.Value = SUPPLIER_NO;
                command.Parameters.Add(deptNameParam7);
                OracleParameter deptNameParam8 = new OracleParameter("SUPPLIER_NAME", OracleDbType.Varchar2);
                deptNameParam8.Direction = ParameterDirection.Input;
                deptNameParam8.Value = SUPPLIER_NAME;
                command.Parameters.Add(deptNameParam8);
                OracleParameter deptNameParam9 = new OracleParameter("ASSIGNQUANTITY", OracleDbType.Varchar2);
                deptNameParam9.Direction = ParameterDirection.Input;
                deptNameParam9.Value = ASSIGNQUANTITY;
                command.Parameters.Add(deptNameParam9);
                OracleParameter deptNameParam10 = new OracleParameter("ASSIGNSHARE", OracleDbType.Varchar2);
                deptNameParam10.Direction = ParameterDirection.Input;
                deptNameParam10.Value = ASSIGNSHARE;
                command.Parameters.Add(deptNameParam10);
                OracleParameter deptNameParam11 = new OracleParameter("PRICE_TAX_NONE", OracleDbType.Varchar2);
                deptNameParam11.Direction = ParameterDirection.Input;
                deptNameParam11.Value = PRICE_TAX_NONE;
                command.Parameters.Add(deptNameParam11);
                OracleParameter deptNameParam12 = new OracleParameter("CURRENCY", OracleDbType.Varchar2);
                deptNameParam12.Direction = ParameterDirection.Input;
                deptNameParam12.Value = CURRENCY;
                command.Parameters.Add(deptNameParam12);
                OracleParameter deptNameParam13 = new OracleParameter("EXCHANGERATE_USD", OracleDbType.Varchar2);
                deptNameParam13.Direction = ParameterDirection.Input;
                deptNameParam13.Value = EXCHANGERATE_USD;
                command.Parameters.Add(deptNameParam13);
                OracleParameter deptNameParam14 = new OracleParameter("REMARK", OracleDbType.Varchar2);
                deptNameParam14.Direction = ParameterDirection.Input;
                deptNameParam14.Value = REMARK;
                command.Parameters.Add(deptNameParam14);
                OracleParameter deptNameParam15 = new OracleParameter("CREATED_BY", OracleDbType.Varchar2);
                deptNameParam15.Direction = ParameterDirection.Input;
                deptNameParam15.Value = CREATED_BY;
                command.Parameters.Add(deptNameParam15);
                OracleParameter deptNameParam16 = new OracleParameter("CREATED_DATE", OracleDbType.Date);
                deptNameParam16.Direction = ParameterDirection.Input;
                deptNameParam16.Value = CREATED_DATE;
                command.Parameters.Add(deptNameParam16);
                OracleParameter deptNameParam17 = new OracleParameter("LAST_UPDATED_BY", OracleDbType.Varchar2);
                deptNameParam17.Direction = ParameterDirection.Input;
                deptNameParam17.Value = LAST_UPDATED_BY;
                command.Parameters.Add(deptNameParam17);
                OracleParameter deptNameParam18 = new OracleParameter("LAST_UPDATED_DATE", OracleDbType.Date);
                deptNameParam18.Direction = ParameterDirection.Input;
                deptNameParam18.Value = LAST_UPDATED_DATE;
                command.Parameters.Add(deptNameParam18);
                OracleParameter deptNameParam19 = new OracleParameter("ENABLED_FLAG", OracleDbType.Int32);
                deptNameParam19.Direction = ParameterDirection.Input;
                deptNameParam19.Value = ENABLED_FLAG;
                command.Parameters.Add(deptNameParam19);
                OracleParameter deptNameParam20 = new OracleParameter("CARGO_PERIOD", OracleDbType.Varchar2);
                deptNameParam20.Direction = ParameterDirection.Input;
                deptNameParam20.Value = CARGO_PERIOD;
                command.Parameters.Add(deptNameParam20);
                OracleParameter deptNameParam21 = new OracleParameter("FORECASTQUANTITY", OracleDbType.Varchar2);
                deptNameParam21.Direction = ParameterDirection.Input;
                deptNameParam21.Value = FORECASTQUANTITY;
                command.Parameters.Add(deptNameParam21);
                Stopwatch sw = new Stopwatch();
                sw.Start();
                DateTime dtTime = DateTime.Now;
                //在下面的循环中,将你得到的表格数据一一分别对应赋值先前定义的数组里面去
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ID[i] = Guid.NewGuid().ToString();
                    BILL_ID[i] = Bill_id;
                    RESULT_ITEM_ID[i] = "";
                    RESULT_SUP_ID[i] = "";
                    ITEM_UUID[i] = "";
                    ITEM_NO[i] = dt.Rows[i]["ITEM_NO"].ToString();
                    ITEM_NAME[i] = dt.Rows[i]["ITEM_NAME"].ToString();
                    SUPPLIER_NO[i] = dt.Rows[i]["SUPPLIER_NO"].ToString();
                    SUPPLIER_NAME[i] = dt.Rows[i]["SUPPLIER_NAME"].ToString();
                    ASSIGNQUANTITY[i] = dt.Rows[i]["ASSIGNQUANTITY"].ToString();
                    ASSIGNSHARE[i] = dt.Rows[i]["ASSIGNSHARE"].ToString();
                    PRICE_TAX_NONE[i] = dt.Rows[i]["PRICE_TAX_NONE"].ToString();
                    CURRENCY[i] = dt.Rows[i]["CURRENCY"].ToString();
                    EXCHANGERATE_USD[i] = dt.Rows[i]["EXCHANGERATE_USD"].ToString();
                    REMARK[i] = dt.Rows[i]["REMARK"].ToString();
                    CREATED_BY[i] = ps_user;
                    CREATED_DATE[i] = dtTime;
                    LAST_UPDATED_BY[i] = ps_user;
                    LAST_UPDATED_DATE[i] = dtTime;
                    ENABLED_FLAG[i] = 1;
                    CARGO_PERIOD[i] = dt.Rows[i]["CARGO_PERIOD"].ToString();
                    FORECASTQUANTITY[i] = dt.Rows[i]["FORECASTQUANTITY"].ToString();
                }
                //这个调用将把参数数组传进SQL,同时写入数据库
                command.ExecuteNonQuery();
                sw.Stop();
            }
        }

0 0