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();
                    }
                }
            }
        }