public static int UpdateQueueTable(SqlConnection conn, QueueMigrate queueMigrate)
{
using (SqlCommand cmd = new SqlCommand(QueryConstants.Update_QueueMigrate, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = queueMigrate.Id;
cmd.Parameters.Add("@SourceServerName", SqlDbType.VarChar).Value = queueMigrate.SourceServerName;
cmd.Parameters.Add("@SourceDatabaseCompany", SqlDbType.VarChar).Value = queueMigrate.SourceDatabaseCompany;
cmd.Parameters.Add("@SourceTable", SqlDbType.VarChar).Value = queueMigrate.SourceTable;
cmd.Parameters.Add("@TargetServerName", SqlDbType.VarChar).Value = queueMigrate.TargetServerName;
cmd.Parameters.Add("@TargetDatabaseCompany", SqlDbType.VarChar).Value = queueMigrate.TargetDatabaseCompany;
cmd.Parameters.Add("@TargetTable", SqlDbType.VarChar).Value = queueMigrate.TargetTable;
cmd.Parameters.Add("@SqlQuery", SqlDbType.NVarChar).Value = queueMigrate.SqlQuery;
cmd.Parameters.Add("@IsLastRecord", SqlDbType.SmallInt).Value = queueMigrate.IsLastRecord;
cmd.Parameters.Add("@IsGetKeys", SqlDbType.SmallInt).Value = queueMigrate.IsGetKeys;
cmd.Parameters.Add("@FieldsMapId", SqlDbType.SmallInt).Value = queueMigrate.FieldsMapId;
cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = queueMigrate.Status;
cmd.Parameters.Add("@Exception", SqlDbType.NVarChar).Value = queueMigrate.Exception;
return cmd.ExecuteNonQuery();
}
}
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();
}
}
public static List<QueueMigrate> GetQueueMigrates(SqlConnection queueConnection,QueueMigrate objectQueue)
{
var lst = new List<QueueMigrate>();
using (SqlCommand cmd = new SqlCommand(QueryConstants.Get_QueueMigrate, queueConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@SourceServerName", SqlDbType.VarChar).Value = objectQueue.SourceServerName;
cmd.Parameters.Add("@SourceDatabaseCompany", SqlDbType.VarChar).Value = objectQueue.SourceDatabaseCompany;
cmd.Parameters.Add("@TargetServerName", SqlDbType.VarChar).Value = objectQueue.TargetServerName;
cmd.Parameters.Add("@TargetDatabaseCompany", SqlDbType.VarChar).Value = objectQueue.TargetDatabaseCompany;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
lst.Add(new QueueMigrate
{
Id = ParseData.GetInt(reader["Id"]) ?? 0,
SourceServerName = ParseData.GetString(reader["SourceServerName"]) ?? "",
SourceDatabaseCompany = ParseData.GetString(reader["SourceDatabaseCompany"]) ?? "",
SourceTable = ParseData.GetString(reader["SourceTable"]) ?? "",
TargetServerName = ParseData.GetString(reader["TargetServerName"]) ?? "",
TargetDatabaseCompany = ParseData.GetString(reader["TargetDatabaseCompany"]) ?? "",
TargetTable = ParseData.GetString(reader["TargetTable"]) ?? "",
SqlQuery = ParseData.GetString(reader["SqlQuery"]) ?? "",
IsGetKeys = ParseData.GetShort(reader["IsGetKeys"]) ?? 0,
IsLastRecord = ParseData.GetShort(reader["IsLastRecord"]) ?? 0,
FieldsMapId = ParseData.GetShort(reader["FieldsMapId"]) ?? 0,
Status = ParseData.GetShort(reader["Status"]) ?? 0,
Exception = ParseData.GetString(reader["Exception"]) ?? "",
CreatedDate = ParseData.GetDateTime(reader["CreatedDate"]),
UpdatedDate = ParseData.GetDateTime(reader["UpdatedDate"]),
});
}
}
}
return lst;
}
/// <summary>
/// execute the migration process
/// iterate the fieldsMaps, query the source and bulkCopy t des
/// </summary>
public void Run()
{
MigrateStatus = MigratorStatus.Running;
var start = DateTime.Now;
Logging.PushInfo("=======================================================================\n\nStart Migrating at : " + start.ToString(CultureInfo.InvariantCulture));
_running = true;
try
{
//raise migration process begin event
if (MigrationProcessBegin != null)
{
TableMigratedCount = 0;
MigrationProcessBegin(this, new MigrationEventArgs() { Message = "Migration process begin..." });
}
// Disable all triggers
/*using (var conn = new SqlConnection(_destinationConnectionString))
{
Logging.PushInfo("Start Disable all triggers");
conn.Open();
const string disableTriggerSql = "sp_MSforeachtable 'alter table ? disable trigger all'";
Logging.PushInfo("Command : " + disableTriggerSql);
var disableTriggerCmd = new SqlCommand(disableTriggerSql, conn);
disableTriggerCmd.CommandTimeout = Int32.MaxValue;
disableTriggerCmd.ExecuteNonQuery();
Logging.PushInfo("End Disable all triggers");
}*/
//open connections
var sourceConn = new SqlConnection(_soureConnectionString);
sourceConn.Open();
Utils.CreateIndexTableAdapt(sourceConn);
Utils.CreateSupportTempDb(sourceConn);
var desConn = new SqlConnection(_destinationConnectionString);
desConn.Open();
Utils.CreateUniqueIndex(desConn);
var usersAdapt = Utils.GetListUserAdapt(sourceConn);
//var listEmailDuplicate = new List<string>();
if (usersAdapt.Any())
{
foreach (var userAdapt in usersAdapt)
{
var isValid = true;//Utils.CheckExistEmail(desConn, TenantID, userAdapt.Email);
if (isValid)
{
MembershipCreateStatus createStatus;
var user = Membership.CreateUser(userAdapt.Email,
ConfigurationManager.AppSettings["DefaultPasswordNewUser"], userAdapt.Email,
Constants.PasswordQuestion,
Constants.PasswordAnswer, true, null, out createStatus);
if (createStatus == MembershipCreateStatus.Success)
{
if (user != null)
{
var query = string.Format(QueryConstants.QueryInsertUserTempdb,
SourceDbSystemAdapt,
"[dbo].[users]", DestinationServer, "st_Security", "[dbo].[sec_User]",
userAdapt.Email, ParseData.GetGuid(user.ProviderUserKey), TenantID, DatabaseID);
using (SqlCommand command = new SqlCommand(query, sourceConn))
{
//Logging.PushInfo(query);
command.ExecuteNonQuery();
}
}
}
else if (createStatus == MembershipCreateStatus.DuplicateEmail ||
createStatus == MembershipCreateStatus.DuplicateUserName)
{
//listEmailDuplicate.Add("'" + userAdapt.Email + "'");
}
}
}
}
if (desConn.State == ConnectionState.Open)
{
desConn.Close();
desConn.Dispose();
}
//insert query to queue table
Logging.PushInfo("===============Start Insert Query To Queue Table===================");
Logging.PushInfo("Enable identity insert on table [SysproMigration].[dbo].[QueueMigrate]");
var migrationConn1 = MigrationConnectionString.CreateAndOpenConnection("Syspro Migration");
var enableIdentityInsert = new SqlCommand("SET IDENTITY_INSERT [SysproMigration].[dbo].[QueueMigrate] ON", migrationConn1);
enableIdentityInsert.ExecuteNonQuery();
migrationConn1.CloseConnection();
foreach (var fieldsMap in FieldsMaps)
{
InsertQueryToQueue(fieldsMap, MigrationConnectionString);
}
Logging.PushInfo("===============End Insert Query To Queue Table===================");
Logging.PushInfo("===============Start Read Query from Query Table===================");
var migrationConn = MigrationConnectionString.CreateAndOpenConnection("Syspro Migration");
var sourceConnObject = SourceConnectionString.GetObjectConnection();
var desConnObject = DestinationConnectionString.GetObjectConnection();
var objectQueue = new QueueMigrate
{
SourceServerName = sourceConnObject.DataSource,
SourceDatabaseCompany = sourceConnObject.InitialCatalog,
TargetServerName = desConnObject.DataSource,
TargetDatabaseCompany = desConnObject.InitialCatalog
};
var lstQueue = Utils.GetQueueMigrates(migrationConn, objectQueue);
migrationConn.CloseConnection();
foreach (var queue in lstQueue)
{
var fieldmap = FieldsMaps.FirstOrDefault(t => t.Id == queue.FieldsMapId);
MigrateFromQueue(queue, fieldmap);
}
/* //iterate the fieldsMaps and migrate every tables
foreach (var fieldsMap in _fieldsMapsCompany)
{
MigrateNew(fieldsMap, _destinationConnectionString, sourceConn,
listEmailDuplicate.Any() ? string.Join(",", listEmailDuplicate) : "");
}*/
//raise migration process end event
if (MigrationProcessEnd != null)
{
MigrationProcessEnd(this, new MigrationEventArgs() { Message = "Migration process done!!" });
}
desConn = DestinationConnectionString.CreateAndOpenConnection("Target");
Utils.DropUniqueIndex(desConn);
desConn.CloseConnection();
sourceConn = SourceConnectionString.CreateAndOpenConnection("Source");
Utils.DropIndexTableAdapt(sourceConn);
sourceConn.CloseConnection();
// Enable all triggers
using (var conn = new SqlConnection(_destinationConnectionString))
{
Logging.PushInfo("Start Enable all triggers");
conn.Open();
const string enableTriggerSql = "sp_MSforeachtable 'alter table ? enable trigger all'";
Logging.PushInfo("Command : " + enableTriggerSql);
var enableTriggerCmd = new SqlCommand(enableTriggerSql, conn);
enableTriggerCmd.ExecuteNonQuery();
Logging.PushInfo("End Enable all triggers");
}
//UpdateMigratedRestList(_migratedFilePath);
Success = true;
MigrateStatus = MigratorStatus.CompletedMigrate;
var finish = DateTime.Now;
Logging.PushInfo("=======================================================================\n\nFinish Migrating at : " + finish.ToString(CultureInfo.InvariantCulture));
Logging.PushInfo("Process time : " + finish.Subtract(start));
}
catch (Exception e)
{
if (MigrationError != null)
{
var evg = new MigrationEventArgs { Message = string.Format("{0}\n{1}", e.Message, e.StackTrace) };
MigrationError(this, evg);
Error = true;
Running = false;
Logging.PutError("MigrateError : ", e);
}
//log.Error("Run : ", e);
}
finally
{
_running = false;
}
}