public void InsertOrUpdateRows(Range range, DataTable workbookTable, SyncDefinition syncDefinition, Settings settings) { if (workbookTable.Rows.Count < 1) { return; } var query = GetQuery(range, DownloadType.Full, syncDefinition, settings); var tableInfo = databaseInfo.TableInfos[range.FullTableName]; var primaryColumnInfos = tableInfo.PrimaryColumnInfos(); using (var tableContext = databaseContext.GetTableContext(query)) { foreach (DataRow sourceRow in workbookTable.Rows) { DataRow TargetRow = SearchRow(tableContext.DataTable, primaryColumnInfos, sourceRow); if (TargetRow == null) { TargetRow = tableContext.DataTable.NewRow(); CopyRow(sourceRow, TargetRow, tableInfo, false); tableContext.DataTable.Rows.Add(TargetRow); } else { CopyRow(sourceRow, TargetRow, tableInfo, true); } } tableContext.Update(); } }
private void RefreshSettings(SyncDefinition syncDefinition, Settings settings) { var systemData = new SystemData(); var consolidatedSettings = syncDefinition.Settings == null ? new Settings() : syncDefinition.Settings.Clone(); foreach (var setting in settings) { if (consolidatedSettings.Contains(setting.Name)) { consolidatedSettings[setting.Name].Value = setting.Value; } else { consolidatedSettings.Add(setting.Clone()); } } foreach (var setting in consolidatedSettings) { var newRow = systemData.SettingDT.NewRow(); newRow[Constants.Name] = setting.Name; newRow[Constants.Value] = setting.Value; systemData.SettingDT.Rows.Add(newRow); } CopyTableToRange(systemData.Setting, systemData.SettingDT); }
public void Download(string connectionStringOrName, string tableNames) { var names = tableNames.Split(','); var syncDefinition = new SyncDefinition(names); Download(connectionStringOrName, syncDefinition: syncDefinition); }
private DataTable DownloadTable(Range range, DownloadType downloadType, SyncDefinition syncDefinition, Settings settings) { var query = GetQuery(range, downloadType, syncDefinition, settings); using (var tableContext = databaseContext.GetTableContext(query)) return(tableContext.DataTable); }
public void EmbedDefinition(string connectionStringOrName, string tableNames, bool fullDefinition = false) { var names = tableNames.Split(','); var syncDefinition = new SyncDefinition(names); EmbedDefinition(connectionStringOrName, syncDefinition: syncDefinition, fullDefinition: fullDefinition); }
public DatabaseManager(ConnectionInfo connectionInfo, SyncDefinition syncDefinition, bool startTransaction) { databaseInfo = new DatabaseInfo(connectionInfo, syncDefinition.TablesOfInterest); ExecutableRanges = new Ranges(); foreach (var range in syncDefinition.Ranges) { var executableRange = range.Clone(); if (!executableRange.HasColumns) { var tableInfo = databaseInfo.TableInfos[executableRange.FullTableName]; var columns = new Columns(); foreach (var columnInfo in tableInfo.ColumnInfos) { columns.Add(new Column() { Name = columnInfo.ColumnName }); } executableRange.Columns = columns; } ExecutableRanges.Add(executableRange); } databaseContext = new DatabaseContext(connectionInfo.ConnectionString, startTransaction); }
private QueryBuilder GetQuery(Range range, DownloadType downloadType, SyncDefinition syncDefinition, Settings settings) { TableInfo tableInfo = databaseInfo.TableInfos[range.FullTableName]; var queryBuilder = new QueryBuilder(); queryBuilder.Append("select "); AppendColumns(range, queryBuilder); queryBuilder.Append(" from "); queryBuilder.Append(tableInfo.FullTableName); if (downloadType == DownloadType.OnlyStructure) { queryBuilder.Append(" where 1=0"); } else if (range.HasCondition) { AppendCondition(range, syncDefinition, settings, queryBuilder); } if (range.HasOrder) { AppendOrder(range, queryBuilder); } return(queryBuilder); }
public void Upload(string connectionStringOrName, SyncDefinition syncDefinition = null, Settings settings = null, bool removeMissingRows = false) { if (syncDefinition == null) { syncDefinition = GetDefinition(); } var connectionInfo = connections.GetConnectionInfo(connectionStringOrName); using (var databaseManager = new DatabaseManager(connectionInfo, syncDefinition, true)) { var ranges = databaseManager.ExecutableRanges; try { var workbookTables = new Dictionary <string, DataTable>(); foreach (var range in ranges) { databaseManager.UploadChecks(range); var workbookTable = databaseManager.DownloadEmptyTableStructure(range, syncDefinition); CopyRangeToTable(range, workbookTable); workbookTables.Add(range.FullTableName, workbookTable); } if (removeMissingRows) { foreach (var fullTableName in databaseManager.FullTableNamesDependantToIndependant(ranges)) { var workbookTable = workbookTables[fullTableName]; var range = ranges.SearchByFullTableName(fullTableName); databaseManager.RemoveMissingRows(range, workbookTable, syncDefinition, settings); } } foreach (var fullTableName in databaseManager.FullTableNamesIndependantToDependant(ranges)) { var workbookTable = workbookTables[fullTableName]; var range = ranges.SearchByFullTableName(fullTableName); databaseManager.InsertOrUpdateRows(range, workbookTable, syncDefinition, settings); } databaseManager.Commit(); } catch { databaseManager.Rollback(); throw; } } }
public bool Resize(string connectionStringOrName, SyncDefinition syncDefinition = null) { var hasChanged = false; if (syncDefinition == null) { hasChanged = ResizeDefinition(); syncDefinition = GetDefinition(); } var connectionInfo = connections.GetConnectionInfo(connectionStringOrName); using (var databaseManager = new DatabaseManager(connectionInfo, syncDefinition, false)) foreach (var range in databaseManager.ExecutableRanges) { var rangeChanges = ResizeRange(range); hasChanged = hasChanged || rangeChanges; } return(hasChanged); }
public void RemoveMissingRows(Range range, DataTable workbookTable, SyncDefinition syncDefinition, Settings settings) { var query = GetQuery(range, DownloadType.Full, syncDefinition, settings); var tableInfo = databaseInfo.TableInfos[range.FullTableName]; var primaryColumnInfos = tableInfo.PrimaryColumnInfos(); using (var tableContext = databaseContext.GetTableContext(query)) { var databaseTable = tableContext.DataTable; for (var rowIndex = databaseTable.Rows.Count - 1; rowIndex >= 0; rowIndex--) { var databaseRow = databaseTable.Rows[rowIndex]; var workbookRow = SearchRow(workbookTable, primaryColumnInfos, databaseRow); if (workbookRow == null) { databaseRow.Delete(); } } tableContext.Update(); } }
static SystemData() { SyncDefinition = new SyncDefinition() { Ranges = new Ranges() { new Range() { Name = Constants.TableSync_Range, Schema = Constants.TableSync, TableName = Constants.Ranges, Columns = new Columns() { new Column() { Name = Constants.RangeName }, new Column() { Name = Constants.Schema }, new Column() { Name = Constants.TableName }, new Column() { Name = Constants.Orientation } } }, new Range() { Name = Constants.TableSync_Column, Schema = Constants.TableSync, TableName = Constants.Columns, Columns = new Columns() { new Column() { Name = Constants.RangeName }, new Column() { Name = Constants.ColumnName }, new Column() { Name = Constants.Title }, new Column() { Name = Constants.NumberFormat }, new Column() { Name = Constants.CustomNumberFormat } } }, new Range() { Name = Constants.TableSync_Order, Schema = Constants.TableSync, TableName = Constants.Order, Columns = new Columns() { new Column() { Name = Constants.RangeName }, new Column() { Name = Constants.ColumnName }, new Column() { Name = Constants.Direction } } }, new Range() { Name = Constants.TableSync_Condition, Schema = Constants.TableSync, TableName = Constants.Condition, Columns = new Columns() { new Column() { Name = Constants.RangeName }, new Column() { Name = Constants.ColumnName }, new Column() { Name = Constants.Operator }, new Column() { Name = Constants.Value }, new Column() { Name = Constants.OperatorTemplate } } }, new Range() { Name = Constants.TableSync_Setting, Schema = Constants.TableSync, TableName = Constants.Settings, Columns = new Columns() { new Column() { Name = Constants.Name }, new Column() { Name = Constants.Value } } }, new Range() { Name = Constants.TableSync_Help, Schema = Constants.TableSync, TableName = Constants.Help, Columns = new Columns() { new Column() { Name = Constants.RangeName }, new Column() { Name = Constants.ColumnName }, new Column() { Name = Constants.Description } } } } }; }
public void EmbedDefinition(string connectionStringOrName, SyncDefinition syncDefinition, bool fullDefinition = false) { if (syncDefinition == null) { syncDefinition = GetDefinition(); } var systemData = new SystemData(); var connectionInfo = connections.GetConnectionInfo(connectionStringOrName); using (var databaseManager = new DatabaseManager(connectionInfo, syncDefinition, false)) { DataRow newRow; foreach (var syncRange in syncDefinition.Ranges) { var range = fullDefinition ? databaseManager.ExecutableRanges[syncRange.Name] : syncRange; newRow = systemData.RangeDT.NewRow(); newRow[Constants.RangeName] = range.Name; newRow[Constants.Schema] = range.Schema; newRow[Constants.TableName] = range.TableName; newRow[Constants.Orientation] = range.Orientation; systemData.RangeDT.Rows.Add(newRow); if (range.HasColumns) { foreach (var item in range.Columns) { newRow = systemData.ColumnsDT.NewRow(); newRow[Constants.RangeName] = range.Name; newRow[Constants.ColumnName] = item.Name; newRow[Constants.Title] = item.Title; newRow[Constants.NumberFormat] = item.NumberFormat.ToString(); newRow[Constants.CustomNumberFormat] = item.CustomNumberFormat; systemData.ColumnsDT.Rows.Add(newRow); } } if (range.HasOrder) { foreach (var item in range.Order) { newRow = systemData.OrderDT.NewRow(); newRow[Constants.RangeName] = range.Name; newRow[Constants.ColumnName] = item.Name; newRow[Constants.Direction] = item.Direction.ToString(); systemData.OrderDT.Rows.Add(newRow); } } if (range.HasCondition) { foreach (var item in range.Condition) { newRow = systemData.ConditionDT.NewRow(); newRow[Constants.RangeName] = range.Name; newRow[Constants.ColumnName] = item.Name; newRow[Constants.Operator] = item.Operator.ToString(); newRow[Constants.Value] = item.Value; newRow[Constants.OperatorTemplate] = item.OperatorTemplate; systemData.ConditionDT.Rows.Add(newRow); } } } if (syncDefinition.HasSettings) { foreach (var item in syncDefinition.Settings) { newRow = systemData.SettingDT.NewRow(); newRow[Constants.Name] = item.Name; newRow[Constants.Value] = item.Value; systemData.SettingDT.Rows.Add(newRow); } } if (systemData.RangeDT.Rows.Count > 0 || fullDefinition) { CopyTableToRange(systemData.Range, systemData.RangeDT); } else if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Range)) { excelPackage.Workbook.Names.Remove(Constants.TableSync_Range); } if (systemData.ColumnsDT.Rows.Count > 0 || fullDefinition) { CopyTableToRange(systemData.Column, systemData.ColumnsDT); } else if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Column)) { excelPackage.Workbook.Names.Remove(Constants.TableSync_Column); } if (systemData.OrderDT.Rows.Count > 0 || fullDefinition) { CopyTableToRange(systemData.Order, systemData.OrderDT); } else if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Order)) { excelPackage.Workbook.Names.Remove(Constants.TableSync_Order); } if (systemData.ConditionDT.Rows.Count > 0 || fullDefinition) { CopyTableToRange(systemData.Condition, systemData.ConditionDT); } else if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Condition)) { excelPackage.Workbook.Names.Remove(Constants.TableSync_Condition); } if (systemData.SettingDT.Rows.Count > 0 || fullDefinition) { CopyTableToRange(systemData.Setting, systemData.SettingDT); } else if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Setting)) { excelPackage.Workbook.Names.Remove(Constants.TableSync_Setting); } if (fullDefinition) { CopyTableToRange(systemData.Help, systemData.HelpDT); } } }
public void Download(string connectionStringOrName, bool keepFormula = false, SyncDefinition syncDefinition = null, Settings settings = null) { if (syncDefinition == null) { syncDefinition = GetDefinition(); } var connectionInfo = connections.GetConnectionInfo(connectionStringOrName); using (var databaseManager = new DatabaseManager(connectionInfo, syncDefinition, false)) { foreach (var range in databaseManager.ExecutableRanges) { var dataTable = databaseManager.DownloadTable(range, syncDefinition, settings); CopyTableToRange(range, dataTable, keepFormula); } } if (settings != null) { RefreshSettings(syncDefinition, settings); } excelPackage.Workbook.Calculate(); }
public DataTable DownloadEmptyTableStructure(Range range, SyncDefinition syncDefinition) { return(DownloadTable(range, DownloadType.OnlyStructure, syncDefinition, null)); }
public DataTable DownloadTable(Range range, SyncDefinition syncDefinition, Settings settings) { return(DownloadTable(range, DownloadType.Full, syncDefinition, settings)); }
public SyncDefinition GetDefinition() { if (!excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Range)) { throw new MissingSyncDefinitionException(); } var systemData = new SystemData(); CopyRangeToTable(systemData.Range, systemData.RangeDT); if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Column)) { CopyRangeToTable(systemData.Column, systemData.ColumnsDT); } if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Order)) { CopyRangeToTable(systemData.Order, systemData.OrderDT); } if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Condition)) { CopyRangeToTable(systemData.Condition, systemData.ConditionDT); } if (excelPackage.Workbook.Names.ContainsKey(Constants.TableSync_Setting)) { CopyRangeToTable(systemData.Setting, systemData.SettingDT); } var result = new SyncDefinition() { Ranges = new Ranges() }; foreach (DataRow row in systemData.RangeDT.Rows) { result.Ranges.Add(new Range() { Name = row[Constants.RangeName].ToString(), Schema = row[Constants.Schema] == DBNull.Value ? null : row[Constants.Schema].ToString(), TableName = row[Constants.TableName] == DBNull.Value ? null : row[Constants.TableName].ToString(), Orientation = (Orientation)Enum.Parse(typeof(Orientation), row[Constants.Orientation].ToString()) }); } foreach (DataRow row in systemData.ColumnsDT.Rows) { var rangeName = row[Constants.RangeName].ToString(); var range = result.Ranges[rangeName]; if (!range.HasColumns) { range.Columns = new Columns(); } range.Columns.Add(new Column() { Name = row[Constants.ColumnName].ToString(), Title = row[Constants.Title] == DBNull.Value ? null : row[Constants.Title].ToString(), NumberFormat = (NumberFormat)Enum.Parse(typeof(NumberFormat), row[Constants.NumberFormat].ToString()), CustomNumberFormat = row[Constants.CustomNumberFormat] == DBNull.Value ? null : row[Constants.CustomNumberFormat].ToString(), }); } foreach (DataRow row in systemData.OrderDT.Rows) { var rangeName = row[Constants.RangeName].ToString(); var range = result.Ranges[rangeName]; if (!range.HasOrder) { range.Order = new Order(); } range.Order.Add(new OrderItem() { Name = row[Constants.ColumnName].ToString(), Direction = (OrderDirection)Enum.Parse(typeof(OrderDirection), row[Constants.Direction].ToString()), }); } foreach (DataRow row in systemData.ConditionDT.Rows) { var rangeName = row[Constants.RangeName].ToString(); var range = result.Ranges[rangeName]; if (!range.HasCondition) { range.Condition = new Condition(); } range.Condition.Add(new ConditionItem() { Name = row[Constants.ColumnName].ToString(), Operator = (ConditionOperator)Enum.Parse(typeof(ConditionOperator), row[Constants.Operator].ToString()), Value = row[Constants.Value] == DBNull.Value ? null : row[Constants.Value], OperatorTemplate = row[Constants.OperatorTemplate] == DBNull.Value ? null : row[Constants.OperatorTemplate].ToString(), }); } foreach (DataRow row in systemData.SettingDT.Rows) { if (result.Settings == null) { result.Settings = new Settings(); } result.Settings.Add(new Setting() { Name = row[Constants.Name].ToString(), Value = row[Constants.Value] == DBNull.Value ? null : row[Constants.Value], }); } return(result); }
private void AppendCondition(Range range, SyncDefinition syncDefinition, Settings settings, QueryBuilder queryBuilder) { int parameterIndex = 0; var tableInfo = databaseInfo.TableInfos[range.FullTableName]; foreach (var item in range.Condition) { parameterIndex++; if (parameterIndex == 1) { queryBuilder.Append(" where "); } else { queryBuilder.Append(" and "); } var fieldName = string.Format("[{0}]", item.Name); var operatorTemplate = string.Empty; switch (item.Operator) { case ConditionOperator.Equal: operatorTemplate = "{0}={1}"; break; case ConditionOperator.Unequal: operatorTemplate = "{0}<>{1}"; break; case ConditionOperator.GreaterThan: operatorTemplate = "{0}>{1}"; break; case ConditionOperator.GreaterThanOrEqual: operatorTemplate = "{0}>={1}"; break; case ConditionOperator.LessThan: operatorTemplate = "{0}<{1}"; break; case ConditionOperator.LessThanOrEqual: operatorTemplate = "{0}<={1}"; break; case ConditionOperator.Like: operatorTemplate = "{0} like {1}"; break; case ConditionOperator.Template: operatorTemplate = item.OperatorTemplate; break; default: throw new InvalidEnumArgumentException(); } var columnInfo = tableInfo.ColumnInfos[item.Name]; var value = item.Value; if (value != null && value.GetType() == typeof(string)) { var parts = ((string)item.Value).Split('$'); if (parts.Length == 2 && string.IsNullOrEmpty(parts[0])) { var settingName = parts[1]; var settingNameInSettings = settings != null && settings.Contains(settingName); var settingNameInDefinitionSettings = syncDefinition.Settings != null && syncDefinition.Settings.Contains(settingName); if (!settingNameInSettings && !settingNameInDefinitionSettings) { throw new MissingSettingException(settingName); } value = settingNameInSettings ? settings[settingName].Value : syncDefinition.Settings[settingName].Value; } } if (string.Compare(columnInfo.ColumnType, "System.DateTime", true) == 0 && value is string) { value = DateTime.Parse(value.ToString()); } var parameterName = $"@value{parameterIndex}"; queryBuilder.Append(string.Format(operatorTemplate, fieldName, parameterName)); queryBuilder.Parameters.Add(new QueryParameter() { Name = parameterName, Value = value }); } }