Stored procedure doesn't return any data when being called from the C# code

Question

I have a stored procedure that accepts around 8 parameters and returns a bunch of data. The stored procedure itself runs fine and it returns the data when I call it in SSMS. But when I call it from my application using Dapper ORM, I don't get any data back.

I looked up similar threads here and I've tried everything that was suggested like this one: Dapper multi-parameter stored procedure query returns nothing back from database

Here's my code:

localConnection = new SqlConnection(defaultSettings.SetConnectionString(auth.DB_Name));

var parameters = new DynamicParameters();

try
{
    parameters.Add("@param2", "val1");
    parameters.Add("@param3", "val2");
    parameters.Add("@param4", "val3");
    parameters.Add("@param5", "val4");
    parameters.Add("@param6", "val5");
    parameters.Add("@param7", "val6");
    parameters.Add("@param8", "val7");

    var orders = localConnection.Query<Order>("spGetData", parameters, commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
    log.Error("Getdata Error: " + ex.Message);
}

Here's the SQL Server stored procedure:

CREATE PROCEDURE [dbo].[spGetData]
     @param1 VARCHAR(8),         
     @param2 VARCHAR(8),        
     @param3 VARCHAR(8),         
     @param4 VARCHAR(8),        
     @param5 VARCHAR(8),         
     @param6 VARCHAR(8),         
     @param7 VARCHAR(8) 
AS        
    SET NOCOUNT ON    

    SELECT TOP 1 * 
    FROM Orders a 
    JOIN ChangeLog b ON a.id = b.id
    WHERE a.name = @param1 
      AND a.col2 = @param2  
      AND b.col1 = @param3
      AND b.col3 = @param4 
      AND b.col4 = @param5 
      AND a.col3 = @param6 
      AND b.col5 = @param7

    RETURN 0

Any idea what is going on? Or what is it that I'm doing wrong?

Answer 1

There's no problem with Dapper and multiple parameters as far as I can see. Here's a test code I created:

class Program
{
    static void Main(string[] args)
    {
        SqlConnection c = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=tempdb;Data Source=.\SQLEXPRESS");

        c.Execute(@"
CREATE PROCEDURE GetPerson(
    @N VARCHAR(10),
    @A INT,
    @S INT
)AS
BEGIN
    SELECT @N as Name, @A as Age, @S as Salary;
END;");

        //works
        var p = c.Query<Person>("GetPerson", new { A = 1, N = "John", S = 1000 }, commandType: System.Data.CommandType.StoredProcedure);

        //doesn't work, "procedure expects parameter @A which was not supplied"
        int i = 2, j = 2000; string n = "Frank";
        var q = c.Query<Person>("GetPerson", new { i, n, j }, commandType: System.Data.CommandType.StoredProcedure);

        //works
        int A = 3, S = 3000; string N = "Joe";
        var r = c.Query<Person>("GetPerson", new { S, A, N }, commandType: System.Data.CommandType.StoredProcedure);

        //works
        DynamicParameters dp = new DynamicParameters();
        dp.Add("@A", 4);
        dp.Add("@N", "Derek");
        dp.Add("@S", 4000);
        var s = c.Query<Person>("GetPerson", dp, commandType: System.Data.CommandType.StoredProcedure);

        DynamicParameters dp2 = new DynamicParameters();
        dp2.Add("A", 5);
        dp2.Add("N", "Tim");
        dp2.Add("S", 5000);
        var t = c.Query<Person>("GetPerson", dp2, commandType: System.Data.CommandType.StoredProcedure);

    }
}

class Person
{
    public int Age { get; set; }
    public string Name { get; set; }
}

All the queries that //works return a list of 1 person. The Salary column output by the query is not represented in the Person class so it just gets lost.

Key takeaways from this:

• dapper is flexible about how it accepts parameters
• dapper parameter name is important
• dapper doesn't care about @ in a parameter name
• query columns that have no matching property in the strong class are not represented

And as you identified it's the last one that was critical in your case; your query wasn't outputting columns with names that matched your orders object