项目环境:公司中心有一个数据库,产品部署地有多个,每个部署地是一个局域网,部署地内有一台对外通讯的服务器(可能会断网)。项目需要将每个部署地的部分数据,同步到中心数据库。
基本思路:在需要同步的数据库的说明中添加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
<code style="margin-left:0">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; } } }</code>
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/181503.html原文链接:https://javaforall.cn
未经允许不得转载:木盒主机 » 内外网数据库同步方式「建议收藏」