Is there a way to call a stored procedure with Dapper?
In the simple case you can do:
var user = cnn.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).First();
If you want something more fancy, you can do:
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);
cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);
int b = p.Get<int>("@b");
int c = p.Get<int>("@c");
Additionally you can use exec in a batch, but that is more clunky.
Here is code for getting value return from Store procedure
Stored procedure:
alter proc [dbo].[UserlogincheckMVC]
@username nvarchar(max),
@password nvarchar(max)
as
begin
if exists(select Username from Adminlogin where Username =@username and Password=@password)
begin
return 1
end
else
begin
return 0
end
end
Code:
var parameters = new DynamicParameters();
string pass = EncrytDecry.Encrypt(objUL.Password);
conx.Open();
parameters.Add("@username", objUL.Username);
parameters.Add("@password", pass);
parameters.Add("@RESULT", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var RS = conx.Execute("UserlogincheckMVC", parameters, null, null, commandType: CommandType.StoredProcedure);
int result = parameters.Get<int>("@RESULT");
I think the answer depends on which features of stored procedures you need to use.
Stored procedures returning a result set can be run using Query
; stored procedures which don't return a result set can be run using Execute
- in both cases (using EXEC <procname>
) as the SQL command (plus input parameters as necessary). See the documentation for more details.
As of revision 2d128ccdc9a2 there doesn't appear to be native support for OUTPUT
parameters; you could add this, or alternatively construct a more complex Query
command which declared TSQL variables, executed the SP collecting OUTPUT
parameters into the local variables and finallyreturned them in a result set:
DECLARE @output int
EXEC <some stored proc> @i = @output OUTPUT
SELECT @output AS output1