Passing Output parameters to stored procedure using dapper in c# code

Question

I have a stored procedure in this format

CREATE PROCEDURE SP_MYTESTpROC
    @VAR1 VARCHAR(10),
    @VAR2 VARCHAR(20),
    @BASEID INT ,
    @NEWID INT OUTPUT
As Begin
   INSERT INTO TABLE_NAME(username, firstname)
      select @VAR1, @VAR2 
      WHERE ID = @BASEID

   SET @NEWID = SCOPE_IDENTITY() AS INT
END

I am calling this stored procedure from C# code using dapper. My question is: how do I pass in the output parameter to the stored procedure while using dapper?

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Mar 12 '14 at 14:33
RE:sp_ in sproc names, noted as per per my previous comment on the answer provided by @Steve and this is definitely for MS SQL server. — lacoder, Mar 12 '14 at 14:54
The following is not `dapper` ... For those interested, I showed a MySQL / c# Visual Studio 2015 working example [HERE](http://stackoverflow.com/a/38706288). That situation was one of IN and `OUT` parameters. The focus naturally was on the `OUT`. — Drew, Aug 01 '16 at 19:38

Steve · Accepted Answer · 2014-03-12T15:56:44.590

122

Just searching the Test.cs file you could find this example

    public void TestProcSupport()
    {
        var p = new DynamicParameters();
        p.Add("a", 11);
        p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
        p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
        connection.Execute(@"create proc #TestProc 
                         @a int,
                             @b int output
                             as 
                             begin
                                 set @b = 999
                                 select 1111
                                 return @a
                             end");
        connection.Query<int>("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);
        p.Get<int>("c").IsEqualTo(11);
        p.Get<int>("b").IsEqualTo(999);
    }

So, I suppose that your C# code could be written as

    public void InsertData()
    {
        var p = new DynamicParameters();
        p.Add("VAR1", "John");
        p.Add("VAR2", "McEnroe");
        p.Add("BASEID", 1);
        p.Add("NEWID", dbType: DbType.Int32, direction: ParameterDirection.Output);
        connection.Query<int>("SP_MYTESTpROC", p, commandType: CommandType.StoredProcedure);
        int newID =  p.Get<int>("NEWID");
    }

As a side note, do not use SP as prefix for your stored procedure. It is reserved for system defined procedures and you could find yourself in troubles if Microsoft decides to use the same name. Albeit improbable it is a bad practice and why risk?

edited Mar 12 '14 at 15:56

answered Mar 12 '14 at 14:03

Steve

213,761
22
232
286

Hi Steve, I will try this out, it does seem reasonable and comprehensive. Yes I know about naming conventions for sprocs, what I meant to use was `spMyTestProc` – lacoder Mar 12 '14 at 14:34
1

This worked cleanly, however I had to change `First()` to `FirstOrDefault()` as `First()` kept returning `sequence contains no elements` – lacoder Mar 12 '14 at 15:51
1

Probably you could remove it (FirstOrDefault). I have forgotten to clear it away (First) when copy/pasting from the example. (And in that example there is a reason because they have a return @a not present in your sp) – Steve Mar 12 '14 at 15:56
Yes! I have just taken the 'FirstOrDefault() out and it worked. – lacoder Mar 12 '14 at 16:18
1

If TestProc were performing inserts and you were calling .Execute instead of .Query, would there be a way to batch the proc calls and get the out parameter for each call that was made in batch and return values? – user1790300 Feb 10 '15 at 07:57
I am not sure of that. Probably yes, but I suggest you to post a new question on this. (Dapper tag) so you could get the attention of the master minds of Dapper that are very active users of this site. – Steve Feb 10 '15 at 08:22

score 16 · Answer 2 · edited Oct 30 '22 at 12:21

16

Further to "ath's" suggestion: To avoid reflection, DynamicParmers.AddDynamicParams() takes an anonymous object, after which you could add the return paramter like this...

var param = new { A="a", B="b" };
var dynamicParameters = new DynamicParameters();
dynamicParameters.AddDynamicParams(param);
dynamicParameters.Add("return", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

now use the dynamicParameters object in your dapper call instead of the anonymous param object.

(You can also do this for an output parameter if preferred)

edited Oct 30 '22 at 12:21

Mohammed Sajid

4,778
2
15
20

answered Jul 24 '19 at 11:54

David Bridge

848
9
14

2

I think `AddDynamicParams(parameters)` should be `param` instead of `parameters`, but I can't edit just that as it has to be 6 characters. – Jason Goemaat Oct 01 '21 at 14:52

score 2 · Answer 3 · answered Feb 20 '19 at 13:21

If you always have an OUTPUT parameter of INTEGER type named @id (@id = @id OUTPUT), you could make an extension method like this which would allow you to use the regular Dapper syntax passing the sql string and an anonymous object:

using Dapper;
using System.Data;
using System.Data.SqlClient;

public static int ExecuteOutputParam
            (this IDbConnection conn, string sql, object args)
        {
            // Stored procedures with output parameter require
            // dynamic params. This assumes the OUTPUT parameter in the
            // SQL is an INTEGER named @id.
            var p = new DynamicParameters();
            p.Add("id", dbType: DbType.Int32, direction: ParameterDirection.Output);

            var properties = args.GetType().GetProperties();
            foreach (var prop in properties)
            {
                var key = prop.Name;
                var value = prop.GetValue(args);

                p.Add(key, value);
            }

            conn.Execute(sql, p);

            int id = p.Get<int>("id");
            return id;
        }

This uses reflection to read all properties, but if you can take that penalty, you don't have to boilerplate the DynamicParameters for every call.

For transactions make an extension method on SqlTransaction passing it to Execute like so:

transaction.Connection.Execute(sql, p, transaction);

Passing Output parameters to stored procedure using dapper in c# code

3 Answers3

Linked