Dapper/SQL Server - get output from Stored Procedure containing Insert and Select statements

Question

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"

@granadaCoder yes, that's exactly what I'm trying to do. I figured that I could use Execute because it's easier for the Insert statement of my stored procedure. But getting the output is something I am not sure on how to do. — thecodeexplorer, Jul 05 '18 at 09:04

granadaCoder · Accepted Answer · 2018-07-05T13:30:19.767

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 */
);

Dapper/SQL Server - get output from Stored Procedure containing Insert and Select statements

1 Answers1