AmpScm.RepoDb.SqlServer.BulkOperations
1.14.0-alphaSD02
dotnet add package AmpScm.RepoDb.SqlServer.BulkOperations --version 1.14.0-alphaSD02
NuGet\Install-Package AmpScm.RepoDb.SqlServer.BulkOperations -Version 1.14.0-alphaSD02
<PackageReference Include="AmpScm.RepoDb.SqlServer.BulkOperations" Version="1.14.0-alphaSD02" />
paket add AmpScm.RepoDb.SqlServer.BulkOperations --version 1.14.0-alphaSD02
#r "nuget: AmpScm.RepoDb.SqlServer.BulkOperations, 1.14.0-alphaSD02"
// Install AmpScm.RepoDb.SqlServer.BulkOperations as a Cake Addin #addin nuget:?package=AmpScm.RepoDb.SqlServer.BulkOperations&version=1.14.0-alphaSD02&prerelease // Install AmpScm.RepoDb.SqlServer.BulkOperations as a Cake Tool #tool nuget:?package=AmpScm.RepoDb.SqlServer.BulkOperations&version=1.14.0-alphaSD02&prerelease
RepoDb.SqlServer.BulkOperations
An extension library that contains the official Bulk Operations of RepoDB for SQL Server.
Important Pages
- GitHub Home Page - to learn more about the core library.
- 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 BulkInsert 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).
The bulk operations can hugely improve the performance by more than 90% when processing a large datasets.
Core Features
Community Engagements
- GitHub - for any issues, requests and problems.
- StackOverflow - for any technical questions.
- Twitter - for the latest news.
- Gitter Chat - for direct and live Q&A.
License
Apache-2.0 - Copyright © 2020 - Michael Camara Pendon
Installation
At the Package Manager Console, write the command below.
> Install-Package RepoDb.SqlServer.BulkOperations
Then call the bootstrapper once.
RepoDb.SqlServerBootstrap.Initialize();
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"));
}
}
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 is compatible. |
.NET Core | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 was computed. |
.NET Framework | net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.0
- AmpScm.RepoDb (>= 1.14.0-alphaSD02)
- AmpScm.RepoDb.SqlServer (>= 1.14.0-alphaSD02)
- Microsoft.Bcl.AsyncInterfaces (>= 8.0.0)
- Microsoft.Bcl.HashCode (>= 1.1.1)
- Microsoft.Data.SqlClient (>= 5.2.2)
- System.ComponentModel.Annotations (>= 5.0.0)
- System.Data.Common (>= 4.3.0)
- System.Dynamic.Runtime (>= 4.3.0)
- System.Linq.Async (>= 6.0.1)
- System.Memory (>= 4.5.5)
- System.Reflection.Emit (>= 4.7.0)
- System.Reflection.Emit.Lightweight (>= 4.7.0)
- System.Reflection.TypeExtensions (>= 4.7.0)
- System.Text.Json (>= 8.0.5)
- System.Text.RegularExpressions (>= 4.3.1)
-
net8.0
- AmpScm.RepoDb (>= 1.14.0-alphaSD02)
- AmpScm.RepoDb.SqlServer (>= 1.14.0-alphaSD02)
- Microsoft.Bcl.AsyncInterfaces (>= 8.0.0)
- Microsoft.Data.SqlClient (>= 5.2.2)
- System.ComponentModel.Annotations (>= 5.0.0)
- System.Data.Common (>= 4.3.0)
- System.Dynamic.Runtime (>= 4.3.0)
- System.Linq.Async (>= 6.0.1)
- System.Memory (>= 4.5.5)
- System.Reflection.Emit (>= 4.7.0)
- System.Reflection.Emit.Lightweight (>= 4.7.0)
- System.Reflection.TypeExtensions (>= 4.7.0)
- System.Text.Json (>= 8.0.5)
- System.Text.RegularExpressions (>= 4.3.1)
-
net9.0
- AmpScm.RepoDb (>= 1.14.0-alphaSD02)
- AmpScm.RepoDb.SqlServer (>= 1.14.0-alphaSD02)
- Microsoft.Bcl.AsyncInterfaces (>= 8.0.0)
- Microsoft.Data.SqlClient (>= 5.2.2)
- System.ComponentModel.Annotations (>= 5.0.0)
- System.Data.Common (>= 4.3.0)
- System.Dynamic.Runtime (>= 4.3.0)
- System.Linq.Async (>= 6.0.1)
- System.Memory (>= 4.5.5)
- System.Reflection.Emit (>= 4.7.0)
- System.Reflection.Emit.Lightweight (>= 4.7.0)
- System.Reflection.TypeExtensions (>= 4.7.0)
- System.Text.Json (>= 8.0.5)
- System.Text.RegularExpressions (>= 4.3.1)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
1.14.0-alphaSD02 | 3 | 11/22/2024 |