Share via


Passing arrays, key value pairs, and other collections to SQL stored procedures using table valued parameters.

Overview

MS SQL server doesn’t support arrays or other collection data types, so we can’t directly send in these types as parameters to stored procedures from client applications. But fortunately, SQL does allow us to send in DataTable objects in MS SQL Server 2008 and later. The solution here is to simply convert our collection types into a DataTable object and then pass the DataTable into the stored procedure. This should work for any type that you can restructure into a table format.

Have an array of string or int? This can be converted into a table with one column with one row for each item in the array. Have a dictionary or other key-value pair collection? This becomes a table with two columns. Have a collection of class objects with multiple properties? Make a column for each class property and a row for each collection item.

Using table valued parameters makes it possible to send in a collection of items from your client application to the SQL server in a single roundtrip, without using XML or string parsing. This is helpful in several scenarios. For example, it is easier to delete a large number of records via stored procedure if you can call the delete procedure one time with a collection of record IDs instead of the same stored procedure over and over (one ID at a time). It also makes processing easier when you have other collections, such as key-value pairs that need to be passed into a stored procedure.

Note: At the time of this writing, table valued parameters are not supported for use in Linq to SQL. Hopefully this support is added in the future.

Step 1: Business objects

The first task is to address the business objects in the client. In the sample application I have defined a very simple class with two properties.

 // code sample 1
/// <summary>
/// Defines a single item object.
/// </summary>
class Item
{
    /// <summary>
    /// Gets or sets the ID of the item.
    /// </summary>
    public Guid ID { get; set; }
     /// <summary>
    /// Gets or sets the Name of the item.
    /// </summary>
    public string Name { get; set; }
}

If we add conversion methods to the collection type, we can easily construct the required DataTable objects on demand. The first example extension returns a DataTable with the ID and Name properties of the business objects. The second example extension returns a DataTable with the ID of the each business object.

 // code sample 2
/// <summary>
/// Defines a collection of item objects.
/// </summary>
class Items : List<Item>
{
    /// <summary>
    /// Returns the items as a DataTable.
    /// </summary>
    /// <returns><c>DataTable</c></returns>
    public DataTable GetItemsAsDataTable()
    {
        // construct the empty DataTable object with columns.
        DataTable table = new DataTable();
        table.Columns.Add("ID", typeof(Guid));
        table.Columns.Add("Name", typeof(string));
         // add a single row for each item in the collection.
        foreach (var item in this)
        {
            table.Rows.Add(item.ID, item.Name);
        }
         return table;
    }
     /// <summary>
    /// Returns only the item IDs as a DataTable.
    /// </summary>
    /// <returns><c>DataTable</c></returns>
    public DataTable GetItemIDsAsDataTable()
    {
        // construct the empty DataTable object with columns.
        DataTable table = new DataTable();
        table.Columns.Add("ID", typeof(Guid));
         // add a single row for each item in the collection.
        foreach (var item in this)
        {
            table.Rows.Add(item.ID);
        }
         return table;
    }
}

Step 2: SQL database prep

On the SQL server side we need to prepare the database to accept our table-valued parameters by defining them ahead of time. We then need to ensure that new or existing stored procedures are in place to accept these new table parameters.

For demo purposes I’m assuming that we need to create a database from scratch. Sample 3 (below) creates the following required objects:

• A new table to store our business object (Item)
• A user defined Table type to store Guids (IDs)
• A user defined Table type to store Guids (IDs) and Strings (Names)
• A stored procedure to remove items, using the table valued parameter.
• A stored procedure to add items, using the table valued parameter.

Note: that when a stored procedure uses a table-valued parameter, it must be passed as read-only. It cannot be modified inside the stored procedure. If it needs to be modified, then insert the rows into another table variable.

 -- code sample 3
create table dbo.ItemsTable
(
 [ID]    uniqueidentifier primary key not null,
    [Name]  nvarchar(255) not null
)
 create type dbo.IDsAndNamesTable as table
(
    [ID]    uniqueidentifier,
   [Name]  nvarchar(255)
)
 create type dbo.IDsTable as table
(
    [ID]    uniqueidentifier
)
go
 create procedure dbo.AddItems
(
  @Items  [IDsAndNamesTable] readonly
)
as
begin
   insert into dbo.ItemsTable ( [ID], [Name] )
 select [ID], [Name] from @Items 
 end
go
 create procedure dbo.RemoveItems
(
   @IDs    [IDsTable] readonly
)
as
begin
   delete from dbo.ItemsTable
  where [ID] in ( select [ID] from @IDs )
 end
go

Step 3: Stored procedure execution

