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