因開發、測試場景經常需要模擬機生產環境的數據，原同事開發了一個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") - 4);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(":v_{0}", dt.Columns[i].ColumnName);}else{strInse.AppendFormat(":v_{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("v_"+cl.ColumnName, 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>