Passing Table Value parameters to Stored Procedures from C#

stephen.elliott@webapplicationsuk.com — Tue, 25 May 2010 12:34:35 GMT

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 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.

Tr@veller Lite Interface

stephen.elliott@webapplicationsuk.com — Fri, 08 May 2009 16:08:35 GMT

Recently we’ve been inserting some new functionality into Tr@veller Lite. Alongside that we’ve also been looking at giving it a distinctively different look and feel to the existing Tr@veller product.

More...

AcuWeb API: Price Command

stephen.elliott@webapplicationsuk.com — Fri, 01 May 2009 10:30:49 GMT

As yesterday I only covered one command, I’ll cover two today instead. So far I’ve covered two commands, the Search and Property commands, today I will be focusing on the Price command. The price command a more detailed breakdown of a properties price than the Search command provides, as well as detailing its extras. These extras can include both optional and compulsory extras.

More...

AcuWeb API: Property Command

stephen.elliott@webapplicationsuk.com — Thu, 30 Apr 2009 08:07:23 GMT

Another post about the AcuWeb API, since I missed posting one yesterday there will be two today. The first command I’m going to focus on today is the Property Command, more details about the request/response can be found within the AcuWeb API, but I hope to give a general overview as to how this command can be used. For more details on AcuWeb API and the search message see my previous post.

More...

Acuweb API Progress

stephen.elliott@webapplicationsuk.com — Tue, 28 Apr 2009 09:45:15 GMT

We’ve been working hard behind the scenes on AcuWeb and getting it ready to roll out as soon as we can, but a lot of the work so far has been laying the groundwork to support the degree of flexibility to websites and the booking engine that we want to provide. At this stage however, everything’s getting tied together and that initial work on backend is really paying off. This week I plan on giving a quick overview of some of the messages and how they can be accessed to give a better idea on how easy AcuWeb is to use.

More...

AcuWeb Interface

stephen.elliott@webapplicationsuk.com — Thu, 19 Mar 2009 18:31:52 GMT

From this blog I’ll be posting about the progress we make on the backend of the Acumen Website replacement project. This system is designed to be a centralised interface that will both drive the replacement Acumen websites and also allow clients to access it directly to drive their own websites, where required.

More...

Stephen Elliott's Blog

Passing Table Value parameters to Stored Procedures from C#

Step One – Create the Data Table Type in SQL

Step Two – Add the parameter to the Stored Procedure

Step Three – Create an Explicit Cast Operator

Step Four – Passing the data from C# to SQL

Tr@veller Lite Interface

AcuWeb API: Price Command

AcuWeb API: Property Command

Acuweb API Progress

AcuWeb Interface