コード例 #1
0
ファイル: BulkInsert.cs プロジェクト: crazyants/RepoDb
        /// <summary>
        /// Bulk insert an instance of <see cref="DbDataReader"/> object into the database in an asynchronous way.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="tableName">The target table for bulk-insert operation.</param>
        /// <param name="reader">The <see cref="DbDataReader"/> object to be used in the bulk-insert operation.</param>
        /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
        /// <param name="options">The bulk-copy options to be used.</param>
        /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
        /// <param name="batchSize">The size per batch to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>The number of rows affected by the execution.</returns>
        public async Task <int> BulkInsertAsync(IDbConnection connection,
                                                string tableName,
                                                DbDataReader reader,
                                                IEnumerable <BulkInsertMapItem> mappings = null,
                                                SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
                                                int?bulkCopyTimeout        = null,
                                                int?batchSize = null,
                                                IDbTransaction transaction = null)
        {
            // Validate the objects
            ValidateConnection(connection);
            ValidateTransaction(transaction);
            DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);

            // Variables for the operation
            var result = 0;

            // Before Execution Time
            var beforeExecutionTime = DateTime.UtcNow;

            // Actual Execution
            using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
            {
                // Set the destinationtable
                sqlBulkCopy.DestinationTableName = tableName;

                // Set the timeout
                if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
                {
                    sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
                }

                // Set the batch szie
                if (batchSize != null && batchSize.HasValue)
                {
                    sqlBulkCopy.BatchSize = batchSize.Value;
                }

                // Add the mappings
                if (mappings != null)
                {
                    foreach (var mapItem in mappings)
                    {
                        sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
                    }
                }

                // Open the connection and do the operation
                await connection.EnsureOpenAsync();

                await sqlBulkCopy.WriteToServerAsync(reader);

                // Hack the 'SqlBulkCopy' object
                var field = GetRowsCopiedField();

                // Set the return value
                result = field != null ? (int)field.GetValue(sqlBulkCopy) : reader.RecordsAffected;
            }

            // Result
            return(result);
        }
コード例 #2
0
ファイル: BulkInsert.cs プロジェクト: crazyants/RepoDb
        /// <summary>
        /// Bulk insert a list of data entity objects into the database.
        /// </summary>
        /// <typeparam name="TEntity">The type of the data entity object.</typeparam>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="entities">The list of the data entities to be bulk-inserted.</param>
        /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
        /// <param name="options">The bulk-copy options to be used.</param>
        /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
        /// <param name="batchSize">The size per batch to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>The number of rows affected by the execution.</returns>
        public int BulkInsert <TEntity>(IDbConnection connection,
                                        IEnumerable <TEntity> entities,
                                        IEnumerable <BulkInsertMapItem> mappings = null,
                                        SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
                                        int?bulkCopyTimeout        = null,
                                        int?batchSize = null,
                                        IDbTransaction transaction = null)
            where TEntity : class
        {
            // Validate the objects
            ValidateConnection(connection);
            ValidateTransaction(transaction);
            DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);

            // Variables for the operation
            var result = 0;

            // Before Execution Time
            var beforeExecutionTime = DateTime.UtcNow;

            // Actual Execution
            using (var reader = new DataEntityDataReader <TEntity>(entities, connection))
            {
                using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
                {
                    // Set the destinationtable
                    sqlBulkCopy.DestinationTableName = ClassMappedNameCache.Get <TEntity>();

                    // Set the timeout
                    if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
                    {
                        sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
                    }

                    // Set the batch szie
                    if (batchSize != null && batchSize.HasValue)
                    {
                        sqlBulkCopy.BatchSize = batchSize.Value;
                    }

                    // Add the mappings
                    if (mappings == null)
                    {
                        foreach (var property in reader.Properties)
                        {
                            var columnName = property.GetUnquotedMappedName();
                            sqlBulkCopy.ColumnMappings.Add(columnName, columnName);
                        }
                    }
                    else
                    {
                        foreach (var mapItem in mappings)
                        {
                            sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
                        }
                    }

                    // Open the connection and do the operation
                    connection.EnsureOpen();
                    sqlBulkCopy.WriteToServer(reader);

                    // Hack the 'SqlBulkCopy' object
                    var field = GetRowsCopiedField();

                    // Set the return value
                    result = field != null ? (int)field.GetValue(sqlBulkCopy) : reader.RecordsAffected;
                }
            }

            // Result
            return(result);
        }
