Return Values from Dapper.net query with stored procedure
I suspect (untested) that this is purely a mismatch in how you name the parameter; try (note the removed @
):
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");
You can read the value as below
var incidentId = retResults.ToList()[0].INCIDENT_ID;
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.