Unable to convert string as output.

[maxiwang]

We find out an issue from stored procedure with DynamicParameters.Get("@outstring"). it always error exception converting data type nvarchar(max) to int.

stored procedure code:

ALTER PROC [dbo].[usp_Test]
@RESULT INT OUT,
	@OUTSTRING NVARCHAR(MAX) OUT
AS 
	SET @RESULT=1
	SET @OUTSTRING = 'HELLO WORLD!!'

c# code:

var dparams = new DynamicParameters();
dparams.Add("@RESULT", DbType.Int32, direction: ParameterDirection.Output);
dparams.Add("@OUTSTRING", DbType.String, direction: ParameterDirection.Output);
int row = conn.Execute("usp_Test", dparams, commandType:CommandType.StoredProcedure);
result = new Result() { 
 RESULT = dparams.Get<int>("@RESULT"), 
 OUTSTRING = dparams.Get<string>("@OUTSTRING") // here
};

[NickCraver]

Where is the int here for OUTSTRING? Is that on the type on the Result class?

[maxiwang]

Yes. your make that.

OUTSTRING = dparams.Get<string>("@OUTSTRING") // here

My result class code

public int? RESULT { get ; set; }
public string OUTSTRING { get; set;}

I make sure that output didn't support string and just support int only.

[NickCraver]

What is the error? Always post the error, it anyone throws an error :)

[maxiwang]

Error converting data type nvarchar to int.

[maxiwang]

I found the root causes of the problem.

didn't working:
dparams.Add("@OUTSTRING", DbType.String, direction: ParameterDirection.Output, size: 4000);

it's working.
dparams.Add("@OUTSTRING", dbType: DbType.String, direction: ParameterDirection.Output, size:4000);

[NickCraver]

Oh damn, yes that's subtle, and not crazy to hit. Adding an overload of Add() with this pattern isn't nuts, but it also makes the general use case less obvious. I'm leaning towards saying: let's not go down that road unless someone else hits this.

[NickCraver]

We haven't gotten any more reports of this - so I'm going to close it out as a one-off :)

[rshahoud]

NickCraver, I just wanted to let you know that my team came across this same issue today when trying to return a varchar output parameter. It was necessary to use the exact syntax described by maxiwang to solve the problem. We're running Dapper 1.60.6 with .Net Core 2.2.0. Thanks maxiwang for sharing the root cause!

[trevor-hackett]

@NickCraver I've also just ran into this. Had to use the exact same syntax to get it to work