private void bw_DoWork(object sender, DoWorkEventArgs e)
{
BulkCopyArgs args = e.Argument as BulkCopyArgs;
if (args == null)
return;
BulkCopyResult result = new BulkCopyResult();
ProgressData pd = new ProgressData();
pd.Total = args.Objects.Count;
SqlTransaction tr = null;
string copyErrors = String.Empty;
bool copyCancelled = false;
SqlConnection sourceConn = null;
SqlConnection destConn = null;
try
{
sourceConn = _cp.CreateSqlConnection(true, false);
destConn = _cpDest.CreateSqlConnection(true, false);
try
{
if (args.StopOnError)
tr = destConn.BeginTransaction();
try
{
if (rbEmpty.Checked)
{
DeleteData(destConn, tr, args.Objects);
}
else if (rbEmptyAll.Checked)
{
DeleteAllData(destConn, tr);
}
}
catch (Exception ex)
{
if (args.StopOnError)
tr.Rollback();
result.Errors += SmoHelpers.FormatExceptionMsg(ex);
return;
}
foreach (DbObjectList.DbObjectInfo selObj in args.Objects)
{
try
{
pd.Info = selObj.Name;
pd.Progress++;
if (_cancelled || bw.CancellationPending)
{
e.Cancel = true;
if (args.StopOnError)
tr.Rollback();
break;
}
else
{
bw.ReportProgress(0, pd);
if (!LoadDataAndCopy(selObj.SchemaAndName, args.CopyOptions, sourceConn, destConn, tr, out copyErrors, out copyCancelled))
{
if (args.StopOnError)
tr.Rollback();
if (copyCancelled)
{
e.Cancel = true;
break;
}
result.Errors += copyErrors;
if (args.StopOnError)
break;
}
}
}
catch (Exception ex)
{
if (args.StopOnError)
{
tr.Rollback();
result.Errors += SmoHelpers.FormatExceptionMsg(ex, "Table/View: " + selObj.Name);
break;
}
else
{
result.Errors += SmoHelpers.FormatExceptionMsg(ex, "Table/View: " + selObj.Name);
}
}
}
if (args.StopOnError && tr.Connection != null)
tr.Commit();
}
catch (Exception ex)
{
result.Errors += SmoHelpers.FormatExceptionMsg(ex);
}
}
finally
{
e.Result = result;
if (sourceConn != null)
{
if (sourceConn.State != ConnectionState.Closed)
sourceConn.Close();
sourceConn.Dispose();
}
if (destConn != null)
{
if (destConn.State != ConnectionState.Closed)
destConn.Close();
destConn.Dispose();
}
}
}
private bool LoadDataAndCopy(string tableName, SqlBulkCopyOptions options, SqlConnection sourceConn, SqlConnection destConn, SqlTransaction tr, out string errors, out bool cancelled)
{
bool result = true;
string[] strTable = tableName.Split('.');
SqlCommand sourceCmd = null;
SqlDataReader sourceReader = null;
SqlBulkCopy bulkCopy = null;
SqlCommand mapCmd = null;
errors = String.Empty;
cancelled = false;
try
{
NotifyTotalRows(tableName, sourceConn);
//Load data
sourceCmd = new SqlCommand("SELECT * FROM " + tableName, sourceConn);
sourceCmd.CommandTimeout = 0;
sourceReader = sourceCmd.ExecuteReader();
//Map Columns
bulkCopy = new SqlBulkCopy(destConn, options, tr);
bulkCopy.NotifyAfter = 1;
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
//mapCmd = new SqlCommand("SELECT COLUMN_NAME,COLUMNPROPERTY( OBJECT_ID('" + strTable[0] + "." + strTable[1] + "'),COLUMN_NAME,'IsComputed')AS 'IsComputed' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + strTable[0] + "' AND TABLE_NAME = '" + strTable[1] + "'", destConn);
mapCmd = new SqlCommand(String.Format(Properties.Resources.Script_ColumnMap, strTable[0], strTable[1], strTable[0], strTable[1]), destConn);
mapCmd.Transaction = tr;
SqlDataReader mapColReader = null;
try
{
mapColReader = mapCmd.ExecuteReader();
while (mapColReader.Read())
{
if (mapColReader.GetInt32(1) != 1)
bulkCopy.ColumnMappings.Add(mapColReader.GetString(0), mapColReader.GetString(0));
}
}
finally
{
if (!mapColReader.IsClosed)
mapColReader.Close();
mapColReader.Dispose();
}
//Copy data
bulkCopy.BatchSize = 50;
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(sourceReader);
}
catch (OperationAbortedException)
{
cancelled = true;
result = false;
}
catch (Exception ex)
{
result = false;
errors = SmoHelpers.FormatExceptionMsg(ex, "Table/View: " + tableName);
}
finally
{
if (sourceCmd != null)
sourceCmd.Dispose();
if (sourceReader != null)
{
if (!sourceReader.IsClosed)
sourceReader.Close();
sourceReader.Dispose();
}
if (mapCmd != null)
mapCmd.Dispose();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
return result;
}