Using Table Valued Parameters in Entity Framework

Table Valued Parameters (TVPs) were introduced in SQL Server 2008. TVPs allow passing multiple rows of data to the server. It can be passed to a Stored Procedure as a parameter. It improves the performance of the application since it prevents several round trips to the server for saving the records in the database. Prior to TVPs we need to pass the data by delimiting with some characters and for doing that we need to write some logic for SQL Server for separating the data.

1. TVPs are stored in Tempdb and can be created and destroyed automatically behind the scenes.
2. We can have select or join against TVP.
3. It is similar to temp tables, table variables or CTEs.
4. It can be passed from client to server over a network.

Creating TVP in SQL Server

 

We will first create a table and then create a type of that. I have created a table named Students as in the following:

CREATE TABLE [dbo].[Students](
   [StudentId] [int] NOT NULL,
   [FirstName] [varchar](50) NOT NULL,
   [LastName] [varchar](50) NOT NULL,
   [Email] [varchar](80) NOT NULL,
   [DateCreated] [datetime] NOT NULL,
   CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
   (
      [StudentId] ASC
   )
)
GO

Now we will create a TVP for the preceding table.

Create Type StudentsUDT as Table
(
StudentID int,
FirstName varchar(50),
LastName varchar(50),
Email varchar(80)
)

I have not created a DateCreated column in the TVP since I want this column value to be the latest depending on my SQL Server date. Now I will create a Stored Procedure that will accept this table value parameter as parameter and save multiple rows into the database.

 

Create proc usp_SaveStudents

@students dbo.StudentsUDT Readonly
as
Begin
insert into dbo.Students Select *,SYSDATETIME() from @students
End

At this point we have completed all the work at the SQL Server side. Now we will create a C# Console project and send the table value parameter from C# to SQL Server. Once the project has been created we will add a reference of Entity Framework and refer to our database. Now I will create a datatable that will represent our TVP and will add rows in that. Then we will create one parameter of type SqlParameter to which that datatable will be assigned.

var studentdt = new DataTable();
studentdt.Columns.Add("StudentId", typeof(int));
studentdt.Columns.Add("FirstName", typeof(string));
studentdt.Columns.Add("LastName", typeof(string));
studentdt.Columns.Add("Email", typeof(string));
studentdt.Rows.Add(1, "Test1", "test", "[email protected]");
studentdt.Rows.Add(2, "Test2", "test", "[email protected]");
studentdt.Rows.Add(3, "Test3", "test", "[email protected]");
studentdt.Rows.Add(4, "Test4", "test", "[email protected]");

var parameter = new SqlParameter("@students", SqlDbType.Structured);
parameter.Value = studentdt;
parameter.TypeName = "dbo.StudentsUDT";

Then we will call the Stored Procedure by passing that SQL parameter to that.

using (SampleDbEntities db = new SampleDbEntities())
{
db.Database.ExecuteSqlCommand("exec dbo.usp_SaveStudents @students", parameter);
}

Disadvantages of Table Value Parameter

1. TVPs are read-only; once created they cannot get changed
2. The Output keyword cannot be used with them
3. To change the schema of a TVP we need to recreate it
4. Statistics are not maintained on TVPs