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