Calling SQL Stored procedure using Dapper

Question

Calling SQL Stored procedure using Dapper

c# dapper sql

Question

I have following SQL Stored Procedure

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetNextNumberTest]
@NEWNUMBER varchar(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @NEWNUMBER = 'ABC00001'
    RETURN 1
END

I am trying to read NENUMBER value using dapper, here is my code

private string GetNextNumber()
{
    var dynamicParams = new DynamicParameters();
    dynamicParams.Add("rval", DbType.Int32, direction: ParameterDirection.ReturnValue);
    dynamicParams.Add("NEWNUMBER", DbType.String, direction: ParameterDirection.Output);
    var data = db.QueryMultiple("GetNextNumberTest", dynamicParams, commandType:    CommandType.StoredProcedure);
    var result = dynamicParams.Get<string>("NEWNUMBER");
    return result;
}

But i always get the following error

An exception of type 'System.Data.SqlClient.SqlException' occurred in Dapper.dll but was    not handled in user code

Additional information: Error converting data type varchar to int.

Any help is greatly appreciated.

Nalin Wickramasinghe

View more on Stack Overflow

Accepted Answer

multiple usage errors:

passing DbType.Int32 / DbType.String as the value rather than the data type
if you aren't passing a string value, it needs a hint for the length
you should use Execute, not QueryMultiple here
using the return of Execute to mean the return value of the stored procedure (that simply isn't how ADO.NET works; the return value there is theoretically the rows affected, but it is usually a meaningless number)

So:

dynamicParams.Add("rval", dbType: DbType.Int32,
    direction: ParameterDirection.ReturnValue);
dynamicParams.Add("NEWNUMBER", dbType: DbType.String,
    direction: ParameterDirection.Output, size: 100);
db.Execute("GetNextNumberTest", dynamicParams,
    commandType: CommandType.StoredProcedure);
var rval = dynamicParams.Get<int>("rval");
var result = dynamicParams.Get<string>("NEWNUMBER");

Calling SQL Stored procedure using Dapper

Question

Accepted Answer

Performance

Expression Evaluator