Passing a table as an input parameter to Dapper in .NET Core

Question

While trying to pass a table in as a parameter to a stored procedure via Dapper, I came across this SO answer, which indicates that it is directly supported in Dapper.

In .NET core however, DataTable seems not to be implemented, so this does not work.

Is there another easy way of passing a custom / user-defined table in as a parameter to a stored procedure using Dapper?

Does this answer your question? [How do I pass a table-valued parameter to Dapper in .NET Core?](https://stackoverflow.com/questions/41132350/how-do-i-pass-a-table-valued-parameter-to-dapper-in-net-core) — TylerH, Apr 14 '22 at 15:03

William Xifaras · Answer 1 · 2017-04-13T18:47:47.910

For now, you will have to create a class that implements SqlMapper.IDynamicParameters for each table value type.

Modified example (not tested)

http://todothinkofname.net/2016/05/30/Dapper-with-Net-core/

  public class ParameterTvp : SqlMapper.IDynamicParameters
  {
      private readonly IEnumerable<string> _parameters;

      public ParameterTvp(IEnumerable<string> parameters)
      {
          _parameters = parameters;
      }

      public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
      {
          var sqlCommand = (SqlCommand) command;
          sqlCommand.CommandType = CommandType.StoredProcedure;
          var items = new List<SqlDataRecord>();
          foreach (var param in _parameters)
          {
              var rec = new SqlDataRecord(new SqlMetaData("Parameter", SqlDbType.NVarChar, 100));
              rec.SetString(0, param);
              items.Add(rec);
          }

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

Usage

  using (var connection = new SqlConnection(connectionString))
  {
    var parameters = new ParameterTvp(new List<string>() { "XXXXXXXX" });
    connection.Execute("test", parameters);
  }

[Here's](http://stackoverflow.com/questions/41132350/is-there-a-way-to-pass-a-tvp-to-dapper-on-net-core-right-now) another example on stack. — bbsimonbb, Apr 13 '17 at 07:44
Can you move some sample code from linked resource back here? — VMAtm, Apr 13 '17 at 18:32

score 0 · Answer 2 · edited May 23 '17 at 12:26

0

You should be able to pass in an IEnumerable<SqlDataRecord>, like this. It's in System.Data, and it's not deprecated.

edited May 23 '17 at 12:26

Community

1
1

answered Apr 13 '17 at 07:42

bbsimonbb

27,056
15
80
110

Passing a table as an input parameter to Dapper in .NET Core

2 Answers2