Is there a way to call a stored procedure with Dapper

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

User · 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

User · Answer

In the simple case you can do    var user   cnn Query lt User gt   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 lt int gt    b     int c   p Get lt int gt    c       Additionally you can use exec in a batch  but that is more clunky

User · Answer

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 lt int gt    RESULT

User · Answer

Same from above  bit more detailed  Using  Net Core  Controller  public class TestController   Controller       private string connectionString       public IDbConnection Connection               get   return new SqlConnection connectionString                public TestController                 connectionString     Data Source OCIUZWORKSPC Initial Catalog SocialStoriesDB Integrated Security True              public JsonResult GetEventCategory string q                using  IDbConnection dbConnection   Connection                        var categories   dbConnection Query lt ResultTokenInput gt   GetEventCategories   new   keyword   q        commandType  CommandType StoredProcedure  FirstOrDefault                 return Json categories                        public class ResultTokenInput               public int ID   get  set            public string name   get  set                          Stored Procedure   parent child relation    create PROCEDURE GetEventCategories  keyword as nvarchar 100  AS     BEGIN      WITH CTE Id  Name  IdHierarchy parentId  AS             SELECT          e EventCategoryID as Id  cast e Title as varchar max   as Name          cast cast e EventCategoryID as char 5   as varchar max   IdHierarchy ParentID       FROM          EventCategory e  where e Title like      keyword          -- WHERE        --  parentid    parentid        UNION ALL        SELECT          p EventCategoryID as Id  cast p Title     gt  gt     c name as varchar max   as Name          c IdHierarchy   cast p EventCategoryID as char 5   p ParentID       FROM          EventCategory p        JOIN  CTE c ON c Id   p parentid          where p Title like      keyword               SELECT               FROM        CTE     ORDER BY        IdHierarchy   References in case  using System  using System Collections Generic  using System Linq  using System Threading Tasks  using Microsoft AspNetCore Mvc  using Microsoft AspNetCore Http  using SocialStoriesCore Data  using Microsoft EntityFrameworkCore  using Dapper  using System Data  using System Data SqlClient

User · Answer

With multiple return and multi parameter  string ConnectionString   CommonFunctions GetConnectionString    using  IDbConnection conn   new SqlConnection ConnectionString         IEnumerable lt dynamic gt  results   conn Query sql   ProductSearch            param  new   CategoryID   1  SubCategoryID     PageNumber 1             commandType  CommandType StoredProcedure        single result      var reader   conn QueryMultiple  ProductSearch            param  new   CategoryID   1  SubCategoryID       PageNumber   1             commandType  CommandType StoredProcedure      multiple result      var userdetails   reader Read lt dynamic gt    ToList       instead of dynamic  you can use your objects     var salarydetails   reader Read lt dynamic gt    ToList       public static string GetConnectionString            Put the name the Sqlconnection from WebConfig       return ConfigurationManager ConnectionStrings  DefaultConnection   ConnectionString

User · Answer

public static IEnumerable lt T gt  ExecuteProcedure lt T gt  this SqlConnection connection      string storedProcedure  object parameters   null      int commandTimeout   180                 try                       if  connection State    ConnectionState Open                                connection Close                    connection Open                               if  parameters    null                                return connection Query lt T gt  storedProcedure  parameters                      commandType  CommandType StoredProcedure  commandTimeout  commandTimeout                             else                               return connection Query lt T gt  storedProcedure                      commandType  CommandType StoredProcedure  commandTimeout  commandTimeout                                   catch  Exception ex                        connection Close                throw ex                    finally                       connection Close                        var data   db Connect ExecuteProcedure lt PictureModel gt   GetPagePicturesById       new               PageId   pageId          LangId   languageId          PictureTypeId   pictureTypeId        ToList

[.net] Is there a way to call a stored procedure with Dapper?

Examples related to .net

Examples related to stored-procedures

Examples related to orm

Examples related to dapper