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(); } } }