Code First. Executing Stored Procedures With Table-Valued Parameter

Today I need to retrieve data from Entity Framework using stored procedure, and passing input values from .NET collection through the table-valued parameter. As for technologies that will be used here, I wrote in these posts:

  1. Using Table-Valued Parameters in SQL Server Stored Procedures – how to create SQL Server type and stored procedure;
  2. Calling SQL Server Stored Procedure With Table-Valued Parameter in C# – calling stored procedure via ADO.NET;
  3. Code First. Executing Stored Procedures – calling stored procedures via DbContext.Database.SqlClient().

As stated in MSDN article Table-Valued Parameters, table-valued parameter can be populated from:

  • DataTable
  • DbDataReader
  • IEnumerable

If you have a .NET collection with parameters, you could transform it to IEnumerable. Here is a good article on C# Corner Passing Table Valued Parameter to Stored Procedure: Part 2 that shows how to make this conversion.

Let’s start to write the code.

1. Create SQL Server type UserType to store the row of a table-valued parameter

2. Create stored procedure with a readonly parameter of type UserType

3. Create .NET class UserEntry to store the row of a table-valued parameter

4. Create .NET class UserParamCollection that must be inherited from the preceding .NET class and also implements IEnumerable

5. Convert List<UserEnty> to a newly created class UserParamCollection

6. Create SqlParameter

Here you need to specify parameter type SqlDbType.Structured. TypeName must be the same as SQL Server type defined in step 1.

7. Call stored procedure and pass the preceding SqlParameter

You must place the parameter name of the SQL Server stored procedure right after the name of the stored procedure as following: “[dbo].[ListUsers] @UserList”. If you omit it, you’ll get an error:
Procedure or function 'ListUsers' expects parameter '@UserList', which was not supplied.

Because SqlQuery() returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery, it would better to cast it to IEnumerable to conform to application interfaces.

Leave a Reply

Your email address will not be published. Required fields are marked *