Dapper using ODBC store procedure Input parm always promt @XXX was not supplied

dapper odbc

Question

I need help from All Dapper master.

I have been learning using Dapper since one month ago, but I have error when executing query using ODBC SP.

The code originally was written by someone(DapperExample) but not using ODBC, thanks to the writer I forgot your name.

My SP:

CREATE PROCEDURE SP_GET_FIND_EMPLOYEES (@EmpID INT) AS BEGIN SET NOCOUNT ON; SELECT * FROM tblEmployee WHERE EmpID = @EmpID

END GO


My Code

public class EmployeeDashBoard : IEmployeeDashBoard {

    private IDbConnection _db;
    string connStr2 = WebConfigurationManager.ConnectionStrings["DapperExample"].ConnectionString;

    public EmployeeDashBoard()
    {

    }

    public Employee Find(int id)
    {

        //type b, by sp 
        using (IDbConnection connection = new OdbcConnection(connStr2))
        {

            var p = new DynamicParameters();

                p.Add("@EmpID", id);



            Employee result = this._db.Query<Employee>("dbo.SP_GET_FIND_EMPLOYEES", new { @EmpID = id }, commandType: CommandType.StoredProcedure).Single();
            return result;

        }



    }


}

Error:

ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Procedure or function 'SP_GET_FIND_EMPLOYEES' expects parameter '@EmpID', which was not supplied.

Thanks in Advance.

Masa Sih

Accepted Answer

I Solved by my self, I'm using Sybase ODBC SP, ( God Job Sam ), now I can avoid entity framework in the feature. Here the tricks:

Solved: SP_GET_FIND_EMPLOYEES ?


        using (IDbConnection connection = new OdbcConnection(connStr2))
        {

            var p = new DynamicParameters();

            p.Add("?EmpID?", id.ToString()); 

            Employee result = this._db.Query<Employee>("dbo.SP_GET_FIND_EMPLOYEES ?", p, commandType: CommandType.StoredProcedure).Single();
            return result;
        }

Popular Answer

Your implementation for ODBC named parameters is incorrect. You encase the named parameter with question marks in your statement and create the named parameter without the question marks. The question marks are used by Dapper to parse the statement to find the names.

p.Add("EmpID", id.ToString());
Employee result = this._db.Query<Employee>("dbo.SP_GET_FIND_EMPLOYEES ?EmpID?", p, commandType: CommandType.StoredProcedure).Single();

See this answer for more information: https://stackoverflow.com/a/26484944/6490042




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why