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