Is there a way to call a stored procedure with Dapper?

.net dapper orm stored-procedures

Question

I am very impressed with the results of Dapper Micro ORM for stackoverflow.com. I am considering it for my new project and but I have one concern about that some times my project requires to have Stored Procedure and I have search a lot on web but not found anything with stored procedure. So is there any way to have Dapper work with a stored procedure?

Please let me know if it is possible otherwise I have to extend it in my way.

Accepted Answer

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.


Popular Answer

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why