How to get flat record set back from stored procedure in Dapper.Net
dapper sql-server-2005 stored-procedures
Question
I've got a project running on SQL Server 2005. I have a stored procedure that accepts some input parameters, has 2 output parameters and also returns a result set from a select statement inside.
The data is not intending to be returned back as objects just a simple dto class 'Foo' which does not have a primary key.
I've tried to use Dapper.Net with the below code, but I get as far as the exception error returned
When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id
Code:
var p = new DynamicParameters();
p.Add("@Code", code);
p.Add("@MessageControlId", getMessageControlId);
p.Add("@SuccessCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@ErrorDescription", dbType: DbType.String, direction: ParameterDirection.Output, size: 255);
var result = this.db.Query(sql: "up_MessageProcessed_get", param: p, commandType: CommandType.StoredProcedure);
Should I be able to do this with Dapper.Net? Should I calling something else to get a simple resultset back?
As requested, here is a shell of the procedure, it will set the returnvalues and also return 1 resultset.
CREATE PROCEDURE [dbo].[up_MessageProcessed_get]
@Code varchar(10),
@MessageControlID varchar(22),
@SuccessCode int OUTPUT,
@ErrorDescription varchar(255) OUTPUT
AS
BEGIN
Select 2 as MessageProcessID, 38349348 as StartNum, 3874900 as EndNum, GETDATE() as StartDate, GETDATE() as EndDate
SET @SuccessCode = 0
SET @ErrorDescription = 'Completed successfully'
END
Accepted Answer
This isn't so much an "answer" as a "needs more context", but: with the code as posted, it works fine (below). I suspect the issue here is specific to something inside the stored procedure - something peculiar it is doing:
public void TestIssue17648290()
{
var p = new DynamicParameters();
int code = 1, getMessageControlId = 2;
p.Add("@Code", code);
p.Add("@MessageControlId", getMessageControlId);
p.Add("@SuccessCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@ErrorDescription", dbType: DbType.String, direction: ParameterDirection.Output, size: 255);
connection.Execute(@"CREATE PROCEDURE #up_MessageProcessed_get
@Code varchar(10),
@MessageControlID varchar(22),
@SuccessCode int OUTPUT,
@ErrorDescription varchar(255) OUTPUT
AS
BEGIN
Select 2 as MessageProcessID, 38349348 as StartNum, 3874900 as EndNum, GETDATE() as StartDate, GETDATE() as EndDate
SET @SuccessCode = 0
SET @ErrorDescription = 'Completed successfully'
END");
var result = connection.Query(sql: "#up_MessageProcessed_get", param: p, commandType: CommandType.StoredProcedure);
var row = result.Single();
((int)row.MessageProcessID).IsEqualTo(2);
((int)row.StartNum).IsEqualTo(38349348);
((int)row.EndNum).IsEqualTo(3874900);
DateTime startDate = row.StartDate, endDate = row.EndDate;
p.Get<int>("SuccessCode").IsEqualTo(0);
p.Get<string>("ErrorDescription").IsEqualTo("Completed successfully");
}
which outputs:
Running TestIssue17648290 - OK!
(which is to say: that works fine)