How to get output from a stored procedure when using Dapper?

c# dapper sql sql-server

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"

1
3
7/5/2018 10:31:33 AM

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 */
);
2
7/5/2018 1:30:19 PM


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow