Sometimes when developing an application that interacts with SQL Server, you may find the need to process a data set such that only one round trip needs to be made to and from the database server. The operation that is being carried out may be intended to operate on one set of data, or batches of data if the overall data set is very large. Either way, by processing in a batch rather than one record at a time, the data can be handled efficiently, preventing many unnecessary individual requests from being issued.
One way to achieve this is by implementing a Stored Procedure that accepts a Table Valued Parameter. Now, while not everyone is a fan of Stored Procedures and prefers to keep all business and data-access logic in the application (including me!), there are some cases where using Stored Procedures can make sense.
So, for those of you who are looking for a way to process batches of data efficiently using Stored Procedures, this article will help show how you can implement a Stored Procedure that uses a Table Valued Parameter to achieve the desired result when developing a C# application.
Getting Started
Before getting stuck into things, I recommend that you start by creating a simple C# Console App using the latest version of Visual Studio or Visual Studio Code and targeting the latest .NET version which is 8.0 at the time of writing.
I also recommend that you install the latest version of SQL Server or LocalDB (usually you will already have access to LocalDB if you have Visual Studio installed) and the latest version of SQL Server Management Studio (SSMS) or Azure Data Studio for interacting with the SQL Server database.
Note that depending on the specific application or framework versions you have installed, the code shown in the following sections may need to be adjusted slightly if certain SQL or C# language features are not available.
The example scenario I’m going to cover in the article focuses on the requirement to import a batch of product records into the database with a single request, inserting new products and updating existing products, where an existing product is determined by the product’s unique ‘ProductNumber’ field.
Note that I’m aiming to keep the example as simple as possible, focusing on the implementation of the Stored Procedure that uses a Table Valued Parameter, so we’ll be skipping anything superfluous in the C# code such as Dependency Injection and the usage of ORMs such as Entity Framework Core.
Database Schema
We are going to base the database schema on a variation of the schema from the sample AdventureWorks database that is frequently used for demos.
Database
Let’s start by creating a blank database – you can issue the following SQL command to create a new database within your SQL Server instance.
CREATEÂ DATABASEÂ AdventureWorksTVP;
I recommend connecting to a LocalDB instance for simplicity, but you can connect to a full SQL Server instance if you have access to one.
To connect to your LocalDB instance, specify the following server name from SSMS or Azure Data Studio.
(localdb)\mssqllocaldb
Assuming you are on a Windows machine, you can use ‘Windows Authentication’ as the Authentication method.
Table
Following on with the theme of simplicity, let’s create a simple table within our new database that is based on the ‘Products’ table found in the sample AdventureWorks database. We’ll use a subset of the columns and standard data types to avoid any unnecessary complexity in the demo, as per the SQL below.
CREATE TABLE dbo.Products ( ProductID INT IDENTITY(1, 1) NOT NULL, [Name] NVARCHAR(255) NOT NULL, ProductNumber NVARCHAR(25) NOT NULL, ListPrice MONEY NOT NULL, Size NVARCHAR(5) NULL, SellStartDate DATETIME NOT NULL, CONSTRAINT PK_Product_ProductID PRIMARY KEY CLUSTERED ( [ProductID] ASC ), CONSTRAINT UQ_Product_ProductNumber UNIQUE(ProductNumber) );
You’ll notice in the above SQL that the ‘Products’ table has a ‘ProductID’ column that acts as the primary key and also features a ‘ProductNumber’ column. For the purposes of the example scenario, we are going to assume that the ‘ProductNumber’ should be unique across all rows in the table. I’ve added a unique constraint to ensure that this rule is adhered to and to help with performance since a non-clustered index will be added as a result of specifying the unique constraint.
Table Type
Next, let’s create the Table Type that will be needed for our Table Valued Parameter, using the SQL below.
CREATE TYPE dbo.ProductData AS TABLE ( [Name] NVARCHAR(255) NOT NULL, ProductNumber NVARCHAR(25) NOT NULL, ListPrice MONEY NOT NULL, Size NVARCHAR(5) NULL, SellStartDate DATETIME NOT NULL );
The CREATE TYPE
syntax is used to create user-defined types and AS TABLE
indicates that it is a Table Type we are creating. The same column names, types, lengths etc. as the ‘Products’ table are used to ensure consistency and compatibility when importing the product data.
Stored Procedure
Now for the Stored Procedure; we can create it using the following SQL.
CREATE OR ALTER PROCEDURE dbo.ImportProducts     @ProductData dbo.ProductData READONLY AS BEGIN     MERGE dbo.Products AS target     USING @ProductData AS source     ON target.ProductNumber = source.ProductNumber     WHEN NOT MATCHED BY target THEN         INSERT (             [Name],             ProductNumber,             ListPrice,             Size,             SellStartDate         )         VALUES (             source.[Name],             source.ProductNumber,             source.ListPrice,             source.Size,             source.SellStartDate         )     WHEN MATCHED THEN UPDATE SET         target.Name = source.Name,         target.ProductNumber = source.ProductNumber,         target.ListPrice = source.ListPrice,         target.Size = source.Size,         target.SellStartDate = source.SellStartDate; END
The dbo.ImportProducts
stored procedure accepts a READONLY
Table Valued Parameter as its input. The TVP will contain all of the product data that the client wants to import.
The body of the stored procedure is a SQL MERGE
statement that allows us to efficiently handle inserting or updating product records in a single request depending on whether or not there is a match on the ‘ProductNumber’ field. If you’re curious, you can read more about how the SQL MERGE
statement works on the Microsoft Docs.
Note that in addition to inserts and updates, deletes can also be handled using WHEN NOT MATCHED BY source
 if required.
