SQL Stored Procedures With Table-Valued Parameters(TVP) & Calling From C#

Have you ever wished that you can call a stored procedure passing a collection of data for batch processing to avoid multiple round trips? If yes, then I encourage you to read the rest of this post.

Table-Valued Parameters is a type which you can create in SQL. This type is defined in order to allow SQL to understand the structure of the collection you wish to pass to a stored procedure. It is a "strong-typed" approach.

So, lets jump into the details and see the magic in action.

Note:
The source code for this demo can be found on this link


Creating the DB structure required for the demo:
1. Run this script to create the required tables

• We have only one table called Employees
• Each record in this table has:
• ID ==> int ==> Identity
• Name ==> nvarchar(max)
• Age ==> int

 USE [TvpTestDB]  
 GO  
   
 SET ANSI_NULLS ON  
 GO  
   
 SET QUOTED_IDENTIFIER ON  
 GO  
   
 --Droping the employees table if the table already exists in the database  
 IF EXISTS  
 (  
  SELECT * FROM dbo.sysobjects  
  WHERE id = object_id(N'[dbo].[Employees]')  
      AND OBJECTPROPERTY(id, N'IsUserTable') = 1  
 )  
 DROP TABLE [dbo].[Employees]  
 GO  
   
 --Creating the employees table  
 CREATE TABLE [dbo].[Employees](  
     [Employee_ID] [int] IDENTITY(1,1) NOT NULL,  
     [Name] [nvarchar](max) NOT NULL,  
     [Age] [int] NOT NULL,  
  CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED   
 (  
     [Employee_ID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  
 GO  

2. Run this script to create the TVPs

• TVP = Table-Valued Parameter
• TVP is a SQL type which you can create providing the suitable structure
• In our case, the TVP will take a table shape with the same structure as the Employee table
• But, we will not include the ID column because it will not be sent as a parameter because it is already an Identity column which will gets its value automatically
• In other cases, if the ID column is not an Identity column, you should include it in the TVP definition

 USE [TvpTestDB]  
 GO  
   
 --Creating Employees TVP  
 CREATE TYPE EmployeesTVP AS TABLE  
 (  
     [Name] [nvarchar](max) NOT NULL,  
     [Age] [int] NOT NULL  
 )  
 GO  

3. Run this script to create the SPs

• We will create only one stored procedure called "AddEmployees" which will be responsible for batch adding employees
• It takes a TVP as an input parameter but with the same structure of the Employees TVP we created in the previous step
• Since we created the TVP above as a type, SQL already knows all required info about this TVP and it is now a "strong-typed" entity which SQL can deal with
• So, the input parameter of the SP will be "EmployeesTVP"
• Also, all TVPs should be marked as READONLY and it will not work otherwise
• This also makes us notice that the TVP data couldn't be manipulated by anyway inside the SP
• The stored procedure only batch inserts into the Employees table the records existing into the EmployeeTVP passed to it as an input parameter

 USE [TvpTestDB]  
 GO  
   
 --Dropping the stored procedure "AddEmployees" if already exists in a database  
 IF EXISTS  
 (  
  SELECT * FROM dbo.sysobjects  
  WHERE id = object_id(N'[dbo].[AddEmployees]')  
      AND OBJECTPROPERTY(id, N'IsProcedure') = 1  
 )  
 DROP PROCEDURE [dbo].[AddEmployees]  
 GO  
   
 CREATE PROCEDURE AddEmployees  
 (  
     @BatchEmployees AS EmployeesTVP READONLY  
 )  
 AS  
 BEGIN  
     INSERT INTO Employees(Name, Age)  
     SELECT Name, Age FROM @BatchEmployees  
 END  

Calling the SP passing the TVP instance as an input parameter through SQL:
Run this script to:

• Declare a variable of type EmployeesTVP
• SQL already knows the structure of this TVP (table)
• Insert some employee data into this variable to use for batch inserting
• Call the stored procedure passing the EmployeeTVP variable

 USE [TvpTestDB]
 GO
 
 --Declaring a variable of TVP type "EmployeesTVP" to be used to hold the input batch employee data
 DECLARE @BatchEmployees AS EmployeesTVP
 
 --Inserting batch employee data in the @BatchEmployees variable
 INSERT INTO @BatchEmployees(Name, Age)
 VALUES ('Ahmed', 27)
 , ('Tarek', 12)
 , ('Hasan', 13)
 , ('Saleh', 14)
 , ('Selim', 15)
 
 --Clearing tables before inserting new records
 DELETE FROM Employees
 
 --Calling the stored procedure "AddEmployees" passing to it the batch data variable "BatchEmployees"
 EXEC AddEmployees @BatchEmployees
 GO

Now, when you check the "Employees" table, you will find that the records are inserted and that is a good thing :)

But, can we do the same using C# like calling a stored procedure and passing to it a collection of certain class or entity? Yes, you can.


Calling the stored procedure from C# passing a TVP as an input parameter:
To do so, you have 2 ways:

1. Creating a DataTable with matching structure to the one of the SQL stored procedure TVP and populating it with data
2. Preparing your own custom collection class to be sent as a TVP by implementing the "IEnumerable<SqlDataRecord>" interface

You can check the code below to understand how to achieve this using both ways. You will find the code simple and well documented.

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Data;  
 using System.Data.SqlClient;  
 using Microsoft.SqlServer.Server;  
   
 namespace TestTVP  
 {  
   class Program  
   {  
     static void Main(string[] args)  
     {  
       //Creating a datatable with the same structure of the TVP   
       DataTable BatchEmployee = new DataTable();  
       BatchEmployee.Columns.Add("Name", typeof(string));  
       BatchEmployee.Columns.Add("Age", typeof(int));  
   
       //Adding records into the datatable  
       BatchEmployee.Rows.Add(new object[] { "Ahmed1", 27 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed2", 28 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed3", 29 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed4", 30 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed5", 31 });  
   
       //Calling the stored procedure passing the datatable as a TVP  
       //Modify the connection string to work with your environment before running the code  
       using (var conn = new SqlConnection("Data Source=DEVMACHINE2;Initial Catalog=TvpTestDB;Integrated Security=True"))  
       {  
         conn.Open();  
         using (var cmd = new SqlCommand("AddEmployees", conn))  
         {  
           cmd.CommandType = CommandType.StoredProcedure;  
           var BatchEmployeeParameter = cmd.Parameters.AddWithValue("@BatchEmployees", BatchEmployee);  
           //This is a very important step  
           //You should set the SqlDbType property of the SqlParameter to "Structured"  
           BatchEmployeeParameter.SqlDbType = SqlDbType.Structured;  
           cmd.ExecuteNonQuery();  
         }  
         conn.Close();  
       }  
   
       //Creating instance of the EmployeeCollection and populate some employee data  
       EmployeeCollection employees = new EmployeeCollection();  
       employees.Add(new Employee() { Name = "Mohamed1", Age = 20 });  
       employees.Add(new Employee() { Name = "Mohamed2", Age = 21 });  
       employees.Add(new Employee() { Name = "Mohamed3", Age = 22 });  
       employees.Add(new Employee() { Name = "Mohamed4", Age = 23 });  
       employees.Add(new Employee() { Name = "Mohamed5", Age = 24 });  
       employees.Add(new Employee() { Name = "Mohamed6", Age = 25 });  
   
       //Calling the stored procedure passing the EmployeeCollection as a TVP  
       //To do so, you should first make sure that the EmployeeCollection implements the "IEnumerable<SqlDataRecord>" interface  
       //Check the EmployeeCollection class definition below  
       //Modify the connection string to work with your environment before running the code  
       using (var conn1 = new SqlConnection("Data Source=DEVMACHINE2;Initial Catalog=TvpTestDB;Integrated Security=True"))  
       {  
         conn1.Open();  
         using (var cmd = new SqlCommand("AddEmployees", conn1))  
         {  
           cmd.CommandType = CommandType.StoredProcedure;  
           var BatchEmployeeParameter = cmd.Parameters.AddWithValue("@BatchEmployees", employees);  
           //This is a very important step  
           //You should set the SqlDbType property of the SqlParameter to "Structured"  
           BatchEmployeeParameter.SqlDbType = SqlDbType.Structured;  
           cmd.ExecuteNonQuery();  
         }  
         conn1.Close();  
       }  
     }  
   }  
   
   //Employee class  
   public class Employee  
   {  
     int id;  
     public int Id  
     {  
       get { return id; }  
     }  
   
     string name;  
     public string Name  
     {  
       get { return name; }  
       set { name = value; }  
     }  
   
     int age;  
     public int Age  
     {  
       get { return age; }  
       set { age = value; }  
     }  
   }  
   
   //EmployeeCollection class  
   //For EmployeeCollection to be used as a TVP, it should implement the "IEnumerable<SqlDataRecord>" interface  
   public class EmployeeCollection : List<Employee>, IEnumerable<SqlDataRecord>  
   {  
     IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()  
     {  
       SqlDataRecord record = new SqlDataRecord  
         (  
           new SqlMetaData("Name", SqlDbType.Text),  
           new SqlMetaData("Age", SqlDbType.Int)  
         );  
   
       foreach(Employee emp in this)  
       {  
         record.SetSqlString(0, emp.Name);  
         record.SetSqlInt32(1, emp.Age);  
         yield return record;  
       }  
     }  
   }  
 }  
   

That's it, you can do magic with this technique :)