Question
I've just recently begun studying Dapper in C#, but I'm having trouble running stored procedures that need two or more SQL statements in a single session.
How do you get the output parameter of a Stored Procedure that contains both Insert and Select statements in C# using Dapper?
My saved procedure is as follows:
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
The code I'm using to run the stored procedure is shown below:
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"
Accepted Answer
To the best of my understanding, Dapper does not have a Query and Execute "combined" method.
B. You may attempt the following, however, as your saved process is a black box with input and output parameters: (Untested pseudocode below)
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 processes)
All in all:
Dapper is a speed demon.
Also. Dapper's capabilities are constrained.
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
QUERY and EXECUTE Comparison in Dapper
and
https://github.com/perliedman/dapper-dot-net/blob/master/Dapper%20NET40/SqlMapper.cs
PS..........
Your output clause seems to include a little problem. Instead of the new value of the freshly added PK, you are putting the file name into the holding-table.
-- 4. insert into FileDetails
INSERT INTO dbo.FileDetails
(
file_name
)
OUTPUT INSERTED.file_details_pk INTO @pk_holder
VALUES
(
@file_name /* << this looks wrong */
);