采用ODP.NET 批量进行数据同步

因开发、测试场景经常需要模拟机生产环境的数据，原同事开发了一个ado.net图形化同步工具，对非技术人员操作友好，但对技术员使用并不方便，每次同步需源库数据与目标的数据源字段进行配置，且同步大数据时慢，因此抽空自己写了简单的同步工具，极大提高了效率。直接贴代码如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Oracle.ManagedDataAccess.Client;
namespace SyncData
{class Program{static void Main(string[] args){//原库连接string strConnSource = System.Configuration.ConfigurationManager.ConnectionStrings["SourceConnectionString"].ConnectionString;//目标连接string strConnTarget = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];//同步方案string[] SyschSchemes = System.Configuration.ConfigurationManager.AppSettings["SyschScheme"].Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);OracleConnection conn = null;List<string> list = new List<string>();Console.WriteLine("------准备导入----------------");try{using (conn = new OracleConnection(strConnSource)){for (int i = 0; i < SyschSchemes.Length; i++){string strSql = SyschSchemes[i].ToLower();string strSqlO = SyschSchemes[i];if (strSql.Trim().Length == 0){continue;}string tb = strSql.Substring(strSql.IndexOf("from ") + 4, strSql.IndexOf("where ") - strSql.IndexOf("from ") -5);using (OracleCommand cmd = conn.CreateCommand()){cmd.CommandText = strSqlO;cmd.CommandType = CommandType.Text;OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd);DataTable dt = new DataTable(tb);oracleDataAdapter.Fill(dt);Console.WriteLine(tb + "共：" + dt.Rows.Count + "条");if (dt.Rows.Count == 0)continue;//暂停该表所有触发器string trigerN =string.Format("alter table {0}  disable all triggers",tb);string trigerY = string.Format("alter table {0}  enable all triggers", tb);string deleteSql = string.Format("delete from  {0}", strSqlO.Substring(strSql.IndexOf("from ") + 4));//暂停目标表触发器executeSQL(strConnTarget, trigerN);//删除目标表条件数据executeSQL(strConnTarget, deleteSql);//同步至目标表InsertData(strConnTarget,dt);//启用触发器executeSQL(strConnTarget, trigerY);}}}}catch (Exception ex){Console.WriteLine(ex.Message);}finally{if (conn != null && conn.State == ConnectionState.Open){conn.Close();}}Console.Read();}private static void InsertData(string strConnTarget, DataTable dt){OracleConnection conn = null;StringBuilder strInse = new StringBuilder();Dictionary<string, List<object>> listData = new Dictionary<string, List<object>>();strInse.AppendFormat("insert into {0} (", dt.TableName);for (int i = 0; i < dt.Columns.Count; i++){if (i == dt.Columns.Count - 1){strInse.AppendFormat("\"{0}\"", dt.Columns[i].ColumnName);}else{strInse.AppendFormat("\"{0}\",", dt.Columns[i].ColumnName);}listData.Add(dt.Columns[i].ColumnName, new List<object>());}strInse.AppendFormat(")values(");for (int i = 0; i < dt.Columns.Count; i++){if (i == dt.Columns.Count - 1){strInse.AppendFormat(":{0}0", dt.Columns[i].ColumnName);}else{strInse.AppendFormat(":{0}0,", dt.Columns[i].ColumnName);}}strInse.AppendFormat(")");foreach (DataRow row in dt.Rows){foreach (DataColumn cl in dt.Columns){listData[cl.ColumnName].Add(row[cl.ColumnName]);}}try{using (conn = new OracleConnection(strConnTarget)){using (OracleCommand cmd = conn.CreateCommand()){cmd.CommandText = strInse.ToString();cmd.CommandType = CommandType.Text;cmd.ArrayBindCount = dt.Rows.Count;cmd.BindByName = true;OracleDbType dbType = OracleDbType.Varchar2;OracleParameter oraParam;foreach (DataColumn cl in dt.Columns){dbType = GetOracleDbType(cl.DataType);oraParam = new OracleParameter(cl.ColumnName+ "0", dbType);oraParam.Direction = ParameterDirection.Input;oraParam.OracleDbTypeEx = dbType;oraParam.Value = listData[cl.ColumnName].ToArray();cmd.Parameters.Add(oraParam);}//打开连接  conn.Open();int result = cmd.ExecuteNonQuery();Console.WriteLine(dt.TableName + "同步完成" + result + "条！");}}}catch (Exception ex){Console.WriteLine(dt.TableName+"产生错误："+ex.Message);}finally{if (conn != null && conn.State == ConnectionState.Open){conn.Close();}}}private static void executeSQL(string strConnTarget, string strSql){OracleConnection conn = null;try{using (conn = new OracleConnection(strConnTarget)){using (OracleCommand cmd = conn.CreateCommand()){cmd.CommandText = strSql;cmd.CommandType = CommandType.Text;//打开连接  conn.Open();int result = cmd.ExecuteNonQuery();}}}catch (Exception ex){Console.WriteLine("产生错误：" + ex.Message+ex.StackTrace);}finally{if (conn != null && conn.State == ConnectionState.Open){conn.Close();}}}private static OracleDbType GetOracleDbType(Type o){switch (o.Name){case "String":return OracleDbType.Varchar2;case "DateTime":return OracleDbType.Date;case "Int64":return OracleDbType.Int64;case "Int32":return OracleDbType.Int32;case "Int16":return OracleDbType.Int16;case "Decimal":return OracleDbType.Decimal;case "Double":return OracleDbType.Double;case "Blob":return OracleDbType.Blob;case "Clob":return OracleDbType.Clob;case "Byte[]":return OracleDbType.Blob;case "Single":return OracleDbType.Single;default:return OracleDbType.Varchar2;}}}
}

app.config进行数据源库、同步目标库、同步表 配置

  <connectionStrings><!--数据源链接--><add name="SourceConnectionString" connectionString=""/></connectionStrings><appSettings><!--目标数据库链接--><!--测试库--><add key="ConnectionString" value=""/><!--同步表--><add key="SyschScheme"value=" 
select * from testa where create_time>sysdate-10; 
select a,b,b,c,d,f from testb where 1=1; 
"/></appSettings>