Passing Sets Through ADO

Yesterday we looked at an old school way to merge large sets of external data into an SQL Server database through the use of a temp table, Bulk Copy, followed by a stored procedure, all from a single ADO database call. This method works great when you are working with more than one database, or do not have user defined table objects in your target database.

Today we’ll look at a preferred method for passing sets into a database stored procedure from an external client. Since SQL Server 2008, you have the ability to define a virtual table object known as a user defined table type. You can create an instance of this type, and populate it from your external ADO code.

In order to pass a set from ADO into an instance of an SQL Server User Defined Table Type you need:

1. Create the table type in the database
2. Create an ADO parameter and assign its value to an object that derives from the DataReader class such as a datatable, datatablereader, etc. Some IEnumerable generic sets are supported as well without explicit conversion.
3. Set the parameter type to SqlDbType.Structured
4. Set the parameter typename to the name of the user defined table type from your database

Here is a snippet of an example take from MSDN referenced below. In this code the application is using an ADO dataReader.

// Configure the SqlCommand and table-valued parameter.

SqlCommand insertCommand = new SqlCommand(

"usp_InsertCategories", connection);

insertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam =

insertCommand.Parameters.AddWithValue(

"@tvpNewCategories", dataReader);

tvpParam.SqlDbType = SqlDbType.Structured;

tvpParam.TypeName = "dbo.CategoryTableType";

Because of step 4, you cannot use this method across multiple databases. The UDT is unique to the database in which it is defined.

Note: if you want to read other old school methods for passing sets there is a summary found under the heading “Passing Multiple Rows in Older Versions of QL Server.”

Why would you want to pass sets in the first place? Often, the need is based on the workflow of an application for maintaining data. An application may allow a user to modify multiple records in a table, but wish to save persistence for all of the updates as a single transaction, rather than updating rows one at a time. If all the rows can be passed to your database, then a MERGE command, or hand crafted merge statements may be executed to Insert/Update/Delete rows from the client source against the database history.

Are you working with sets in your application? Then this implementation may be the ticket for you.

Cheers,

Ben