private void MigrateFromQueue(QueueMigrate queue,FieldsMap fieldsMap)
{
SqlConnection sourceConn = null;
SqlConnection desConn = null;
SqlBulkCopy sbc = null; //raise table migrating event
try
{
sourceConn = SourceConnectionString.CreateAndOpenConnection("Source");
desConn = DestinationConnectionString.CreateAndOpenConnection("Target");
sbc = new SqlBulkCopy(queue.IsGetKeys > 0 ? SourceConnectionString : DestinationConnectionString,
SqlBulkCopyOptions.KeepIdentity)
{
BatchSize = _batchSize,
BulkCopyTimeout = 0,
EnableStreaming = true
};
/*if (TableMigrating != null)
{
TableMigrating(this,
new MigrationEventArgs()
{
Message =
(queue.IsLastRecord > 0 ? "Continue migrate table" : "Begin migrate table ") + (queue.IsGetKeys > 0 ? queue.TargetTable : queue.SourceTable)
});
}*/
//migrate table
sbc.DestinationTableName = queue.IsGetKeys > 0 ? Constants.MiggrateSupportTable : queue.TargetTable;
var map = queue.IsGetKeys > 0
? fieldsMap.MapKeys.ToArray<KeyValuePair<string, string>>()
: fieldsMap.Map.ToArray<KeyValuePair<string, string>>();
sbc.ColumnMappings.Clear();
for (var i = 0; i < map.Length; i++)
{
sbc.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, map[i].Value));
}
var p = 0;
var done = false;
var records = 0;
while (!done)
{
using (
var dr = QueryBlock(queue.IsGetKeys > 0 ? desConn : sourceConn,
queue.IsGetKeys > 0
? queue.SqlQuery
: queue.SqlQuery.CreateQueryFromQueue(p,
fieldsMap.Size > 0 ? fieldsMap.Size : _batchSize,
fieldsMap.WhereGlobal)
, false,
out done,
out records))
{
done = queue.IsGetKeys > 0 || done;
Logging.PushInfo("Copying data to sqlserver");
sbc.WriteToServer(dr);
if (!string.IsNullOrEmpty(fieldsMap.Destination.Script))
{
desConn.CloseConnection();
desConn = DestinationConnectionString.CreateAndOpenConnection("Target With Script" + fieldsMap.Destination.Script);
Logging.PushInfo("\nStart run script at:"+DateTime.Now);
Utils.Execute(desConn, fieldsMap.Destination.Script.GetTextInQueryFixedFolder(), fieldsMap.Destination.TimeOut);
Logging.PushInfo("\nEnd Run script at:" + DateTime.Now);
desConn.CloseConnection();
}
dr.Close();
Logging.PushInfo("Copyied");
}
p++;
}
var migrationConn = MigrationConnectionString.CreateAndOpenConnection("Syspro Migration");
queue.Status = (int)QueueStatusEnum.Success;
Utils.UpdateQueueTable(migrationConn, queue);
migrationConn.CloseConnection();
//raise table migrated event
if (TableMigrated != null && queue.IsLastRecord > 0)
{
TableMigrated(this, new MigrationEventArgs() { Message = "Table " + fieldsMap.Destination.Tables + " migrated!" });
TableMigratedCount++;
}
}
catch (Exception ex)
{
Logging.PutError("Migrate From Queue : ", ex);
var migrationConn = MigrationConnectionString.CreateAndOpenConnection("Syspro Migration");
queue.Status = (int)QueueStatusEnum.Error;
queue.Exception = ex.ToString();
Utils.UpdateQueueTable(migrationConn, queue);
TableMigratedCount++;
migrationConn.CloseConnection();
}
finally
{
var connStringAdmin =
string.Format(
ConfigurationManager.ConnectionStrings[Constants.AdminConnectionConfig].ConnectionString,
DestinationDb);
const string strFeeBuffer = " DBCC DROPCLEANBUFFERS ";
const string strFeeSession = " DBCC FREESESSIONCACHE WITH NO_INFOMSGS ";
var connAdmin = connStringAdmin.CreateAndOpenConnection("Target Admin");
var cmd = new SqlCommand(strFeeBuffer, connAdmin);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(strFeeSession, connAdmin);
cmd.ExecuteNonQuery();
sourceConn.CloseConnection();
desConn.CloseConnection();
connAdmin.CloseConnection();
}
}
private void MigrateNew(FieldsMap fieldsMap, string desconnectionString, SqlConnection sourceConn, string conditionWhere)
{
SqlBulkCopy sbc = null; //raise table migrating event
SqlBulkCopy sbcGetKeys = null;
SqlConnection desConn = null;
try
{
desConn = new SqlConnection(desconnectionString);
Logging.PushInfo("Connecting to SQL Server");
desConn.Open();
Logging.PushInfo("Connected");
if (fieldsMap.NeedTruncate)
{
var sqlString = string.Format(TRUNCATE_TEMPLATE, fieldsMap.Destination.Tables);
var cmd = new SqlCommand(sqlString, desConn);
cmd.ExecuteNonQuery();
Logging.PushInfo("Truncate table " + fieldsMap.Destination.Tables);
Logging.PushInfo("Query : " + sqlString);
}
sbc = new SqlBulkCopy(desconnectionString, SqlBulkCopyOptions.KeepIdentity)
{
BatchSize = _batchSize,
BulkCopyTimeout = 0,
EnableStreaming = true
};
if (TableMigrating != null)
{
TableMigrating(this, new MigrationEventArgs() { Message = "Begin migrate table " + fieldsMap.Destination.Tables });
}
//migrate table
sbc.DestinationTableName = fieldsMap.Destination.Tables;
var map = fieldsMap.Map.ToArray<KeyValuePair<string, string>>();
sbc.ColumnMappings.Clear();
for (var i = 0; i < map.Length; i++)
{
sbc.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, map[i].Value));
}
var p = 0;
var done = false;
while (!done)
{
var sql = fieldsMap.CreateQueryFromSource(p, _batchSize, TimeZoneString, TimeZone, TenantID);
if (PackageMigrating != null)
{
PackageMigrating(this,
new MigrationEventArgs()
{
Message = "Begin import package",
DestinationTable = fieldsMap.Destination.Tables,
Package = p + 1,
MigratedRecords = p * _batchSize
});
}
var records = 0;
using (var dr = QueryBlock(sourceConn, sql, fieldsMap.IsGetKeys, out done, out records))
{
Logging.PushInfo("Copying data to sqlserver");
sbc.WriteToServer(dr);
dr.Close();
Logging.PushInfo("Copyied");
//get keys and insert to table migrate support
if (fieldsMap.IsGetKeys)
{
fieldsMap.Destination.Top = records.ToString(CultureInfo.InvariantCulture);
sbcGetKeys = new SqlBulkCopy(sourceConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity)
{
BatchSize = _batchSize,
BulkCopyTimeout = 0,
EnableStreaming = true
};
sbcGetKeys.DestinationTableName = Constants.MiggrateSupportTable;
var mapKeys = fieldsMap.MapKeys.ToArray();
sbcGetKeys.ColumnMappings.Clear();
for (var i = 0; i < mapKeys.Length; i++)
{
sbcGetKeys.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, mapKeys[i].Value));
}
var doneGetKeys = false;
var sqlGetKeys = fieldsMap.CreateQueryFromDestination(TimeZoneString, TimeZone, TenantID);
if (PackageMigrating != null)
{
PackageMigrating(this,
new MigrationEventArgs()
{
Message = "Begin import package",
DestinationTable = fieldsMap.Source.Tables,
MigratedRecords = records
});
}
var recordGetKeys = 0;
using (
var drGetKeys = QueryBlock(desConn, sqlGetKeys, fieldsMap.IsGetKeys, out doneGetKeys, out recordGetKeys)
)
{
Logging.PushInfo("Copying data to sqlserver");
sbcGetKeys.WriteToServer(drGetKeys);
drGetKeys.Close();
Logging.PushInfo("Copyied");
}
}
}
//raise package migrated event
if (PackageMigrated != null)
{
PackageMigrated(this, new MigrationEventArgs() { Message = "Begin import package", DestinationTable = fieldsMap.Destination.Tables, Package = p + 1, MigratedRecords = (p + 1) * _batchSize });
}
p++;
}
//raise table migrated event
if (TableMigrated != null)
{
TableMigrated(this, new MigrationEventArgs() { Message = "Table " + fieldsMap.Destination.Tables + " migrated!" });
TableMigratedCount++;
}
}
catch (Exception ex)
{
Logging.PutError("MigrateNew : ", ex);
throw;
}
finally
{
var connStringAdmin =
string.Format(
ConfigurationManager.ConnectionStrings[Constants.AdminConnectionConfig].ConnectionString,
DestinationDb);
const string strFeeBuffer = " DBCC DROPCLEANBUFFERS ";
const string strFeeSession = " DBCC FREESESSIONCACHE WITH NO_INFOMSGS ";
var connAdmin = new SqlConnection(connStringAdmin);
connAdmin.Open();
var cmd = new SqlCommand(strFeeBuffer, connAdmin);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(strFeeSession, connAdmin);
cmd.ExecuteNonQuery();
if (sbc != null)
{
sbc.Close();
sbc = null;
}
if (sbcGetKeys != null)
{
sbcGetKeys.Close();
sbcGetKeys = null;
}
if (desConn != null && desConn.State == ConnectionState.Open)
{
desConn.Close();
desConn.Dispose();
desConn = null;
}
if (connAdmin.State == ConnectionState.Open)
{
connAdmin.Close();
connAdmin.Dispose();
connAdmin = null;
}
}
}
private void InsertQueryToQueue(FieldsMap fieldsMap, string migrateConnString)
{
SqlConnection migrateConn = null;
SqlConnection sourceConn = null;
SqlConnection desConn = null;
var sourceConnObject = SourceConnectionString.GetObjectConnection();
var desConnObject = DestinationConnectionString.GetObjectConnection();
try
{
sourceConn = SourceConnectionString.CreateAndOpenConnection("Source");
desConn = DestinationConnectionString.CreateAndOpenConnection("Target");
migrateConn = migrateConnString.CreateAndOpenConnection("Syspro Migration");
if (fieldsMap.NeedTruncate)
{
var sqlString = string.Format(TRUNCATE_TEMPLATE, fieldsMap.Destination.Tables);
var cmd = new SqlCommand(sqlString, desConn);
cmd.ExecuteNonQuery();
Logging.PushInfo("Truncate table " + fieldsMap.Destination.Tables);
Logging.PushInfo("Query : " + sqlString);
}
var p = 0;
var sql = fieldsMap.CreateQueryFromSource(p, _batchSize, TimeZoneString, TimeZone, TenantID, true);
var records = 1000;
Logging.PushInfo("Write query from source table " + fieldsMap.Source.Tables);
//insert query
Utils.UpdateQueueTable(migrateConn, new QueueMigrate
{
SourceServerName = sourceConnObject.DataSource,
SourceDatabaseCompany = sourceConnObject.InitialCatalog,
SourceTable = fieldsMap.Source.Tables,
TargetServerName = desConnObject.DataSource,
TargetDatabaseCompany = desConnObject.InitialCatalog,
TargetTable = fieldsMap.Destination.Tables,
SqlQuery = sql,
Status = (int) QueueStatusEnum.NotStart,
IsGetKeys = 0,
FieldsMapId = fieldsMap.Id,
IsLastRecord = 1,
Exception = ""
});
Logging.PushInfo("Writed");
//get keys and insert to table migrate support
if (fieldsMap.IsGetKeys)
{
fieldsMap.Destination.Top = records.ToString(CultureInfo.InvariantCulture);
var doneGetKeys = false;
var sqlGetKeys = fieldsMap.CreateQueryFromDestination(TimeZoneString, TimeZone, TenantID);
var recordGetKeys = 0;
using (
var drGetKeys = QueryBlock(desConn, sqlGetKeys.QueryInsertQueue(), fieldsMap.IsGetKeys,
out doneGetKeys, out recordGetKeys)
)
{
Logging.PushInfo("Write query get keys");
//insert query
Utils.UpdateQueueTable(migrateConn, new QueueMigrate
{
SourceServerName = desConnObject.DataSource,
SourceDatabaseCompany = desConnObject.InitialCatalog,
SourceTable = fieldsMap.Destination.Tables,
TargetServerName = sourceConnObject.DataSource,
TargetDatabaseCompany = sourceConnObject.InitialCatalog,
TargetTable = fieldsMap.Source.Tables,
SqlQuery = sqlGetKeys,
Status = (int) QueueStatusEnum.NotStart,
IsGetKeys = 1,
FieldsMapId = fieldsMap.Id,
IsLastRecord = 0,
Exception = ""
});
drGetKeys.Close();
Logging.PushInfo("Writed get keys");
}
}
}
catch (Exception ex)
{
Logging.PutError("MigrateNew : ", ex);
throw;
}
finally
{
sourceConn.CloseConnection();
desConn.CloseConnection();
migrateConn.CloseConnection();
SqlConnection.ClearAllPools();
}
}
