Oracle 临时表 OracleDataAdapter 批量更新的方法
Oracle 临时表 OracleDataAdapter 批量更新
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | /// <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 语法进行增删改 操作