采用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") - 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>