.net 調用Oracle.DataAccess.Client批量增加資料

標籤：des   blog   http   io   ar   os   sp   for   on   

　　

1、添加引用

using Oracle.DataAccess.Client;

using System.Configuration;

 2、代碼 增加方法

//DestinationTableName 表名

//connectionString 串連

string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString;

config設定檔中 

<add name="DefaultConnectionString" connectionString="Data Source=庫名;User ID=使用者名稱;Password=密碼;" providerName="System.Data.OracleClient"/>

//dataTable 資料

 public void Insert(string DestinationTableName, string connectionString, DataTable dataTable)
        {
            if (dataTable.Rows.Count == 0)
            {
                return;
            }

　　　　　　　　//string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString;

 　　　　　　　　string strMainTableName = "表名";
 　　　　　　　　// 鎖表防止並發產生重複資料 刪除資料表已經存在的同一批資料
　　　　　　　　string sSQLLock = "lock table  " + strMainTableName + " in share row exclusive mode";
　　　　　　　　string strDeleSqlTemp = string.Format("delete from " + strMainTableName + " where ny=‘{0}‘ and dz in ({1})", strDate, strAdd);

　　　　　　　　//串連

　　　　　　　　using (OracleConnection conn = new OracleConnection(connectionString))
                        {
                            int recordCount = dtTemp.Rows.Count;//資料條數
                            conn.Open();//開啟串連
                            OracleTransaction trans = conn.BeginTransaction();//串連開啟事務

　　　　　　　　　　try
                            {
                                //鎖表
                                OracleCommand cmdLock = new OracleCommand(sSQLLock, conn);
                                cmdLock.ExecuteNonQuery();
                                //刪除原有資料
                                OracleCommand cmdDele = new OracleCommand(strDeleSqlTemp, conn);
                                cmdDele.ExecuteNonQuery();

 　　　　　　　　　　 //擷取目標表表結構
                                DataTable DestDataTable = GetOracleTableSchema(strMainTableName);

　　　　　　　　　　　//擷取insert 語句
                                string strSql = common.GenerateInserSql(strMainTableName, dtTemp, "");
                                OracleCommand cmd = new OracleCommand(strSql, conn);

　　　　　　　　　　　//參數賦值
                                common.GenerateParameter(DestDataTable, dtTemp, cmd);
                                cmd.Transaction = trans;
                                cmd.ArrayBindCount = recordCount;
                                cmd.BindByName = true;
                                cmd.ExecuteNonQuery();
                                trans.Commit(); //執行

　　　　　　　　　　}
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                strRetVal = common.CreateXml("0007", "錯誤資訊:" + ex.Message);
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }

 }

擷取表列的方法GetOracleTableSchema，參數賦值方法GenerateInserSql在部落格中詳細說明http://www.cnblogs.com/xuxin-1989/p/4157697.html

.net 調用Oracle.DataAccess.Client批量增加資料