How to Send a Complex Type List of Objects to SQL Server

Hey community, I'm back! This time I want to share with you how I solved a problem I recently had while working with SQL Server and C#.

The Problem

So a little bit of background to get all of you on the same channel.
For this particular project, I'm using SQL Server and ASP.NET MVC 5. I manage the database connection through ADO.NET. I work mostly through Stored Procedures to let the database handle the load of working with the data. When I needed to modify multiple rows I always ended up sending a comma-separated string with the ids I wanted to work with.

Here is an example of how I send a list of ids to modify.

CREATE PROCEDURE 
    /*This will contain a comma separated list e.g 1,2,3,4,5 and so on*/
    @idList varchar(MAX)
AS
BEGIN
     --Do something useful with @idList
END

But this time I needed to send not just the ids but a list of complex type elements. I once worked doing this but in that time I serialized my complex type to XML in order to be able to send it as a string to the database then once in the database I deserialized the XML (Probably you're wondering why XML and not JSON? Well the SQL Server version I was working with was SQL Server 2008 so no JSON available yet for that version) to a table in order to perform the operations I needed. That sounds like a lot of work right? So I decided to search if there was something else easier to work with.

The Solution: Table-valued parameters to the rescue!

While searching for a solution I found this interesting way to send parameters to SQL Server for store procedures. But what are table-valued parameters?

A Table-valued parameter as its name implies is a special parameter you can send to a stored procedure that can hold a specific structure table like defined by us. Basically, you can send a table, you previously defined, as one single parameter to a store procedure what awesome that is?

Goodbye to those long list of parameters or id lists sent as strings or complex types converted to XML and then serialized to send them also as strings!

How do we use them?

In order to use table-valued parameters we need to complete three steps:

1. Create a User-Defined Table Type.
2. Create the stored procedure where we are going to use the table-valued parameter and use the recently created User-Defined Table Type.
3. Configure ADO.NET to send the parameter as a special parameter.

Let's break it down each step. Let's imagine that we are working with a movie's web app where you can assign movies to different movie theaters in a single operation.

Create a User-Defined Table Type

This is the way we tell SQL Server how is the structure we want to send in the table-valued parameter and it's pretty simple to create one.
Let's see how we need to structure it.

CREATE TYPE <schema>.<name of the type> AS TABLE
(
   <ColumnName> <Colum type>
)
GO

An example could be

CREATE TYPE dbo.MovieAssignment AS TABLE
(
    MovieId INT,
    Order TINYINT,
    MovieTheaterId INT
)
GO

Create the stored procedure where we are going to use the table-valued parameter

Then we need to use the just created User-Defined Table Type in the SP where we want to send the table-valued parameter. The structure is the same as a normal SP.

CREATE PROCEDURE <schema>.<name of the type>
(
    @<parameter name> <user-defined table type> READONLY
)
AS 
BEGIN
   -- SP's body
END

An important note here is that we must add the READONLY keyword after the parameter type to tell SQL Server this is a table-valued parameter.

Back to our movie's app example, we could define an SP as follows.

CREATE PROCEDURE dbo.PersistMovieAssignments
(
    @MovieAssignments dbo.MovieAssignment READONLY
)
AS 
BEGIN
   -- SP's body
END

Configure ADO.NET to send the parameter as a special parameter

The last step to make this work is to configure our call to the SP in the C# code to let it know the parameter we are sending is a special table-valued parameter.

The first thing we need to take into consideration is that we need to send the parameter as DataTable, and mark the parameter type as Structured, this will tell ADO.NET to send the signal this parameter is a table-valued parameter.
Let's see an example.

using (var sqlConnection = new SqlConnection(/*Our connection string*/))
{
   sqlConnection.Open();
   /*Create the SqlCommand object to tell SQL what we want to execute, in our case is a stored procedure.*/
   var sqlCommand = new SqlCommand("dbo.PersistMovieAssignments", sqlConnection)
   {
      CommandType = CommandType.StoredProcedure
   };
   /*Then we create DataTable we want to send as table-valued parameter*/
   var movieAssignments = new DataTable();
   movieAssignments.Columns.Add("MovieId", typeof(int));
   movieAssignments.Columns.Add("Order", typeof(int));
   movieAssignments.Columns.Add("MovieTheaterId", typeof(int));

   /*Add the data to the DataTable object*/
   movieAssignments.Rows.Add(1, 1, 1);
   movieAssignments.Rows.Add(1, 2, 2);
   movieAssignments.Rows.Add(1, 5, 3);

   /*Then we create the SqlParameter object where we are going to assign the DataTable containing the data we want to send to SQL Server*/
   SqlParameter movieAssignmentsParameter = sqlCommand.Parameters.AddWithValue("@MovieAssignments", movieAssignments);
   movieAssignmentsParameter.SqlDbType = SqlDbType.Structured;

   /*Then we just execute our command*/
   sqlCommand.ExecuteReader();
}

If in our PersistMovieAssignments store procedure we are returning any kind of value we should store the result of executing the ExecuteReader method in some variable and process it accordingly to extract the values.

Important Notes

I added this section as part of an update to the post as I think is worth it to let you know. There are some limitations and important things we need to be aware of when we are working with table-valued parameters some of these are:

1. When using ADO.Net with a table-valued parameter, the order of the columns in the data table you're sending to the database must match the order of the columns defined in the user-defined table type. (Thank you Zohar for this important note).

2. Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in the table-valued parameter, you must insert the data into a temporary table or into a table variable.

3. You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

if you want to know more about the limitations and about table-valued parameters in general the best place to start is its official documentation.

Conclusion

Before I know this exists I always was having a hard time when needed to send multiple rows of data to an SP serializing data into XML and then deserializing it back again in SQL Server as I mentioned before. Although this is not wrong this could lead you to insert bugs in the serialize/deserialize part. I personally found this method much more strong typed which is a good thing because you can catch a lot of bugs at compile time.

And you? Did you know this exists? Have you ever used this? Have you ever faced the need to send complex type lists of objects to SQL Server before? How did you tackle the problem?

I hope this can be useful for anyone reading it. If you have any comments please leave it in the comments section down below. I will appreciate it!

Thank you for reading and see you in the next post!

Comments

[Zohar Peled]

Good article! A couple of important notes, though:

1. When using a ADO.Net with a table valued parameter, the order of the columns in the data table you're sending to the database must match the order of the columns defined in the user defined table type.

2. table valued parameters are always a better choice than delimited strings, even if the type is not complex (i.e. a list of ints etc.)

3. Instead of AddWithValue (see Can we stop using AddWithValue() already? you can simply use Add:
   

sqlCommand.Parameters.Add("@MovieAssignments", SqlDbType.Structured).Value = movieAssignments;

[Edwin Aragon]

Thank you for your reply.

The first thing you mention in your reply is worth adding to the post, I'll update it right away.

About point 2, I think the same, it's better to take advantage of strongly typed things when possible since you can detect bugs earlier in your development process.

I didn't know about point 3, super useful information thank you for bringing in it up!

I think we are on the same page though since in the next line I'm assigning the SQL type explicitly through:

movieAssignmentsParameter.SqlDbType = SqlDbType.Structured;

So I think it does the same as your line, please correct me if I'm wrong.

[Zohar Peled]

I'm glad I could help.
About AddWithValue - yes, you can use it like you've done.
Personally, I find using Add more readable - but that's probably a personal preference.
Cheers!