因开发、测试场景经常需要模拟机生产环境的数据，原同事开发了一个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>