Ejemplo n.º 1
0
 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);
         }
         );
 }
Ejemplo n.º 2
0
 public static void CreateSyncTable(this SqlConnection targetConn, TableSchema tableSchema)
 {
     targetConn.Execute(
         commandType: CommandType.Text,
         commandTimeout: 300,
         sql: tableSchema.CreateSyncTableStatement
         );
 }
Ejemplo n.º 3
0
 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);
 }
Ejemplo n.º 4
0
        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
                );
        }
Ejemplo n.º 5
0
 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);
                     }
                 }
             }
         }
         );
 }
Ejemplo n.º 6
0
 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);
             }
         }
     }
 }
Ejemplo n.º 7
0
 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
                     );
             }
         }
         );
 }
Ejemplo n.º 8
0
 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);
     }
 }
Ejemplo n.º 9
0
        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();
                }
                    );
            }
        }
Ejemplo n.º 10
0
 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);
             }
         }
     }
 }