コード例 #3
0
        /// <summary>
        /// Bulk insert an instance of <see cref="DbDataReader"/> object into the database in an asynchronous way.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="tableName">The target table for bulk-insert operation.</param>
        /// <param name="reader">The <see cref="DbDataReader"/> object to be used in the bulk-insert operation.</param>
        /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
        /// <param name="options">The bulk-copy options to be used.</param>
        /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
        /// <param name="batchSize">The size per batch to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>The number of rows affected by the execution.</returns>
        public async Task <int> BulkInsertAsync(IDbConnection connection,
                                                string tableName,
                                                DbDataReader reader,
                                                IEnumerable <BulkInsertMapItem> mappings = null,
                                                SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
                                                int?bulkCopyTimeout        = null,
                                                int?batchSize = null,
                                                IDbTransaction transaction = null)
        {
            // Validate the objects
            ValidateConnection(connection);
            ValidateTransaction(transaction);
            DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);

            // Variables for the operation
            var result = 0;

            // Before Execution Time
            var beforeExecutionTime = DateTime.UtcNow;

            // Actual Execution
            using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
            {
                // Set the destinationtable
                sqlBulkCopy.DestinationTableName = tableName;

                // Set the timeout
                if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
                {
                    sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
                }

                // Set the batch szie
                if (batchSize != null && batchSize.HasValue)
                {
                    sqlBulkCopy.BatchSize = batchSize.Value;
                }

                // Add the mappings
                if (mappings == null)
                {
                    // Get the actual DB fields
                    var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);

                    var fields         = Enumerable.Range(0, reader.FieldCount).Select((index) => reader.GetName(index));
                    var filteredFields = new List <Tuple <string, string> >();

                    // To fix the casing problem of the bulk inserts
                    foreach (var dbField in dbFields)
                    {
                        var readerField = fields.FirstOrDefault(field => string.Equals(field, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
                        if (!string.IsNullOrEmpty(readerField))
                        {
                            filteredFields.Add(new Tuple <string, string>(readerField, dbField.UnquotedName));
                        }
                    }

                    // Iterate the filtered fields
                    foreach (var field in filteredFields)
                    {
                        sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2);
                    }
                }
                else
                {
                    // Iterate the provided mappings
                    foreach (var mapItem in mappings)
                    {
                        sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
                    }
                }

                // Open the connection and do the operation
                await connection.EnsureOpenAsync();

                await sqlBulkCopy.WriteToServerAsync(reader);

                // Hack the 'SqlBulkCopy' object
                var copiedField = GetRowsCopiedField();

                // Set the return value
                result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected;
            }

            // Result
            return(result);
        }
