Back to: ADO.NET Core Tutorial For Beginners and Professionals
Bulk Operations using ADO.NET Core
In this article, I will discuss How to Implement Bulk Insert, Update, and Delete Operations using ADO.NET Core with Examples. Please read our previous article discussing ADO.NET Core SqlBulkCopy with Examples.
Bulk Operations using ADO.NET Core
Bulk operations in ADO.NET Core, which involve inserting, updating, and deleting large volumes of data efficiently, can significantly improve the performance of your application by reducing the number of round-trips to the database.
You will typically need to interact with a database to perform bulk insert, update, and delete operations using ADO.NET Core in a console application. I will use an SQL Server database and create an Employee table for this example. I will also show you how to use SqlBulkCopy for bulk insert, a combination of ADO.NET for update and delete operations, and how to utilize transactions to ensure data integrity.
First, let’s start with the SQL script to create the Employee table and insert some dummy data. So, please execute the following script on the SQL Server database:
CREATE DATABASE EmployeeDB;
GO
Use EmployeeDB;
GO
-- Create Employee table
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(50),
Position NVARCHAR(50),
Department NVARCHAR(50),
Salary DECIMAL(10, 2)
);
-- Insert some dummy data
INSERT INTO Employee (Name, Position, Department, Salary) VALUES
('Pranaya Rout', 'Software Engineer', 'IT', 70000),
('Anurag Mohanty', 'Project Manager', 'IT', 85000),
('Hina Sharma', 'Analyst', 'Finance', 65000),
('Rakesh Sing', 'Software Engineer', 'IT', 70000),
('Priyanka Deo', 'Project Manager', 'Finance', 85000),
('Preety Tiwary', 'Analyst', 'Finance', 65000);
Implementing Bulk Insert, Update, and Delete Operations using ADO.NET Core.
Next, let’s move on to the C# part of the console application. Make sure you have the Microsoft.Data.SqlClient package installed for SQL Server database access.
In the below example, the BulkInsert method demonstrates how to use SqlBulkCopy to insert a batch of records efficiently. For bulk update and delete operations, ADO.NET Core doesn’t provide built-in bulk update/delete functionality similar to SqlBulkCopy for insert operations. You would typically execute an SQL command that updates or deletes multiple rows based on specific criteria. Transactions ensure that your operations are completed successfully or rolled back in case of an error. Modify the Program class as follows:
using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
static void Main(string[] args)
{
// Dummy data to insert
var newEmployees = new DataTable();
newEmployees.Columns.Add("Name", typeof(string));
newEmployees.Columns.Add("Position", typeof(string));
newEmployees.Columns.Add("Department", typeof(string));
newEmployees.Columns.Add("Salary", typeof(decimal));
newEmployees.Rows.Add("Alice Smith", "Developer", "IT", 72000);
newEmployees.Rows.Add("Bob Johnson", "Developer", "IT", 76000);
BulkInsert(newEmployees);
BulkUpdate();
BulkDelete();
}
static void BulkInsert(DataTable employees)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.DestinationTableName = "Employee";
try
{
// Explicitly map DataTable columns to database table columns
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("Position", "Position");
bulkCopy.ColumnMappings.Add("Department", "Department");
bulkCopy.ColumnMappings.Add("Salary", "Salary");
bulkCopy.WriteToServer(employees);
transaction.Commit();
Console.WriteLine("Bulk insert successful.");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
transaction.Rollback();
}
}
}
}
}
static void BulkUpdate()
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
var command = new SqlCommand("UPDATE Employee SET Salary = Salary + 5000 WHERE Department = 'IT'", connection, transaction);
int affectedRows = command.ExecuteNonQuery();
Console.WriteLine($"{affectedRows} rows updated.");
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine($"Error during bulk update: {ex.Message}");
transaction.Rollback();
}
}
}
}
static void BulkDelete()
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
var command = new SqlCommand("DELETE FROM Employee WHERE Department = 'Finance'", connection, transaction);
int affectedRows = command.ExecuteNonQuery();
Console.WriteLine($"{affectedRows} rows deleted.");
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine($"Error during bulk delete: {ex.Message}");
transaction.Rollback();
}
}
}
}
}
}
Output:
Understanding the BulkInsert, BulkUpdate, and BulkDelete Methods:
The methods BulkInsert, BulkUpdate, and BulkDelete are designed to perform large-scale operations on a database efficiently. Each of these methods handles a different aspect of database manipulation, specifically for scenarios where you need to insert, update, or delete a large number of records at once. Let’s break down what each method does and how it works within the context of ADO.NET Core and SQL Server.
BulkInsert
The BulkInsert method uses the SqlBulkCopy class in ADO.NET Core to quickly insert large numbers of records into a database table. SqlBulkCopy is designed to be a high-performance method for adding large volumes of data to a SQL Server table. It works by copying data from a data source like a DataTable or an IDataReader into an SQL Server table. Here’s a brief overview of how BulkInsert works:
- Open a Connection: Establish a connection to the SQL Server database using a connection string.
- Begin a Transaction: Optionally start a database transaction to ensure the operation is atomic. This means either all inserts succeed or none are applied, maintaining data integrity.
- Configure SqlBulkCopy: Create an instance of SqlBulkCopy bound to the open connection and, optionally, the transaction. Configure it by setting the destination table name and any other options if necessary.
- Copy Data: Invoke WriteToServer, passing in the data to be inserted (e.g., a DataTable). This method efficiently transfers the data to the destination table.
- Commit/Rollback: Depending on the success of the operation, either commit the transaction to make the changes permanent or roll it back to undo the operation.
BulkUpdate
Unlike BulkInsert, there’s no built-in SqlBulkCopy equivalent for bulk update operations in ADO.NET. Bulk updates are typically done using a single SQL UPDATE statement that modifies all rows matching a specified condition or by using more advanced techniques like joining a source table with the target table on the server.
The example provided for BulkUpdate demonstrates a simple approach where a single SQL command is executed to update multiple rows based on a condition, such as increasing the salary of all employees in a specific department.
BulkDelete
Like BulkUpdate, BulkDelete operations involve executing an SQL DELETE statement to remove multiple rows from a table based on a specified condition, such as deleting all records in a specific department. This method is straightforward and efficient for conditions that affect many rows.
Async Bulk Operations using ADO.NET Core:
Let us rewrite the previous example using asynchronous methods in ADO.NET Core. We will use the async and await the keywords that are available in C#. This approach allows the program to perform database operations without blocking the main thread, improving the application’s responsiveness, especially in environments with high-latency database connections or when executing long-running queries. So, modify the Program class as follows:
using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
static async Task Main(string[] args)
{
// Dummy data to insert
var newEmployees = new DataTable();
newEmployees.Columns.Add("Name", typeof(string));
newEmployees.Columns.Add("Position", typeof(string));
newEmployees.Columns.Add("Department", typeof(string));
newEmployees.Columns.Add("Salary", typeof(decimal));
newEmployees.Rows.Add("Alice Smith", "Developer", "IT", 72000);
newEmployees.Rows.Add("Bob Johnson", "Developer", "IT", 76000);
await BulkInsertAsync(newEmployees);
await BulkUpdateAsync();
await BulkDeleteAsync();
}
static async Task BulkInsertAsync(DataTable employees)
{
using (var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = (SqlTransaction) await connection.BeginTransactionAsync())
{
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.DestinationTableName = "Employee";
try
{
// Explicitly map DataTable columns to database table columns
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("Position", "Position");
bulkCopy.ColumnMappings.Add("Department", "Department");
bulkCopy.ColumnMappings.Add("Salary", "Salary");
await bulkCopy.WriteToServerAsync(employees);
await transaction.CommitAsync();
Console.WriteLine("Bulk insert successful.");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
await transaction.RollbackAsync();
}
}
}
}
}
static async Task BulkUpdateAsync()
{
using (var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = (SqlTransaction)await connection.BeginTransactionAsync())
{
try
{
var command = new SqlCommand("UPDATE Employee SET Salary = Salary + 5000 WHERE Department = 'IT'", connection, transaction);
int affectedRows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"{affectedRows} rows updated.");
await transaction.CommitAsync();
}
catch (Exception ex)
{
Console.WriteLine($"Error during bulk update: {ex.Message}");
await transaction.RollbackAsync();
}
}
}
}
static async Task BulkDeleteAsync()
{
using (var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = (SqlTransaction)await connection.BeginTransactionAsync())
{
try
{
var command = new SqlCommand("DELETE FROM Employee WHERE Department = 'Finance'", connection, transaction);
int affectedRows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"{affectedRows} rows deleted.");
await transaction.CommitAsync();
}
catch (Exception ex)
{
Console.WriteLine($"Error during bulk delete: {ex.Message}");
await transaction.RollbackAsync();
}
}
}
}
}
}
Bulk Update by Joining a Source Table with Target Table using ADO.NET Core:
To perform a bulk update by joining a source table with the target table on the server, you can use a combination of a temporary table or table-valued parameters (TVPs) and a stored procedure or direct SQL command. This method is useful when updating many rows with different values. For this example, I’ll show how to use a temporary table to facilitate the bulk update, as it’s a common approach that doesn’t require the setup of TVPs.
First, ensure your SQL Server version supports the operations we’re discussing. The approach outlined below is supported in SQL Server 2008 and later versions.
Step 1: Creating a Temporary Table
You would typically create a temporary table that mirrors the data structure you wish to update. This table will hold the new values you want to apply to the target table.
-- Assuming the connection is already open and in the context of a transaction
CREATE TABLE #TempEmployeeUpdates
(
EmployeeID INT,
Salary DECIMAL(10, 2)
);
Step 2: Bulk Insert Into Temporary Table
Use SqlBulkCopy to insert the new data into the #TempEmployeeUpdates temporary table. This data represents the updates you wish to apply.
// Assuming `newSalaryUpdates` is a DataTable containing the updates (EmployeeID, NewSalary)
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.DestinationTableName = "#TempEmployeeUpdates";
bulkCopy.ColumnMappings.Add("EmployeeID", "EmployeeID");
bulkCopy.ColumnMappings.Add("Salary", "Salary");
bulkCopy.WriteToServer(newSalaryUpdates);
}
Step 3: Perform the Bulk Update
Now, you can perform the bulk update by joining the temporary table with the target table and updating the target table’s rows based on the values in the temporary table.
var updateCommand = @"
UPDATE e
SET e.Salary = tu.Salary
FROM Employee e
INNER JOIN #TempEmployeeUpdates tu ON e.EmployeeID = tu.EmployeeID";
using (var command = new SqlCommand(updateCommand, connection, transaction))
{
command.ExecuteNonQuery();
}
Full Example
Here is how you might integrate the temporary table approach into the BulkUpdate method, assuming you have a DataTable (newSalaryUpdates) ready with the updates:
using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
static void Main(string[] args)
{
DataTable newSalaryUpdates = new DataTable();
newSalaryUpdates.Columns.Add("EmployeeID", typeof(int));
newSalaryUpdates.Columns.Add("Salary", typeof(decimal));
// Example data - replace with actual data
newSalaryUpdates.Rows.Add(1, 78000); // EmployeeID = 1, New Salary = 78000
newSalaryUpdates.Rows.Add(2, 82000); // EmployeeID = 2, New Salary = 82000
// Add more rows as needed
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
// Call the BulkUpdate method
BulkUpdate(connection, transaction, newSalaryUpdates);
// Commit transaction if everything is successful
transaction.Commit();
}
catch (Exception ex)
{
// Handle any errors that occurred during the update
Console.WriteLine($"An error occurred: {ex.Message}");
// Rollback the transaction on error
transaction.Rollback();
}
}
}
}
static void BulkUpdate(SqlConnection connection, SqlTransaction transaction, DataTable newSalaryUpdates)
{
// Step 1: Create temporary table
var createTempTableCommand = @"
CREATE TABLE #TempEmployeeUpdates
(
EmployeeID INT,
Salary DECIMAL(10, 2)
);";
using (var command = new SqlCommand(createTempTableCommand, connection, transaction))
{
command.ExecuteNonQuery();
}
Console.WriteLine("Temporary Table #TempEmployeeUpdates Created");
// Step 2: Bulk insert into temporary table
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.DestinationTableName = "#TempEmployeeUpdates";
bulkCopy.ColumnMappings.Add("EmployeeID", "EmployeeID");
bulkCopy.ColumnMappings.Add("Salary", "Salary");
bulkCopy.WriteToServer(newSalaryUpdates);
}
// Step 3: Update the target table using a join with the temporary table
var updateCommand = @"
UPDATE e
SET e.Salary = tu.Salary
FROM Employee e
INNER JOIN #TempEmployeeUpdates tu ON e.EmployeeID = tu.EmployeeID";
using (var command = new SqlCommand(updateCommand, connection, transaction))
{
command.ExecuteNonQuery();
}
Console.WriteLine("Bulk Update Completed using Join");
// Optionally, drop the temporary table if no longer needed
// Step 4: Drop the temporary table
var dropTempTableCommand = "DROP TABLE #TempEmployeeUpdates;";
using (var command = new SqlCommand(dropTempTableCommand, connection, transaction))
{
command.ExecuteNonQuery();
}
Console.WriteLine("Temporary Table #TempEmployeeUpdates Deleted");
}
}
}
Output:
When Should We Use Bulk Insert Update Delete Operation in ADO.NET Core?
Using bulk insert, update, and delete operations in ADO.NET Core is particularly beneficial when you need to process large volumes of data efficiently. These operations allow for the manipulation of multiple rows in a single database round-trip, significantly improving performance by reducing network latency and the overhead of executing individual commands for each row. Here are some scenarios where using bulk operations is advantageous:
- Large Data Imports: Bulk insert operations can dramatically reduce the time required to transfer this data into your database when importing large datasets from external sources, such as CSV files or external databases.
- Batch Processing: In applications that process large amounts of data in batches (e.g., nightly jobs to update database records based on daily transactions), bulk update and delete operations can optimize performance and ensure timely completion of such tasks.
- Data Migration or Synchronization: During data migration projects or synchronizing data between different environments (such as staging and production), using bulk operations can help accomplish the task more efficiently.
- Performance Optimization: For applications that experience performance bottlenecks due to frequent database operations, consolidating these operations into bulk actions can significantly reduce load times and improve user experience.
- Real-time Data Processing: In scenarios where an application must process and update large volumes of data in real-time (such as financial transactions, logs, or sensor data), bulk operations can help maintain performance and ensure data integrity.
Best Practices for Using Bulk Operations
- Transaction Management: Use transactions to ensure data integrity, especially when performing bulk updates or deletes. This helps in rolling back changes in case of errors.
- Batch Size Consideration: Determine an optimal batch size for your operations. A batch size that is too large may lead to timeouts or excessive memory consumption, while a batch size that is too small might not offer the desired performance improvements.
- Monitoring and Logging: Implement proper monitoring and logging to track the performance of your bulk operations and quickly identify any issues or bottlenecks.
- Error Handling: Implement robust error handling mechanisms to manage partial failures or data inconsistencies during bulk operations.
In the next article, I will discuss ADO.NET Core Bulk Insert and Update and Delete using Stored Procedure with Examples. In this article, I explain How to Implement Bulk Insert, Update, and Delete Operations using ADO.NET Core with Examples. I would like to have your feedback. Please post your feedback, questions, or comments about this Bulk Insert, Update, and Delete Operations using the ADO.NET Core with Examples article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.