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