コード例 #4
0
        /// <summary>
        /// Bulk insert an instance of <see cref="DataTable"/> object into the database in an asynchronous way.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="tableName">The target table for bulk-insert operation.</param>
        /// <param name="dataTable">The <see cref="DataTable"/> object to be used in the bulk-insert operation.</param>
        /// <param name="rowState">The state of the rows to be copied to the destination.</param>
        /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
        /// <param name="options">The bulk-copy options to be used.</param>
        /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
        /// <param name="batchSize">The size per batch to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>The number of rows affected by the execution.</returns>
        public async Task <int> BulkInsertAsync(IDbConnection connection,
                                                string tableName,
                                                DataTable dataTable,
                                                DataRowState rowState = DataRowState.Unchanged,
                                                IEnumerable <BulkInsertMapItem> mappings = null,
                                                SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
                                                int?bulkCopyTimeout        = null,
                                                int?batchSize = null,
                                                IDbTransaction transaction = null)
        {
            // Validate the objects
            ValidateConnection(connection);
            ValidateTransaction(transaction);
            DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);

            // Variables for the operation
            var result = 0;

            // Before Execution Time
            var beforeExecutionTime = DateTime.UtcNow;

            // Actual Execution
            using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
            {
                // Set the destinationtable
                sqlBulkCopy.DestinationTableName = tableName;

                // Set the timeout
                if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
                {
                    sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
                }

                // Set the batch szie
                if (batchSize != null && batchSize.HasValue)
                {
                    sqlBulkCopy.BatchSize = batchSize.Value;
                }

                // Add the mappings
                if (mappings == null)
                {
                    // Get the actual DB fields
                    var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);

                    var fields         = GetDataColumns(dataTable).Select(column => column.ColumnName);
                    var filteredFields = new List <Tuple <string, string> >();

                    // To fix the casing problem of the bulk inserts
                    foreach (var dbField in dbFields)
                    {
                        var field = fields.FirstOrDefault(f => string.Equals(f, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
                        if (field != null)
                        {
                            filteredFields.Add(new Tuple <string, string>(field, dbField.UnquotedName));
                        }
                    }

                    // Iterate the filtered fields
                    foreach (var field in filteredFields)
                    {
                        sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2);
                    }
                }
                else
                {
                    // Iterate the provided mappings
                    foreach (var mapItem in mappings)
                    {
                        sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
                    }
                }

                // Open the connection and do the operation
                connection.EnsureOpen();
                await sqlBulkCopy.WriteToServerAsync(dataTable, rowState);

                // Set the return value
                result = GetDataRows(dataTable, rowState).Count();
            }

            // Result
            return(result);
        }
コード例 #5
0
        /// <summary>
        /// Bulk insert a list of data entity objects into the database.
        /// </summary>
        /// <typeparam name="TEntity">The type of the data entity object.</typeparam>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="entities">The list of the data entities to be bulk-inserted.</param>
        /// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
        /// <param name="options">The bulk-copy options to be used.</param>
        /// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
        /// <param name="batchSize">The size per batch to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>The number of rows affected by the execution.</returns>
        public int BulkInsert <TEntity>(IDbConnection connection,
                                        IEnumerable <TEntity> entities,
                                        IEnumerable <BulkInsertMapItem> mappings = null,
                                        SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
                                        int?bulkCopyTimeout        = null,
                                        int?batchSize = null,
                                        IDbTransaction transaction = null)
            where TEntity : class
        {
            // Validate the objects
            ValidateConnection(connection);
            ValidateTransaction(transaction);
            DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);

            // Variables for the operation
            var result = 0;

            // Before Execution Time
            var beforeExecutionTime = DateTime.UtcNow;

            // Actual Execution
            using (var reader = new DataEntityDataReader <TEntity>(entities, connection))
            {
                using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
                {
                    var dbSetting = connection.GetDbSetting();

                    // Set the destinationtable
                    sqlBulkCopy.DestinationTableName = ClassMappedNameCache.Get <TEntity>(dbSetting);

                    // Set the timeout
                    if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
                    {
                        sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
                    }

                    // Set the batch szie
                    if (batchSize != null && batchSize.HasValue)
                    {
                        sqlBulkCopy.BatchSize = batchSize.Value;
                    }

                    // Add the mappings
                    if (mappings == null)
                    {
                        // Get the actual DB fields
                        var dbFields       = DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>(dbSetting), transaction);
                        var fields         = reader.Properties.AsFields();
                        var filteredFields = new List <Tuple <string, string> >();

                        // To fix the casing problem of the bulk inserts
                        foreach (var dbField in dbFields)
                        {
                            var field = fields.FirstOrDefault(f => string.Equals(f.UnquotedName, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
                            if (field != null)
                            {
                                filteredFields.Add(new Tuple <string, string>(field.UnquotedName, dbField.UnquotedName));
                            }
                        }

                        // Iterate the filtered fields
                        foreach (var field in filteredFields)
                        {
                            sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2);
                        }
                    }
                    else
                    {
                        // Iterate the provided mappings
                        foreach (var mapItem in mappings)
                        {
                            sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
                        }
                    }

                    // Open the connection and do the operation
                    connection.EnsureOpen();
                    sqlBulkCopy.WriteToServer(reader);

                    // Hack the 'SqlBulkCopy' object
                    var copiedField = GetRowsCopiedField();

                    // Set the return value
                    result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected;
                }
            }

            // Result
            return(result);
        }