Dapper With .Net Core

I’ve been looking at converting one of our existing WebApi site to ASP.Net core. One issue that I found is that we us Table Value Parameters (TVP) in our sql server stored procedures.

In .Net 4.6 Dapper uses DataTables and the .AsTableValuedParameter helper method. DataTables don’t exist in .Net core which means that we have an issue.

What I found is that you can go back to what appears to be the original way that Dapper supported TVPs and create a class that implements IDaynamicParameters.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public class EmailAddressesParameter : SqlMapper.IDynamicParameters
  {
      private readonly IEnumerable<string> _emailAddresses;

      /// <summary>Initializes a new instance of the
      /// <see cref="T:System.Object" /> class.</summary>
      public EmailAddressesParameter(IEnumerable<string> emailAddresses)
      {
          _emailAddresses = emailAddresses;
      }

      /// <summary>
      /// Add all the parameters needed to the command just before it executes
      /// </summary>
      /// <param name="command">The raw command prior to execution</param>
      /// <param name="identity">Information about the query</param>
      public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
      {
          var sqlCommand = (SqlCommand) command;
          sqlCommand.CommandType = CommandType.StoredProcedure;
          var items = new List<SqlDataRecord>();

          foreach (var emailAddress in _emailAddresses)
          {
              var rec = new SqlDataRecord(new SqlMetaData("EmailAddress",
                SqlDbType.NVarChar, 100));
              rec.SetString(0, emailAddress);
              items.Add(rec);
          }

          var p = sqlCommand.Parameters.Add("@emailAddress", SqlDbType.Structured);
          p.Direction = ParameterDirection.Input;
          p.TypeName = "EmailTableType";
          p.Value = items;
      }
  }

Then you call it like this.

1
2
3
4
5
using (var connection = new SqlConnection(connectionString))
{
  var parameters = new EmailAddressesParameter(new List<string>() { "test@test.com" });
  connection.Execute("test", parameters);
}

It has a slight downside that you need to create a new class for each TVP you use. I don’t think that is too big of a deal as it would be a one time set up.