Passing Table Value parameters to Stored Procedures from C#

Jack Simpson

Head of Marketing and Communications

So it goes

This will only work with SQL Server 2008 and above as it requires a new feature of SQL Server only present in 2008 and 2008 R2: Table Value Parameters.

Previously when we wanted to provide SQL with a set of data, e.g. a table, we had to pass in XML and parse that within the SQL. SQL’s parsing of XML isn’t great when dealing with large quantities of data. Additionally we want the data in table form anyway, so why bother with the XML? Ideally we just want to be able to pass in a table to SQL, just like we can get tables out.

In this post I’m going to go through the steps in both SQL and C# to allow you to do this. My examples use C# 3.5 and above (through use of Linq) but it could be easily converted to .NET 2.0.

Step One – Create the Data Table Type in SQL

Use SQL Server Management Studio to navigate to the correct database. First thing we need to do is define a Table Type. This is a requirement of passing Table value parameters to a Stored Procedure. In this post I’m going to use an example of passing in a set of products.

To create a data table type, you can use the following syntax:

   1: CREATE TYPE [dbo].[Product] AS TABLE(

   2:     [ID] [int],

   3:     [Name] [nvarchar](max),

   4:     [BestBefore] [datetime] null

   5: )

This creates a type with three columns: ID, of type integer, doesn’t allow nulls, Name, of type nvarchar(max), again, doesn’t allow nulls, BestBefore, a date time that does allow nulls.

Once the data table type is created it can be found within: Programmability -> Types -> User-Defined Table Types on the target database and is ready to be used in stored procedures.

Step Two – Add the parameter to the Stored Procedure

Now that the Data Table type has been created, we can add the parameter to the stored procedure. When passing table types as parameter values, they must be marked as Read-Only, this is another requirement enforced by SQL Server. The syntax below shows the head of a stored procedure that will add a list of products to the database. Once inside the body of the stored procedure, the parameter can be used like any other Temporary Table Variable.

   1: ALTER PROCEDURE [dbo].[spProductAddList] ( 

   2:             @SessionID uniqueidentifier,

   3:             @ProductList as dbo.Product readonly )

   4: AS

   5: BEGIN

   6:

   7: END    

Step Three – Create an Explicit Cast Operator

There are several different options for passing the data to SQL; we found a couple of articles on this but none provided the quality of solution we wanted:

  • http://www.codeproject.com/KB/cs/CSharpAndTableValueParams.aspx – This is the original article we found; it has a good introduction to how the principles worked, but we weren’t happy with the use of an ad-hoc DataTable implementation.
  • http://orionseven.com/blog/tag/c/ – In this article Bryan Smith uses a custom IEnumerable implementation to create his own GetEnumerator method which outputs SqlDataRecords of the correct structure/value. We preferred this method but felt we could build on it to create a nicer OO solution without the need for a separate, specific collection class.

With this in mind we settled on a method that used an explicit cast operator on the class in question to create a SqlDataRecord from the base object.

In the application for our example we already have a “Product” class; to this class we’re going to add the following method:

   1: public static explicit operator SqlDataRecord(Product product)

   2: {

   3:     SqlDataRecord sqlDataRecord = new SqlDataRecord(

   4:         new SqlMetaData("ID", SqlDbType.VarChar, -1),

   5:         new SqlMetaData("Name", SqlDbType.VarChar, -1),

   6:         new SqlMetaData("BestBefore", SqlDbType.VarChar, -1)

   7:         );

   8:  

   9:     sqlDataRecord.SetInt32(0, product.ID);

  10:     sqlDataRecord.SetString(1, product.Name);

  11:  

  12:     if (product.BestBefore > DateTime.MinValue)

  13:         sqlDataRecord.SetDateTime(2, product.BestBefore);

  14:  

  15:     return sqlDataRecord;

  16: }

This creates an explicit cast operator from the type product to the type SqlDataRecord. First thing we do is create the SqlDataRecord instance, in the constructor we add 3 SqlMetaData objects which define the columns of the Data Table Type we defined in step one.

Once we’ve created the record, we need to set the values; this is done using the SetString/SetInt32/SetDateTime methods. When this is done we return the sqlDataRecord object which contains the data from our object in the same format as the Data Table Type in SQL.

Step Four – Passing the data from C# to SQL

Now we have a way of generating our SqlDataRecords and have told SQL what format to expect the data in, we have to program the C# to provide this to the SQL. We have to create a parameter in ADO.NET, but give it a type of “SqlDbType.Structured”. This tells SQL that it should expect a Table Variable for the value of the parameter.

   1: using (SqlCommand command = new SqlCommand("spProductAddList", connection))

   2: {

   3:     command.CommandType = CommandType.StoredProcedure;

   4:  

   5:     SqlParameter productsParameter = new SqlParameter("@ProductList", SqlDbType.Structured)

   6:     {

   7:         Value =

   8:                 products.Count() > 0

   9:                         ? products.Select(

  10:                                 p => (SqlDataRecord)p)

  11:                         : null

  12:     };

  13:  

  14:     command.Parameters.Add(productsParameter);

  15:  

  16:     command.ExecuteNonQuery();

  17: }

Using LINQ we cast our products into SqlDataRecord objects using the explicit cast from step 3. The result of the LINQ Select is an IEnumerable<SqlDataRecord> which is provided to SQL as the value. If there are no products then “Null” is provided to SQL. SQL does not seem to like a value of “default” for Table Variable parameters.

And that’s it, C# passes the collection of SqlDataRecords to SQL, which can then do as it pleases with the data it has without having to resort to XML.

As mentioned this will only work on SQL Server 2008 and SQL Server 2008 R2 and the example was written using C#/.NET 3.5; however it could be easily ported to .NET 2.0 by removing the use of LINQ and replacing it with a foreach loop.