public TableMapping GetTableMapping(SyncChange change)
{
var tableMap = Mappings.FirstOrDefault(m => m.SourceTable == change.TableName && m.SourceSchema == change.SchemaName);
if (tableMap == null)
{
return new TableMapping
{
SourceTable = change.TableName,
DestinationTable = change.TableName,
SourceSchema = string.Empty,
DestinationSchema = string.Empty
};
}
return tableMap;
}
public TableMapping GetTableMapping(SyncChange change)
{
var tableMap = Mappings.FirstOrDefault(m => m.SourceTable == change.TableName && m.SourceSchema == change.SchemaName);
if (tableMap == null)
{
return(new TableMapping
{
SourceTable = change.TableName,
DestinationTable = change.TableName,
SourceSchema = string.Empty,
DestinationSchema = string.Empty
});
}
return(tableMap);
}
private SQLQuery GetUpdateQuery(SyncChange change, List<Tuple<string, object>> data)
{
var tableMap = tableSet.GetTableMapping(change);
var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName);
var updates = tableInfo.Columns.Where(c => !c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName);
var sql = "update " + tableMap.FullyQualifiedDestinationTable + " set " + string.Join(", ", updates) + " where " + string.Join(" AND ", whereColumns) + ";";
var query = new SQLQuery(sql, SQLQueryType.NonQuery);
AddColumnParametersToQuery(query, tableInfo.Columns, data);
return query;
}
private SQLQuery GetQueryForChange(SyncChange change, List<Tuple<string, object>> data)
{
if (change.Operation == Operation.insert)
{
return GetInsertQuery(change, data);
}
else if (change.Operation == Operation.delete)
{
return GetDeleteQuery(change, data);
}
else if (change.Operation == Operation.update)
{
return GetUpdateQuery(change, data);
}
return null;
}
private SQLQuery GetInsertQuery(SyncChange change, List<Tuple<string, object>> rowData)
{
var tableMap = tableSet.GetTableMapping(change);
var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
var columnList = string.Join(",", tableInfo.Columns.OrderBy(c => c.ColumnID).Select(c => "[" + c.ColumnName + "]"));
var parameterList = string.Join(",", tableInfo.Columns.OrderBy(c => c.ColumnID).Select(c => "@" + c.ColumnName));
var sql = "insert into " + tableMap.FullyQualifiedDestinationTable + " ( " + columnList + ") values (" + parameterList + ");";
if (tableInfo.Columns.Any(c => c.Identity))
{
sql = "set identity_insert " + tableMap.FullyQualifiedDestinationTable + " ON; " + sql + " set identity_insert " + tableMap.FullyQualifiedDestinationTable + " OFF;";
}
var query = new SQLQuery(sql, SQLQueryType.NonQuery);
foreach (var columnInfo in tableInfo.Columns)
{
var tuple = rowData.First(t => t.Item1 == columnInfo.ColumnName);
query.Parameters.Add("@" + columnInfo.ColumnName, tuple.Item2);
}
return query;
}
private List<Tuple<string, object>> GetDataFromSource(SyncChange change)
{
var tableMap = tableSet.GetTableMapping(change);
var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
if (change.Operation == Operation.delete)
{
return GetDataForDelete(change);
}
string sql = null;
if (!string.IsNullOrEmpty(tableMap.CustomSourceSQLForSyncOnly))
{
sql = tableMap.CustomSourceSQLForSyncOnly;
}
else
{
var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "(" + c.ColumnName + " = ?" + c.ColumnName + ")");
sql = "select * from " + tableMap.FullyQualifiedSourceTable + " where " + string.Join(" AND ", whereColumns) + ";";
}
var query = new SQLQuery(sql);
bool firstKey = true; //yes, it's hacky
foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID))
{
if (sql.Contains("?" + column.ColumnName)) //get rid of this and just throw.....
{
query.Parameters.Add(column.ColumnName, firstKey ? change.PrimaryKey1 : change.PrimaryKey2);
}
firstKey = false;
}
var dataList = new List<Tuple<string, object>>();
query.ProcessRow = reader =>
{
for (int i = 0; i < reader.FieldCount; i++)
{
dataList.Add(new Tuple<string, object>(reader.GetName(i), reader[i]));
}
return false;
//return true here, but have a separate variable to see if this gets called again, if so, throw error...
};
sourceDb.RunQuery(query);
return ConvertDataTypes(dataList);
}
private List<Tuple<string, object>> GetDataForDelete(SyncChange change)
{
var tableMap = tableSet.GetTableMapping(change);
var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
bool firstKey = true; //yes, it's hacky
var tupleList = new List<Tuple<string, object>>();
foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID))
{
tupleList.Add(new Tuple<string, object>(column.ColumnName, firstKey ? change.PrimaryKey1 : change.PrimaryKey2));
firstKey = false;
}
return tupleList;
}