public static bool Syncronize(IEnumerable <SqlTable> tables,
string sourceConnectionString,
string destinationConnectionString,
SqlBatchProgressHandler sqlBatchProgress)
{
using (var dc = new SqlConnection(destinationConnectionString))
{
var counter = new List <SqlTable>(tables);
dc.Open();
var transaction = dc.BeginTransaction();
var transactionSuccess = false;
var steps = 0;
foreach (var table in tables)
{
LaunchSqlBatchProgressHandler(sqlBatchProgress, "Truncando tabla ", table.ToString(), ComputeSteps(counter.Count),
steps++);
var c = TruncateTable(dc, table.Synonym, transaction);
STrace.Debug(typeof(Toolkit).FullName, String.Format("La Tabla {0} fue truncada, tenia {1} registros.", table, c));
}
try
{
using (var sc = new SqlConnection(sourceConnectionString))
{
sc.Open();
foreach (var table in tables)
{
LaunchSqlBatchProgressHandler(sqlBatchProgress, "Copiando tabla ", table.ToString(), ComputeSteps(counter.Count),
steps++);
var c = CopyTable(sc, dc, table, sqlBatchProgress, transaction);
STrace.Debug(typeof(Toolkit).FullName, String.Format("Tabla [{0}] copiados {1} registros.", table, c));
}
}
transactionSuccess = true;
return(true);
}
catch (Exception e)
{
STrace.Exception(typeof(Toolkit).FullName, e);
transactionSuccess = false;
return(false);
}
finally
{
if (transactionSuccess)
{
STrace.Debug(typeof(Toolkit).FullName, "Database Syncronization Success.");
transaction.Commit();
}
else
{
STrace.Debug(typeof(Toolkit).FullName, "Database Syncronization Failure.");
transaction.Rollback();
}
}
}
}
public static void LaunchSqlBatchProgressHandler(SqlBatchProgressHandler handler, string action, string entity,
int totalSteps, int doneSteps)
{
if (handler == null)
{
return;
}
handler(action, entity, totalSteps, doneSteps);
}
public static int CopyTable(SqlConnection sourceConnection, SqlConnection destinationConnection, SqlTable table,
SqlBatchProgressHandler sqlBatchProgress, SqlTransaction transaction)
{
_copyTableSqlBatchProgress = sqlBatchProgress;
var state = BulkCopyStates.WfBegin;
try
{
STrace.Debug(typeof(Toolkit).FullName, String.Format(
"SQLTOOLKIT/COPY_TABLE: {0}, catalogo={1}, table={2}, transaccional={3}",
sourceConnection.DataSource, sourceConnection.Database, table, transaction != null ? "SI" : "NO"));
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.SNAPSHOTING).ToString("G"), table.ToString(), -1, 1);
_copyTableRecords = CountTableRows(sourceConnection, table.Synonym, null);
var timeout = Math.Max(6000, _copyTableRecords);
int fileSteps;
int batchSize;
if (_copyTableRecords < 10)
{
fileSteps = 1;
batchSize = 1;
}
else if (_copyTableRecords < 100)
{
fileSteps = 10;
batchSize = 10;
}
else
{
fileSteps = Math.Min(500, Math.Max(100, _copyTableRecords / 100));
batchSize = Math.Min(50, Math.Max(10, _copyTableRecords / 100));
}
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.LOADING).ToString("G"), table.ToString(), -1, 1);
var source = RetrieveTable(sourceConnection, table.Synonym, null);
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.BULKCOPY).ToString("G"), table.ToString(), -1, 1);
using (var destination = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity,
transaction))
{
_copyTableTable = table.ToString();
destination.BatchSize = batchSize;
destination.SqlRowsCopied += DestinationSqlRowsCopied;
destination.NotifyAfter = fileSteps;
destination.BulkCopyTimeout = timeout;
destination.DestinationTableName = table.Synonym;
destination.WriteToServer(source);
}
source.Close();
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.VERIFING).ToString("G"), table.ToString(), -1, 1);
return(CountTableRows(destinationConnection, table.Synonym, transaction));
}
catch (Exception e)
{
STrace.Exception(typeof(Toolkit).FullName, e, String.Format("Imposible copiar la tabla {0} estado {1}", table.Synonym, state));
throw new Exception("SQLTOOLKIT/COPY_TABLE: imposible copiar la tabla.", e);
}
}
public static int CopyBuggyTable(SqlConnection sourceConnection, SqlConnection destinationConnection, SqlTable table,
SqlBatchProgressHandler sqlBatchProgress, SqlTransaction transaction)
{
_copyTableSqlBatchProgress = sqlBatchProgress;
var state = BulkCopyStates.WfBegin;
try
{
STrace.Debug(typeof(Toolkit).FullName, String.Format(
"SQLTOOLKIT/COPY_TABLE: {0}, catalogo={1}, table={2}, transaccional={3}",
sourceConnection.DataSource, sourceConnection.Database, table, transaction != null ? "SI" : "NO"));
//WORKAROUND: debido a un defecto en SqlBulkCopy
// se hace este rodeo de usar una tabla intermedia,
// la cosa es que si la tabla incluye un . en su nombre
// el metodo falla. Todas nuestras tablas incluyen
// . en el nombre.
// http://support.microsoft.com/kb/944389/en-us/
var tempTable = "srctmp_" + table.Synonym;
var safeTable = "dsttmp_" + table.Synonym;
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.SNAPSHOTING).ToString("G"), table.ToString(), -1, 1);
SelectInto(sourceConnection, table.Synonym, tempTable, table.SourceWhere, true, null);
_copyTableRecords = CountTableRows(sourceConnection, tempTable, null);
var timeout = Math.Max(6000, _copyTableRecords);
int fileSteps;
int batchSize;
if (_copyTableRecords < 10)
{
fileSteps = 1;
batchSize = 1;
}
else if (_copyTableRecords < 100)
{
fileSteps = 10;
batchSize = 10;
}
else
{
fileSteps = Math.Min(500, Math.Max(100, _copyTableRecords / 100));
batchSize = Math.Min(50, Math.Max(10, _copyTableRecords / 100));
}
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.LOADING).ToString("G"), table.ToString(), -1, 1);
var source = RetrieveTable(sourceConnection, tempTable, null);
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.APPROACH).ToString("G"), table.ToString(), -1, 1);
// creo una tabla temporal igual a la destino, y la vacio.
RenameTable(destinationConnection, table.QualifiedName, safeTable, transaction);
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.BULKCOPY).ToString("G"), table.ToString(), -1, 1);
using (var destination = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity,
transaction))
{
_copyTableTable = table.ToString();
destination.BatchSize = batchSize;
destination.SqlRowsCopied += DestinationSqlRowsCopied;
destination.NotifyAfter = fileSteps;
destination.BulkCopyTimeout = timeout;
destination.DestinationTableName = safeTable;
destination.WriteToServer(source);
}
source.Close();
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.VERIFING).ToString("G"), table.ToString(), -1, 1);
var records = CountTableRows(destinationConnection, safeTable, transaction);
LaunchSqlBatchProgressHandler(sqlBatchProgress, (state = BulkCopyStates.COMPLETE).ToString("G"), table.ToString(), -1, 1);
RenameTable(destinationConnection, safeTable, table.FullName, transaction);
return(records);
}
catch (Exception e)
{
STrace.Exception(typeof(Toolkit).FullName, e, String.Format("Imposible copiar la tabla {0} estado {1}", table.Synonym, state));
throw new Exception("SQLTOOLKIT/COPY_TABLE: imposible copiar la tabla.", e);
}
}
