Return Values from Dapper.net query with stored procedure

Question

I am trying to call a stored procedure using Dapper.Net and get return values.

p.Add("@INCIDENT_ID", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

var retResults = con.Execute("usp_GetIncidentID", p, commandType:CommandType.StoredProcedure);

int IncidentID = p.Get<int>("INCIDENT_ID"); 

I have tried a couple of different things with the parameter direction and using the "@INCIDENT_ID". If you step through the results, you can see that the proper return values are coming down in the retResults value, but I am not able to access the values the way it is described in the documentation as below..

Stored Procedures Dapper supports fully stored procs:

var user = cnn.Query<User>("spGetUser", new {Id = 1}, 
    commandType: CommandType.StoredProcedure).First();}}}
If you want something more fancy, you can do:

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: commandType.StoredProcedure); 

int b = p.Get<int>("@b");
int c = p.Get<int>("@c");   

Answer 1

Using the test version of Dapper, I found this works like a charm:

result = dbConnection.ExecuteScalar<int>(typeof(UCCCCException).Name + "_c",
                        obj, commandType: CommandType.StoredProcedure);

I am writing a generic for use in inserting objects of any type into a database.

The stored procedure looks like this:

ALTER PROCEDURE [dbo].[UCCCCException_c]
(
@Id int = null,
@ExceptionDate datetime = null,
@HResult int = 0,
@Message varchar(8000) = null,
@Source varchar(8000) = null,
@StackTrace varchar(8000) = null,
@Module varchar(8000) = null,
@Name varchar(8000) = null,
@created_at datetime = null,
@updated_at datetime = null,
@created_by int = null,
@updated_by int = null
,
@Creator varchar(255) = null,
@Updator varchar(255) = null
)
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Insert into dbo.uccccexceptions ( ExceptionDate, HResult, [Message], [Source], StackTrace, Module, Name)
                            values (
                                    coalesce(@ExceptionDate,getdate()),
                                    @HResult,
                                    @Message,
                                    @Source,
                                    @StackTrace,
                                    @Module,
                                    @Name
                                    )
                                    ;
select @@Identity;
go

The caveats on this are that you must specify an entry for every property in the class you are using, even if those properties do not exist in the target table, as a parameter for the stored procedure. This can be annoying if you have a number of fields which are view fields in MVC situations.

To get the return value, you just have to use Execute, and make sure your last statement is Select @@Identity in the stored procedure.

It works perfectly, and allows me to write a generic insert command in my repository like this:

        public virtual int Insert(T obj)
    {
        int result = -2;
        if (!databaseOnline)
        {
            throw new Exception(HttpStatusCode.ServiceUnavailable.ToString());
        }
        if (obj != null)
        {
            try
            {
                using (IDbConnection dbConnection = ConnectionProvider.OpenConnection())
                {
                    dbConnection.Open();
                    result = dbConnection.ExecuteScalar<int>(typeof(T).Name + "_c",
                        obj, commandType: CommandType.StoredProcedure);
                }
            }
            catch (SqlException sqlex)
            {
                Logger.LogError(sqlex, false);
                throw;
            }
            catch (Exception ex)
            {
                Logger.LogError(ex);
                throw;
            }
        }
        return result;
    }

I use the convention in my database that the name of the stored procedure is the type name followed by "_s" for select, "_c" for insert, "_d" for delete and "_u" for update.

PS: I hate using Dapper's DynamicParameters or any other device that requires you to use a different insert or update method for each of your classes in a generic repository.