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

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

Question

For operations, we connect to the database in my project using Dapper.

In my SQL Server database, I've written a stored procedure 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 

Additionally, I'm attempting to use a TVP in C# to consume my stored method as follows:

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();
    }
}

However, I cannot send the TVP to the stored procedure.

I'm seeing this problem on my first try.

Procedure or function CheckBrokerNPNExist has too many arguments specified

Moreover, I am unable to utilize pass on the second try.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 the definition of your saved procedure:

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

The name of your parameter is@TestTableType - yet you're using a different name when you call this stored method from C#:

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

I'd attempt to use the exact same:

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