Search code examples
.netstored-proceduresormdapper

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

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.

Solution

  • 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.