Using Dapper, how to map a stored procedure to a table-valued parameter?

c# dapper sql-server stored-procedures table-valued-parameters

Question

In my project we are using Dapper to connect with database for operations.

I have created a stored procedure in my SQL Server database as:

CREATE PROCEDURE dbo.usp_Check
    @TestTableType [dbo].[TestTableType] READONLY
AS
BEGIN
  SELECT 
      TestTableType.BrokerNPN,
      CASE WHEN Sales.BrokerNPN IS NULL THEN 0 ELSE 1 END as isBrokerNPNExist
  FROM 
      [dbo].[tbl_Sales] Sales
  LEFT JOIN 
      @TestTableType TestTableType ON Sales.BrokerNPN = TestTableType.BrokerNPN
END 

And in C# I am trying to consume my stored procedure with a TVP like this:

public void CheckSP(List<string> ParamData)
{
    using (var connection = new ConnectionProvider("DbName").GetOpenConnection())
    {
        var dt = new DataTable("dbo.TestTableType");
        dt.Columns.Add("NPN", typeof(string));
        dt.Rows.Add("12345");

        // First attempt
        var result = connection.Query<CheckData>("usp_Check", new { BrokerNPN = ParamData }, CommandType.StoredProcedure).ToList();

        // Second attempt
        var result = connection.Query<CheckData>("usp_Check", new { BrokerNPN = dt}, CommandType.StoredProcedure).ToList();
    }
}

But I am not able to pass the TVP to the stored procedure.

For the first attempt, I am getting this error

Procedure or function CheckBrokerNPNExist has too many arguments specified

And for the second attempt, I cannot use pass DataTable directly.

1
0
11/3/2016 7:12:53 AM

Popular Answer

I think you're just using a wrong name for the parameter...

See your stored procedure definition:

CREATE PROCEDURE dbo.usp_Check
    @TestTableType [dbo].[TestTableType] READONLY

Your parameter is called @TestTableType - yet when you call this stored procedure from C#, you're using a different name:

var result = connection
                .Query<CheckData>("usp_Check", 
                                  new { BrokerNPN = ParamData }, 
                                  CommandType.StoredProcedure).ToList();

I would try to use the same name:

var result = connection
                .Query<CheckData>("usp_Check", 
                                  new { TestTableType = ParamData }, 
                                  CommandType.StoredProcedure).ToList();
0
11/3/2016 7:14:42 AM


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