内外网数据库同步方式「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

项目环境：公司中心有一个数据库，产品部署地有多个，每个部署地是一个局域网，部署地内有一台对外通讯的服务器(可能会断网)。项目需要将每个部署地的部分数据，同步到中心数据库。

基本思路：在需要同步的数据库的说明中添加Update标识，在需要同步的表中添加UpdateDate字段。在每一天的0点时候，会执行一次同步，根据UpdateDate字段进行时间比对，同步会遍历当前数据库的所以后表，如果表中包含Update标识，就执行同步操作。同步时执行事务操作，全部数据同步完成后进行提交操作。如果有错误，则执行RollBack，并发送邮件通知维护人员。

本地数据库LocalDB

远端数据库RemoteDB

表名 TableName

主键PrimaryKey

简要步奏

0. BeginTrans

LocalDB：

1  从LocalDB读出DateTable

RemoteDB：

2. 数据库使用select * into tmpTable from TableName where PrimaryKey=-1 生成临时表

3. 将数据集提交到临时表中

4. 


—更新相同数据 / 删除相同数据  二选一即可


 –更新(需要对知道每个表的哪些字段需要更新)


update TableName set CreateDate=A.CreateDate from tmpTable A,TableName B where B.PrimaryKey=A.PrimaryKey

 –删除

       delete from TableName  where PrimaryKey in (select PrimaryKey from tmpTable)

—插入不同数据的记录


insert into TableName   select * from tmpTable A where not exists(select PrimaryKey  from TableName   B where B.{1}=A.PrimaryKey )

5. 跳到第一步 直到所有表都更新完成

6. Commit

