在Dapper中执行存储过程w/参数。

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.

我使用Dapper(谢谢Sam，伟大的项目)一个带有DAL的微型ORM，由于某种原因，我不能执行带有输入参数的存储过程。

In a certain service I've got something like this:

在某种服务中，我有这样的东西:

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

    ...

}

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

当存储过程的执行发生时，会抛出一个SqlException，声明我需要提供“somethingId”

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

程序或函数'spMyStoredProcedure'预期参数'@somethingId'，该参数没有提供。

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

我的DAL是基于Pencroff的github项目。

Am I missing something here?

我是不是漏掉了什么?

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)
    {
    }
}

4 个解决方案

#1

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:

您需要告诉它命令类型:确保有命令类型:commandType。在dapper调用中存储过程。否则，它只是执行文本命令:

spMyStoredProcedure

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

(在周围环境中有一些未使用的参数)。这是合法的TSQL，并尝试调用spMyStoredProcedure而不传递参数——就像将spMyStoredProcedure放入SSMS和按f5一样。

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

而且，如果你的参数是固定的，我实际上建议你使用:

var param = new { somethingId };

or even just inline it completely:

或者甚至直接嵌入它:

var result = repository.Exec<Something>(SomethingEnum.spMyStoredProcedure,
    new { somethingId }, 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)

(注意:如果您的Exec 方法只处理存储过程，那么您可以将commandType内部移动到方法中——或者您可以使它成为一个可选参数，默认为commandType . storedprocedure)。

#2

var queryParameters = new DynamicParameters();
queryParameters.Add("@parameter1", valueOfparameter1);
queryParameters.Add("@parameter2", valueOfparameter2);

await db.QueryAsync<YourReturnType>(
    "{NameOfStoredProcedure}",
    queryParameters,
    commandType: CommandType.StoredProcedure)

#3

You'll need to extend it to support outbound parameters and returning results, but it contains the portion for creating the Dapper dynamic parameters.

您需要扩展它来支持出站参数和返回结果，但是它包含了创建Dapper动态参数的部分。

internal static bool ExecuteProc(string sql, List<SqlParameter> paramList = null)
{
    try
    {
        using (SqlConnection conn = new SqlConnection (GetConnectionString()))
        {                    
           DynamicParameters dp = new DynamicParameters();
           if(paramList != null)
               foreach (SqlParameter sp in paramList)
                   dp.Add(sp.ParameterName, sp.SqlValue, sp.DbType);
           conn.Open();
           return conn.Execute(sql, dp, commandType: CommandType.StoredProcedure) > 0;
        }
    }
    catch (Exception e)
    {
        //do logging
        return false;
    }

}

#4

Since this was the top result for me, but there were no answers that deal with ExecuteNonQuery with table valued parameters, here is the code for that:

由于这是我的最高结果，但是没有与表值参数处理ExecuteNonQuery的答案，下面是代码:

var queryParameters = new DynamicParameters();
queryParameters.Add("@Param0", datatable0.AsTableValuedParameter());
queryParameters.Add("@Param1", datatable1.AsTableValuedParameter());
var result = await ExecuteStoredProc("usp_InsertUpdateTest", queryParameters);

private async Task<Result<int>> ExecuteStoredProc(string sqlStatement, DynamicParameters parameters)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                await conn.OpenAsync();
                var affectedRows = await conn.ExecuteAsync(
                    sql: sqlStatement,
                    param: parameters,
                    commandType: CommandType.StoredProcedure);
                return Result.Ok(affectedRows);
            }
        }
        catch (Exception e)
        {
            //do logging
            return Result.Fail<int>(e.Message);
        }
    }

#1