C# SqlBulkCopy.WriteToServerAsync方法代码示例

        private async Task BulkCopyExecuteAsync(IDataReader sourceDataReader, SqlConnection targetConnection, string targetTableName,
           IEnumerable<ColumnsMapping> columnsMappings, Action<long> rowsCopiedNotify)
        {
            using (var bulkCopy = new SqlBulkCopy(targetConnection))
            {
               
                if (columnsMappings != null)
                {
                    var bulkMappings = CreateBulkCopyMappings(columnsMappings);
                    bulkCopy.AddMappings(bulkMappings);
                }

                bulkCopy.DestinationTableName = targetTableName;

                bulkCopy.BatchSize = _batchSize;
                bulkCopy.NotifyAfter = _notifyAfter;
                bulkCopy.BulkCopyTimeout = _bulkCopyTimeout;

                if (rowsCopiedNotify != null)
                {
                    bulkCopy.SqlRowsCopied += (sender, e) => rowsCopiedNotify(e.RowsCopied);
                }
                
                await bulkCopy.WriteToServerAsync(sourceDataReader);
            }
        }

        public async Task DeleteDataAsync(IDataReader dataReader, string targetTableName, string targetConnectionString)
        {
            //dataReader.
            //var tempTableName = "#Temp" + targetTableName;
            //var createTempTableCommnadText = "create table #MyTempTable(Id int, SomeColumn varchar(50))"
            //using (var connection = DbCommonHelper.CreateDbConnection(
            //    PROVIDER_NAME, targetConnectionString))
            //{
            //    using (var createTempTableCommand = DbCommonHelper.CreateDbConnection)
            //}
           
            //SqlConnection conn = new SqlConnection("your connection string here");
            //SqlCommand cmd = new SqlCommand("create table #MyTempTable(Id int, SomeColumn varchar(50))", conn);
            //conn.Open();
            //cmd.ExecuteNonQuery();
            //SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
            //bulkCopy.DestinationTableName = "#MyTempTable";
            //bulkCopy.WriteToServer(dt);
            //conn.Close();

            var tempTableName = "#Temp" + targetTableName;
            var createTempTableCommnadText = "create table " + tempTableName + "(EAN13 varchar(50))";
            var deleteDataCommandText = "delete from " + targetTableName + " where EAN13 in (select EAN13 from " + tempTableName + ");";

            using (var connection = DbCommonHelper.CreateDbConnection(
                PROVIDER_NAME, targetConnectionString))
            {
                await connection.OpenAsync();

                using (var createTempTableCommand = DbCommonHelper.CreateCommand(
                    createTempTableCommnadText, connection))
                {
                    await createTempTableCommand.ExecuteNonQueryAsync();
                }

                using (var bulkCopy = new SqlBulkCopy((SqlConnection)connection))
                {
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("EAN13", "EAN13"));
                    bulkCopy.DestinationTableName = tempTableName;
                    bulkCopy.BulkCopyTimeout = 1800;
                    
                    await bulkCopy.WriteToServerAsync(dataReader);
                }

                using (var deleteDataCommand = DbCommonHelper.CreateCommand(
                    deleteDataCommandText, connection))
                {
                    await deleteDataCommand.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task BulkLoadInTransactionAsync(DataTable dataTable, string tableNameAndSchema, SqlConnection connection, SqlTransaction transaction)
        {
            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                foreach (DataColumn column in dataTable.Columns)
                    bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);

                bulkCopy.DestinationTableName = tableNameAndSchema;
                bulkCopy.BatchSize = _configuration.BatchWriteSize;
                try
                {
                    await bulkCopy.WriteToServerAsync(dataTable);
                }
                catch (SqlException ex)
                {
                    if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
                    {
                        string pattern = @"\d+";
                        Match match = Regex.Match(ex.Message.ToString(), pattern);
                        var index = Convert.ToInt32(match.Value) - 1;

                        FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings",
                                                                     BindingFlags.NonPublic | BindingFlags.Instance);
                        var sortedColumns = fi.GetValue(bulkCopy);
                        var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

                        FieldInfo itemdata = items[1].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
                        var metadata = itemdata.GetValue(items[1]);

                        var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                        var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                        throw new DataException(String.Format("Column: {0} contains data with a length greater than: {1}", column, length));
                    }

                    throw;
                }
            }


        }

        /// <summary>
        ///     Emit a batch of log events, running asynchronously.
        /// </summary>
        /// <param name="events">The events to emit.</param>
        /// <remarks>
        ///     Override either <see cref="PeriodicBatchingSink.EmitBatch" /> or <see cref="PeriodicBatchingSink.EmitBatchAsync" />
        ///     ,
        ///     not both.
        /// </remarks>
        protected override async Task EmitBatchAsync(IEnumerable<LogEvent> events)
        {
            // Copy the events to the data table
            FillDataTable(events);

            using (var cn = new SqlConnection(_connectionString))
            {
                await cn.OpenAsync(_token.Token);
                using (var copy = new SqlBulkCopy(cn))
                {
                    copy.DestinationTableName = _tableName;
                    await copy.WriteToServerAsync(_eventsTable, _token.Token);

                    // Processed the items, clear for the next run
                    _eventsTable.Clear();
                }
            }
        }

        /// <summary>
        /// Pushes the Data to SQL
        /// </summary>        
        /// <param name="sqlBulkCopy">Sql Bulk Copy object to which the Datas to be pushed</param>
        /// <param name="dataTable">Datatable containing the Data to be pushed using SQL Bulk Copy</param>
        /// <returns></returns>
        private async Task InsertDataTable(SqlBulkCopy sqlBulkCopy, DataTable dataTable)
        {
            await sqlBulkCopy.WriteToServerAsync(dataTable);

            dataTable.Rows.Clear();
        }

        public Task<AsyncTaskResult> BatchAppendAsync(IEnumerable<DomainEventStream> eventStreams)
        {
            var table = BuildEventTable();
            foreach (var eventStream in eventStreams)
            {
                AddDataRow(table, eventStream);
            }

            return _ioHelper.TryIOFuncAsync<AsyncTaskResult>(async () =>
            {
                try
                {
                    using (var connection = GetConnection())
                    {
                        await connection.OpenAsync();
                        var transaction = await Task.Run<SqlTransaction>(() => connection.BeginTransaction());

                        using (var copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                        {
                            copy.BatchSize = _bulkCopyBatchSize;
                            copy.BulkCopyTimeout = _bulkCopyTimeout;
                            copy.DestinationTableName = _tableName;
                            copy.ColumnMappings.Add("AggregateRootId", "AggregateRootId");
                            copy.ColumnMappings.Add("AggregateRootTypeName", "AggregateRootTypeName");
                            copy.ColumnMappings.Add("CommandId", "CommandId");
                            copy.ColumnMappings.Add("Version", "Version");
                            copy.ColumnMappings.Add("CreatedOn", "CreatedOn");
                            copy.ColumnMappings.Add("Events", "Events");

                            try
                            {
                                await copy.WriteToServerAsync(table);
                                await Task.Run(() => transaction.Commit());
                                return AsyncTaskResult.Success;
                            }
                            catch (Exception ex)
                            {
                                try { transaction.Rollback(); }
                                catch { }
                                return new AsyncTaskResult(AsyncTaskStatus.Failed, ex.Message);
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    _logger.Error("Batch append event has sql exception.", ex);
                    return new AsyncTaskResult(AsyncTaskStatus.IOException, ex.Message);
                }
                catch (Exception ex)
                {
                    _logger.Error("Batch append event has unknown exception.", ex);
                    return new AsyncTaskResult(AsyncTaskStatus.Failed, ex.Message);
                }
            }, "BatchAppendEventsAsync");
        }

        /// <summary>
        ///     Emit a batch of log events, running asynchronously.
        /// </summary>
        /// <param name="events">The events to emit.</param>
        /// <remarks>
        ///     Override either <see cref="PeriodicBatchingSink.EmitBatch" /> or <see cref="PeriodicBatchingSink.EmitBatchAsync" />
        ///     ,
        ///     not both.
        /// </remarks>
        protected override async Task EmitBatchAsync(IEnumerable<LogEvent> events)
        {
            // Copy the events to the data table
            FillDataTable(events);

            try
            {
                using (var cn = new SqlConnection(_connectionString))
                {
                    await cn.OpenAsync(_token.Token).ConfigureAwait(false);
                    using (var copy = new SqlBulkCopy(cn))
                    {
                        copy.DestinationTableName = _tableName;
                        foreach (var column in _eventsTable.Columns)
                        {
                            var columnName = ((DataColumn)column).ColumnName;
                            var mapping = new SqlBulkCopyColumnMapping(columnName, columnName);
                            copy.ColumnMappings.Add(mapping);
                        }

                        await copy.WriteToServerAsync(_eventsTable, _token.Token).ConfigureAwait(false);
                    }

                }
            }
            catch (Exception ex)
            {
                SelfLog.WriteLine("Unable to write {0} log events to the database due to following error: {1}", events.Count(), ex.Message);
            }
            finally
            {
                // Processed the items, clear for the next run
                _eventsTable.Clear();
            }
        }

        public async Task WriteToServerAsync(CancellationToken cancellationToken)
        {
            string[] sourceFields = fields.Length > 0 ? fields.ToArray() : reader.GetColumnNames().ToArray();

            if (bulkOptions.CreateTable)
            {
                await CreateTableAsync(sourceFields);
            }

            Mapping[] map;

            if (bulkOptions.FieldsSelector == FieldsSelector.Source && !bulkOptions.CaseSensitive.HasValue)
            {
                map = sourceFields.Select(x => new Mapping() { Source = x, Destination = x }).ToArray();
            }
            else
            {
                string[] destFields = (await GetTableColumnsAsync(cancellationToken).ConfigureAwait(false)).ToArray();

                map = bulkOptions.CaseSensitive.HasValue && bulkOptions.CaseSensitive.Value ?
                  sourceFields.Join(destFields, x => x, x => x, (x, y) => new Mapping() { Source = x, Destination = y }).ToArray() :
                  sourceFields.Join(destFields, x => x.ToLowerInvariant(), x => x.ToLowerInvariant(), (x, y) => new Mapping() { Source = x, Destination = y }).ToArray();

                Helpers.CheckFieldSelection(bulkOptions.FieldsSelector, sourceFields.Length, destFields.Length, map.Length);
            }

            if (map.Length > 0)
            {
                using (SqlBulkCopy bcp = new SqlBulkCopy(connection, bulkOptions.SqlBulkCopyOptions, transaction))
                {
                    bcp.DestinationTableName = destinationTable;
                    if (bulkOptions.BatchSize.HasValue)
                    {
                        bcp.BatchSize = bulkOptions.BatchSize.Value;
                    }

                    if (bulkOptions.BulkCopyTimeout.HasValue)
                    {
                        bcp.BulkCopyTimeout = bulkOptions.BulkCopyTimeout.Value;
                    }

                    if (bulkOptions.EnableStreaming.HasValue)
                    {
                        bcp.EnableStreaming = bulkOptions.EnableStreaming.Value;
                    }

                    foreach (var item in map)
                    {
                        bcp.ColumnMappings.Add(item.Source, item.Destination);
                    }

                    await bcp.WriteToServerAsync(reader, cancellationToken).ConfigureAwait(false);
                }
            }
        }

        /// <summary>
        ///     Emit a batch of log events, running asynchronously.
        /// </summary>
        /// <param name="events">The events to emit.</param>
        /// <remarks>
        ///     Override either <see cref="PeriodicBatchingSink.EmitBatch" /> or <see cref="PeriodicBatchingSink.EmitBatchAsync" />
        ///     ,
        ///     not both.
        /// </remarks>
        protected override async Task EmitBatchAsync(IEnumerable<LogEvent> events)
        {            
            // Copy the events to the data table
            FillDataTable(events);

            using (var cn = new SqlConnection(_connectionString))
            {
                await cn.OpenAsync(_token.Token);
                using (var copy = new SqlBulkCopy(cn))
                {
                    copy.DestinationTableName = _tableName;
                    try
                    {
                        await copy.WriteToServerAsync(_eventsTable, _token.Token);
                    }
                    catch (Exception ex)
                    {
                        // create LogEvent so exception can be added to DB during next batch run.
                        var logEvent = new LogEvent(
                            DateTimeOffset.Now,
                            LogEventLevel.Error,
                            ex,
                            new MessageTemplate("", new[] {new TextToken("Internal error while trying to write events to database")}),
                            Enumerable.Empty<LogEventProperty>());

                        Emit(logEvent);
                    }
                    finally
                    {
                        // Processed the items, clear for the next run
                        _eventsTable.Clear();   
                    }
                }
            }
        }

        public Task<AsyncTaskResult<EventAppendResult>> BatchAppendAsync(IEnumerable<DomainEventStream> eventStreams)
        {
            if (eventStreams.Count() == 0)
            {
                throw new ArgumentException("Event streams cannot be empty.");
            }
            var table = BuildEventTable();
            var aggregateRootIds = eventStreams.Select(x => x.AggregateRootId).Distinct();
            if (aggregateRootIds.Count() > 1)
            {
                throw new ArgumentException("Batch append event only support for one aggregate.");
            }
            var aggregateRootId = aggregateRootIds.Single();

            foreach (var eventStream in eventStreams)
            {
                AddDataRow(table, eventStream);
            }

            return _ioHelper.TryIOFuncAsync(async () =>
            {
                try
                {
                    using (var connection = GetConnection())
                    {
                        await connection.OpenAsync();
                        var transaction = await Task.Run<SqlTransaction>(() => connection.BeginTransaction());

                        using (var copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                        {
                            InitializeSqlBulkCopy(copy, aggregateRootId);
                            try
                            {
                                await copy.WriteToServerAsync(table);
                                await Task.Run(() => transaction.Commit());
                                return new AsyncTaskResult<EventAppendResult>(AsyncTaskStatus.Success, EventAppendResult.Success);
                            }
                            catch
                            {
                                try
                                {
                                    transaction.Rollback();
                                }
                                catch (Exception ex)
                                {
                                    _logger.ErrorFormat("Transaction rollback failed.", ex);
                                }
                                throw;
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    if (ex.Number == 2601 && ex.Message.Contains(_versionIndexName))
                    {
                        return new AsyncTaskResult<EventAppendResult>(AsyncTaskStatus.Success, EventAppendResult.DuplicateEvent);
                    }
                    else if (ex.Number == 2601 && ex.Message.Contains(_commandIndexName))
                    {
                        return new AsyncTaskResult<EventAppendResult>(AsyncTaskStatus.Success, EventAppendResult.DuplicateCommand);
                    }
                    _logger.Error("Batch append event has sql exception.", ex);
                    return new AsyncTaskResult<EventAppendResult>(AsyncTaskStatus.IOException, ex.Message, EventAppendResult.Failed);
                }
                catch (Exception ex)
                {
                    _logger.Error("Batch append event has unknown exception.", ex);
                    return new AsyncTaskResult<EventAppendResult>(AsyncTaskStatus.Failed, ex.Message, EventAppendResult.Failed);
                }
            }, "BatchAppendEventsAsync");
        }

        private static void BulkyCopyTables()
        {
            foreach (var table in GetWorbookTablesList(sourceConnection))
            {
                //Console.WriteLine("Carregar tabela " + table + "(s/n)");
                //if (Console.ReadLine() == "n") continue;

                if (!regex.IsMatch(table)) continue;

                Console.WriteLine("Tabela: " + table);

                if (lembrar == "n")
                {
                    Console.WriteLine("Digite o nome para tabela temporaria destino: ");
                    _destinationTable = Console.ReadLine();
                    Console.WriteLine("Deseja usar esta tabela para o resto da operação: ");
                    lembrar = Console.ReadLine();
                }

                //if (Console.ReadLine() == null || Console.ReadLine() == "")
                //{
                //    Console.WriteLine("Não vou insistir: ");
                //    _destinationTable = Console.ReadLine();
                //}
                var dataTable = new DataTable(table);
                string query = "SELECT * FROM [" + table + "]";

                using (var adapter = new OleDbDataAdapter(query, sourceConnection))
                {
                    adapter.Fill(dataTable);
                }

                using (var copy = new SqlBulkCopy(destinyConnection))
                {
                    copy.DestinationTableName = _destinationTable;
                    copy.BulkCopyTimeout = 0;
                    //copy.BatchSize = 2000;

                    VinculaColumnTable(dataTable);
                    DestinyTableHelper(dataTable);
                    BulkCopyMapping(copy, dataTable);

                    //gerando select
                    var selectCommand = GenerateSelectCommand(dataTable);
                    Console.WriteLine("Inicido do bulk copy da tabela: " + dataTable.TableName);
                    copy.SqlRowsCopied += (o, s) =>
                    {
                        Console.Write("\rQtde. linhas copiadas {0} do total de {1}", +s.RowsCopied, dataTable.Rows.Count);
                    };
                    copy.NotifyAfter = 2;

                    destinyConnection.Open();

                    //copy.WriteToServer(dataTable);
                    var task = copy.WriteToServerAsync(dataTable);
                    task.Wait();
                    Console.WriteLine("");

                    destinyConnection.Close();
                    dataTable.Clear();
                    dataTable.Dispose();
                }
            }
        }

        private async Task<DatabaseStatusViewModel> ProcessDataFile(string filePath)
        {
            var startTime = DateTime.Now;

            if (filePath == null || !File.Exists(filePath))
                return new DatabaseStatusViewModel() { Message = "File does not exist or No file was uploaded" };

            var status = new DatabaseStatusViewModel()
            {
                Success = false,
                RecordsInFile = 0,
                RecordsLoaded = 0,
            };

            var config = new CsvConfiguration()
            {
                IsHeaderCaseSensitive = false,
                WillThrowOnMissingField = false,
                IgnoreReadingExceptions = true,
                ThrowOnBadData = false,
                SkipEmptyRecords = true,
            };
            var csv = new CsvReader(new StreamReader(filePath, Encoding.Default, true), config);
            csv.Configuration.RegisterClassMap<CsvMap>();
            var csvTaxRecords = csv.GetRecords<CsvTaxRecordViewModel>().ToList();

            var csvConstituents = csvTaxRecords.DistinctBy(m => m.LookupId).AsQueryable().ProjectTo<ConstituentViewModel>().ToList();
            var dbConstituents = db.Constituents.ProjectTo<ConstituentViewModel>().ToList();

            var newConstituentList = csvConstituents.Except(dbConstituents, new ConstituentIdComparer()).ToList();
            var existingConstituentList = csvConstituents.Except(newConstituentList, new ConstituentIdComparer());
            var constituentChangeList = existingConstituentList.Except(dbConstituents, new ConstituentComparer());

            // Update existing constituents that differ from database
            foreach (var vm in constituentChangeList)
            {
                ConstituentViewModel cvm = dbConstituents.FirstOrDefault(x => x.LookupId == vm.LookupId);
                if (cvm == null) continue;
                vm.Id = cvm.Id;
                vm.UpdatedBy = "system";
                vm.UpdatedDate = DateTime.Now;
                cvm.CopyPropertiesFrom(vm);
                var constituent = Mapper.Map<ConstituentViewModel, Constituent>(cvm);
                db.Constituents.AddOrUpdate(constituent);
            }
            status.ConstituentsUpdated = db.SaveChanges();

            // Add new Constituents missing from database
            // Bulk copy new Constituent records
            if (newConstituentList.Count > 0)
            {
                foreach (var vm in newConstituentList)
                {
                    vm.CreatedBy = "system";
                    vm.UpdatedBy = "system";
                    vm.CreatedDate = DateTime.Now;
                    vm.UpdatedDate = DateTime.Now;
                }

                var missingTbl = newConstituentList.ToDataTable();
                using (var sbc = new SqlBulkCopy(db.Database.Connection.ConnectionString))
                {
                    sbc.DestinationTableName = db.GetTableName<Constituent>();
                    sbc.BatchSize = 10000;
                    sbc.BulkCopyTimeout = 0;
                    foreach (var col in missingTbl.Columns)
                    {
                        sbc.ColumnMappings.Add(col.ToString(), col.ToString());
                    }
                    try
                    {
                        await sbc.WriteToServerAsync(missingTbl);
                        status.ConstituentsCreated = sbc.RowsCopiedCount();
                    }
                    catch (Exception e)
                    {
                        status.Message = e.Message;
                    }
                }

            }

            // Update constituents because of new bulk copy constituents
            //TODO: Change Created and Updated user to logged in user
            dbConstituents = db.Constituents.ProjectTo<ConstituentViewModel>().ToList();
            // Build dictionary to map database key to csv records LookupId
            var dic = new Dictionary<int, string>();
            dbConstituents.ForEach(x => dic.Add(x.Id, x.LookupId));

            // Update parent key for each tax record
            //csvTaxRecords.ForEach(x => x.ConstituentId = dic.FirstOrDefault(d => d.Value == x.LookupId).Key);
            csvTaxRecords.ForEach((s) =>
            {
                s.ConstituentId = dic.FirstOrDefault(d => d.Value == s.LookupId).Key;
                s.CreatedBy = "system";
                s.UpdatedBy = "system";
                s.CreatedDate = DateTime.Now;
                s.UpdatedDate = DateTime.Now;
            });
            // Bulk insert new tax records
//.........这里部分代码省略.........