Oracle 臨時表 OracleDataAdapter 批次更新

        /// <summary>
        /// 注意，欄位名必需大小寫保持一致
        /// </summary>
        protected static string updateSql = @"
Merge into Table_Name  T 
Using TempTable  S 
ON (T.USER_ID = S.USERID )
WHEN MATCHED 
THEN UPDATE SET T.NICK_NAME = S.NICKNAME,T.PHONENUMBER = S.PHONENUMBER ,T.AVATAR = S.AVATAR,T.JOB = S.JOB

";

        protected static string crateTemplateSql = @"
declare tableExistedCount number;   --宣告變數儲存要查詢的表是否存在
 p_sql varchar(200);
Begin
     select count(1) into tableExistedCount  from user_tables t where t.table_name = upper('TempTable'); --從系統表中查詢當表是否存在
DBMS_OUTPUT.PUT_LINE(tableExistedCount);
     if tableExistedCount  >0 then --如果不存在，使用快速執行語句建立新表
     DBMS_OUTPUT.PUT_LINE('刪除舊錶');
     for r in (select a.table_name from user_tables a where a.table_name =upper('TempTable')) loop
      p_sql:='TRUNCATE TABLE '||r.table_name;
      DBMS_OUTPUT.PUT_LINE(p_sql);
      execute immediate p_sql;
      p_sql:='drop table '||r.table_name;
      DBMS_OUTPUT.PUT_LINE(p_sql);
      execute immediate p_sql;
      end Loop;
     
     end if;
     
     execute Immediate
         '
        create global temporary table TempTable (
          userId Varchar(255),
          nickName Varchar(255) NULL,
          phonenumber Varchar(255) NULL,
          avatar Varchar(255) NULL,
          job Varchar(255) NULL
        )
         on commit delete  rows
 ';
end;";

        /// <summary>
        /// SqlBulkCopy 批次更新資料
        /// </summary>
        /// <param name="dataTable">資料集</param>
        /// <param name="crateTemplateSql">臨時表建立欄位</param>
        /// <param name="updateSql">更新語句</param>
        public static void BulkUpdateData(DataTable dataTable, string crateTemplateSql, string updateSql)
        {
            using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString))
            {
                OracleTransaction trans = null;//關鍵第一步
                using (var command = new Oracle.ManagedDataAccess.Client.OracleCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        trans = conn.BeginTransaction();
                        //資料庫並建立一個臨時表來儲存資料表的資料
                        command.CommandText = crateTemplateSql;
                        command.ExecuteNonQuery();
                        OracleCommand selectCmd=conn.CreateCommand();
                        //查詢表頭
                        selectCmd.CommandText = "select userId,nickName,phonenumber,avatar,job from TempTable where rownum=0";
                        OracleDataAdapter myDataAdapter = new OracleDataAdapter(selectCmd);
                        DataTable data = new DataTable();
                        myDataAdapter.Fill(data);
                        foreach (DataRow row in dataTable.Rows) {
                            DataRow newRow = data.NewRow();
                            newRow["userId"] = row["userId"];
                            newRow["nickName"] = row["nickName"];
                            newRow["phonenumber"] = row["phonenumber"];
                            newRow["avatar"] = row["avatar"];
                            newRow["job"] = row["job"];
                            
                            data.Rows.Add(newRow);
                        }
                        //插入語句
                        myDataAdapter.InsertCommand=new OracleCommand("insert into TempTable(userId,nickName,phonenumber,avatar,job) values(:userId,:nickName,:phonenumber,:avatar,:job)", conn);

                        
                        OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
                        custCB.ConflictOption = ConflictOption.OverwriteChanges;
                        custCB.SetAllValues = true;
                        
                        foreach (DataColumn c in dataTable.Columns)
                        {
                            OracleParameter oraParameter = new OracleParameter(c.ColumnName, OracleDbType.Varchar2);
                            oraParameter.SourceColumn = c.ColumnName;
                            oraParameter.SourceVersion = DataRowVersion.Current;
                            myDataAdapter.InsertCommand.Parameters.Add(oraParameter);
                        }

                        int count = myDataAdapter.Update(dataTable);

                        dataTable.AcceptChanges();
                        myDataAdapter.Dispose();
                        /*
                        var cmd = new OracleCommand("select count(*) from transaction_temp_tb_lz2 --where user_Id in('615896266','602185346') ", conn);
                        var obj = cmd.ExecuteScalar();
                        */
                        // 執行Command命令 使用臨時表的資料去更新目標表中的資料  然後刪除臨時表
                        command.CommandTimeout = 300;
                        command.CommandText = updateSql;
                        try {
                            int num = command.ExecuteNonQuery();
                            trans.Commit();
                        }
                        catch (Exception ex) {
                            trans.Rollback();
                            throw ex;
                        }
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

整體需求如下：

從外部獲取資料，要更新到資料庫中的一張表，且每天都需要進行資料同步

實現思路

在資料庫中建立一個事務臨時表，表結構與外部獲得的資料保持，將資料匯入到臨時表，然後使用 Merge into 語法進行增刪改 操作