CODEWITHSUNDEEP
c#sql-serverdapper
spring summer
spring summer

Reputation: 121

Dapper How to Handle DBNull by DynamicParameter.Get

var param = new DynamicParameters();
param.Add("MsgId", DbType.Int32, direction: ParameterDirection.Output);

connection.Execute(MessageSelOutMessageId, param, commandType: CommandType.StoredProcedure);
count = param.Get<int>("MsgId");

By referencing Dapper, I used the code above to call a Stored Procedure with an output parameter - MsgId. It is working fine, but in some cases, there would be no value returned from the Stored Procedure and the returned output parameter value would be null. In these cases, I got this exception :

Attempting to cast a DBNull to a non nullable type! Note that out/return parameters will not have updated values until the data stream completes (after the 'foreach' for Query(..., buffered: false), or after the GridReader has been disposed for QueryMultiple)

I'd understood that we could mark the return Data Type as nullable to avoid this error by using the code below 

count = param.Get<int?>("MsgId");

But, is there any other way to check param.Get("MsgId") == null instead of using nullable data type - int?

Upvotes: 10

Views: 16952

Answers (3)

Hossein Khosrojerdi
Hossein Khosrojerdi

Reputation: 11

You know handle by add nullable int and cast to int nullable to code for bind value null :

count = (int?) param.Get<int?>("MsgId");

Upvotes: 1

Koder101
Koder101

Reputation: 892

In my case it was due to the Id parameter in stored proc was set as IN and at last, I was also setting its value to LAST_INSERT_ID() whereas in my C# (Dapper code) it was made ParameterDirection.InputOutput.

After I made my stored proc ID as INOUT, the problem got resolved.

In short what I understood was that the Id parameter behaviour (IN/OUT/INOUT) should be in sync at both the places i.e in stored proc and C#.

Upvotes: 1

spring summer
spring summer

Reputation: 121

Thanks Paulius, tried with dynamic datatype count = param.Get<dynamic>("MsgId"); and it work as what I'm looking for.

Upvotes: 2

Related Questions