Once the business objects and the database are prepared, we can add client side code to call the stored procedures. To demonstrate calling the stored procedures and passing in the DataTables, I have added the following code to the sample application.

Note: The SqlDbType on the parameter is SqlDbType.Structured to indicate a user defined table type.

Also, at this location we convert the client collection type into a DataTable via the methods written earlier.

 // code sample 4
/// <summary>
/// Adds items to the database.
/// </summary>
/// <param name="items">Collection of items to add.</param>
static void AddItemsToDatabase(Items items)
{
    // construct sql connection and sql command objects.
    using (SqlConnection sqlcon = new SqlConnection("<insert connection string here>"))
    {
        using (SqlCommand cmd = new SqlCommand("AddItems", sqlcon))
        {
            // add the table-valued-parameter. 
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Items", SqlDbType.Structured).Value = items.GetItemsAsDataTable();
             // execute
            sqlcon.Open();
            cmd.ExecuteNonQuery();
        }
    }
}
 /// <summary>
/// Removes items from the database.
/// </summary>
/// <param name="items">Collection of items to remove.</param>
static void RemoveItemsFromDatabase(Items items)
{
    // construct sql connection and sql command objects.
    using (SqlConnection sqlcon = new SqlConnection("<insert connection string here>"))
    {
        using (SqlCommand cmd = new SqlCommand("RemoveItems", sqlcon))
        {
            // add the table-valued-parameter. 
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@IDs", SqlDbType.Structured).Value = items.GetItemIDsAsDataTable();
             // execute
            sqlcon.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Now it’s time for a test run. In the main method I have decided to call the AddItems stored procedure just by passing in a collection:

 // code sample 5
static void Main(string[] args)
{
    // add these new items
     Items items = new Items();
    items.Add(new Item() { ID = new Guid("EE612E96-1577-4C43-978D-1ACF6BE8A22D"), Name = "Orange" });
    items.Add(new Item() { ID = new Guid("55D000B9-51E7-43FD-9540-D24ACA711231"), Name = "Yellow" });
    items.Add(new Item() { ID = new Guid("89DAB4DD-35D2-4BFA-8735-9C6DCFC4110E"), Name = "Green" });
    items.Add(new Item() { ID = new Guid("F4E0965E-8501-4FFB-A1B7-1D08B6EF3DBC"), Name = "Red" });
    items.Add(new Item() { ID = new Guid("40F3DFAA-65CD-45EC-814F-3E59E46C95E2"), Name = "Blue" });
    items.Add(new Item() { ID = new Guid("A453B8C3-E9AF-447E-B847-FC7FA6D822AD"), Name = "Grey" });
     AddItemsToDatabase(items);
}

As expected, it inserted all the items:

I slightly re-arranged the main method to remove some of the existing items:

 // code sample 6
static void Main(string[] args)
{
    // remove these existing items
     Items items = new Items();
    items.Add(new Item() { ID = new Guid("F4E0965E-8501-4FFB-A1B7-1D08B6EF3DBC"), Name = "Red" });
    items.Add(new Item() { ID = new Guid("40F3DFAA-65CD-45EC-814F-3E59E46C95E2"), Name = "Blue" });
    items.Add(new Item() { ID = new Guid("A453B8C3-E9AF-447E-B847-FC7FA6D822AD"), Name = "Grey" });
     RemoveItemsFromDatabase(items);
}

After execution we can see our items were removed correctly:

Conclusion

Table valued parameters allow you to pass tables full of data directly to a stored procedure. Using methods like the ones defined in code sample 2 will allow you to easily generate DataTables on demand from your client application. Since the custom DataTables can be defined according to your own specifications, there is no technical requirement to limit them to just one or two columns like the samples above. You can leverage them to fit whatever objects or columns that need to be passed in.

Comments

  • Anonymous
    July 16, 2014
    Thanks for posting!  We've been using table value parameters more and more to avoid multiple calls to a stored procedure or having to parse and build collections as strings.  It has helped performance considerably in the applications we have used it so far.

  • Anonymous
    July 17, 2014
    @JB - thanks! We have been using it more and more as well. Lots of benefits.

  • Anonymous
    July 20, 2014
    Beautifully simple, nice one.

  • Anonymous
    August 01, 2014
    Nice! I've been using @table declarations and INSERT INTO statements with multiple VALUES() to prep a block of things I need to work on, this looks like it would perform much better. Can't wait to try it on Monday.

  • Anonymous
    September 18, 2014
    Can we map the result to Object back from Stored Procedure?

  • Anonymous
    October 01, 2014
    This article is slightly misleading. The title has to be changed to "From Client to Data Layer"

  • Anonymous
    February 11, 2015
    Very helpful article. Thank you!