ADO - Table-Valued Parameters (TVP) in ADO.NET

Table-Valued Parameters (TVPs) are a feature in SQL Server that allow you to pass an entire table of data as a single parameter from an ADO.NET application to a stored procedure. Instead of sending multiple rows one by one or building complex comma-separated strings, you can send structured tabular data in one operation.

This makes data transfer between application and database more efficient, clean, and scalable.

1. The problem TVP solves

Before TVPs existed, developers typically used methods like:

  • Inserting rows one by one (slow and inefficient)

  • Using multiple insert statements in loops

  • Passing comma-separated values and splitting them in SQL

  • Using temporary tables with multiple round trips

These approaches cause:

  • High network overhead

  • Poor performance for large datasets

  • Complex and error-prone SQL logic

  • Difficult maintenance

TVP solves this by allowing structured bulk data transfer in a single parameter.

2. What is a Table-Valued Parameter

A Table-Valued Parameter is a user-defined table type in SQL Server that acts like a table structure passed as a parameter to a stored procedure.

It behaves like a read-only table inside SQL Server during execution.

3. How TVP works conceptually

The flow is:

  1. Define a table type in SQL Server

  2. Create a stored procedure that accepts this table type as a parameter

  3. From ADO.NET, create a structured data object (like DataTable)

  4. Pass it to SQL Server as a parameter

  5. SQL Server processes it like a normal table

4. Step 1: Create user-defined table type in SQL Server

CREATE TYPE EmployeeType AS TABLE
(
    EmployeeID INT,
    EmployeeName NVARCHAR(100),
    Salary DECIMAL(10,2)
);

This defines a reusable table structure.

5. Step 2: Create stored procedure using TVP

CREATE PROCEDURE InsertEmployees
    @Employees EmployeeType READONLY
AS
BEGIN
    INSERT INTO Employees (EmployeeID, EmployeeName, Salary)
    SELECT EmployeeID, EmployeeName, Salary
    FROM @Employees;
END;

Important point:

  • TVPs must be marked as READONLY in SQL Server

6. Step 3: Creating DataTable in ADO.NET

In ADO.NET, a DataTable is commonly used as a TVP source.

DataTable dt = new DataTable();

dt.Columns.Add("EmployeeID", typeof(int));
dt.Columns.Add("EmployeeName", typeof(string));
dt.Columns.Add("Salary", typeof(decimal));

dt.Rows.Add(1, "John", 50000);
dt.Rows.Add(2, "Alice", 60000);

This DataTable structure must match the SQL Server table type exactly.

7. Step 4: Passing TVP from ADO.NET

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("InsertEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter param = cmd.Parameters.AddWithValue("@Employees", dt);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "EmployeeType";

    conn.Open();
    cmd.ExecuteNonQuery();
}

Key points:

  • SqlDbType.Structured is required

  • TypeName must match SQL Server user-defined table type

  • DataTable is passed directly as a single parameter

8. How SQL Server processes TVP

When TVP is received:

  • SQL Server treats it as a temporary in-memory table

  • Data is available for set-based operations

  • It avoids row-by-row processing

  • Can be used in joins, inserts, updates, and deletes

9. Advantages of Table-Valued Parameters

1. High performance

  • Reduces multiple database calls into one

  • Uses set-based processing instead of loops

2. Cleaner code

  • No need for string concatenation or loops

  • Simplifies application logic

3. Reduced network traffic

  • Entire dataset is sent in a single request

4. Strong typing

  • Schema is defined in SQL Server

  • Prevents invalid data structure

5. Better maintainability

  • Easier to understand and modify

  • Less error-prone than dynamic SQL

10. Limitations of TVP

1. Read-only in stored procedures

TVPs cannot be modified inside SQL Server.

2. No indexing control

You cannot create indexes on TVPs.

3. Memory usage

Large TVPs may consume significant memory.

4. SQL Server dependency

TVPs are specific to SQL Server and not portable to other databases.

11. TVP vs traditional methods

Row-by-row insertion

  • Slow

  • Many database calls

  • High overhead

Bulk insert (SqlBulkCopy)

  • Very fast

  • Mainly for loading data into tables directly

TVP

  • Balanced approach

  • Good for passing structured data into stored procedures

  • Supports business logic processing inside SQL Server

12. When to use TVP

Use TVPs when:

  • You need to pass multiple rows to a stored procedure

  • You want to apply business logic on batches of data

  • You need structured input for complex operations

  • You want to avoid multiple database calls

Example scenarios:

  • Bulk inserting orders for a customer

  • Processing batch updates

  • Passing cart items in e-commerce checkout

  • Synchronizing data between systems

13. Best practices

  • Keep TVP schema simple and aligned with business needs

  • Avoid extremely large TVPs; use batching if needed

  • Use meaningful table type names

  • Combine TVPs with stored procedures for best performance

  • Validate data before sending from application

14. Real-world analogy

Without TVP:
Like sending items one by one through separate deliveries.

With TVP:
Like sending a complete packaged box containing all items in one shipment with structured labeling.

15. Summary

Table-Valued Parameters in ADO.NET allow developers to pass multiple rows of structured data from an application to SQL Server in a single call. They improve performance, reduce network overhead, and simplify code by enabling set-based operations inside stored procedures. However, they are SQL Server specific and require careful schema design and memory consideration.