Client Code
Now that we’ve been through the database schema, let’s explore how to implement the C# code on the client side which will import product data into the sample database via the stored procedure.
As mentioned earlier in the article, we’re going to keep things as simple as possible for the sake of the demo, so we’ll be using ADO.NET via the latest Microsoft.Data.SqlClient NuGet package.
Model
First, let’s create a ProductData
model class that maps to the ‘ProductData’ SQL Table Type created in the previous section.
public class ProductData {     public string Name { get; set; } = "";     public string ProductNumber { get; set; } = "";     public decimal ListPrice { get; set; }     public string? Size { get; set; }     public DateTime SellStartDate { get; set; } }
Notice that the nullability of the types and the property names match the SQL schema.
Repository
Next, let’s create a basic repository class that will coordinate the data access part of the application.
using Microsoft.Data.SqlClient; using System.Data; public class ProductRepository {     private readonly string _connectionString;     public ProductRepository(string connectionString)     {         _connectionString = connectionString;     }     public async Task<int> ImportProducts(IEnumerable<ProductData> products)     {       var productData = new DataTable();        productData.Columns.Add(nameof(ProductData.Name), typeof(string));         productData.Columns.Add(nameof(ProductData.ProductNumber), typeof(string));         productData.Columns.Add(nameof(ProductData.ListPrice), typeof(decimal));         productData.Columns.Add(nameof(ProductData.Size), typeof(string));         productData.Columns.Add(nameof(ProductData.SellStartDate), typeof(DateTime));         foreach (ProductData product in products)         {             productData.Rows.Add(                product.Name,                 product.ProductNumber,                 product.ListPrice,                product.Size,                product.SellStartDate             );         }        using var connection = new SqlConnection(_connectionString);         await connection.OpenAsync();         using var command = new SqlCommand("dbo.ImportProducts", connection);         command.CommandType = CommandType.StoredProcedure;         var tvp = command.Parameters.AddWithValue("@ProductData", productData);         tvp.SqlDbType = SqlDbType.Structured;         tvp.TypeName = "dbo.ProductData";        return await command.ExecuteNonQueryAsync();     } }
In the above code, the ImportProducts
method creates a DataTable
with column names and types that match the definition of the ProductData
class.
Rows are then added to the DataTable
, based on the ProductData
collection that has been passed to the method.
Following this, a SqlConnection
is opened and a SqlCommand
is created. It is important to set the CommandType
to StoredProcedure
.
A Table Valued Parameter is then created as a SqlParameter
with the SqlDbType
set to Structured
(indicates a TVP).
Lastly, the SQL command is executed, calling the stored procedure asynchronously.
Extension Method
While the code for the ImportProducts
method is pretty straightforward, it could get tedious to implement lots of methods like this, especially if the model we are working with has many properties.
To simplify the code, we can implement an extension method as follows.
using System.Data; public static class IEnumerableExtensions {     /// <summary>     /// Converts the specified collection to a <see cref="DataTable"/>.     /// </summary>     /// <typeparam name="T">The type of objects contained in the collection</typeparam>     /// <param name="collection">The collection to operate on</param>     /// <returns>A populated <see cref="DataTable"/> instance</returns>     public static DataTable ToDataTable<T>(this IEnumerable<T> collection) where T : class     {         var properties = typeof(T).GetProperties();         var dataTable = new DataTable();         foreach (var property in properties)         {             dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);         }         foreach (var item in collection)         {             dataTable.Rows.Add(properties.Select(p => p.GetValue(item)).ToArray());         }         return dataTable;     } }
The extension method gets all properties of the specified type using Reflection and creates a new DataTable
. The method then iterates through all properties of the type and adds columns to the DataTable
using the appropriate property type. Following this, the method iterates through each item in the collection that is being operated on and adds rows by getting the property values, again via Reflection. Lastly, the populated DataTable
is returned to the caller.
With the extension method in place, we can shorten the implementation of the ImportProducts
method to the following.
public async Task<int> ImportProducts(IEnumerable<ProductData> products) {   DataTable productData = products.ToDataTable();     using var connection = new SqlConnection(_connectionString);     await connection.OpenAsync();     using var command = new SqlCommand("dbo.ImportProducts", connection);     command.CommandType = CommandType.StoredProcedure;     var tvp = command.Parameters.AddWithValue("@ProductData", productData);     tvp.SqlDbType = SqlDbType.Structured;     tvp.TypeName = "dbo.ProductData";    return await command.ExecuteNonQueryAsync(); }
Nice! The extension method provides us with a convenient way to convert a collection of objects into a DataTable
instance with a single line of code.
Import
Now for the product import!
Below is the code that should be added to the ‘Program.cs’ file of the Console App if you are following along.
string connectionString =     "Server=(localdb)\\mssqllocaldb;" +     "Integrated Security=true;" +     "Database=AdventureWorksTVP;" +     "TrustServerCertificate=true;"; string[] sizes = { "S", "M", "L", "XL" }; Console.WriteLine("Creating products"); var products = new List<ProductData>(); for (int i = 0; i < 10_000; i++) {    products.Add(CreateProduct()); } Console.WriteLine("Importing products"); var repository = new ProductRepository(connectionString); int importedCount = await repository.ImportProducts(products); Console.WriteLine("Imported {0} products", importedCount); ProductData CreateProduct() {     string productNumber = Random.Shared.Next(1, int.MaxValue).ToString();     return new()     {         Name = $"Product {productNumber}",         ProductNumber = productNumber,         ListPrice = (decimal)Random.Shared.NextDouble() * 100,         Size = sizes[Random.Shared.Next(sizes.Length)],         SellStartDate = DateTime.UtcNow     }; }
Note that in a production application, you should store your connection string securely in a configuration file or key vault. The hardcoded connection string in this example is purely for demonstration purposes. Additionally, under normal circumstances, you should not trust a server certificate without validating it.
The above code has been kept as minimal as possible to demonstrate how to call the product import stored procedure via the repository instance.
First, a collection of 10,000 products is created with the help of a private method that generates some crude random data for the product properties.
Next, an instance of the ProductRepository
class is created and the ImportProducts
method is called, passing in the collection of 10,000 products that were previously created.
Lastly, the Console App outputs the number of records that were affected.
When the Console App is run, provided you have followed all of the setup steps, you should find that the program successfully executes the stored procedure and imports the product records into the sample database.
In a real-world application, you can extend the concept to import batches of products e.g. import 10,000 records at a time from an overall set of hundreds of thousands or millions of products that have been read from a file or some other data source.
Summary
In this article, I walked through how to efficiently operate on a data set via a SQL Server Stored procedure by leveraging Table Value Parameters to allow many records to be processed together.
I started by going through the setup of the sample database schema, which included the creation of a sample Database, Table, Table Type, and Stored Procedure.
I then walked through the key parts of the C# client code required to call the Stored Procedure with the Table Valued Parameter specified. This included the creation of a model class, repository, extension method, and the main program code that creates the product data and calls the procedure.
I trust that you found the content of this article helpful for understanding how to leverage Table Valued Parameters within Stored Procedures and how to call such procedures from C# code.
Comments