public ImportActivity(
ImportMode mode,
SqlConnection connection,
BatchParams batchParams,
string targetTable,
char csvDelimiterChar,
string numberFormatInfo,
IEnumerable<CsvColumnInfo> csvMetadata)
{
if (connection == null)
throw new ArgumentNullException("connection");
if (string.IsNullOrEmpty(targetTable))
throw new ArgumentException("Target table name cannot be empty", "targetTable");
if (csvMetadata == null)
throw new ArgumentNullException("csvMetadata");
_mode = mode;
_csvMetadata = csvMetadata;
_csvDelimiterChar = csvDelimiterChar;
_numberFormatInfo = numberFormatInfo;
_loader = DataLoaderBase.CreateLoader(
mode,
connection,
batchParams,
targetTable,
_csvMetadata
.Where(sci => !sci.ExcludeFromMapping && (mode != ImportMode.BulkDelete || sci.DeletionKey))
.Select(sci => sci.Name));
}
protected override bool LoadBatch(IDataReader reader, BatchParams batchParams, LoadCounters rowCounters)
{
bool read = true;
//batch size = 0 works as a single batch since --batchParams.Size != 0 will be never reached
while (--batchParams.Size != 0 && (read = reader.Read()))
{
try
{
if (RowExists(reader))
{
if (_updateCmd != null)
{
DoUpdate(reader);
rowCounters.Updated++;
}
}
else
{
DoInsert(reader);
rowCounters.Added++;
}
}
catch (Exception ex)
{
OnLoadError(ex);
}
}
return read;
}
public DeleteDataLoader(SqlConnection connection, BatchParams batchParams,
string targetTable, IEnumerable<string> columns)
: base(connection, batchParams,
"#" + targetTable.Replace('.', '_') + "_TMP_FOR_DELETION", columns, false)
{
_dataTable = targetTable;
}
public SmartDataLoader(
SqlConnection connection,
BatchParams batchParams,
string targetTable,
IEnumerable<string> columns)
: base(connection, batchParams, targetTable, columns)
{
BuildCommands();
}
public DataCleanerBase(ImportSettings settings)
{
if (settings == null)
throw new ArgumentNullException("settings");
_connString = ConfigurationManager.
ConnectionStrings[settings.ConnectionStringName].ConnectionString;
_batchParams = settings.BatchParams;
_targetTable = settings.TargetTable;
}
private int DeleteData(BatchParams batchParams, string tempTable, string dataTable)
{
string joinCondition = _Columns
.Select(col => "dat." + col + "=tmp." + col)
.Aggregate((acc, c) => acc + " and " + c);
string cmdText = string.Format(@"
delete {1}
from {1} as dat
join {0} as tmp on {2}
select @@rowcount", tempTable, dataTable, joinCondition);
using (SqlCommand cmd = new SqlCommand(cmdText, _Connection))
{
cmd.CommandTimeout = batchParams.Timeout;
return (int)cmd.ExecuteScalar();
}
}
public static DataLoaderBase CreateLoader(ImportMode mode, SqlConnection connection,
BatchParams batchParams, string targetTable, IEnumerable<string> columns)
{
switch (mode)
{
case ImportMode.BulkInsert:
return new BulkDataLoader(connection, batchParams, targetTable, columns, false);
case ImportMode.BulkInsertWithSkipped:
return new BulkDataLoader(connection, batchParams, targetTable, columns, true);
case ImportMode.BulkDelete:
return new DeleteDataLoader(connection, batchParams, targetTable, columns);
case ImportMode.Smart:
return new SmartDataLoader(connection, batchParams, targetTable, columns);
default:
throw new InvalidOperationException("Unknown ImportMode - '" + mode.ToString() + "'");
}
}
protected override bool ClearBatch(SqlConnection connection,
BatchParams batchParams, string targetTable, object key, LoadCounters counters)
{
if (!(key is int)) throw new
ArgumentException("SupplierID key must be integer", "key");
using (SqlCommand cmd = new SqlCommand(string.Format(
"DELETE " + (batchParams.Size == 0 ? "" : "TOP({0}) ") +
"FROM {1} WHERE SupplierID = @SupplierID",
batchParams.Size, targetTable), connection))
{
cmd.CommandTimeout = batchParams.Timeout;
cmd.Parameters.AddWithValue("@SupplierID", key);
int rowsAffected = cmd.ExecuteNonQuery();
counters.Deleted += rowsAffected;
return rowsAffected >= batchParams.Size && batchParams.Size > 0;
//batchParams.Size = 0 works as a single batch
}
}
public static void ExecBatched(Func<bool> processBatch,
BatchParams batchParams, LoadCounters counters)
{
Stopwatch sw = Stopwatch.StartNew();
while (processBatch())
{
sw.Stop();
int delay = batchParams.Delay;
if (batchParams.DelayMode == DelayMode.Percentage)
{
delay = (int)(sw.ElapsedMilliseconds * delay / 100m);
}
counters.BatchCount++;
counters.BatchTime += sw.Elapsed;
counters.DelayTime += new TimeSpan(0, 0, 0, 0, delay);
System.Threading.Thread.Sleep(delay);
sw.Reset();
sw.Start();
}
}
public DataLoaderBase(SqlConnection connection, BatchParams batchParams,
string targetTable, IEnumerable<string> columns)
{
if (connection == null)
throw new ArgumentNullException("connection");
if (string.IsNullOrEmpty(targetTable))
throw new ArgumentException("TargetTable name cannot be empty", "targetTable");
if (columns == null)
throw new ArgumentNullException("columns");
if (columns.Count() == 0)
throw new ArgumentException("Column list cannot be empty", "columns");
_connection = connection;
_batchParams = batchParams;
_targetTable = targetTable;
_columns = columns;
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
_closeConnection = true;
}
}
protected override bool LoadBatch(IDataReader reader, BatchParams batchParams, LoadCounters rowCounters)
{
LoadCounters impCounters = new LoadCounters();
bool aborted;
int deleted;
CreateTempTable(_TargetTable, _dataTable);
try
{
//вставка ключей удаляемых записей во временную таблицу
aborted = base.LoadBatch(reader, batchParams, impCounters);
//удаление данных на основе содержимого временной таблицы
deleted = this.DeleteData(batchParams, _TargetTable, _dataTable);
//подсчет строк
rowCounters.Deleted += deleted;
rowCounters.Skipped += (impCounters.Added - deleted);
}
finally
{
DropTempTable(_TargetTable);
}
return aborted;
}
protected abstract bool LoadBatch(IDataReader reader,
BatchParams batchParams, LoadCounters rowCounters);
protected abstract bool ClearBatch(SqlConnection connection,
BatchParams batchParams, string targetTable, object key, LoadCounters counters);