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; } }