public static void CreateSyncTables(this SqlConnection targetConn, TableSchema tableSchema)
{
Array.ForEach(
new[]
{
new
{
Name = tableSchema.SyncNewOrUpdatedTableName,
CreateStatement = tableSchema.CreateNewOrUpdatedSyncTableStatement
},
new
{
Name = tableSchema.SyncDeletedTableName,
CreateStatement = tableSchema.CreateDeletedSyncTableStatement
}
},
table =>
{
targetConn.Execute(
commandType: CommandType.Text,
commandTimeout: 500,
sql: table.CreateStatement
);
Log.Success("Sync table {0} created.", table.Name, table.CreateStatement);
}
);
}
public static void CreateSyncTable(this SqlConnection targetConn, TableSchema tableSchema)
{
targetConn.Execute(
commandType: CommandType.Text,
commandTimeout: 300,
sql: tableSchema.CreateSyncTableStatement
);
}
public static void MergeData(this SqlConnection targetConn, TableSchema tableSchema)
{
var rowCount = targetConn.Query<long>(
commandTimeout: 300,
sql: tableSchema.MergeStatement
).First();
Log.Success("{0} records affected", rowCount);
}
public static void DropSyncTable(this SqlConnection targetConn, TableSchema tableSchema)
{
if (tableSchema == null || string.IsNullOrEmpty(tableSchema.SyncTableName))
return;
targetConn.Execute(
commandType: CommandType.Text,
commandTimeout: 300,
sql: tableSchema.DropStatment
);
}
public static void BulkCopyData(this SqlConnection sourceConn, SqlConnection targetConn, TableSchema tableSchema)
{
Array.ForEach(
new[]
{
new
{
Name = tableSchema.SyncNewOrUpdatedTableName,
SelectStatement = tableSchema.SourceNewOrUpdatedSelectStatment,
Description = "new or updated"
},
new
{
Name = tableSchema.SyncDeletedTableName,
SelectStatement = tableSchema.SourceDeletedSelectStatement,
Description = "deleted"
}
},
table =>
{
using (var sourceCmd = new SqlCommand
{
Connection = sourceConn,
CommandType = CommandType.Text,
CommandText = table.SelectStatement,
CommandTimeout = 500000
})
{
using (var reader = sourceCmd.ExecuteReader())
{
using (var bcp = new SqlBulkCopy(targetConn)
{
DestinationTableName = table.Name,
BatchSize = tableSchema.BatchSize,
NotifyAfter = tableSchema.BatchSize
})
{
bcp.SqlRowsCopied += (s, e) => Log.Info("{0} {1} rows copied", e.RowsCopied, table.Description);
bcp.WriteToServer(reader);
Log.Success("Bulk copy complete for {0} rows", table.Description);
}
}
}
}
);
}
public static void BulkCopyData(this SqlConnection sourceConn, SqlConnection targetConn, TableSchema tableSchema)
{
using (var sourceCmd = new SqlCommand
{
Connection = sourceConn,
CommandType = CommandType.Text,
CommandText = tableSchema.SourceSelectStatment,
CommandTimeout = 300
})
{
using (var reader = sourceCmd.ExecuteReader())
{
using (var bcp = new SqlBulkCopy(targetConn)
{
DestinationTableName = tableSchema.SyncTableName,
BatchSize = tableSchema.BatchSize
})
{
bcp.WriteToServer(reader);
}
}
}
}
public static void DropSyncTables(this SqlConnection targetConn, TableSchema tableSchema)
{
Array.ForEach(
new[]
{
new
{
Name = tableSchema.SyncNewOrUpdatedTableName,
DropStatement = tableSchema.DropNewOrUpdatedTableStatment
},
new
{
Name = tableSchema.SyncDeletedTableName,
DropStatement = tableSchema.DropDeletedTableStatment
}
},
table =>
{
if (string.IsNullOrEmpty(tableSchema?.SyncNewOrUpdatedTableName))
return;
try
{
targetConn.Execute(
commandType: CommandType.Text,
commandTimeout: 500000,
sql: table.DropStatement
);
Log.Success("Sync table {0} dropped.", table.Name);
}
catch (Exception ex)
{
Log.Exception(
"Failed to drop sync table {0}\r\n{1}",
tableSchema.SyncNewOrUpdatedTableName,
ex
);
}
}
);
}
private static void SyncTable(SqlConnection targetConn, TableSchema tableSchema, SqlConnection sourceConn)
{
try
{
targetConn.CreateSyncTables(tableSchema);
sourceConn.BulkCopyData(targetConn, tableSchema);
targetConn.DeleteData(tableSchema);
targetConn.MergeData(tableSchema);
}
finally
{
targetConn.DropSyncTables(tableSchema);
}
}
private static void ProcessSync(SyncJob syncJob)
{
using (SqlConnection
sourceConn = new SqlConnection(syncJob.SourceDbConnection),
targetConn = new SqlConnection(syncJob.TargetDbConnection)
)
{
Log.Info(
"Connecting to source database {0}.{1}",
sourceConn.DataSource,
sourceConn.Database
);
sourceConn.Open();
Log.Success(
"Connected {0}.{1} ({2})",
sourceConn.DataSource,
sourceConn.Database,
sourceConn.ServerVersion
);
Log.Info(
"Connecting to target database {0}.{1}",
targetConn.DataSource,
targetConn.Database
);
targetConn.Open();
Log.Success(
"Connected {0}.{1} ({2})",
targetConn.DataSource,
targetConn.Database,
targetConn.ServerVersion
);
Log.Info("Fetching table schemas");
var schemaStopWatch = Stopwatch.StartNew();
var tableSchemas = (
syncJob
.Tables ?? new List <string>()
)
.Select(
table => TableSchema.LoadSchema(sourceConn, targetConn, table, syncJob.BatchSize)
).ToArray();
schemaStopWatch.Stop();
Log.Success("Found {0} tables, duration {1}", tableSchemas.Length, schemaStopWatch.Elapsed);
Array.ForEach(
tableSchemas,
tableSchema =>
{
Log.Info("Begin {0}", tableSchema.TableName);
var syncStopWatch = Stopwatch.StartNew();
if (tableSchema.SourceVersion.Equals(tableSchema.TargetVersion))
{
Log.HighLight("Allready up to date");
}
else
{
SyncTable(targetConn, tableSchema, sourceConn);
}
syncStopWatch.Stop();
Log.Info("End {0}, duration {1}", tableSchema.TableName, syncStopWatch.Elapsed);
tableSchema.PersistsSourceTargetVersionState();
}
);
}
}
public static void BulkCopyData(this SqlConnection sourceConn, SqlConnection targetConn, TableSchema tableSchema)
{
using (var sourceCmd = new SqlCommand
{
Connection = sourceConn,
CommandType = CommandType.Text,
CommandText = tableSchema.SourceSelectStatment,
CommandTimeout = 300
})
{
using (var reader = sourceCmd.ExecuteReader())
{
using (var bcp = new SqlBulkCopy(targetConn)
{
DestinationTableName = tableSchema.SyncTableName,
BatchSize = tableSchema.BatchSize
})
{
bcp.WriteToServer(reader);
}
}
}
}