Website - docs, features, classes, references, releases and blogs.
Why use the Bulk Operations?
Basically, we do the normal Delete, Insert, Merge and Update operations when interacting with the database. The data is processed in an atomic way. If we do call the batch operations, the multiple single operation is just being batched and executed at the same time. In short, there are round-trips between your application and the database. Thus does not give you the maximum performance when doing the CRUD operations.
With bulk operations, all data is brought from the client application to the database via process. It ignores the audit, logs, constraints and any other database special handling. After that, the data is being processed at the same time in the database (server).
Or, visit our installation page for more information.
Special Arguments
The arguments qualifiers, isReturnIdentity and usePhysicalPseudoTempTable is provided at BulkDelete, BulkMerge and BulkUpdate operations.
The argument qualifiers is used to define the qualifier fields to be used in the operation. It usually refers to the WHERE expression of SQL Statements. If not given, the primary key (or identity) field will be used.
The argument isReturnIdentity is used to define the behaviour of the execution whether the newly generated identity will be set-back to the data entities. By default, it is disabled.
The argument usePhysicalPseudoTempTable is used to define whether a physical pseudo-table will be created during the operation. By default, a temporary table (ie: #TableName) is used.
Identity Setting Alignment
The library has enforced an additional logic to ensure the identity setting alignment if the isReturnIdentity is enabled during the calls. This affects both the BulkInsert and BulkMerge operations.
Basically, a new column named __RepoDb_OrderColumn is being added into the pseudo-temporary table if the identity field is present on the underlying target table. This column will contain the actual index of the entity model from the IEnumerable<T> object.
During the bulk operation, a dedicated DbParameter object is created that targets this additional column with a value of the entity model index, thus ensuring that the index value is really equating the index of the entity data from the IEnumerable<T> object. The resultsets of the pseudo-temporary table are being ordered using this newly generated column prior the actual merge to the underlying table.
When the newly generated identity value is being set back to the data model, the value of the __RepoDb_OrderColumn column is being used to look-up the proper index of the equating entity model from the IEnumerable<T> object, then, the compiled identity-setter function is used to assign back the identity value into the identity property.
Async Methods
All synchronous methods has an equivalent asynchronous (Async) methods.
Caveats
RepoDB is automatically setting the value of options argument to SqlBulkCopyOptions.KeepIdentity when calling the BulkDelete, BulkMerge and BulkUpdate if you have not passed any qualifiers and if your table has an IDENTITY primary key column. The same logic will apply if there is no primary key but has an IDENTITY column defined in the table.
In addition, when calling the BulkDelete, BulkMerge and BulkUpdate operations, the library is creating a pseudo temporary table behind the scene. It requires your user to have the correct privilege to create a table in the database, otherwise a SqlException will be thrown.
BulkDelete
Bulk delete a list of data entity objects (or via primary keys) from the database. It returns the number of rows deleted from the database.
BulkDelete via PrimaryKeys
using (var connection = new SqlConnection(ConnectionString))
{
var primaryKeys = new object[] { 10045, ..., 11902 };
var rows = connection.BulkDelete<Customer>(primaryKeys);
}
Or
using (var connection = new SqlConnection(ConnectionString))
{
var primaryKeys = new object[] { 10045, ..., 11902 };
var rows = connection.BulkDelete("Customer", primaryKeys);
}
BulkDelete via DataEntities
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkDelete<Customer>(customers);
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkDelete<Customer>(customers, qualifiers: e => new { e.LastName, e.DateOfBirth });
}
Or via table-name
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkDelete("Customer", customers);
}
Or via table-name with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkDelete("Customer", customers, qualifiers: Field.From("LastName", "DateOfBirth"));
}
BulkDelete via DataTable
using (var connection = new SqlConnection(ConnectionString))
{
var table = GetCustomersAsDataTable();
var rows = connection.BulkDelete("Customer", table);
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var table = GetCustomersAsDataTable();
var rows = connection.BulkDelete("Customer", table, qualifiers: Field.From("LastName", "DateOfBirth"));
}
BulkDelete via DbDataReader
using (var connection = new SqlConnection(ConnectionString))
{
using (var reader = connection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
{
var rows = connection.BulkDelete("Customer", reader);
}
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
using (var reader = connection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
{
var rows = connection.BulkDelete("Customer", reader, qualifiers: Field.From("LastName", "DateOfBirth"));
}
}
BulkInsert
Bulk insert a list of data entity objects into the database. All data entities will be inserted as new records in the database. It returns the number of rows inserted in the database.
BulkInsert via DataEntities
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkInsert<Customer>(customers);
}
Or via table-name
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkInsert("Customer", customers);
}
BulkInsert via DataTable
using (var connection = new SqlConnection(ConnectionString))
{
var table = GetCustomersAsDataTable();
var rows = connection.BulkInsert("Customer", table);
}
BulkInsert via DbDataReader
using (var connection = new SqlConnection(ConnectionString))
{
using (var reader = connection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
{
var rows = connection.BulkInsert("Customer", reader);
}
}
BulkMerge
Bulk merge a list of data entity objects into the database. A record is being inserted in the database if it is not exists using the defined qualifiers. It returns the number of rows inserted/updated in the database.
BulkMerge via DataEntities
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkMerge<Customer>(customers);
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkMerge<Customer>(customers, qualifiers: e => new { e.LastName, e.DateOfBirth });
}
Or via table-name
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkMerge("Customer", customers);
}
Or via table-name with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkMerge("Customer", customers, qualifiers: Field.From("LastName", "DateOfBirth"));
}
BulkMerge via DataTable
using (var connection = new SqlConnection(ConnectionString))
{
var table = GetCustomersAsDataTable();
var rows = connection.BulkMerge("Customer", table);
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var table = GetCustomersAsDataTable();
var rows = connection.BulkMerge("Customer", table, qualifiers: Field.From("LastName", "DateOfBirth"));
}
BulkMerge via DbDataReader
using (var connection = new SqlConnection(ConnectionString))
{
using (var reader = connection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
{
var rows = connection.BulkMerge("Customer", reader);
}
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
using (var reader = connection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
{
var rows = connection.BulkMerge("Customer", reader, qualifiers: Field.From("LastName", "DateOfBirth"));
}
}
BulkUpdate
Bulk update a list of data entity objects into the database. It returns the number of rows updated in the database.
BulkUpdate via DataEntities
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkUpdate<Customer>(customers);
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkUpdate<Customer>(customers, qualifiers: e => new { e.LastName, e.DateOfBirth });
}
Or via table-name
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkUpdate("Customer", customers);
}
Or via table-name with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var customers = GetCustomers();
var rows = connection.BulkUpdate("Customer", customers, qualifiers: Field.From("LastName", "DateOfBirth"));
}
BulkUpdate via DataTable
using (var connection = new SqlConnection(ConnectionString))
{
var table = GetCustomersAsDataTable();
var rows = connection.BulkUpdate("Customer", table);
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
var table = GetCustomersAsDataTable();
var rows = connection.BulkUpdate("Customer", table, qualifiers: Field.From("LastName", "DateOfBirth"));
}
BulkUpdate via DbDataReader
using (var connection = new SqlConnection(ConnectionString))
{
using (var reader = connection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
{
var rows = connection.BulkUpdate("Customer", reader);
}
}
Or with qualifiers
using (var connection = new SqlConnection(ConnectionString))
{
using (var reader = connection.ExecuteReader("SELECT * FROM [dbo].[Customer];"))
{
var rows = connection.BulkUpdate("Customer", reader, qualifiers: Field.From("LastName", "DateOfBirth"));
}
}
