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