public static void SynchronizeFieldPermission( ) { GEPermissionFieldsController permissionCtrl = new GEPermissionFieldsController(); String strQuery = String.Format(@"DELETE FROM GEPermissionFields WHERE FK_GEPermissionID NOT IN (SELECT GEPermissionID FROM GEPermissions)"); BusinessObjectController.RunQuery(strQuery); foreach (GEPermissionsInfo permission in new GEPermissionsController().GetListAllObjects()) { #region Field Dictionary <String, GEPermissionFieldsInfo> lstFields = new Dictionary <string, GEPermissionFieldsInfo>(); foreach (GEPermissionFieldsInfo fieldInfo in permissionCtrl.GetListByForeignKey("FK_GEPermissionID", permission.GEPermissionID).Cast <GEPermissionFieldsInfo>().ToList()) { if (lstFields.ContainsKey(fieldInfo.FieldName) == false) { String strTableCaption = DataConfigProvider.GetTableCaption(fieldInfo.TableName); String strFieldCaption = DataConfigProvider.GetFieldCaption(fieldInfo.TableName, fieldInfo.FieldName); if (DataStructureProvider.IsTableColumn(fieldInfo.TableName, fieldInfo.FieldName) && strTableCaption != String.Empty && strTableCaption != fieldInfo.TableName && strFieldCaption != String.Empty && strFieldCaption != fieldInfo.FieldName) { lstFields.Add(fieldInfo.TableName + fieldInfo.FieldName, fieldInfo); } else { permissionCtrl.DeleteObject(fieldInfo); } } } foreach (String strTableName in DataStructureProvider.DataTablesList.Keys) { foreach (String strFieldName in DataStructureProvider.DataTablesList[strTableName].ColumnsList.Keys) { if (DataStructureProvider.IsPrimaryKey(strTableName, strFieldName)) { continue; } if (lstFields.ContainsKey(strTableName + strFieldName) == false) { GEPermissionFieldsInfo fieldInfo = new GEPermissionFieldsInfo(); fieldInfo.FK_STFieldConfigID = DataConfigProvider.TableConfigList[strTableName].FieldConfigList[strFieldName].ConfigID; fieldInfo.FK_STTableConfigID = DataConfigProvider.TableConfigList[strTableName].ConfigID; fieldInfo.TableName = strTableName; fieldInfo.FieldName = strFieldName; fieldInfo.FK_GEPermissionID = permission.GEPermissionID; fieldInfo.AllowView = true; fieldInfo.AllowEdit = true; permissionCtrl.CreateObject(fieldInfo); lstFields.Add(fieldInfo.TableName + fieldInfo.FieldName, fieldInfo); } } } #endregion } }
public static void InvalidateConfigList( ) { String strCheckCondition = String.Empty; if (DataQueryProvider.IsSQLConnection("STTableConfigs")) { strCheckCondition = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='STTableConfigs'"; } else { strCheckCondition = "SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name='STTableConfigs'"; } DataSet dsTemp = DataQueryProvider.RunQuery(strCheckCondition); if (dsTemp == null || dsTemp.Tables.Count <= 0 || dsTemp.Tables[0].Rows.Count <= 0) { return; } TableConfigList.Clear(); #region Init ConfigList #region Field Config DataSet ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(@"SELECT * FROM STFieldConfigs"); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { TableConfig tableConfig = null; String strTableName = dr["TableName"].ToString(); if (TableConfigList.TryGetValue(strTableName, out tableConfig) == false) { tableConfig = new TableConfig(); TableConfigList.Add(strTableName, tableConfig); } if (tableConfig.FieldConfigList.ContainsKey(dr["FieldName"].ToString()) == false) { #region Field FieldConfig config = new FieldConfig(); config.ConfigID = ABCHelper.DataConverter.ConvertToGuid(dr["STFieldConfigID"]); config.FieldName = dr["FieldName"].ToString(); config.CaptionVN = dr["CaptionVN"].ToString(); config.CaptionEN = dr["CaptionEN"].ToString(); config.DescVN = dr["DescriptionVN"].ToString(); config.DescEN = dr["DescriptionEN"].ToString(); config.TypeName = DataStructureProvider.GetCodingType(strTableName, config.FieldName); config.InUse = false; if (dr["InUse"] != DBNull.Value) { config.InUse = Convert.ToBoolean(dr["InUse"]); } if (dr["IsDefault"] != DBNull.Value) { config.IsDefault = Convert.ToBoolean(dr["IsDefault"]); } if (dr["IsDisplayField"] != DBNull.Value) { config.IsDisplayField = Convert.ToBoolean(dr["IsDisplayField"]); } if (dr["IsGrouping"] != DBNull.Value) { config.IsGrouping = Convert.ToBoolean(dr["IsGrouping"]); } if (dr["AssignedEnum"] != DBNull.Value) { config.AssignedEnum = dr["AssignedEnum"].ToString(); } if (dr["FilterString"] != DBNull.Value) { config.FilterString = dr["FilterString"].ToString(); } if (dr["SortOrder"] != DBNull.Value) { config.SortOrder = Convert.ToInt32(dr["SortOrder"]); } #region FieldFormat if (Enum.IsDefined(typeof(DataFormatProvider.FieldFormat), dr["Format"].ToString())) { DataFormatProvider.FieldFormat format = (DataFormatProvider.FieldFormat)Enum.Parse(typeof(DataFormatProvider.FieldFormat), dr["Format"].ToString()); if (format != DataFormatProvider.FieldFormat.None) { config.Format = format; } else { if (config.TypeName == "DateTime" || config.TypeName == "Nullable<DateTime>") { config.Format = DataFormatProvider.FieldFormat.Date; } if ((config.TypeName == "int" || config.TypeName == "Nullable<int>") && config.FieldName.ToUpper().Contains("FK_") == false && DataStructureProvider.IsForeignKey(strTableName, config.FieldName) == false && DataStructureProvider.IsPrimaryKey(strTableName, config.FieldName) == false) { config.Format = DataFormatProvider.FieldFormat.Quantity; } if (config.TypeName == "double" || config.TypeName == "decimal") { if (config.FieldName.ToLower().Contains("amt") || config.FieldName.ToLower().Contains("amount") || config.FieldName.ToLower().Contains("exchange")) { config.Format = DataFormatProvider.FieldFormat.Currency; } else { config.Format = DataFormatProvider.FieldFormat.Amount; } } } } #endregion tableConfig.FieldConfigList.Add(config.FieldName, config); #endregion } } } #endregion #region Table Config ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(@"SELECT * FROM STTableConfigs"); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { TableConfig tableConfig = null; String strTableName = dr["TableName"].ToString(); if (TableConfigList.TryGetValue(strTableName, out tableConfig)) { tableConfig.ConfigID = ABCHelper.DataConverter.ConvertToGuid(dr["STTableConfigID"]); tableConfig.TableName = strTableName; tableConfig.CaptionVN = dr["CaptionVN"].ToString(); tableConfig.CaptionEN = dr["CaptionEN"].ToString(); tableConfig.DescVN = dr["DescriptionVN"].ToString(); tableConfig.DescEN = dr["DescriptionEN"].ToString(); if (dr["IsCaching"] != DBNull.Value) { tableConfig.IsCaching = Convert.ToBoolean(dr["IsCaching"]); } else { tableConfig.IsCaching = false; } } } } #endregion #endregion }
public static void GenerateDefaultTableConfig( ) { String strCheckCondition = String.Empty; if (DataQueryProvider.IsSQLConnection("STTableConfigs")) { strCheckCondition = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='STTableConfigs'"; } else { strCheckCondition = "SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name='STTableConfigs'"; } DataSet dsTemp = DataQueryProvider.RunQuery(strCheckCondition); if (dsTemp == null || dsTemp.Tables.Count <= 0 || dsTemp.Tables[0].Rows.Count <= 0) { return; } #region Clean First DataSet ds2 = DataQueryProvider.SystemDatabaseHelper.RunQuery("SELECT STTableConfigID,TableName FROM STTableConfigs "); if (ds2 != null && ds2.Tables.Count > 0) { foreach (DataRow dr in ds2.Tables[0].Rows) { if (String.IsNullOrWhiteSpace(dr[0].ToString()) == false) { Guid iID = Guid.Empty; Guid.TryParse(dr[0].ToString(), out iID); String strTableName = dr[1].ToString(); if (DataStructureProvider.DataTablesList.ContainsKey(strTableName) == false) { DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STTableConfigs WHERE STTableConfigID='{0}'", iID.ToString())); } } } } ds2 = DataQueryProvider.SystemDatabaseHelper.RunQuery("SELECT STFieldConfigID,TableName,FieldName FROM STFieldConfigs "); if (ds2 != null && ds2.Tables.Count > 0) { foreach (DataRow dr in ds2.Tables[0].Rows) { if (String.IsNullOrWhiteSpace(dr[0].ToString()) == false) { Guid iID = Guid.Empty; Guid.TryParse(dr[0].ToString(), out iID); String strTableName = dr[1].ToString(); String strFieldName = dr[2].ToString(); if (DataStructureProvider.DataTablesList.ContainsKey(strTableName) == false || DataStructureProvider.DataTablesList[strTableName].ColumnsList.ContainsKey(strFieldName) == false) { DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STFieldConfigs WHERE STFieldConfigID='{0}'", iID.ToString())); } } } } #endregion foreach (String strTableName in DataStructureProvider.DataTablesList.Keys) { #region Table Config DataSet ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("SELECT COUNT(*) FROM STTableConfigs WHERE TableName='{0}'", strTableName)); if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0 || Convert.ToInt32(ds.Tables[0].Rows[0][0]) <= 0) { String strQuery = QueryTemplateGenerator.GenInsert("STTableConfigs"); strQuery = strQuery.Replace("@STTableConfigID", "'" + Guid.Empty.ToString() + "'"); strQuery = strQuery.Replace("@TableName", "'" + strTableName + "'"); String s = strTableName.Substring(2, strTableName.Length - 2); //var r=new System.Text.RegularExpressions.Regex( @"(?<=[A-Z])(?=[A-Z][a-z]) |(?<=[^A-Z])(?=[A-Z]) |(?<=[A-Za-z])(?=[^A-Za-z])" , System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace ); //s=r.Replace( s , " " ); strQuery = strQuery.Replace("@CaptionVN", "'" + s + "'"); strQuery = strQuery.Replace("@CaptionEN", "'" + s + "'"); strQuery = strQuery.Replace("@DescriptionVN", "''"); strQuery = strQuery.Replace("@DescriptionEN", "''"); strQuery = strQuery.Replace("@IsCaching", "0"); strQuery = strQuery.Replace("@CalcOnClient", "1"); strQuery = strQuery.Replace("@DeleteByOwnerOnly", "0"); strQuery = strQuery.Replace("@EditByOwnerOnly", "0"); DataQueryProvider.SystemDatabaseHelper.RunScript(strQuery); } #endregion #region Field Config List <String> lstConfig = new List <String>(); ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("SELECT STFieldConfigID,FieldName FROM STFieldConfigs WHERE TableName='{0}'", strTableName)); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { if (String.IsNullOrWhiteSpace(dr[1].ToString()) == false) { if (lstConfig.Contains(dr[1].ToString()) == false) { lstConfig.Add(dr[1].ToString()); } else { Guid iID = Guid.Empty; Guid.TryParse(dr[0].ToString(), out iID); DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STFieldConfigs WHERE STFieldConfigID='{0}'", strTableName)); } } } } int iCount = -1; foreach (String strColName in DataStructureProvider.DataTablesList[strTableName].ColumnsList.Keys) { if (DataStructureProvider.IsPrimaryKey(strTableName, strColName)) { continue; } iCount++; if (lstConfig.Contains(strColName) == false) { String strQuery = QueryTemplateGenerator.GenInsert("STFieldConfigs"); Guid iID = Guid.NewGuid(); strQuery = strQuery.Replace("@STFieldConfigID", "'" + iID.ToString() + "'"); strQuery = strQuery.Replace("@TableName", "'" + strTableName + "'"); strQuery = strQuery.Replace("@FieldName", "'" + strColName + "'"); if (DataStructureProvider.IsForeignKey(strTableName, strColName)) { String strFKTableName = DataStructureProvider.GetTableNameOfForeignKey(strTableName, strColName); String strCaptionVN = GetTableCaption(strFKTableName); if (String.IsNullOrWhiteSpace(strCaptionVN)) { strCaptionVN = strFKTableName.Substring(2, strFKTableName.Length - 3); } //var r=new System.Text.RegularExpressions.Regex( @"(?<=[A-Z])(?=[A-Z][a-z]) |(?<=[^A-Z])(?=[A-Z]) |(?<=[A-Za-z])(?=[^A-Za-z])" , System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace ); //strCaptionVN=r.Replace( strCaptionVN , " " ); strQuery = strQuery.Replace("@CaptionVN", String.Format("N'{0}'", strCaptionVN)); strQuery = strQuery.Replace("@CaptionEN", "'" + strFKTableName.Substring(2, strFKTableName.Length - 3) + "'"); } else { strQuery = strQuery.Replace("@CaptionVN", String.Format("N'{0}'", GetFieldCaptionFromDictionary(strColName))); strQuery = strQuery.Replace("@CaptionEN", "'" + strColName + "'"); if (DataStructureProvider.IsNOColumn(strTableName, strColName)) { String strTableCap = GetTableCaption(strTableName); if (!String.IsNullOrWhiteSpace(strTableCap)) { strQuery = strQuery.Replace("@CaptionVN", String.Format("N'{0}'", strTableCap)); } } } strQuery = strQuery.Replace("@DescriptionVN", "''"); strQuery = strQuery.Replace("@DescriptionEN", "''"); if (strColName.Equals("ABCStatus") || strColName.Equals("EditCount") || strColName.Equals("CreateTime") || strColName.Equals("CreateUser") || strColName.Equals("UpdateTime") || strColName.Equals("UpdateUser") || strColName.Equals("NoIndex") || strColName.Equals("LastCalcDate")) { strQuery = strQuery.Replace("@InUse", "0"); } else { strQuery = strQuery.Replace("@InUse", "1"); } if (DataStructureProvider.IsNOColumn(strTableName, strColName) || DataStructureProvider.IsNAMEColumn(strTableName, strColName) || DataStructureProvider.IsDisplayColumn(strTableName, strColName)) { strQuery = strQuery.Replace("@IsDefault", "1"); } else { strQuery = strQuery.Replace("@IsDefault", "0"); } strQuery = strQuery.Replace("@AssignedEnum", "''"); strQuery = strQuery.Replace("@FilterString", "''"); strQuery = strQuery.Replace("@SortOrder", String.Format("{0}", iCount)); if (DataStructureProvider.IsNOColumn(strTableName, strColName) || DataStructureProvider.IsNAMEColumn(strTableName, strColName)) { strQuery = strQuery.Replace("@IsDisplayField", "1"); } else { strQuery = strQuery.Replace("@IsDisplayField", "0"); } strQuery = strQuery.Replace("@IsDisplayField", "0"); strQuery = strQuery.Replace("@IsGrouping", "0"); #region DataFormatProvider.FieldFormat String strTypeName = DataStructureProvider.GetCodingType(strTableName, strColName); DataFormatProvider.FieldFormat format = DataFormatProvider.FieldFormat.None; if (strTypeName == "DateTime" || strTypeName == "Nullable<DateTime>") { format = DataFormatProvider.FieldFormat.Date; } if ((strTypeName == "int" || strTypeName == "Nullable<int>" || strTypeName == "Guid" || strTypeName == "Nullable<Guid>") && strColName.ToUpper().Contains("FK_") == false && DataStructureProvider.IsForeignKey(strTableName, strColName) == false && DataStructureProvider.IsPrimaryKey(strTableName, strColName) == false) { format = DataFormatProvider.FieldFormat.Quantity; } if (strTypeName == "double" || strTypeName == "decimal") { if (strColName.ToLower().Contains("amt") || strColName.ToLower().Contains("fee") || strColName.ToLower().Contains("cost") || strColName.ToLower().Contains("expense") || strColName.ToLower().Contains("pay") || strColName.ToLower().Contains("tax") || strColName.ToLower().Contains("gross") || strColName.ToLower().Contains("net") || strColName.ToLower().Contains("amount") || strColName.ToLower().Contains("exchange")) { format = DataFormatProvider.FieldFormat.Currency; } else { format = DataFormatProvider.FieldFormat.Amount; } } if (format != DataFormatProvider.FieldFormat.None) { strQuery = strQuery.Replace("@Format", String.Format("'{0}'", format.ToString())); } else { strQuery = strQuery.Replace("@Format", "''"); } #endregion DataQueryProvider.SystemDatabaseHelper.RunScript(strQuery); } } foreach (String strColName in lstConfig) { if (DataStructureProvider.DataTablesList[strTableName].ColumnsList.ContainsKey(strColName) == false) { DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STFieldConfigs WHERE TableName='{0}' AND FieldName='{1}'", strTableName, strColName)); } } #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(); } } }