Back to: ADO.NET Core Tutorial For Beginners and Professionals
Bulk Operations Using Stored Procedure in ADO.NET Core
In this article, I will discuss how to Implement Bulk Operations using Stored Procedures in ADO.NET Core with Examples. Please read our previous article, which discusses implementing Bulk Operations using the ADO.NET Core SqlBulkCopy Class with examples.
Stored Procedures using Table-Valued Parameters in ADO.NET Core
Bulk INSERT, UPDATE, and DELETE operations are common tasks in database management, especially when dealing with large datasets. Bulk operations in a database can significantly improve performance by minimizing the number of round-trips to the database. ADO.NET Core provides efficient ways to handle such operations, such as using Stored Procedures with table-valued parameters.
What are Table-Valued Parameters (TVPs)?
Table-Valued Parameters (TVPs) are user-defined table types in SQL Server that allow you to pass multiple rows of structured data as a parameter to a stored procedure or function. Instead of sending multiple individual SQL statements for operations such as insert, update, or delete, TVPs enable batch operations using a single structured input. They are ideal for sending bulk data from a client application, such as a .NET app, to SQL Server. Under the hood, we need to define a user-defined table type in the database, and then we need to send a .NET DataTable as a single, strongly typed parameter.
Key Benefits of Table-Valued Parameters (TVPs):
The following are the Key Benefits of using Table-Valued Parameters (TVPs):
- Efficiency: By sending multiple rows at once, TVPs drastically reduce the number of round-trips required between the application and the database. This can significantly improve performance for batch operations.
- Performance: TVPs handle hundreds or thousands of rows in one call, avoiding the overhead of multiple individual INSERT or UPDATE statements.
- Type Safety: Because TVPs are defined by a user-defined table type, the database ensures that the data structure adheres to the predefined schema. This helps maintain data integrity.
- Flexibility: TVPs support a wide range of operations (inserts, updates, deletes), making them a versatile choice for bulk data processing.
Stored Procedure Using Table-Valued Parameters (TVPs)
A Stored Procedure using a TVP accepts a parameter of a user‑defined table type. This enables the procedure to handle multiple rows of data in a single execution, enhancing performance, particularly for bulk data operations. Inside the Stored Procedure, you can join this TVP with other tables or perform set-based operations directly within the procedure.
Use Cases:
- Bulk Insert: Insert all rows from the TVP into a target table in one command.
- Bulk Update: Update rows in the target table by joining them with the TVP.
- Bulk Delete: Delete rows in the target table that match the criteria provided by the TVP.
Steps to Work with TVPs in Stored Procedures:
- Define a user-defined table type in SQL Server that matches the schema of the data you want to pass.
- Create a stored procedure that accepts a parameter of that table type.
- Use set-based operations (e.g., INSERT INTO … SELECT …, UPDATE … JOIN, DELETE … JOIN) within the procedure to process all rows from the TVP in a single execution.
- Call the stored procedure from the client application, passing in a DataTable that corresponds to the TVP’s schema.
SQL Server Database Setup:
Here, we will create the ProductsDB database, the Products table, the Table Type Parameter, and stored procedures that will accept the Table Type Parameter. The Table Type Parameter allows us to pass multiple records using a DataTable to the Stored Procedure. Then, using the Table Type Parameter, we will perform bulk Insert, Update, and Delete Operations within the stored procedure.
Please execute the following SQL Script to create the SQL Server database, table, table type parameter, and stored procedures to perform BULK operations. Here, we are performing bulk operations using an SQL Join between the target table and the TVP.
-- Create ProductsDB Database
CREATE DATABASE ProductsDB;
GO
-- Switch to ProductsDB Database
USE ProductsDB;
GO
-- Create the Products table
CREATE TABLE Products
(
ProductId INT PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
IsDiscontinued BIT NOT NULL DEFAULT(0)
);
GO
-- Create a User‑defined table type that matching the Products schema
CREATE TYPE ProductTVP AS TABLE
(
ProductId INT,
Name NVARCHAR(200),
Price DECIMAL(10,2),
IsDiscontinued BIT
);
GO
-- Bulk Insert Using User‑defined table type
CREATE PROCEDURE SP_Product_BulkInsert
@Products ProductTVP READONLY
AS
BEGIN
INSERT INTO Products
SELECT ProductId, Name, Price, IsDiscontinued FROM @Products;
END;
GO
-- Bulk Update Using User‑defined table type
CREATE PROCEDURE SP_Product_BulkUpdate
@Products ProductTVP READONLY
AS
BEGIN
UPDATE P
SET P.Name = T.Name,
P.Price = T.Price,
P.IsDiscontinued = T.IsDiscontinued
FROM Products P
JOIN @Products T ON T.ProductId = P.ProductId;
END;
GO
-- Bulk Delete Using User‑defined table type
CREATE PROCEDURE SP_Product_BulkDelete
@Products ProductTVP READONLY
AS
BEGIN
DELETE P
FROM Products P
JOIN @Products T ON T.ProductId = P.ProductId;
END;
GO
SQL Script Explanations:
- ProductsDB Database: This database will hold a Products table, a user-defined table type (ProductTVP), and stored procedures for each bulk operation (insert, update, delete).
- Products Table: The schema includes a primary key (ProductId), product details (Name, Price), and a status flag (IsDiscontinued).
- ProductTVP Table Type: A user-defined table type that matches the Products table schema, used for passing structured data into stored procedures. The ProductTVP defines the structure of data that can be passed as a parameter. It ensures consistent data formatting and type safety.
Stored Procedures for Bulk Operations:
The stored procedures use the @Products TVP parameter in their operations. This parameter is marked READONLY because TVPs cannot be modified inside the procedure.
- SP_Product_BulkInsert: Inserts all rows from the TVP into the Products table.
- SP_Product_BulkUpdate: Updates existing rows in Products based on matches found in the TVP.
- SP_Product_BulkDelete: Deletes rows in Products that match the keys in the TVP.
Each stored procedure employs set-based logic (INSERT … SELECT …, UPDATE … JOIN, DELETE … JOIN) to efficiently process all rows in one operation, avoiding the performance hit of row-by-row processing.
Note: The READONLY keyword is mandatory for TVP parameters. Declaring it as READONLY means the procedure can read but not modify the TVP rows.
Consuming Stored Procedures with TVPs Using ADO.NET Core
To call a Stored Procedure with TVP in ADO.NET Core, we need to follow the steps below:
- Prepare a DataTable: The DataTable should have columns that match the TVP’s schema (ProductId, Name, Price, IsDiscontinued). Populate this DataTable with the data you wish to insert, update, or delete.
- Create a SqlCommand: Set the CommandType to StoredProcedure. Set the CommandText to the stored procedure name (e.g., SP_Product_BulkInsert).
- Add the TVP Parameter: Use SqlParameter with SqlDbType.Structured data type. Specify the TypeName to match the user-defined table type (e.g., ProductTVP). Set the Value to the DataTable you prepared.
- Execute the Command: Open a SqlConnection and call ExecuteNonQueryAsync to run the procedure.
For a better understanding, please look at the following code, which shows the syntax to call a stored procedure with a TVP.
Example to Understand Bulk Operations using Stored Procedures with TVP in ADO.NET Core
The following example demonstrates bulk insert, update, and delete operations using ADO.NET Core, stored procedures, and TVPs. The example code is self-explained, so please read the comment lines for a better understanding.
using System.Data;
using Microsoft.Data.SqlClient;
namespace BulkOperationsDemo
{
public class Program
{
// Connection string to ProductsDB database
private const string ConnectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductsDB;Trusted_Connection=True;TrustServerCertificate=True;";
static async Task Main(string[] args)
{
try
{
Console.WriteLine("Bulk Operations using Stored Procedure and TVP Started.");
// Create and populate DataTables for each operation
var insertTable = CreateOrderDataTable();
insertTable.Rows.Add(1, "USB‑C Cable", 299.00m, false);
insertTable.Rows.Add(2, "Wireless Mouse", 749.00m, false);
insertTable.Rows.Add(3, "Laptop Stand", 1399.00m, false);
insertTable.Rows.Add(4, "Samsung Mobile", 5599.00m, false);
var updateTable = CreateOrderDataTable();
updateTable.Rows.Add(1, "USB Type C Cable", 399.00m, false);
updateTable.Rows.Add(3, "Laptop Premimum Stand", 1599.00m, false);
// Only ProductId matters for delete; other columns ignored by SP
var deleteTable = CreateOrderDataTable();
deleteTable.Rows.Add(2, null, 0, false);
deleteTable.Rows.Add(4, null, 0, false);
// Perform bulk operations
await BulkInsertAsync(insertTable);
await BulkUpdateAsync(updateTable);
await BulkDeleteAsync(deleteTable);
Console.WriteLine("All bulk operations completed successfully.");
}
catch (Exception ex)
{
Console.Error.WriteLine($"Error during bulk operations: {ex.Message}");
}
}
// Creates an empty DataTable whose schema matches ProductTVP
private static DataTable CreateOrderDataTable()
{
var table = new DataTable();
table.Columns.Add("ProductId", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Price", typeof(decimal));
table.Columns.Add("IsDiscontinued", typeof(bool));
return table;
}
// Bulk Insert via stored procedure with TVP
private static async Task BulkInsertAsync(DataTable products)
{
using (var connection = new SqlConnection(ConnectionString))
{
// Prepare command for SP_Product_BulkInsert
using var command = new SqlCommand("SP_Product_BulkInsert", connection)
{
CommandType = CommandType.StoredProcedure
};
// Attach the TVP parameter
var tvpParameter = new SqlParameter("@Products", SqlDbType.Structured)
{
TypeName = "ProductTVP",
Value = products
};
command.Parameters.Add(tvpParameter);
// Open connection & execute command
await connection.OpenAsync();
int rows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"Inserted {rows} rows.");
}
}
// Bulk Update via stored procedure with TVP
private static async Task BulkUpdateAsync(DataTable products)
{
// Prepare command for SP_Product_BulkUpdate
using (var connection = new SqlConnection(ConnectionString))
{
using var command = new SqlCommand("SP_Product_BulkUpdate", connection)
{
CommandType = CommandType.StoredProcedure
};
// Attach TVP in a slightly different overload
command.Parameters.Add(new SqlParameter("@Products", SqlDbType.Structured)
{
TypeName = "ProductTVP",
Value = products
});
// Open connection & execute command
await connection.OpenAsync();
int rows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"Updated {rows} rows.");
}
}
// Bulk Delete via stored procedure with TVP
private static async Task BulkDeleteAsync(DataTable products)
{
// Prepare command for SP_Product_BulkDelete
using (var connection = new SqlConnection(ConnectionString))
{
using var command = new SqlCommand("SP_Product_BulkDelete", connection)
{
CommandType = CommandType.StoredProcedure
};
// Attach the same TVP pattern
command.Parameters.Add(new SqlParameter("@Products", SqlDbType.Structured)
{
TypeName = "ProductTVP",
Value = products
});
// Execute DELETE
await connection.OpenAsync();
int rows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"Deleted {rows} rows.");
}
}
}
}
Code Explanations:
- Connection & Command: Each helper method (BulkInsertAsync, BulkUpdateAsync, and BulkDeleteAsync) creates its own SqlConnection and SqlCommand pointing to the appropriate stored procedure.
- DataTable Schema: CreateProductDataTable() ensures your in‑memory table columns perfectly match ProductTVP.
- TVP Parameter: You must set SqlDbType = Structured and TypeName to your user‑defined table type.
Output:
What is SQL MERGE?
SQL MERGE is a single SQL statement that combines INSERT, UPDATE, and DELETE operations based on specified conditions. It can compare a target table to a source dataset (like a TVP or Temporary table) and apply different actions depending on whether matches are found.
When to use SQL MERGE?
- Perform multiple operations (insert, update, delete) in one statement.
- Easier to maintain as the logic for all operations resides in a single block of code.
- Useful for scenarios where you need conditional logic to decide which rows to insert, update, or delete.
Basic Syntax of SQL MERGE:
MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.Id = S.Id
WHEN MATCHED THEN
UPDATE SET T.Col = S.Col
WHEN NOT MATCHED THEN
INSERT (Col1, Col2) VALUES (S.Col1, S.Col2)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Syntax Explanations:
- MERGE INTO TargetTable AS T: TargetTable is the table you want to modify. AS T gives it the alias T, so you can refer to TargetTable columns using this alias.
- USING SourceTable AS S: SourceTable can be another physical table, a temporary table, a derived table (subquery), or even a table‑valued parameter. AS S gives it the alias S, so you can refer to SourceTable columns using this alias.
- ON T.Id = S.Id: Defines the match condition. Rows in T and S are considered matching when T.Id = S.Id.
- WHEN MATCHED THEN UPDATE …: Matched means the row exists in both T and S (as per the ON condition). We then UPDATE the target row with values from the source. We can update one or more columns (T.Col1 = S.Col1, T.Col2 = S.Col2, …).
- WHEN NOT MATCHED THEN INSERT …: Not matched (also called “NOT MATCHED BY TARGET”) means the row exists in S but not in T. In this case, we perform an INSERT into T operation, specifying the target columns and pulling values from S.
- WHEN NOT MATCHED BY SOURCE THEN DELETE;: Not matched by source means the row exists in T but not in S. You DELETE that orphaned row from the target.
Note: In real-time, you can also add some other conditions to decide what operations to perform. For example, we can add an additional parameter in the source data set, such as Operation flag (I/U/D), indicating whether to perform Insert (I), Update (U), or Delete (D) operations.
Modifying the Stored Procedure to Use SQL MERGE:
Please modify the stored procedures to use SQL Merge to perform Bulk Insert, Update, and Delete operations as follows:
-- MERGE‑based Bulk Insert (only inserts missing rows)
ALTER PROCEDURE SP_Product_BulkInsert
@Products ProductTVP READONLY
AS
BEGIN
MERGE INTO Products AS Target
USING @Products AS Source
ON Target.ProductId = Source.ProductId
-- If the row doesn’t exist, insert it
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductId, Name, Price, IsDiscontinued)
VALUES (Source.ProductId, Source.Name, Source.Price, Source.IsDiscontinued);
END;
GO
-- MERGE‑based Bulk Update (only updates matching rows)
ALTER PROCEDURE SP_Product_BulkUpdate
@Products ProductTVP READONLY
AS
BEGIN
MERGE INTO Products AS Target
USING @Products AS Source
ON Target.ProductId = Source.ProductId
-- If the row exists, update it
WHEN MATCHED THEN
UPDATE SET
Target.Name = Source.Name,
Target.Price = Source.Price,
Target.IsDiscontinued = Source.IsDiscontinued;
END;
GO
-- MERGE‑based Bulk Delete (only deletes matching rows)
ALTER PROCEDURE SP_Product_BulkDelete
@Products ProductTVP READONLY
AS
BEGIN
MERGE INTO Products AS Target
USING @Products AS Source
ON Target.ProductId = Source.ProductId
-- If the row exists in both, delete it
WHEN MATCHED THEN
DELETE;
END;
GO
Note: No application code changes are required to accommodate SQL MERGE. Now, you can run the application, and it should work as expected.
What are the differences between SQL Join and SQL Merge to Perform BULK Operations in SQL Server?
Let us understand the Differences Between SQL JOIN and SQL MERGE, and when to use one over the other.
SQL JOIN:
Primarily used to retrieve or manipulate data across multiple tables. Requires separate statements for insert, update, and delete.
- Typically used in separate UPDATE or DELETE statements.
- Requires multiple statements for different operations.
- Better when operations are straightforward and well-separated.’
- Good for isolated operations.
SQL MERGE:
Can perform insert, update, and delete operations simultaneously, enhancing performance and reducing complexity.
- Combines multiple operations into a single statement.
- Ideal for complex scenarios where rows might need to be inserted, updated, or deleted based on conditions.
- Reduces repetitive code and centralizes logic.
- More efficient for mixed upsert/delete scenarios
When to Use One Over the Other:
- Use SQL JOIN when each operation (insert, update, delete) has clearly defined conditions and can be handled separately. So, when you know which operation to perform (only insert, only update, or only delete), you need to use SQL JOIN.
- Use SQL MERGE when operations need to be unified, especially when frequently switching between inserting new rows and updating existing ones. When the same source data contains both new and existing rows, and you want to upsert in a single atomic action, you can use SQL MERGE.
Stored Procedure to Perform Bulk INSERT, UPDATE, and DELETE Operations:
Now, we will create a single stored procedure to perform Bulk INSERT, UPDATE, and DELETE Operations using SQL MERGE. Here, we will:
- Create a new TVP that includes an Operation Flag (I/U/D).
- Build a single stored procedure SP_Product_Merge that INSERTs, UPDATEs, or DELETEs based on that flag.
Please execute the following code to create the TVP and a stored procedure using SQL MERGE in our ProductsDB database.
-- Switch to ProductsDB database
USE ProductsDB;
GO
-- Create TVP to include an Operation column
CREATE TYPE ProductMergeTVP AS TABLE
(
ProductId INT,
Name NVARCHAR(200),
Price DECIMAL(10,2),
IsDiscontinued BIT,
Operation CHAR(1) -- 'I' = Insert, 'U' = Update, 'D' = Delete
);
GO
-- The Single MERGE‑based Stored Procedure
CREATE OR ALTER PROCEDURE SP_Product_Merge
@Products ProductMergeTVP READONLY
AS
BEGIN
-- We will not reurned the number of Rows Affected
SET NOCOUNT ON;
MERGE INTO Products AS Target
USING @Products AS Source
ON Target.ProductId = Source.ProductId
-- Delete rows where Operation = 'D'
WHEN MATCHED AND Source.Operation = 'D' THEN
DELETE
-- Update existing rows where Operation = 'U'
WHEN MATCHED AND Source.Operation = 'U' THEN
UPDATE SET
Target.Name = Source.Name,
Target.Price = Source.Price,
Target.IsDiscontinued = Source.IsDiscontinued
-- Insert new rows where Operation = 'I'
WHEN NOT MATCHED BY TARGET AND Source.Operation = 'I' THEN
INSERT (ProductId, Name, Price, IsDiscontinued)
VALUES (Source.ProductId, Source.Name, Source.Price, Source.IsDiscontinued);
END;
GO
Consuming the above Stored Procedure:
Now, we need to change the C# side to populate one DataTable with all three operation types and call only SP_Product_Merge. So, please modify the code as follows. The example code is self-explained, so please read the comment lines for a better understanding.
using System.Data;
using Microsoft.Data.SqlClient;
namespace BulkOperationsDemo
{
public class Program
{
// Connection string to ProductsDB
private const string ConnectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductsDB;Trusted_Connection=True;TrustServerCertificate=True;";
static async Task Main(string[] args)
{
Console.WriteLine("Bulk MERGE Operations using TVP Started.");
// Build a single DataTable with all operations
var mergeTable = CreateMergeDataTable();
// Rows for INSERT
mergeTable.Rows.Add(1, "USB‑C Cable", 299.00m, false, 'I');
mergeTable.Rows.Add(2, "Wireless Mouse", 749.00m, false, 'I');
mergeTable.Rows.Add(3, "Laptop Stand", 1399.00m, false, 'I');
mergeTable.Rows.Add(4, "Samsung Mobile", 5599.00m, false, 'I');
// Rows for UPDATE
mergeTable.Rows.Add(1, "USB Type C Cable", 399.00m, false, 'U');
mergeTable.Rows.Add(3, "Laptop Premium Stand", 1599.00m, false, 'U');
// Rows for DELETE
mergeTable.Rows.Add(2, null, 0m, false, 'D');
mergeTable.Rows.Add(4, null, 0m, false, 'D');
try
{
// Call the single MERGE SP
await BulkMergeAsync(mergeTable);
Console.WriteLine("Bulk MERGE completed successfully.");
}
catch (Exception ex)
{
Console.Error.WriteLine($"Error: {ex.Message}");
}
}
// Creates a DataTable matching ProductMergeTVP schema.
private static DataTable CreateMergeDataTable()
{
var table = new DataTable();
table.Columns.Add("ProductId", typeof(int)); // primary key
table.Columns.Add("Name", typeof(string)); // new name (or null for delete)
table.Columns.Add("Price", typeof(decimal)); // new price
table.Columns.Add("IsDiscontinued", typeof(bool)); // flag field
table.Columns.Add("Operation", typeof(char)); // 'I','U','D'
return table;
}
// Sends the TVP to SP_Product_Merge for a combined MERGE.
private static async Task BulkMergeAsync(DataTable products)
{
using (var connection = new SqlConnection(ConnectionString))
{
using var command = new SqlCommand("SP_Product_Merge", connection)
{
CommandType = CommandType.StoredProcedure
};
// Attach the TVP parameter
var tvpParam = command.Parameters.AddWithValue("@Products", products);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "ProductMergeTVP";
// Execute the stored procedure
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
}
}
}
}
Output:
When to Perform Bulk CRUD via TVPs in ADO.NET Core
- High-Volume Data Changes: If you regularly process large datasets in your application, TVPs and stored procedures can dramatically improve performance.
- Complex Business Logic: When logic involves multiple changes to the same set of rows (e.g., first updating specific fields, then inserting missing records), a TVP with a stored procedure helps maintain consistency and reduces code duplication.
- ETL/Data Imports: Load thousands of CSV or Excel rows via TVP and stored procedure for maximum throughput.
- Grid-Based UIs: Users edit dozens of rows online, batch-saving them in one TVP call rather than 50 round-trips.
Real-Time Scenarios:
- A reporting application that imports thousands of sales records.
- An inventory system that periodically updates product quantities and adds new items from a supplier feed.
- A user management system that deactivates old user accounts and inserts new users in one streamlined operation.
Stored procedures with TVPs accept user-defined table-type parameters, allowing for the efficient processing of multiple rows simultaneously. These procedures commonly perform bulk operations using set-based logic using SQL MERGE or JOIN statements.
In the next article, I will discuss how to Implement Bulk Operations using SqlDataAdapter in ADO.NET Core with Examples. In this article, I explain how to Implement Bulk Operations using Stored Procedures in ADO.NET Core with Examples. I would appreciate your feedback. Please post feedback, questions, or comments about this Bulk Operations using Stored Procedures in 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.