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.
multiple usage errors:
DbType.Int32
/ DbType.String
as the value rather than the data typeExecute
, not QueryMultiple
hereExecute
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");