Passing a DataTable as table parameter from C# to stored procedures using table type

So may times I’ve seen useless loops to save many rows of data into a table, where data was captured in the front end part of the application. And sometimes, the row number was quite high – for example, where user was filtering some rows available to him, or imported from a data source like excel or access mdb file.
One of the options I’ve seen to replace loops was to transform the data into an XML, and pass the XML to a stored procedure that would handle it – but there are other things you need to take care of when doing this.
What I like to use, and this is not used as much as it should be in my opinion, is a table value type parameter passed to a stored procedure.
Firstly, I need a table type declared:

After this is on, I need to create the procedure that will handle the data:

You don’t necessarily need a stored procedure, you can use the parameter passed directly in your sql statement.

Ok, now the c# part, for demonstration purposes, let’s create a DataTable variable matching our MY_TABLE_TYPE table type above:

Now, if I want to add this DataTable variable as a SqlParameter, and pass it to the stored procedure above, I need to specify for the SqlParameter the following:
– name and value – which are self explanatory
– SqlDbType = SqlDbType.Structured – which indicates it’s a table value parameter
– TypeName – the name of the table value type we defined = MY_TABLE_TYPE

That’s all!

Next thing, you can use the parameter to call the stored procedure. Here’s how I’ve used it as a whole:

This will replace the need to loop through your DataTable / List, and process lines one by one, which would mean one call to your database per line, and when there are a lot of rows there, it means improvements to your application. I also prefer this solution over XML solution because is cleaner, and I don’t need to build the xml inside the application, and query it inside the database afterwards.

Here’s the Program.cs and DbUtil.cs to download if you’d like.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+
  1. Nice stuff, thx bro!

Leave a Comment