예제 #1
0
        internal static bool checkCollation(DataTransferTask t)
        {
            string colate1 = "1";
            string colate2 = "2";

            using (SqlConnection con1 = new SqlConnection(t.SourceConnectionString))
            {
                con1.Open();
                using (SqlCommand com = new SqlCommand($"SELECT DATABASEPROPERTYEX('{con1.Database}', 'Collation') AS Collation", con1))
                {
                    colate1 = (string)com.ExecuteScalar();
                }
                con1.Close();
            }
            using (SqlConnection con2 = new SqlConnection(t.TargetConnectionString))
            {
                con2.Open();
                using (SqlCommand com = new SqlCommand($"SELECT DATABASEPROPERTYEX('{con2.Database}', 'Collation') AS Collation", con2))
                {
                    colate2 = (string)com.ExecuteScalar();
                }
                con2.Close();
            }

            return(colate1 == colate2);
        }
예제 #2
0
        public static bool CheckTarget(DataTransferTask dataTransferTask)
        {
            bool TargetIsExists = splSqlService.ObjectIsExist(dataTransferTask.TargetConnectionString, dataTransferTask.TargetTableName);

            if (!TargetIsExists)
            {
                System.Data.DataRowCollection SchemaRows = DataSchemaService.getSchemaRowsCollection(dataTransferTask.SourceConnectionString, dataTransferTask.SourceGetDataQuery);
                if (SchemaRows == null)
                {
                    return(false);
                }
                CreateTargetTable(dataTransferTask.TargetTableName, SchemaRows, dataTransferTask.TargetConnectionString, true);
                return(true);
            }
            else //check Source = Target
            {
                DataRowCollection SourceRows  = DataSchemaService.getSchemaRowsCollection(dataTransferTask.SourceConnectionString, dataTransferTask.SourceGetDataQuery);
                string            TargetQuery = $"select * from {dataTransferTask.TargetTableName}";
                DataRowCollection TargetRows  = DataSchemaService.getSchemaRowsCollection(dataTransferTask.TargetConnectionString, TargetQuery);
                if (DataSchemaService.DataRowsIsEqual(SourceRows, TargetRows))
                {
                    return(true);
                }
                else
                {
                    System.Data.DataRowCollection SchemaRows = DataSchemaService.getSchemaRowsCollection(dataTransferTask.SourceConnectionString, dataTransferTask.SourceGetDataQuery);
                    if (SchemaRows == null)
                    {
                        return(false);
                    }
                    CreateTargetTable(dataTransferTask.TargetTableName, SchemaRows, dataTransferTask.TargetConnectionString, true);
                    return(true);
                }
            }
        }
예제 #3
0
        public static int CopyDataOverDataTable(DataTransferTask dataTransferTask, int batchSize = 5000)
        {
            int totalCount = 0;
            int rowsCount  = 0;

            List <Thread> bulkTasks = new List <Thread>();
            DataTable     dt        = DataSchemaService.getDataTableSchemaOnly(
                dataConnectionSting: dataTransferTask.SourceConnectionString,
                dataQuery: dataTransferTask.SourceGetDataQuery,
                option: CommandBehavior.SchemaOnly
                );

            dt.TableName = dataTransferTask.TargetTableName;

            //clear target table
            splSqlService.ExecuteSQL(dataTransferTask.TargetConnectionString, $"TRUNCATE TABLE {dataTransferTask.TargetTableName}");

            object[][] dataRows = new object[batchSize][];
            using (SqlDataReader dr = splSqlService.GetDataReader(dataTransferTask.SourceConnectionString, dataTransferTask.SourceGetDataQuery))
            {
                try
                {
                    while (dr.Read())
                    {
                        dataRows[rowsCount] = GetDataRow(dr);
                        rowsCount++;
                        if (rowsCount == batchSize)
                        {
                            bulkTasks.Add(Megafunction(dataTransferTask.TargetConnectionString, dataRows, dt, rowsCount));
                            totalCount += rowsCount;
                            dataRows    = new object[batchSize][];
                            rowsCount   = 0;
                        }
                    }
                    if (rowsCount > 0)
                    {
                        totalCount += rowsCount;
                        bulkTasks.Add(Megafunction(dataTransferTask.TargetConnectionString, dataRows, dt, rowsCount));
                    }
                }
                catch (SqlException sqlex)
                {
                    Logger.WriteErrorLog($"Errore while SqlBulkCopy {dataTransferTask.TargetTableName} to {dataTransferTask.TargetConnectionString}");
                    Logger.WriteErrorLog(sqlex.Message);
                }
                finally
                {
                }
                while (bulkTasks.Any(a => a.ThreadState != ThreadState.Stopped))
                {
                    Thread.Sleep(500);
                }
                ;  //wait for last bulk insert
                dr.Close();
            }
            //System.GC.Collect();
            return(totalCount);
        }
예제 #4
0
 private static void ExecuteTask(DataTransferTask t)
 {
     if (!DataTransferTaskService.CheckTaskIsBusy(t.TaskId) || true)
     {
         if (DataTransferTaskService.CheckTarget(t))
         {
             DataTransferTaskService.TakeTask(t.TaskId);
             var ds       = DateTime.Now;
             int rowcount = DataTransferTaskService.CopyDataOverDataTable(t, 15000);
             Logger.WriteLog($"{rowcount} rows affected, duration: {(DateTime.Now - ds).TotalMilliseconds} ms");
             DataTransferTaskService.ReleaseTask(t.TaskId);
         }
     }
     System.GC.Collect();
 }
예제 #5
0
        public static void CopyDataOverDataReader(DataTransferTask dataTransferTask)
        {
            //clear target table
            splSqlService.ExecuteSQL(dataTransferTask.TargetConnectionString, $"TRUNCATE TABLE {dataTransferTask.TargetTableName}");
            SqlDataReader dr = splSqlService.GetDataReader(dataTransferTask.SourceConnectionString, dataTransferTask.SourceGetDataQuery);

            using (SqlConnection targetCon = new SqlConnection(dataTransferTask.TargetConnectionString))
            {
                try
                {
                    using (SqlBulkCopy bulk = new SqlBulkCopy(targetCon)
                    {
                        DestinationTableName = dataTransferTask.TargetTableName,
                        BatchSize = 10000
                    })
                    {
                        foreach (DataRow col in dr.GetSchemaTable().Rows)
                        {
                            bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping((string)col["ColumnName"], (string)col["ColumnName"]));
                        }
                        targetCon.Open();
                        bulk.WriteToServer(dr);
                        bulk.Close();
                    }
                    targetCon.Close();
                }
                catch (SqlException sqlex)
                {
                    Logger.WriteErrorLog($"Errore while SqlBulkCopy {dataTransferTask.TargetTableName} to {dataTransferTask.TargetConnectionString}");
                    Logger.WriteErrorLog(sqlex.Message);
                }
                finally
                {
                    if (targetCon.State == ConnectionState.Open)
                    {
                        targetCon.Close();
                    }
                    if (!dr.IsClosed)
                    {
                        dr.Close();
                    }
                }
            }
            dr.Close();
            //System.GC.Collect();
        }