public static DBConnectionController InitSystemConnection(Guid serverID)
{
STDataServersController serverCtrl = new STDataServersController();
STDataServersInfo server = serverCtrl.GetObjectByID(serverID) as STDataServersInfo;
if (server == null)
{
return(null);
}
if (SystemConnections.ContainsKey(serverID))
{
if (SystemConnections[serverID].Connection.TestConnection())
{
return(SystemConnections[serverID]);
}
}
DBConnectionController connection = new DBConnectionController(server.Name, server.Name);
connection.InitNewConnection(server.Name, DatabaseType.MSSQL, server.ServerAddress, server.SystemDatabase, server.UserName, server.EncryptedPassword, false);
connection.Connect();
if (connection.Connection.TestConnection())
{
SystemConnections.Add(serverID, connection);
return(connection);
}
return(null);
}
public static bool Connect(String strCompanyName)
{
#region Connect
DBConnectionController system = null;
DBConnectionController company = ConnectCompany(strCompanyName);
if (company != null)
{
system = ConnectSystem();
}
if (company == null || system == null)
{
return(false);
}
CompanyConnection = company;
SystemConnection = system;
if (IsCompanySQLConnection)
{
CompanyDatabaseHelper = new SqlDatabaseHelper(CompanyDatabase);
}
else
{
CompanyDatabaseHelper = new SQLiteDatabaseHelper(CompanyDatabase);
}
if (IsSystemSQLConnection)
{
SystemDatabaseHelper = new SqlDatabaseHelper(SystemDatabase);
}
else
{
SystemDatabaseHelper = new SQLiteDatabaseHelper(SystemDatabase);
}
#endregion
if (TestCompanyConnection() && TestSystemConnection())
{
return(true);
}
return(false);
}
public static void InitConnectionsFromXML( )
{
CompanyCollection.Clear();
SystemCollection.Clear();
String strFileName = @"Config.xml";
XmlDocument doc = new XmlDocument();
doc.Load(strFileName);
#region Companys
XmlNodeList nodeCompanyList = doc.GetElementsByTagName("Company");
foreach (XmlNode nodeCompany in nodeCompanyList)
{
String strCompany = nodeCompany.Attributes["Name"].Value.ToString();
String strDesc = nodeCompany.Attributes["Desc"].Value.ToString();
DBConnectionController company = new DBConnectionController(strCompany, strDesc);
#region Connection
DatabaseType DBType = (DatabaseType)Enum.Parse(typeof(DatabaseType), nodeCompany.Attributes["DBType"].Value.ToString());
String strServer = nodeCompany.Attributes["Server"].Value.ToString();
String strDatabase = nodeCompany.Attributes["Database"].Value.ToString();
String strUser = nodeCompany.Attributes["User"].Value.ToString();
String strPassword = nodeCompany.Attributes["Password"].Value.ToString();
bool isDefault = Convert.ToBoolean(nodeCompany.Attributes["IsDefault"].Value.ToString());
company.InitNewConnection(strCompany, DBType, strServer, strDatabase, strUser, strPassword, isDefault);
#endregion
if (CompanyCollection.ContainsKey(strCompany) == false)
{
CompanyCollection.Add(strCompany, company);
}
}
#endregion
#region System
XmlNodeList nodeSystemList = doc.GetElementsByTagName("System");
foreach (XmlNode nodeSystem in nodeSystemList)
{
String strSystem = nodeSystem.Attributes["Name"].Value.ToString();
String strDesc = nodeSystem.Attributes["Desc"].Value.ToString();
DBConnectionController System = new DBConnectionController(strSystem, strDesc);
#region Connection
DatabaseType DBType = (DatabaseType)Enum.Parse(typeof(DatabaseType), nodeSystem.Attributes["DBType"].Value.ToString());
String strServer = nodeSystem.Attributes["Server"].Value.ToString();
String strDatabase = nodeSystem.Attributes["Database"].Value.ToString();
String strUser = nodeSystem.Attributes["User"].Value.ToString();
String strPassword = nodeSystem.Attributes["Password"].Value.ToString();
bool isDefault = Convert.ToBoolean(nodeSystem.Attributes["IsDefault"].Value.ToString());
System.InitNewConnection(strSystem, DBType, strServer, strDatabase, strUser, strPassword, isDefault);
#endregion
if (SystemCollection.ContainsKey(strSystem) == false)
{
SystemCollection.Add(strSystem, System);
}
}
#endregion
}
public static void Synchronize(STDataServerSyncsInfo syncInfo)
{
if (!syncInfo.FK_STDataServerID.HasValue)
{
return;
}
if (!syncInfo.IsPull && !syncInfo.IsPush)
{
return;
}
if (syncInfo.LastSyncDate.HasValue && syncInfo.IntervalMinute.HasValue &&
(DateTime.Now < syncInfo.LastSyncDate.Value || DateTime.Now.Subtract(syncInfo.LastSyncDate.Value).TotalMinutes < syncInfo.IntervalMinute.Value))
{
return;
}
STDataServersController serverCtrl = new STDataServersController();
STDataServersInfo server = serverCtrl.GetObjectByID(syncInfo.FK_STDataServerID.Value) as STDataServersInfo;
if (server == null)
{
return;
}
DBConnectionController connection = null;
if (DataStructureProvider.IsSystemTable(syncInfo.TableName))
{
connection = InitSystemConnection(server.STDataServerID);
}
else
{
connection = InitCompanyConnection(server.STDataServerID);
}
if (connection != null)
{
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
String strRunQuery = @"SET XACT_ABORT ON;BEGIN TRANSACTION ABCSYNC; ";
String strQuery = String.Empty;
if (syncInfo.IsPush)
{
if (syncInfo.IsPushMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [{0}].[{1}].[dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"INSERT INTO [{0}].[{1}].[dbo].[{2}] SELECT * FROM [dbo].[{2}] WHERE [dbo].[{2}].[{3}] IN ({4}) AND [dbo].[{2}].[{3}] NOT IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"UPDATE [{0}].[{1}].[dbo].[{2}] SET #FIELD# FROM [{0}].[{1}].[dbo].[{2}] Target,[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
if (syncInfo.IsPull)
{
if (syncInfo.IsPullMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [{0}].[{1}].[dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"INSERT INTO [dbo].[{2}] SELECT * FROM [{0}].[{1}].[dbo].[{2}] WHERE [{3}] IN ({4}) AND [{3}] NOT IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"UPDATE [dbo].[{2}] SET #FIELD# FROM [dbo].[{2}] Target,[{0}].[{1}].[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
strRunQuery = String.Format(@"{0} COMMIT TRANSACTION ABCSYNC; SET XACT_ABORT OFF;", strRunQuery + Environment.NewLine);
BusinessObjectController.RunQuery(strRunQuery, syncInfo.TableName);
BusinessObjectController.RunQuery(String.Format(@"UPDATE STDataServerSyncs SET LastSyncDate =GetDate() WHERE STDataServerSyncID='{0}'", syncInfo.GetID()), syncInfo.AATableName);
scope.Complete();
}
}
}
