ADO - Working with SQL Server Table-Valued Parameters (TVPs)

Connection pooling and basic parameterized queries are common features in ADO.NET, but when an application needs to send hundreds or thousands of records to a SQL Server stored procedure, sending each record individually can be slow and inefficient. Table-Valued Parameters (TVPs) provide an elegant solution by allowing multiple rows of data to be passed to a stored procedure as a single parameter. This feature improves performance, reduces network traffic, and simplifies data transfer between an application and a SQL Server database.

What are Table-Valued Parameters?

A Table-Valued Parameter is a special parameter type in SQL Server that allows an entire table of data to be passed to a stored procedure or function. Instead of sending one value at a time, developers can send multiple rows and columns in a structured format. SQL Server treats the incoming data as a read-only table that can be queried just like a regular database table.

For example, instead of calling a stored procedure 500 times to insert 500 employee records, a developer can send all 500 records in a single call using a TVP.

Why Table-Valued Parameters are Needed

Many business applications process large collections of data simultaneously. Consider the following scenarios:

  • Importing student records into a school management system.

  • Uploading product information into an inventory database.

  • Processing customer orders with multiple order items.

  • Updating attendance records for an entire class.

  • Saving survey responses collected from many users.

Without TVPs, the application would either execute multiple SQL commands or create complex SQL statements. Both methods increase execution time and consume additional network resources.

Benefits of Using Table-Valued Parameters

Improved Performance

Since multiple records are sent in one database request, the number of database round trips is significantly reduced. This leads to faster execution and better application performance.

Reduced Network Traffic

Instead of transmitting each record separately, all records are bundled into one structured parameter. This minimizes communication between the application and the database server.

Cleaner Application Code

Developers can work with collections of objects or DataTables instead of writing repetitive SQL commands inside loops.

Better Data Consistency

All records can be processed together inside a single stored procedure, making it easier to validate data and manage transactions.

Enhanced Security

TVPs work with parameterized stored procedures, reducing the risk of SQL injection attacks.

How Table-Valued Parameters Work

The process involves several steps.

Step 1: Create a User-Defined Table Type

SQL Server requires a table structure before receiving table-valued data.

Example:

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

This statement creates a custom table type that defines the columns and data types.

Step 2: Create a Stored Procedure

The stored procedure accepts the table type as a parameter.

Example:

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

Notice that the parameter is marked as READONLY. SQL Server does not allow modifications to the incoming TVP.

Step 3: Create a DataTable in ADO.NET

The application prepares data using a DataTable.

Example:

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(101, "John", 45000);
dt.Rows.Add(102, "Mary", 52000);
dt.Rows.Add(103, "David", 61000);

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

Step 4: Pass the DataTable to SQL Server

The DataTable is passed as a parameter.

Example:

SqlCommand cmd = new SqlCommand("InsertEmployees", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.AddWithValue("@Employees", dt);

param.SqlDbType = SqlDbType.Structured;

param.TypeName = "EmployeeTableType";

cmd.ExecuteNonQuery();

The application sends the entire DataTable in one database call.

Practical Example

Suppose an online shopping website receives an order containing ten products.

Instead of inserting each product individually like this:

Insert Product 1
Insert Product 2
Insert Product 3
...
Insert Product 10

The application creates a DataTable containing all ten products and sends them together using a TVP. The stored procedure inserts every record in one execution, making the process faster and more efficient.

Read-Only Nature of TVPs

One important limitation is that Table-Valued Parameters are always read-only.

This means the stored procedure cannot perform operations like:

UPDATE @Employees
DELETE FROM @Employees
INSERT INTO @Employees

If modifications are needed, the procedure must first copy the data into a temporary table or another database table.

Comparing TVPs with Traditional Parameters

Traditional Parameters Table-Valued Parameters
One value per parameter Multiple rows in one parameter
Many database calls Single database call
Higher network traffic Lower network traffic
More coding effort Simpler code
Slower for bulk operations Faster for bulk operations

Common Applications of TVPs

Table-Valued Parameters are widely used in enterprise applications for:

  • Bulk employee record insertion

  • Student admission systems

  • Product catalog uploads

  • Payroll processing

  • Attendance management

  • Order processing systems

  • Inventory updates

  • Financial transaction processing

  • Customer information imports

  • Healthcare patient record uploads

Best Practices

  • Keep the table structure simple and include only required columns.

  • Ensure the DataTable column names and data types exactly match the SQL Server table type.

  • Use TVPs with stored procedures rather than dynamic SQL.

  • Validate data before sending it to the database.

  • Use transactions when processing critical business data.

  • Avoid sending unnecessary rows to improve performance.

  • Handle exceptions to manage invalid or incomplete data gracefully.

  • Test the application with large datasets to ensure optimal performance.

Advantages

  • Transfers multiple records efficiently.

  • Reduces the number of database calls.

  • Improves overall application performance.

  • Simplifies bulk data processing.

  • Provides secure parameterized execution.

  • Makes code more organized and maintainable.

  • Integrates seamlessly with stored procedures.

  • Suitable for enterprise-scale applications.

Limitations

  • Supported only by SQL Server.

  • Parameters are read-only.

  • Requires creating a user-defined table type.

  • DataTable structure must exactly match the SQL Server type.

  • Not ideal for modifying the passed data directly within the stored procedure.

Conclusion

Table-Valued Parameters are one of the most powerful features available in ADO.NET for transferring multiple rows of data efficiently to SQL Server. They eliminate the need for repetitive database calls, reduce network overhead, and simplify application development. By combining user-defined table types, stored procedures, and ADO.NET's DataTable class, developers can build high-performance applications capable of handling bulk data operations with greater efficiency, reliability, and security.