SQL TVP Made Easy

Introduction

It is important to minimize calls to the database whenever possible.

SQL can be hard, especially when it comes to things like Table-valued parameters (TVP). TVP, in very basic terms, is a way of wrangling many rows worth of data in one database call. You can use TVP to insert multiple rows into a table, or ask for multiple records by id, without putting your stored procedure into a loop (which is not only bad practice but can lead to efficiency issues and overtapping the database). TVP is one way to cut down on calls to the DB while improving efficiency. Which means better experiences for your users.

Old Implementation

First of all, let's look at the code that we want to update to use TVP:

foreach (Employee employee in Employees)
{
    SqlCommand getEmployerById = new SqlCommand("get_employer_by_id", myConnection)
    { CommandType = System.Data.CommandType.StoredProcedure };
    getEmployerById .Parameters.AddWithValue("@id", employee.EmployerId);

    SqlDataReader employerReader = getEmployerById.ExecuteReader();
    while (employerReader.Read())
    {
        Employer employer = new Employer();
        employer.Id = (int)employerReader["employer_id"];
        employer.Name = employerReader["name"].ToString();
        Employers.Add(employer);
    }
    
    employerReader.Close();
}

In the example above, we are looping through a list of Employees which contain an EmployerId. We then feed that id into a stored procedure called get_employer_by_id in order to retrieve employer data from the database.

CREATE PROCEDURE [dbo].[get_employer_by_id]
    @id int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM dbo.employers WHERE employer_id = @id
         
END

The stored procedure simply returns the rows (one in this case since the id column is a primary key) that match @id. This is fine if you are only going to retrieve one row, but is not okay if you are going to retrieve multiple rows.

New Implementation

In order to update the existing code to use TVP, there are three things we need to do.

1. Add a User-Defined Table Type to the database.
2. Update stored procedure to take the new Table Type as a parameter.
3. Refactor the code in order to pass in valid TVP values.

Note: We are going to make this entire thing as generic as possible. We will only be going through how to do this with one type of data in the Table Type. If you are feeling adventurous, you can expand this method to use more than one type of data.

Adding User-Defined Table Types

Creating a new Table Type is as easy as running this call in SSMS. Make sure that you are on the correct database and schema!

USE [database_name]
GO

CREATE TYPE [dbo].[id_tvp] AS TABLE(
    [id] [int] NULL
)
GO

Updating Stored Procedure

To update the stored procedure, we will make use of the IN keyword. Here is what it looks like:

ALTER PROCEDURE [dbo].[get_employer_by_id]
    @ids id_tvp READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM dbo.employers WHERE employer_id IN (SELECT id FROM @ids)
         
END

To explain a little bit more, the reason this works is because we changed @id to @ids which is now the type id_tvp. Remember that id_tvp is a temporary table that will be loaded with a list of ints. When you see (SELECT id FROM @id), it works exactly like a normal SELECT call, but the data is returned and referenced inline.

Refactoring Code

Here is where things get a little more complicated. We need to add a class called IdCollection to our project. IdCollection implements IEnumerable<SqlDataRecord> and Inherits from List<int>. You can see that there are 3 constructors depending on how you want to pass a collection into the class. IdCollection also overwrites IEnumerable.GetEnumerator() which converts our collection to an Enumerator<SqlDataRecord> (without doing this, SQL will fail to convert the list to an IEnumerator and you will be very confused and frustrated). Finally, the class contains GetSQLParameter() which returns an SqlParameter in a way that tells the SqlCommand what we want to do.

public class IdCollection : List<int>, IEnumerable<SqlDataRecord>
{
    public IdCollection() { }

    public IdCollection(IList<int> idList)
    {
        AddRange(idList);
    }

    public IdCollection(IEnumerable<int> idEnumerable)
    {
        AddRange(idEnumerable);
    }
    
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
    {
        var sqlRow = new SqlDataRecord(
              new SqlMetaData("id", SqlDbType.Int));

        foreach (int id in this)
        {
            sqlRow.SetInt32(0, id);

            yield return sqlRow;
        }
    }

    public SqlParameter GetSQLParameter()
    {
        return new SqlParameter("@ids", this)
        {
            TypeName = "dbo.id_tvp",
            SqlDbType = SqlDbType.Structured
        };
    }
}

Once we have IdCollection added, we are finally ready to refactor our SqlCommand.

SqlCommand getEmployerById = new SqlCommand("get_employer_by_id", myConnection)
{ CommandType = System.Data.CommandType.StoredProcedure };
getEmployerById.Parameters.Add(new IdCollection(employees.Select(x => x.EmployerId)).GetSQLParameter());

SqlDataReader employerReader = getEmployerById.ExecuteReader();
while (employerReader.Read())
{
    Employer employer = new Employer();
    employer.Id = (int)employerReader["employer_id"];
    employer.Name = employerReader["name"].ToString();
    Employers.Add(employer);
}

employerReader.Close();

First, we have to remove the foreach loop from earlier.

Next, make sure that you change AddWithValue to Add and update the parameters according to what you see in the code above.

Conclusion

And that's it. When you run your code, it should visibly function the same, but now you only call the stored procedure once.

Credit where it is due: I used and modified some code from this forum post.