Using Table-Valued Parameters in C# and SQL Server

Introduction to Table-Valued Parameters

Ever needed to send a bunch of rows from your C# app to SQL Server but didn’t want to make dozens of separate calls? That’s where table-valued parameters come to the rescue. They let you package up multiple rows of data and send them to a stored procedure in just one trip to the database.

SQL Server introduced this feature back in 2008, and it’s a game-changer compared to old approaches like building giant string commands, parsing XML, or making individual database calls for each record.

Why You Should Use Table-Valued Parameters

Here’s why I love using table-valued parameters in my projects:

Less back-and-forth with the server: Send all your data in one go
Way faster performance: Process everything in batches instead of one row at a time
Type safety: Everything is properly typed so SQL Server knows exactly what to expect
Cleaner code: Both your C# and SQL code become more straightforward
All-or-nothing operations: Everything happens in a single transaction

Creating a User-Defined Table Type

First things first, before you can pass a table to SQL Server, you need to tell it what your table will look like. You do this by creating a user-defined table type in your database:

-- Create a user-defined table type
CREATE TYPE dbo.ProductBatchType AS TABLE
(
    ProductID INT,
    ProductName NVARCHAR(100),
    UnitPrice DECIMAL(10, 2),
    Quantity INT
);

-- Create a stored procedure that uses the table type
CREATE PROCEDURE dbo.InsertProductBatch
(
    @ProductBatch ProductBatchType READONLY -- That READONLY is important!
)
AS
BEGIN
    -- Insert all records from our parameter into the real Products table
    INSERT INTO Products (ProductID, ProductName, UnitPrice, Quantity)
    SELECT ProductID, ProductName, UnitPrice, Quantity
    FROM @ProductBatch;

    -- Let's return how many records were inserted
    SELECT @@ROWCOUNT AS InsertedCount;
END;

Using Table-Valued Parameters in SQL Code

If you’re writing SQL directly, you can use these table parameters just like any regular table variable. Here’s how you’d test your stored procedure:

-- Create a variable using our custom table type
DECLARE @ProductBatch AS ProductBatchType;

-- Add some test products
INSERT INTO @ProductBatch (ProductID, ProductName, UnitPrice, Quantity)
VALUES
    (1001, 'Laptop', 1200.00, 5),
    (1002, 'Smartphone', 800.00, 10),
    (1003, 'Headphones', 150.00, 20);

-- Call the stored procedure and pass in our table of products
EXEC dbo.InsertProductBatch @ProductBatch;

Passing Table-Valued Parameters from C#

Now for the good stuff, how to actually use this from your C# code. This is where the real magic happens:

using System;
using System.Data;
using System.Data.SqlClient;

public void InsertProductBatch(List<Product> products)
{
    string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";

    // Create a DataTable with the same structure as our SQL table type
    DataTable productTable = new DataTable();
    productTable.Columns.Add("ProductID", typeof(int));
    productTable.Columns.Add("ProductName", typeof(string));
    productTable.Columns.Add("UnitPrice", typeof(decimal));
    productTable.Columns.Add("Quantity", typeof(int));

    // Fill the table with our product data
    foreach (var product in products)
    {
        productTable.Rows.Add(
            product.ProductID,
            product.ProductName,
            product.UnitPrice,
            product.Quantity
        );
    }

    // Now let's send it to SQL Server
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlCommand command = new SqlCommand("dbo.InsertProductBatch", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            // Here's the key part -> we tell SQL this is a structured parameter
            SqlParameter tvpParam = command.Parameters.AddWithValue("@ProductBatch", productTable);
            tvpParam.SqlDbType = SqlDbType.Structured;

            // Run it and see how many records were inserted
            int insertedCount = (int)command.ExecuteScalar();
            Console.WriteLine($"Great! We inserted {insertedCount} products in one go.");
        }
    }
}

Important Things to Watch Out For

When I first started using table-valued parameters, I ran into a few gotchas. Here are some tips to save you some headaches:

1. Names and Types Need to Match Exactly

Make sure your DataTable column names and types match what you defined in SQL Server. If you named a column “ProductID” in SQL, don’t name it “ProductId” in your DataTable. SQL Server is picky about this and will error out if things don’t match up perfectly.

2. You Must Use SqlDbType.Structured

This is the special flag that tells ADO.NET you’re sending a table and not just a regular parameter:

SqlParameter tvpParam = new SqlParameter
{
    ParameterName = "@ProductBatch",
    SqlDbType = SqlDbType.Structured,  // This is the magic part
    TypeName = "dbo.ProductBatchType", // This helps SQL Server find your type
    Value = productTable
};

3. Consider Adding the TypeName

While you can sometimes get away without setting the TypeName property, I recommend always including it. It helps SQL Server match your parameter to the right type definition, especially if you have multiple databases or schemas.

4. Remember These Are Read-Only in SQL

Notice that READONLY keyword in the SQL procedure? That’s not optional. SQL Server won’t let you update values in a table parameter within your stored procedure. You can only read from it.

Getting the Best Performance

Table parameters shine in a few specific situations:

When you need to insert or update a bunch of related rows at once
When you’re passing complex data sets from your app to your database
When you’d otherwise be making tons of individual database calls

I’ve found these tips help squeeze the best performance out of table parameters:

Add indexes to your table type if you’ll be doing lookups or joins on it
Don’t try to pass thousands of rows this way, for really big datasets, SQL bulk copy might be better
If your records should be unique, add a primary key to your table type to enforce that

Wrapping Up

Table-valued parameters are one of those features that once you start using, you’ll wonder how you lived without them. They make it so much easier to pass sets of data from your C# code to SQL Server without the mess of building giant SQL strings or making dozens of separate calls.

The best part is they keep your code clean and maintainable while still giving you great performance. No more string concatenation, no more XML parsing, and no more looping through records to send them one at a time.

Give table-valued parameters a try in your next project, your code (and your users waiting for those batch operations to complete) will thank you!

Introduction to Table-Valued Parameters#

Why You Should Use Table-Valued Parameters#

Creating a User-Defined Table Type#

Using Table-Valued Parameters in SQL Code#

Passing Table-Valued Parameters from C##

Important Things to Watch Out For#

1. Names and Types Need to Match Exactly#

2. You Must Use SqlDbType.Structured#

3. Consider Adding the TypeName#

4. Remember These Are Read-Only in SQL#

Getting the Best Performance#

Wrapping Up#