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");
You can read the value as below
var incidentId = retResults.ToList()[0].INCIDENT_ID;
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");
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.
I ran into a similar problem reading the results of a QueryMultiple. The first call to Read() returned the correct type, the second returned a DapperRow. I found that using the typed version of Read():
var lineExists = query.Read<int?>().FirstOrDefault() == 1;
solved my problem.
I've pared this down for simplicity. I'm using output parameters because that gives me full control of the datatype that is passed back. I could use this approach with any other scenario, not just inserts.
Stored Proc:
ALTER PROCEDURE User_Insert (
@EmailAddress nvarchar(255),
@Id bigint OUT
) AS
INSERT INTO User (
[EmailAddress]
) VALUES (
@EmailAddress
)
set @Id = SCOPE_IDENTITY()
Respository Code:
var sql = "Execute User_Insert @EmailAddress, @Id = @Id OUTPUT";
var _params = new DynamicParameters();
_params.Add("EmailAddress", user.EmailAddress);
_params.Add("Id", dbType: DbType.Int64, direction: ParameterDirection.Output);
using (var connection = new SqlConnection(ConnectionString)) {
connection.Open();
using (var transaction = connection.BeginTransaction()) {
var result = SqlMapper.Execute(connection, sql, param, transaction);
transaction.Commit();
}
}
var id = _params.Get<long>("Id");
The DynamicParameters solution is not clean to me. It is much better to make the stored procedure code return an integer (Select 1;) and use dapper ExecuteScalar<int>("[sp_name]", params);
