Beispiel #1
0
        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));
        }
Beispiel #2
0
        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;
 }
Beispiel #4
0
 public SmartDataLoader(
     SqlConnection connection,
     BatchParams batchParams,
     string targetTable,
     IEnumerable<string> columns)
     : base(connection, batchParams, targetTable, columns)
 {
     BuildCommands();
 }
Beispiel #5
0
 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();
     }
 }
Beispiel #7
0
 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() + "'");
     }
 }
Beispiel #8
0
        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
            }
        }
Beispiel #9
0
        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();
            }
        }
Beispiel #10
0
        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;
            }
        }
Beispiel #11
0
        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;
        }
Beispiel #12
0
 protected abstract bool LoadBatch(IDataReader reader,
     BatchParams batchParams, LoadCounters rowCounters);
Beispiel #13
0
 protected abstract bool ClearBatch(SqlConnection connection,
     BatchParams batchParams, string targetTable, object key, LoadCounters counters);