Until recently I had not had an opportunity to use Table-Valued Parameters a new feature in SQL Server 2008. I had looked at them briefly, thought they were a nice addition, and then moved on. Finally though, I found a chance to use them.
The intended use for Table-Valued Parameters is of course to send multiple rows of data to SQL Server from the client. Previous ways to do this involved calling a single stored procedure repeatedly, using XML, using SQLBulkCopy, or my least favorite, creating a stored procedure with many parameters. Each of these methods had draw backs. Calling a single stored procedure over and over works just fine (and was my preferred method) but you are creating a lot of traffic for something that should be simple. XML was nice too, but depending on the complexity of what you are sending you will need a lot of XQuery in your stored procedure; making something simple much more complex. SQLBulkCopy works great, I’ve used it before, but sometimes you may want to do more to your data once it is at the database. Thankfully table-valued parameters solve many of these short comings.
You can use table-valued parameters from your application with DataTables, DbDataReader, or IEnumerable objects. The majority of examples for table-valued parameters are done using DataTables. The problem I have with DataTables is that this seems like too much of an ad-hoc method with more overhead then is needed. Whereas going the IEnumerable route lets you create and use objects that you would likely already be using. This is the route I prefer, and the one I will demonstrate.
For this example assume we have a database of people and each of the people may have one or more aliases. They must be rather shady people. Here is the table for storing the people:
CREATE TABLE tbl_Person ( [ID] INT IDENTITY(1,1) NOT NULL, [FirstName] VARCHAR(20) NOT NULL, [LastName] VARCHAR(20) NOT NULL )
It is quite simple, an identity field called ID to give everyone a unique number and a first and last name.
Now we have a table to store their aliases:
CREATE TABLE tbl_Alias ( [ID] INT NOT NULL, [FirstName] VARCHAR(20) NOT NULL, [LastName] VARCHAR(20) NULL )
Another simple table to keep our shady people’s aliases; let’s pretend for ease of use there are primary keys and foreign keys shall we?
To add to this table we will use a stored procedure, my favorite mechanism to get data to the database. Why? Because you give the DBA something concrete upon which they can optimize your database, among other reasons. But that’s another post.
Our stored procedure will be an inserting stored procedure which will add our new shady person and their aliases in one call. Thus we will need to create our user-defined table type first.
CREATE TYPE udt_tbl_Alias AS TABLE ( [FirstName] VARCHAR(20) NOT NULL, [LastName] VARCHAR(20) NULL )
Again, nothing complicated. You will see why I left off the ID field in a bit.
You should note that you cannot use ALTER commands on user-defined table types. So if you want to update the table type later you will have drop it and create it. But if it is being used by a stored procedure you will have to temporarily comment that out of your stored procedure first, then drop the UDT, create the UDT again, and uncomment the UDT in your stored procedure. So plan ahead! Having planned ahead, here is our stored procedure for inserting:
CREATE PROCEDURE usp_ins_Person @FirstName VARCHAR(20), @LastName VARCHAR(20), @Aliases udt_tbl_Alias READONLY AS BEGIN SET NOCOUNT ON INSERT INTO tbl_Person VALUES (@FirstName, @LastName) INSERT INTO tbl_Alias (ID, FirstName, LastName) SELECT @@IDENTITY [ID], FirstName, LastName FROM @Aliases END
The stored procedure requires three parameters: a first name, a last name and the user-defined table-valued parameter. The table type must be marked as READONLY and thus you will not be able to update the table within the stored procedure, only select from it. Otherwise, the stored procedure is rather straight forward. The person is added to tbl_Person and the aliases are added to tbl_Alias. You can see why I chose not to have the ID part of the user-defined table type, since we will not the ID till the person has been inserted.
To use the stored procedure in SQL Server Management Studio you can do the following.
DECLARE @Aliases udt_tbl_Alias
DECLARE @FirstName VARCHAR(20) = 'Bryan'
DECLARE @LastName VARCHAR(20) = 'Smith'
INSERT INTO @Aliases VALUES ('Database', 'Guy'), ('DBA', NULL)
EXEC usp_ins_Person @FirstName, @LastName, @Aliases
SELECT * FROM tbl_Person
SELECT * FROM tbl_Alias
The output should look like this.
Output
Great, we’re halfway there! The database side of things is taken care of, now we will need to take care of our client. Within the client code we will create a Person class. The class will have a property for the first name, last name, and aliases. This is why I like the IEnumerable route; it makes sense to store the aliases for a person with the person, and doing so as a list makes sense.
Person Class:
namespace TestUDTApplication
{
class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public AliasCollection Aliases { get; set; }
public Person(string firstName, string lastName)
{
FirstName = firstName;
LastName = lastName;
Aliases = new AliasCollection();
}
}
}
Alias Class:
namespace TestUDTApplication
{
class Alias
{
public string FirstName { get; set; }
public string LastName { get; set; }
public Alias(string firstName, string lastName)
{
FirstName = firstName;
LastName = lastName;
}
}
}
AliasCollection Class:
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Server;
namespace TestUDTApplication
{
class AliasCollection : List<Alias>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("FirstName", SqlDbType.VarChar, 20),
new SqlMetaData("LastName", SqlDbType.VarChar, 20)
);
foreach (Alias alias in this)
{
ret.SetString(0, alias.FirstName);
ret.SetString(1, alias.LastName);
yield return ret;
}
}
}
}
Let’s look over this code. First off the Person class has three public properties. FirstName, LastName, and Aliases. The Aliases property is an instance of the AliasCollection class, which inherits the Alias class as a List and then implements the IEnumerable interface. The List<Alias> turns our Alias into a List, which is perfect for handling our aliases within the client code.
The implementation IEnumerable<SqlDataRecord> is what will let us use our List as the input to our user-defined table. Specifically we are implementing IEnumerable with SqlDataRecord, this represents a single row of data and its associated metadata. This is what ADO.Net and SQL Server will need to map our list of aliases to our user-defined table. The documentation is not really clear on this part unfortunately, it leads you to believe that anything that implements IEnumerable should suffice, however; what you must have is IEnumerable with SqlDataReader. This does not come out of the box with List<Alias> so we create our own, otherwise we’ll get an InvalidCastException.
Finally, from our client, using the Enterprise Library Data Access, we can load people and aliases using a user-defined table type.
Person person = new Person("Bryan", "Smith");
person.Aliases.Add(new Alias("DBA", "Dude"));
person.Aliases.Add(new Alias("Database", "Guy"));
SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase("UDTTest");
DbCommand cmd = db.GetStoredProcCommand("usp_ins_Person");
db.AddInParameter(cmd, "@FirstName", DbType.String, person.FirstName);
db.AddInParameter(cmd, "@LastName", DbType.String, person.LastName);
db.AddInParameter(cmd, "@Aliases", SqlDbType.Structured, person.Aliases);
db.ExecuteNonQuery(cmd);
I know personally, I’ll be using this method from now one whenever possible over using user-defined tables with ad-hoc DataTables to load data, or heaven forbid calling the same stored procedure repeatedly.