在Dapper中执行带有参数的存储过程 [英] Execute stored procedure w/parameters in Dapper

问题描述

我正在使用 Dapper （感谢 Sam ，很棒的项目。）带有DAL的微型ORM，由于某种原因，我无法使用输入参数执行存储过程。

I'm using Dapper (thanks Sam, great project.) a micro ORM with a DAL and by some reason I'm not able to execute stored procedures with input parameters.

在示例服务中，我具有以下代码：

In a example service I've the following code:

public void GetSomething(int somethingId)
{
    IRepository<Something, SomethingEnum> repository = UnitOfWork.GetRepository<Something, SomethingEnum>();

    var param = new DynamicParameters();
    param.Add("@somethingId", dbType: DbType.Int32, value:somethingId, direction: ParameterDirection.Input);

    var result = repository.Exec<Something>(SomethingEnum.spMyStoredProcedure, param);

    ...

}

何时存储过程的执行触发了SqlException异常，提示我需要提供'somethingId'

When the execution of the stored procedure is triggered a SqlException is thrown stating that I need to provide the 'somethingId'

过程或函数'spMyStoredProcedure'期望参数'@somethingId'未提供。

Procedure or function 'spMyStoredProcedure' expects parameter '@somethingId', which was not supplied.

我的 DAL 与基于Pencroff的github项目类似。

My DAL is similar based on this github project of Pencroff.

我在这里缺少什么吗？

更新：我实际上是通过SomethingEnum传递commandType的：

Update: I am actually passing the commandType via the SomethingEnum:

 public class SomethingEnum : EnumBase<SomethingEnum, string>
 {
    public static readonly SomethingEnum spMyStoredProcedure = new SomethingEnum("spMyStoredProcedure", "[dbo].[spMyStoredProcedure]", CommandType.StoredProcedure);

    public SomethingEnum(string Name, string EnumValue, CommandType? cmdType): base(Name, EnumValue, cmdType)
    {
    }
}

推荐答案

您需要告诉它命令类型：确保有一个 commandType：dapper调用中的CommandType.StoredProcedure 。否则，它只是执行 text 命令：

You need to tell it the command type: make sure there's a commandType: CommandType.StoredProcedure in the dapper call. Otherwise, it is simply executing the text command:

spMyStoredProcedure

（在环境中有一些未使用的参数）。这是合法的TSQL，并尝试在不传递参数的情况下调用 spMyStoredProcedure 就像您放置 spMyStoredProcedure 进入SSMS，然后按 f5 。

(with some unused parameters in the ambient context). This is legal TSQL, and attempts to call spMyStoredProcedure without passing parameters - the same as if you put spMyStoredProcedure into SSMS and press f5.

此外，如果您的参数固定，我实际上建议只使用：

Also, if your parameters are fixed, I would actually suggest just using:

var param = new { somethingId };

甚至只是完全内联它：

var result = repository.Exec<Something>(SomethingEnum.spMyStoredProcedure,
    new { somethingId }, commandType: CommandType.StoredProcedure);

（注意：如果您的 Exec< T> 方法只能处理存储过程，您可以将内部的 commandType 移动到该方法中，也可以将其设置为默认为 CommandType的可选参数。 StoredProcedure ）

(note: if your Exec<T> method only ever handles stored procedures, you could move the commandType internal to the method - or you could make it an optional parameter that defaults to CommandType.StoredProcedure)

这篇关于在Dapper中执行带有参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！