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);
}
}
public void ToString_WithoutJoins()
{
var result = _sqlTable.ToString();
Assert.That(result, Is.EqualTo("[table1] [t]"));
}