.net 有没有办法用 Dapper 调用存储过程？

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

提问by Jalpesh Vadgama

I am very impressed with the results of Dapper Micro ORMfor 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?

我对 stackoverflow.com的Dapper Micro ORM的结果印象深刻。我正在考虑将它用于我的新项目，但我担心有时我的项目需要有存储过程，我在网上搜索了很多但没有找到任何存储过程。那么有没有办法让 Dapper 使用存储过程呢？

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

请让我知道是否可能，否则我必须以我的方式扩展它。

回答by Sam Saffron

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.

此外，您可以批量使用 exec ，但这更笨拙。

回答by Ed Harper

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 documentationfor more details.

返回结果集的存储过程可以使用Query; 不返回结果集的存储过程可以使用Execute- 在两种情况下（使用EXEC <procname>）作为 SQL 命令（根据需要加上输入参数）运行。有关更多详细信息，请参阅文档。

As of revision 2d128ccdc9a2there doesn't appear to be native support for OUTPUTparameters; you could add this, or alternatively construct a more complex Querycommand which declared TSQL variables, executed the SP collecting OUTPUTparameters into the local variables and finallyreturned them in a result set:

从修订版2d128ccdc9a2 开始，似乎没有对OUTPUT参数的本机支持；你可以添加这个，或者构造一个更复杂的Query命令，声明 TSQL 变量，执行 SP 将OUTPUT参数收集到局部变量中，最后在结果集中返回它们：

DECLARE @output int

EXEC <some stored proc> @i = @output OUTPUT

SELECT @output AS output1

回答by Saineshwar

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

回答by Arun Prasad E S

Same from above, bit more detailed

与上面相同，更详细一点

Using .Net Core

使用 .Net 核心

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 Microsoft.EntityFrameworkCore;
using Dapper;
using System.Data;
using System.Data.SqlClient;

回答by Arun Prasad E S

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;
}

回答by Hakan YILMAZ

public static IEnumerable<T> ExecuteProcedure<T>(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<T>(storedProcedure, parameters,
                    commandType: CommandType.StoredProcedure, commandTimeout: commandTimeout);
            }
            else
            {
                return connection.Query<T>(storedProcedure,
                    commandType: CommandType.StoredProcedure, commandTimeout: commandTimeout);
            }
        }
        catch (Exception ex)
        {
            connection.Close();
            throw ex;
        }
        finally
        {
            connection.Close();
        }

    }
}

var data = db.Connect.ExecuteProcedure<PictureModel>("GetPagePicturesById",
    new
    {
        PageId = pageId,
        LangId = languageId,
        PictureTypeId = pictureTypeId
    }).ToList();