AmpScm.RepoDb.SqlServer.BulkOperations 1.14.0-alphaSD02

This is a prerelease version of AmpScm.RepoDb.SqlServer.BulkOperations.

dotnet add package AmpScm.RepoDb.SqlServer.BulkOperations --version 1.14.0-alphaSD02

NuGet\Install-Package AmpScm.RepoDb.SqlServer.BulkOperations -Version 1.14.0-alphaSD02

This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.

<PackageReference Include="AmpScm.RepoDb.SqlServer.BulkOperations" Version="1.14.0-alphaSD02" />

For projects that support PackageReference, copy this XML node into the project file to reference the package.

paket add AmpScm.RepoDb.SqlServer.BulkOperations --version 1.14.0-alphaSD02

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

#r "nuget: AmpScm.RepoDb.SqlServer.BulkOperations, 1.14.0-alphaSD02"

#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.

// 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

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

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.

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

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);
}

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	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.

Compatible target framework(s)

Included target framework(s) (in package)

Learn more about Target Frameworks and .NET Standard.

.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	40	11/22/2024

http://repodb.net/release/sqlserverbulk

AmpScm.RepoDb.SqlServer.BulkOperations 1.14.0-alphaSD02

RepoDb.SqlServer.BulkOperations

Important Pages

Why use the Bulk Operations?

Core Features

Community Engagements

License

Installation

Special Arguments

Identity Setting Alignment

Async Methods

Caveats

BulkDelete

BulkDelete via PrimaryKeys

BulkDelete via DataEntities

BulkDelete via DataTable

BulkDelete via DbDataReader

BulkInsert

BulkInsert via DataEntities

BulkInsert via DataTable

BulkInsert via DbDataReader

BulkMerge

BulkMerge via DataEntities

BulkMerge via DataTable

BulkMerge via DbDataReader

BulkUpdate

BulkUpdate via DataEntities

BulkUpdate via DataTable

BulkUpdate via DbDataReader

.NETStandard 2.0

net8.0

net9.0

NuGet packages

GitHub repositories