I just started learning Dapper in C# but I am having difficulty in executing stored procedures with two or more SQL Statements in one transaction.
How do you get the output parameter of a Stored Procedure that contains both Insert and Select statements in C# using Dapper?
Here is my stored procedure:
ALTER PROCEDURE [dbo].[AddToFileDetailsAndGetPrimaryKey]
-- 1. declare input variables
@file_name NVARCHAR(100) = NULL,
-- 2. declare output variable
@file_details_pk UNIQUEIDENTIFIER OUTPUT
AS
-- 3. instantiate holder table
DECLARE @pk_holder TABLE
(
retrieved_pk UNIQUEIDENTIFIER
)
-- 4. insert into FileDetails
INSERT INTO dbo.FileDetails
(
file_name
)
OUTPUT INSERTED.file_details_pk INTO @pk_holder
VALUES
(
@file_name
);
-- 5. set FileDetails primary key to OUTPUT variable
SELECT @file_details_pk = retrieved_pk
FROM @pk_holder
Here is the code I'm using to execute the stored procedure:
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Configuration.GetConnectionString("TESTDB")))
{
List<FileDetails> fileList = new List<FileDetails>();
fileList.Add(new FileDetails { file_name = fileName});
Guid outputPrimaryKey;
connection.Execute("dbo.AddToFileDetailsAndGetPrimaryKey @file_name, @file_details_pk", fileList, outputPrimaryKey);
}
Is this the correct way to do it? Should I use connection.Execute or connection.Query? I am also getting an error of "cannot convert from System.Guid to System.Data.IDbTransaction in my outputPrimaryKey"
A. Dapper does not have a Query and Execute "combined" method, to my best knowledge.
B. However, since your stored procedure is a black box with input and output parameters, you can try this: (pseudo code below, not tested)
var p = new DynamicParameters();
p.Add("@file_name", "fileOne");
p.Add("@file_details_pk", dbType: DbType.Guid, direction: ParameterDirection.Output);
cnn.Execute("dbo.AddToFileDetailsAndGetPrimaryKey", p, commandType: CommandType.StoredProcedure);
Guid b = p.Get<Guid>("@file_details_pk");
From:
https://github.com/perliedman/dapper-dot-net
("stored procedures")
In general:
Dapper is built for speed.
Also. Dapper has limited functionality:
From
https://github.com/perliedman/dapper-dot-net
Dapper is a single file you can drop in to your project that will extend your IDbConnection interface.
It provides 3 helpers:
See
Comparing QUERY and EXECUTE in Dapper
and
https://github.com/perliedman/dapper-dot-net/blob/master/Dapper%20NET40/SqlMapper.cs
PS..........
You seem to have a small bug in your "output" clause..... You are pushing the file_name into the holding-table, not new value of the newly inserted PK.
-- 4. insert into FileDetails
INSERT INTO dbo.FileDetails
(
file_name
)
OUTPUT INSERTED.file_details_pk INTO @pk_holder
VALUES
(
@file_name /* << this looks wrong */
);