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