How can I use a DataTable as a stored procedure parameter in Dapper?

c# dapper sql sql-server

Question

I use Dapper to communicate with SQL Server in C#. I execute a stored procedure with 2 user-defined tables accepted as arguments.

I also just passed aDataTable to my stored method. This page specifies the syntax to pass aDataTable is as follows:

DataTable value = new DataTable();

DynamicParameters allParameters = new DynamicParameters();
allParameters.Add("name", value, value.AsTableValuedParameter("dbo.udt_MyTable"), ParameterDirection.Input, -1);

But I'm encountering this issue:

Argument 3: cannot convert from 'Dapper.SqlMapper.ICustomQueryParameter' to 'System.Data.DbType?'

What is incorrect with my syntax? Is Dapper's functionality described above broken?

1
0
11/13/2019 5:18:55 PM

Popular Answer

using (SqlConnection con1 = new SqlConnection(connectionstring))
 {
  using (SqlCommand cmd1 = new SqlCommand("your_procedure_name"))
    {
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.Connection = con1;                                               

    SqlParameter Param = cmd1.Parameters.AddWithValue("@parameter_name", dt);
    Param.SqlDbType = SqlDbType.Structured;
        con1.Open();
        cmd1.ExecuteNonQuery();
        con1.Close();
    }
}
0
11/13/2019 5:33:05 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow