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?

Answer 1

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?

Answer 2

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(parameters); 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)