public class Base_TableUpdate
    {
        public int Run(DateTime lastUpdateDate)
        {
            IDatabase dbRemote = null;
            int updateCount = 0;
            try
            {
                IDatabase dbLocal = DataFactory.Database("LocalSqlServer");
                dbRemote = DataFactory.Database("RemoteSqlServer");

                DbTransaction trans = dbRemote.BeginTrans();


                //查询出所有表
                /*
                select
                  ROW_NUMBER() OVER (ORDER BY a.object_id) AS No, 
                  a.name AS TableName,
                  isnull(g.[value],'') AS Explain
                from
                  sys.tables a left join sys.extended_properties g
                  on (a.object_id = g.major_id AND g.minor_id = 0)
                */
                string sql = string.Format("select ROW_NUMBER() OVER (ORDER BY a.object_id) AS No, a.name AS TableName, isnull(g.[value],'') AS Explain  from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)");
                List<TableNameEntity> list = dbLocal.FindListBySql<TableNameEntity>(sql);

                foreach(TableNameEntity en in list)
                {
                    if (string.IsNullOrEmpty(en.Explain) || !en.Explain.ToLower().Contains("update"))
                        continue;

                    string tableName = en.TableName;
                    //1 查出数据
                    //where UpdateDate>'{1}'
                    DataSet ds = dbLocal.FindDataSetBySql(string.Format("select * from {0} where UpdateDate>'{1}'", tableName, lastUpdateDate.ToString("yyyy-MM-dd hh:mm:ss")));

                    updateCount += ds.Tables[0].Rows.Count;

                    //2 创建临时表
                    int res = dbRemote.FindCountBySql("select Count(Name) from sysobjects where name='tmpTable'");
                    if (res == -1)
                    {//失败
                        throw new Exception("10001 查询临时表时失败，Code{ dbLocal.FindCountBySql(\"select Count(Name) from sysobjects where name='tmpTable'\"); }");
                    }
                    else if (res == 1)
                    {//表存在,删除表
                        dbRemote.ExecuteBySql(new StringBuilder("Drop Table tmpTable"));
                    }
                    //创建
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat("select * into tmpTable from {0} where (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}') is null", tableName);
                    res = dbRemote.ExecuteBySql(sb);
                    if (res == -1)
                    {
                        throw new Exception("10002 创建临时表时失败，Code{ sb.AppendFormat(\"select * into tmpTable from {0} where (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}') is null\", tableName);dbRemote.ExecuteBySql(sb); }");
                    }


                    //3 往临时表插入数据
                    //获得表主键
                    sb = new StringBuilder();
                    sb.AppendFormat("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}'", tableName);
                    PrimaryKeyEntity entity = dbRemote.FindEntityBySql<PrimaryKeyEntity>(sb.ToString());

                    string primaryKey = entity.COLUMN_NAME;
                    if (string.IsNullOrEmpty(primaryKey))
                    {
                        string errmsg = string.Format("10006 表主键为空，Code{ sb.AppendFormat(\"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}'\", tableName); } Parameter{ tableName:{0}}", tableName);
                        throw new Exception(errmsg);
                    }

                    //插入所有数据
                    ds.Tables[0].TableName = "tmpTable";
                    bool bInRes = dbRemote.BulkInsert(ds.Tables[0], trans);
                    if (!bInRes)
                    {
                        string errmsg = string.Format("10003 插入所有数据时失败，Code{ ds.Tables[0].TableName = \"tmpTable\";bool bInRes = dbRemote.BulkInsert(ds.Tables[0], trans); }");
                        throw new Exception(errmsg);
                    }


                    //4 合并临时表与服务器对应表数据
                    //删除已有数据
                    //delete from ' + @TableName + ' where ' + @PKey + ' in (select ' + @PKey + ' from tmpTable)
                    sb = new StringBuilder();
                    sb.AppendFormat("delete from {0} where {1} in (select {1} from tmpTable)", tableName, primaryKey);
                    res = dbRemote.ExecuteBySql(sb, trans);
                    if (res == -1)
                    {
                        string errmsg = string.Format("10004 删除已有数据时失败，Code{ sb.AppendFormat(\"delete from {0} where {1} in (select {1} from tmpTable)\", tableName, primaryKey);res =dbRemote.ExecuteBySql(sb); } Parameter{ tableName:{0}, primaryKey:{1}}", tableName, primaryKey);
                        throw new Exception(errmsg);
                    }

                    //将数据插入表
                    //insert into tmpTable select * from ' + @TableName + ' A where not exists(select ' + @PKey + ' from tmpTable B where B.' + @PKey + '=A.' + @PKey + ')';
                    sb = new StringBuilder();
                    sb.AppendFormat("insert into {0} select * from tmpTable A where not exists(select {1} from {0} B where B.{1}=A.{1})", tableName, primaryKey);
                    res = dbRemote.ExecuteBySql(sb, trans);
                    if (res == -1)
                    {
                        string errmsg = string.Format("10005 合并数据时失败，Code{ sb.AppendFormat(\"insert into {0} select * from tmpTable A where not exists(select {1} from {0} B where B.{1}=A.{1})\", tableName, primaryKey);res = dbRemote.ExecuteBySql(sb); } Parameter{ tableName:{0}, primaryKey:{1}}", tableName, primaryKey);
                        throw new Exception(errmsg);
                    }

                    //5 删除临时表
                    dbRemote.ExecuteBySql(new StringBuilder("Drop Table tmpTable"));
                }
                dbRemote.Commit();

                return updateCount;
            }
            catch(Exception ex)
            {
                try{ dbRemote.Rollback(); } catch { }
                
                throw ex;
            }
        }
    }

    public class PrimaryKeyEntity
    {
        private string _COLUMN_NAME;

        /// <summary>
        /// 表的主键名
        /// </summary>
        public string COLUMN_NAME
        {
            get { return _COLUMN_NAME; }
            set { _COLUMN_NAME = value; }
        }
    }

    public class TableNameEntity
    {
        private string _TableName;

        public string TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }
        private string _Explain;
        public string Explain
        {
            get { return _Explain; }
            set { _Explain = value; }
        }
    }