Geek asks and answers

Call SQL stored procedure using Dapper

I have an 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 the 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.

+3
source share
1 answer

few usage errors:

  • pass DbType.Int32/ DbType.Stringas value, not data type
  • If you don't pass a string value, it needs a hint for the length
  • you should use Execute, not QueryMultiplehere
  • using return Executeto indicate the return value of the stored procedure (it's just not the way ADO.NET works; the return value theoretically affects strings, but usually it's 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");
+1
source

More articles:


All Articles