예제 #1
0
        private async Task MergeAsync(DataTable tableData, CancellationToken cancellationToken, Func <DbCommand, CancellationToken, Task> executeFactory)
        {
            var isBulk = _mergeDefinition.Mode == DataMergeMode.BulkCopy ||
                         (_mergeDefinition.Mode == DataMergeMode.Auto && tableData.Rows.Count > 1000);

            // Step 1, validate definition
            if (!Validate(_mergeDefinition, isBulk))
            {
                return;
            }

            try
            {
                await _dataSession
                .EnsureConnectionAsync(cancellationToken)
                .ConfigureAwait(false);

                var sqlConnection = _dataSession.Connection as SqlConnection;
                if (sqlConnection == null)
                {
                    throw new InvalidOperationException(
                              "Bulk-Copy only supported by SQL Server.  Make sure DataSession was create with a valid SqlConnection.");
                }

                var    sqlTransaction = _dataSession.Transaction as SqlTransaction;
                string mergeSql;

                if (isBulk)
                {
                    // Step 2, create temp table
                    string tableSql = DataMergeGenerator.BuildTable(_mergeDefinition);
                    using (var tableCommand = _dataSession.Connection.CreateCommand())
                    {
                        tableCommand.CommandText = tableSql;
                        tableCommand.CommandType = CommandType.Text;
                        tableCommand.Transaction = sqlTransaction;

                        await tableCommand
                        .ExecuteNonQueryAsync(cancellationToken)
                        .ConfigureAwait(false);
                    }

                    // Step 3, bulk copy into temp table
                    using (var bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
                    {
                        bulkCopy.DestinationTableName = _mergeDefinition.TemporaryTable;
                        bulkCopy.BatchSize            = 1000;
                        foreach (var mergeColumn in _mergeDefinition.Columns.Where(c => !c.IsIgnored && c.CanBulkCopy))
                        {
                            bulkCopy.ColumnMappings.Add(mergeColumn.SourceColumn, mergeColumn.SourceColumn);
                        }

                        await bulkCopy
                        .WriteToServerAsync(tableData, cancellationToken)
                        .ConfigureAwait(false);
                    }

                    // Step 4, merge sql
                    mergeSql = DataMergeGenerator.BuildMerge(_mergeDefinition);
                }
                else
                {
                    // build merge from data
                    mergeSql = DataMergeGenerator.BuildMerge(_mergeDefinition, tableData);
                }

                // run merge statement
                using (var mergeCommand = _dataSession.Connection.CreateCommand())
                {
                    mergeCommand.CommandText = mergeSql;
                    mergeCommand.CommandType = CommandType.Text;
                    mergeCommand.Transaction = sqlTransaction;

                    // run merge with factory
                    await executeFactory(mergeCommand, cancellationToken)
                    .ConfigureAwait(false);
                }
            }
            finally
            {
                _dataSession.ReleaseConnection();
            }
        }
예제 #2
0
        private void Merge(DataTable table, Action <IDbCommand> executeFactory)
        {
            // Step 1, validate definition
            if (!Validate(_mergeDefinition))
            {
                return;
            }

            try
            {
                _dataSession.EnsureConnection();

                var sqlConnection = _dataSession.Connection as SqlConnection;
                if (sqlConnection == null)
                {
                    throw new InvalidOperationException(
                              "Bulk-Copy only supported by SQL Server.  Make sure DataSession was create with a valid SqlConnection.");
                }

                var sqlTransaction = _dataSession.Transaction as SqlTransaction;

                // Step 2, create temp table
                string tableSql = DataMergeGenerator.BuildTable(_mergeDefinition);
                using (var tableCommand = _dataSession.Connection.CreateCommand())
                {
                    tableCommand.CommandText = tableSql;
                    tableCommand.CommandType = CommandType.Text;
                    tableCommand.Transaction = sqlTransaction;

                    tableCommand.ExecuteNonQuery();
                }

                // Step 3, bulk copy into temp table
                using (var bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
                {
                    bulkCopy.DestinationTableName = _mergeDefinition.TemporaryTable;
                    bulkCopy.BatchSize            = 1000;
                    foreach (var mergeColumn in _mergeDefinition.Columns.Where(c => !c.IsIgnored && c.CanBulkCopy))
                    {
                        bulkCopy.ColumnMappings.Add(mergeColumn.SourceColumn, mergeColumn.SourceColumn);
                    }

                    bulkCopy.WriteToServer(table);
                }

                // Step 4, run merge sql
                string mergeSql = DataMergeGenerator.BuildMerge(_mergeDefinition);
                using (var mergeCommand = _dataSession.Connection.CreateCommand())
                {
                    mergeCommand.CommandText = mergeSql;
                    mergeCommand.CommandType = CommandType.Text;
                    mergeCommand.Transaction = sqlTransaction;

                    // run merge with factory
                    executeFactory(mergeCommand);
                }
            }
            finally
            {
                _dataSession.ReleaseConnection();
            }
        }