DEV Community: Edwin Aragon

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

Edwin Aragon — Sat, 03 Oct 2020 20:19:29 +0000

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:

Create a User-Defined Table Type.
Create the stored procedure where we are going to use the table-valued parameter and use the recently created User-Defined Table Type.
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:

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

The Evil Is In The Details

Edwin Aragon — Sat, 05 Sep 2020 20:28:41 +0000

Let's be honest who doesn't love to apply new cool features when you know of their existence or explore different things to accomplish the same task? even if they're not truly new and they have existed for a long time but you just have heard about them. That's exactly what happened to me and it give me a valuable lesson.

Let me introduce myself a little bit. I'm Edwin, I'm from México and I'm a 4 year web developer who loves to learn new things and apply them whenever is possible, I mainly around C#, Javascript, HTML, CSS and SQL. I recently realized that if I want to become better at coding I should start putting extra effort on it, this means to practice more even after work, reading more about web development, work on side projects and start posting web development stuff where I can share my knowledge no matter if it's something simple (like this one). So welcome to my first post I hope you find it useful and easy to understand.

Let's jump in to the point!

The XMLHttpRequest Object

Most of web developers know what this dude does. It helps you to communicate to a sever making AJAX calls to it. Although I had to studied it a while ago to complete a certification I've never used it before in a real project because you know, all the libraries out there makes it easy for you to accomplish this task so why bother?

This time was different, as part of my training to become a better developer I decided to use vanilla JS whenever possible to understand better how JS works, go to the basics to master them and reinforce my current JS knowledge.

I went directly to the MDN docs to read how to implement a simple call using the XMLHttpRequest object. All I needed was to POST some data into the server and once the call was finished log a Success message in the console if the call was completed successfully or log the error message received if not.

Until this moment I knew about the onreadystatechanged callback was used to notify the status of the call but I found something else in the docs:

onreadystatechange as a property of the XMLHttpRequest instance is supported in all browsers.
Since then, a number of additional event handlers have been implemented in various browsers (onload, onerror, onprogress, etc.)

What catched my attention was the second line, event handlers for load and error cool! Let's read more about them.

XMLHttpRequest provides the ability to listen to various events that can occur while the request is being processed. This includes periodic progress notifications, error notifications, and so forth.
load The transfer is complete; all data is now in the response.
The error event is fired when the request encountered an error.

Nice then I will use them to notify the status of my call!

NOTE: Although this was not something new per se it was new for me.

The Task

So I proceeded to write my code and ended up with something like this:

const ajaxCall = new XMLHttpRequest();
ajaxCall.addEventListener("load", () => console.log("Success")});
ajaxCall.addEventListener("error", error => console.log(error));
ajaxCall.open("POST", localhost:30408/post/valid/url);
ajaxCall.setRequestHeader("Content-Type", "application/json;charset=UTF-8");
ajaxCall.send(JSON.stringify(getPostData()));

When tried this code the POST was correctly sent and processed by the server and logged in the console.

Then I forced the POST to return a 500 http status code to try my error event listener and send a message to log in the console but my surprise was this.

Wait a minute this shouldn't be happening right? I should see my error message instead of my Success message. After some googling I fount out that I was using the error event in the wrong way and how is that? Well it turns out that the error event is not in charge of listening for the response's status but for network problems instead!

Let's fake a network problem just pointing the request url to something that doesn't exist and see if our error listener catches it.

Victory! the error event was fired, you can see the console.log(error) line being executed before the console ERR_NAME_NOT_RESOLVED error. Here we are printing the object because the error listener receives a ProgressEvent as callback parameter so our error variable is of type ProgressEvent.

At the end I will end up using the onreadystatechange callback but with something new added to my knowledge just for being curious.

Conclusion

It's OK to try new ways to do the same things you already know how to (as a simple AJAX call) this way you add some extra valuable knowledge to you and keep you learning, which is always a good thing. So keep finding different ways to complete a task you never know what new thing you will learn this time.

Happy Coding!