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

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");

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  lt procname gt   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  lt some stored proc gt   i    output OUTPUT  SELECT  output AS output1

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

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

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

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

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