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?
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?
spMyTestProcFirst() to FirstOrDefault() as First() kept returning sequence contains no elements
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)
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);
sp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!dapper... For those interested, I showed a MySQL / c# Visual Studio 2015 working example HERE. That situation was one of IN andOUTparameters. The focus naturally was on theOUT.