数据库同步核心代码

使用微软同步模块实现的数据同步核心代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Reflection;
using DataModel;
using System.Runtime.Serialization;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Common
{
/// <summary>
/// 关于本地数据库与远程服务器数据库同步的设计
/// </summary>
public class DBSyncBySyncFrameWorkhelper
{

/// <summary>
/// 配置服务器端的同步设置(全部需要同步的表)（目前不建议使用此方法，因为访问速度很慢）（其中作用域为表名）
/// </summary>
/// <param name="remoteConnectString">服服务器端的连接字符串</param>
/// <returns>是否操作完成</returns>
public bool RemoteConfigSyncInformationForAll(string remoteConnectString, string scopeName)
{
bool success = false;
SqlConnection serverConn = new SqlConnection(remoteConnectString);
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);
DbSyncTableDescription scopeDescAlarmCategory = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_AlarmCategory", serverConn);
DbSyncTableDescription scopeDescAlarmCode = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_AlarmCode", serverConn);
DbSyncTableDescription scopeDescContactsInfo = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_ContactsInfo", serverConn);
DbSyncTableDescription scopeDescUserInfo = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_UserInfo ", serverConn);
DbSyncTableDescription scopeDescDisplayBoard = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_DisplayBoard", serverConn);
DbSyncTableDescription scopeDescZoneInfo = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_ZoneInfo", serverConn);
DbSyncTableDescription scopeDescZoneImageInfo = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_ZoneImage", serverConn);
DbSyncTableDescription scopeDescComboBoxItems = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_ComboBoxItems", serverConn);
DbSyncTableDescription scopeDesctDisplayBoardCategory = SqlSyncDescriptionBuilder.GetDescriptionForTable("tbl_DisplayBoardCategory", serverConn);
scopeDesc.Tables.Add(scopeDescAlarmCategory);
scopeDesc.Tables.Add(scopeDescAlarmCode);
scopeDesc.Tables.Add(scopeDescContactsInfo);
scopeDesc.Tables.Add(scopeDescUserInfo);
scopeDesc.Tables.Add(scopeDescDisplayBoard);
scopeDesc.Tables.Add(scopeDescZoneInfo);
scopeDesc.Tables.Add(scopeDescZoneImageInfo);
try
{
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
if (!serverProvision.ScopeExists("tbl_AlarmCategory"))
{
serverProvision.Apply();
success = true;
}
else
{
success = true;
}
}
catch (Exception ex)
{
LogHelper.WriteError("配置服务器全部数据同步失败", ex);
success = false;

}
return success;

}
/// <summary>
/// 进行服务器端同步的设置（其中scopeName建议使用表名，便于管理使用）
/// </summary>
/// <param name="remoteConnectString">服务器端的连接字符串</param>
/// <param name="scopeName">进行同步的作用域（建议使用表名，可使用枚举参数进行转换）</param>
/// <returns>操作是否成功</returns>
public bool RemoteConfigSyncInformation(string remoteConnectString, string scopeName)
{
bool success = false;
SqlConnection serverConn = new SqlConnection(remoteConnectString);
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(ScopeTableName.tbl_UserInfo.ToString());
DbSyncTableDescription scopeDescTable = SqlSyncDescriptionBuilder.GetDescriptionForTable(scopeName, serverConn);
scopeDesc.Tables.Add(scopeDescTable);
try
{
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
if (!serverProvision.ScopeExists(scopeName))
{
serverProvision.Apply();
success = true;
}
else
{
success = true;
}
}
catch (Exception ex)
{
LogHelper.WriteError("配置服务器" + scopeName + "数据同步失败", ex);
success = false;

}
return success;

}

/// </summary>
/// <param name="remoteConnectString">服务器连接字符串</param>
/// <param name="localConnectString">客户端连接字符串</param>
/// <param name="scopeName">进行同步的作用域（建议使用表名，可使用枚举参数进行转换）</param>
/// <returns>操作是否成功</returns>
public bool LocalConfigSyncInformation(string remoteConnectString, string localConnectString, string scopeName)
{
bool success = false;
SqlConnection clientConn = new SqlConnection(localConnectString);
SqlConnection serverConn = new SqlConnection(remoteConnectString);
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
if (!clientProvision.ScopeExists(scopeName))
{
try
{
clientProvision.Apply();
success = true;
}
catch (Exception ex)
{
LogHelper.WriteError("配置本地" + scopeName + "数据同步失败", ex);
success = false;
}

}
else
{
success = true;
}
return success;
}
/// <summary>
/// 客户端同步配置(全部需要同步的表)（目前不建议使用此方法，因为访问速度很慢）请使用单表的方式（其中作用域为表名）
/// </summary>
/// <param name="remoteConnectString">服务器连接字符串</param>
/// <param name="localConnectString">客户端连接字符串</param>
/// <returns>是否操作完成</returns>
public bool LocalConfigSyncInformationForAll(string remoteConnectString, string localConnectString, string scopeName)
{
bool success = false;
SqlConnection clientConn = new SqlConnection(localConnectString);
SqlConnection serverConn = new SqlConnection(remoteConnectString);
DbSyncScopeDescription scopeDescAlarmCategory = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescAlarmCode = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescContactsInfo = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescUserInfo = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescDisplayBoard = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescZoneInfo = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescZoneImage = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescComboBoxItems = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
DbSyncScopeDescription scopeDescDisplayBoardCategory = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
SqlSyncScopeProvisioning clientProvisionAlarmCategory = new SqlSyncScopeProvisioning(clientConn, scopeDescAlarmCategory);
SqlSyncScopeProvisioning clientProvisionAlarmCode = new SqlSyncScopeProvisioning(clientConn, scopeDescAlarmCode);
SqlSyncScopeProvisioning clientProvisionContactsInfo = new SqlSyncScopeProvisioning(clientConn, scopeDescUserInfo);
SqlSyncScopeProvisioning clientProvisionUserInfo = new SqlSyncScopeProvisioning(clientConn, scopeDescUserInfo);
SqlSyncScopeProvisioning clientProvisionDisplayBoard = new SqlSyncScopeProvisioning(clientConn, scopeDescDisplayBoard);
SqlSyncScopeProvisioning clientProvisionZoneImage = new SqlSyncScopeProvisioning(clientConn, scopeDescZoneImage);
SqlSyncScopeProvisioning clientProvisionDescZoneInfo = new SqlSyncScopeProvisioning(clientConn, scopeDescZoneInfo);
SqlSyncScopeProvisioning clientProvisionComboBoxItems = new SqlSyncScopeProvisioning(clientConn, scopeDescComboBoxItems);
SqlSyncScopeProvisioning clientProvisionDisplayBoardCategory = new SqlSyncScopeProvisioning(clientConn, scopeDescDisplayBoardCategory);
if (!clientProvisionAlarmCategory.ScopeExists(scopeName))
{
try
{
clientProvisionAlarmCategory.Apply();
clientProvisionAlarmCode.Apply();
clientProvisionContactsInfo.Apply();
clientProvisionUserInfo.Apply();
clientProvisionDisplayBoard.Apply();
clientProvisionDescZoneInfo.Apply();
clientProvisionZoneImage.Apply();
success = true;
}
catch (Exception ex)
{
LogHelper.WriteError("配置本地" + scopeName + "数据全部同步失败", ex);
success = false;
}

}
else
{
success = true;
}
return success;
}
/// <summary>
/// 进行数据库远程与本地的同步
/// </summary>
/// <param name="scopeName">同步的作用域的名字（对于单表是表的名字，可以使用枚举进行装换）</param>
/// <param name="localConnectString">本地连接字符串</param>
/// <param name="remoteConnectString">远程连接字符串</param>
/// <param name="ApplyChangeFailedCallBack">进行异常处理的回调事件</param>
/// <returns>进行同步数据统计的SyncOperationStatistics类</returns>
public SyncOperationStatistics SyncDB(string scopeName, string localConnectString, string remoteConnectString, EventHandler<DbApplyChangeFailedEventArgs> ApplyChangeFailedCallBack)
{
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
SqlConnection clientConn = new SqlConnection(localConnectString);
SqlConnection serverConn = new SqlConnection(remoteConnectString);
syncOrchestrator.LocalProvider = new SqlSyncProvider(scopeName, clientConn);
syncOrchestrator.RemoteProvider = new SqlSyncProvider(scopeName, serverConn);
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += ApplyChangeFailedCallBack;
SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
return syncStats;
}
/// <summary>
/// 移除同步的作用域（服务器与客户端）
/// </summary>
/// <param name="sqlConnection">服务器或者客户机的连接字符串</param>
/// <param name="scopeName">需要删除的同步作用域的名称（对于单表是表的名字，可以使用枚举进行装换）</param>
/// <returns>是否成功</returns>
public bool RemoveSyncScope(SqlConnection sqlConnection,string scopeName )
{
bool success = false;
try
{
SqlSyncScopeDeprovisioning SqlDepro = new SqlSyncScopeDeprovisioning(sqlConnection);
SqlDepro.DeprovisionScope(scopeName);
success = true;
}
catch (Exception ex)
{
LogHelper.WriteError("移除同步作用域" + scopeName + "失败", ex);
success = false;
}
return success;

}
/// <summary>
/// 需要同步的表
/// </summary>
public enum ScopeTableName
{
tbl_AlarmCategory = 0,
tbl_AlarmCode = 1,
tbl_ContactsInfo = 2,
tbl_UserInfo = 3,
tbl_DisplayBoard = 4,
tbl_ZoneInfo = 5,
tbl_ZoneImage=6,
tbl_ComboBoxItems=7,
tbl_DisplayBoardCategory=8

}
/// <summary>
/// 需要进行同步的数据库的集合
/// </summary>
/// <returns>需要进行同步的数据库的集合</returns>
public List<string> SyncTables()
{
List<string> list = new List<string>();
list.Add("tbl_AlarmCategory");
list.Add("tbl_AlarmCode");
list.Add("tbl_ContactsInfo");
list.Add("tbl_DisplayBoard");
list.Add("tbl_UserInfo");
list.Add("tbl_ZoneInfo");
list.Add("tbl_ZoneImage");
list.Add("tbl_ComboBoxItems");
list.Add("tbl_DisplayBoardCategory");
return list;
}
}
/// <summary>
/// 使用EF加SQL 进行同步的帮助类
/// </summary>
public class DBSyncBySQLhelper
{

/// <summary>
/// 同步的策略是根据选择的时间段
/// </summary>
/// <param name="StartTime"></param>
/// <param name="EndTime"></param>
/// <returns></returns>
public bool GetAlarmDataByTime(DateTime oldserverTime, int intervaHours)
{
bool success = false;
FixAlarmEntities server = new FixAlarmEntities();
DateTime newserverTime = DateTime.Now.Add(new TimeSpan(1,0,0));
DateTime oneDayStartTime = new DateTime(oldserverTime.Year, oldserverTime.Month, oldserverTime.Day);
DateTime searchTime = new DateTime(oldserverTime.Year, oldserverTime.Month, oldserverTime.Day).Subtract(new TimeSpan(intervaHours,0,0));
LocalFixAlarmEntities local = new LocalFixAlarmEntities();
try
{
local.Database.ExecuteSqlCommand("delete from tbl_AlarmInfo where AlarmTime between '" + searchTime + "'and '" + newserverTime + "'");
local.Database.ExecuteSqlCommand("delete from tbl_AlarmPreprocessInfo where PreProcessTime between '" + searchTime + "'and '" + newserverTime + "'");
var importAlarmInfoData = (from n in server.tbl_AlarmInfo where n.AlarmTime >= searchTime && n.AlarmTime <= newserverTime select n).ToList();
StringBuilder insertToLocalAlarmInfo=new StringBuilder();
if (importAlarmInfoData.Count > 0)
{
for (int i = 0; i < importAlarmInfoData.Count(); i++)
{
insertToLocalAlarmInfo.Append("insert into tbl_AlarmInfo(ACID,AlarmCallerNo,AlarmDetail,AlarmID,AlarmPriority,AlarmTime,AlarmType,BeginTime,CategoryID,CommData,EndTime,Operator,PoliceArriveTime,PoliceEndTime,PoliceMan,PoliceNotifyTime,PoliceReal,PoliceResult,PreProcessingCounts,PreResult,ProcessingComputerIP,ProcessStatus,ReceiveID,Remark,Result,UserAddr,UserGroup,UserID,UserIndustry,UserName,UserType,ZoneGroup,ZoneID,ZoneName,ZonePos,ZoneType) Values('"
+ importAlarmInfoData[i].ACID + "','"
+ importAlarmInfoData[i].AlarmCallerNo + "','"
+ importAlarmInfoData[i].AlarmDetail + "','"
+ importAlarmInfoData[i].AlarmID + "','"
+ importAlarmInfoData[i].AlarmPriority + "','"
+ importAlarmInfoData[i].AlarmTime + "','"
+ importAlarmInfoData[i].AlarmType + "','"
+ importAlarmInfoData[i].BeginTime + "','"
+ importAlarmInfoData[i].CategoryID + "','"
+ importAlarmInfoData[i].CommData + "','"
+ importAlarmInfoData[i].EndTime + "','"
+ importAlarmInfoData[i].Operator + "','"
+ importAlarmInfoData[i].PoliceArriveTime + "','"
+ importAlarmInfoData[i].PoliceEndTime + "','"
+ importAlarmInfoData[i].PoliceMan + "','"
+ importAlarmInfoData[i].PoliceNotifyTime + "','"
+ importAlarmInfoData[i].PoliceReal + "','"
+ importAlarmInfoData[i].PoliceResult + "',"
+ importAlarmInfoData[i].PreProcessingCounts + ",'"
+ importAlarmInfoData[i].PreResult + "','"
+ importAlarmInfoData[i].ProcessingComputerIP + "','"
+ importAlarmInfoData[i].ProcessStatus + "','"
+ importAlarmInfoData[i].ReceiveID + "',' "
+ importAlarmInfoData[i].Remark + "','"
+ importAlarmInfoData[i].Result + "','"
+ importAlarmInfoData[i].UserAddr + "','"
+ importAlarmInfoData[i].UserGroup + "','"
+ importAlarmInfoData[i].UserID + "','"
+ importAlarmInfoData[i].UserIndustry + "','"
+ importAlarmInfoData[i].UserName + "','"
+ importAlarmInfoData[i].UserType + "','"
+ importAlarmInfoData[i].ZoneGroup + "','"
+ importAlarmInfoData[i].ZoneID + "','"
+ importAlarmInfoData[i].ZoneName + "','"
+ importAlarmInfoData[i].ZonePos + "','"
+ importAlarmInfoData[i].ZoneType + "');");

}
insertToLocalAlarmInfo.Remove(insertToLocalAlarmInfo.ToString().LastIndexOf(";"), 1);
string sqlAlarm = insertToLocalAlarmInfo.ToString();
local.Database.ExecuteSqlCommand(sqlAlarm);
}

StringBuilder insertToLocalAlarmPreprocessInfo = new StringBuilder();
var importAlarmPreprocessInfoData = (from n in server.tbl_AlarmPreprocessInfo where n.PreProcessTime >= searchTime && n.PreProcessTime <= newserverTime select n).ToList();
if (importAlarmPreprocessInfoData.Count >0)
{
for (int i = 0; i < importAlarmPreprocessInfoData.Count(); i++)
{
insertToLocalAlarmPreprocessInfo.Append("insert into tbl_AlarmPreprocessInfo(AlarmID,Details,Operator,PreProcessID,PreProcessTime,ProcessingComputerIP,Remark) Values('"
+ importAlarmPreprocessInfoData[i].AlarmID + "','"
+ importAlarmPreprocessInfoData[i].Details + "','"
+ importAlarmPreprocessInfoData[i].Operator + "','"
+ importAlarmPreprocessInfoData[i].PreProcessID + "','"
+ importAlarmPreprocessInfoData[i].PreProcessTime + "','"
+ importAlarmPreprocessInfoData[i].ProcessingComputerIP + "','"
+ importAlarmPreprocessInfoData[i].Remark + "');");
}

insertToLocalAlarmPreprocessInfo.Remove(insertToLocalAlarmPreprocessInfo.ToString().LastIndexOf(";"), 1);
string sqlpreAlarm = insertToLocalAlarmPreprocessInfo.ToString();
local.Database.ExecuteSqlCommand(sqlpreAlarm);
}
success = true;
}
catch (Exception ex)
{
LogHelper.WriteError("同步数据失败", ex);

success = false;

}

return success;

}
}
}