Background

Ever since I started out working with stuff like Data Access, SQL server, stored procedures and all that sort of gizmo's, I felt a need in certain situations for a stored procedure that is able to take in some kind of array, list, table of items and insert them in a table. This pops out the most in many-to-many relationships between tables. You'd take a .NET object, fill it up with several List<T> properties to hold that sort of info, and then what? Loop over them and open up a set of DB connections for each item? Parse the list into a comma separated string? Or parse it into an xml to process on the server? Sure, they all work, but it's a lot of work every time you need to do it. And between you and me, I'm not too fond of xml processing in SQL Server myself?

Eureka!

So after all those years (well I'm still young, so maybe not all THOSE years) I finally found ONE answer to my problem. Let me repeat the ONE keyword here, because in all honesty, I'm not even sure if this is actually a decent way to get things done. The only thing I guarantee is that it works for me, and kind of makes sense in how it works too (talk about luck!)

How it's done

Part 1: SQL Server

Starting out with the SQL Server part. Keep in mind a tiny scenario where we have a List<Person> object, that we want to insert into a table.

The first thing to do is create a User-Defined Table Type in SQL Server.

So first thing to notice is this is actually the same syntax as to create a regular table, except for the keyword TYPE ? AS TABLE. The rest of it is basic column definition. For simplicity reasons, we only keep track of an Id, a surname and a first name.

Next thing to do is to create the table itself. I'll create them with the same column names for the ease of it, but this of course, is not a restriction.

And to top it off, I'll add a stored procedure to the party that takes care of the insert logic.

So the parameter for this stored procedure is of the User-Defined Table Type Person. This needs to be marked as READONLY, otherwise the procedure won't get created.

Part 2: .NET

Now that the SQL part is out of the way, it's time to start the work in .NET. Let's take a step back and go over what needs to be there. We will definately need:

• A Person object to hold the properties
• A List<Person> to hold the people that need to be added to the table
• Some kind of hocus-pocus to transform a List<T> to a DataTable(System.Data)

Going in a chronological order, we start out with the object itself, which as suspected, is nothing magic.

Next on the list is adding some folks to a List<Person>:

Now, the biggest piece of work in all this would be to parse the List into a DataTable object. Considering the fact that the amount of possible objects is, well, infinite, you shouldn't be translating every single object to a DataTable right? Right. So my solution is to have a DataTable<T> object that holds a List<T>, and translates every property of T into a DataTable object. Nice and easy!

And if you remember the List<Person> object that was created a minute ago, called persons, all we need to do now is call the stored procedure to insert those persons in the table:

Part 3: the outcome

So when this small project started out, there was this situation at hand:

Now when I run this app, we get this:

Conclusion

There is a little bit of prep work to do with this solution, in configuring the SQL objects etc. But then again, all database designs require prep work, so that's something I'm willing to let slide. But to this day, it's the first easy to implement solution I have to a problem I've been dealing with for over four years now.