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.


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

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

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<ResultTokenInput>("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 + '>>' + 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 Dapper;
using System.Data;
using System.Data.SqlClient;

With multiple return and multi parameter

string ConnectionString = CommonFunctions.GetConnectionString();
using (IDbConnection conn = new SqlConnection(ConnectionString))
{
    IEnumerable<dynamic> 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<dynamic>().ToList(); // instead of dynamic, you can use your objects
    var salarydetails = reader.Read<dynamic>().ToList();
}

public static string GetConnectionString()
{
    // Put the name the Sqlconnection from WebConfig..
    return ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
}

Comments

  1. Robert

    • 2019/6/19

    In the simple case you can do: var user = cnn.Query<User>("spGetUser", new {Id = 1}, commandType: CommandType.StoredProcedure).First();.

  2. Anakin

    • 2016/10/20

    There are two ways to execute stored procedures with Dapper: with the CommandType as Text; or as StoredProcedure. CommandType.Text. All DbCommand objects have a CommandType property. By default this is set to Text. If you want to execute a stored procedure in a SQL statement (text) you use the Execute (or Exec) statement:

  3. Chaim

    • 2015/3/29

    In the simple case you can do: var user = cnn.Query ("spGetUser", new {Id = 1}, commandType: CommandType.StoredProcedure).First(); If you want something mo.

  4. Joaquin

    • 2015/8/12

    How do you call a stored procedure with Dapper? using (var connection = new SqlConnection(CONNECTION_STRING)) { var storedProcedureName = "getAllUsers"; var results = await connection .QueryAsync<ApplicationUser> (storedProcedureName, commandType: CommandType.StoredProcedure); }

  5. Joziah

    • 2018/1/31

    Dapper Tutorial Dapper - Stored Procedure. Description. Using Stored Procedure in Dapper is very easy, you simply need to specify the command type. Execute 

  6. Augustus

    • 2021/7/13

    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.

  7. Ty

    • 2015/7/23

    During one of my latest renditions of Better Object Relational Mapping with Dapper, the question arose about using stored procedures. How do 

  8. Bobby

    • 2019/7/14

    I have been trying to call a Postgresql stored procedure using Dapper and every example I have seen has the same thing but for some reason it is not working for me. It seems like it is calling the stored procedure but it never returns any results.

  9. Adriel

    • 2017/11/11

    To execute this stored procedure and map the results to a collection of Aircraft objects, use the QueryAsync method almost exactly like we did 

  10. Ellis

    • 2016/8/11

    PDF - Download Dapper.NET for free Previous Next This modified text is an extract of the original Stack Overflow Documentation created by following contributors and released under CC BY-SA 3.0

  11. Cameron

    • 2016/4/27

    StoredProcedure); } }. PDF - Download Dapper.NET for free · Previous Next. This modified text is an extract of the original Stack Overflow Documentation 

  12. Wilson

    • 2015/1/6

    Dapper Tutorial Dapper - Stored Procedure Description. Using Stored Procedure in Dapper is very easy, you simply need to specify the command type. Execute Single. Execute a Stored Procedure a single time.

  13. Alexzander

    • 2018/12/19

    c# dapper execute stored procedure with parameters how can i replace Any for All method and vice versa linq in c# · c# prototype function 

  14. Drake

    • 2018/4/11

    Discussions on the .NET Core, .NET Framework and .NET 5 frameworks; Updated: 14 Sep 2021.

  15. Di Stefano

    • 2018/11/21

    Call SQL Server stored procedure using Dapper from C# console app - Dapper Stored Procedure. WriteLine("Press any key to exit");. Console.ReadKey();. }.

Comments are closed.

Recent Posts