Oracle 临时表 OracleDataAdapter 批量更新的实现思路

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();
                        //数据库并创建一个临时表来保存数据表的数据
               javascript         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) {
                 ThaSz           trans.Rollback();
                            throw ex;
                        }
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

整体需求如下：

从外部获取数据，要更新到数据库中的一张表，且每天都需要进行数据同步

实现思路

在数据库中创建一个事务临时表，表结构与外部获得的数据保持，将数据导入到临时表，然后使用 Merge into 语法进行增删改 操作

到此这篇关于Oracle 临时表 OracleDataAdapter 批量更新的文章就介绍到这了,更多相关Oracle 临时表 OracleDataAdapter 内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！