Using Table Valued Parameters in Dapper

Today I discovered a neat way of passing values into a SQL Server stored procedure with Dapper using a Table Valued Parameter. Up to now, I’d been using a DataTable, adding the columns programatically and then populating it row-by-row. Whilst re-reading Erland Sommarskog’s detailed post about dynamic SQL, I noticed something I hadn’t picked up on before – that there was another way of doing this. Some more research via Leonard Lobel’s site lead me to this solution, which is much neater that the DataTable shenanigans.

Firstly we need to create a table-valued parameter in SQL Server: In your database, open a new query and type:

CREATE TYPE dbo.SimpleKeyList AS TABLE( [KeyId] INT NULL)

Next we create a procedure that will accept the TVP as a parameter:


CREATE PROC dbo.EchoKey
@KeyList dbo.SimpleKeyList READONLY
AS
SELECT KeyId FROM dbo.SimpleKeyList ORDER BY KeyId DESC

This procedure will just return all the keys sent in but in reverse numerical order, so we can see that some processing has occurred.

Now we need to do a bit with Dapper;

This is our basic method:


        public List<int> EchoSomeKeys(KeyList inputData)
        {
            using (var db = new SqlConnection(_connString))
            {
                var data = db.Query<int>("EXEC Core.EchoKey @KeysIn", 
                    new {KeysIn = inputData.AsTableValuedParameter("dbo.SimpleKeyList") }
                );
                return data.ToList();
            }
            
        }

Looks simple doesn’t it? Now all we need is our input class KeyList:


public class KeyDto
{
    public int KeyId { get; set; }
}
public class KeyList : List<KeyDto>, IEnumerable<SqlDataRecord>
{
  IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
  {
    var sdr = new SqlDataRecord(new SqlMetaData("KeyId", SqlDbType.Int));

    foreach (KeyDto item in this)
    {
      sdr.SetInt32(0, item.KeyId);
      yield return sdr;
    }
  }
}

You can see that a little bit of magic is injected via the implementation of

IEnumerable<SqlDataRecord>

Dapper provides the AsTableValuedParameter method which iterates over the enumerator provided by the interface and populates the parameter TVP.

I knocked up a quick NUnit test to check it all works:


[TestFixture]
public class TestTableValuedParam
{
  [Test]
  public void TVP1()
  {
    var a = new Tvps();
    KeyList keysIn = new KeyList();
    keysIn.Add(new KeyDto { KeyId = 68});
    keysIn.Add(new KeyDto { KeyId = 12 });
    keysIn.Add(new KeyDto { KeyId = 42 });

    var m= a.EchoSomeKeys(keysIn);
    Assert.That(m.Count, Is.EqualTo(3));
    foreach (var i in m)
    {
      Console.WriteLine(i);
    }
  }
}

Which verifies that we get the values returned in reverse order.