Dapper存储过程的表值参数 [英] Table Value Parameter with Dapper stored procedures

问题描述

我正在尝试调用一个接受表值参数的存储过程。

I am attempting to call a stored procedure that accepts a table valued parameter.

我正在此问题，实现了自定义参数类型：

I am following guidelines on this question, implementing a custom parameter type:

internal class IntDynamicParam
{
    string name;
    IEnumerable<int> numbers;

    public IntDynamicParam(string name,IEnumerable<int> numbers)
    {
        this.name = name;
        this.numbers = numbers;
    }

    public void AddParameters(IDbCommand command)
    {
        var sqlCommand = (SqlCommand)command;
        sqlCommand.CommandType = CommandType.StoredProcedure;

        List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>();

        // Create an SqlMetaData object that describes our table type.
        Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int) };

        foreach (int n in numbers)
        {
            // Create a new record, using the metadata array above.
            Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
            rec.SetInt32(0, n);    // Set the value.
            number_list.Add(rec);      // Add it to the list.
        }

        // Add the table parameter.
        var p = sqlCommand.Parameters.Add("@" +name, SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = "int_list_type";
        p.Value = number_list;

    }
}

，并尝试将其用作

var p = new DynamicParameters();
 p.AddDynamicParams(new IntDynamicParam("@IDList", new int[] { 1000, 2000, 3000 }));
 p.Add("@StartRow", startRow);
 p.Add("@EndRow", endRow);
 p.Add("@OrderByField", orderByField.ToString());
 p.Add("@OrderBy", orderBy.ToString());
 p.Add("@TotalRows", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
 var v = cnn.Query<venue>(spName, p,
                    commandType: CommandType.StoredProcedure).ToList<IDBVenueLite>();

但是未传递参数 @IDList 。显然，AddDynamicParams并非可行之路，有人可以帮我吗？

However the parameter '@IDList' is not passed. Clearly AddDynamicParams is not the way to go, can anyone help me out?

推荐答案

从历史上看，表值参数还没有精打细算；主要是因为它们仅在 SqlConnection 上工作（dapper尝试针对任意提供程序，包括装饰的 ADO.NET提供程序-即，其中 SqlConnection 隐藏在某些包装器下面）。您可以做的是手动实现 IDynamicParameters （或仅借用现有的 DynamicParameters 类）添加此功能：

Historically table-valued-parameters haven't been a huge focus in dapper; mainly because they only work on SqlConnection (dapper tries to target arbitrary providers, including "decorated" ADO.NET providers - i.e. where a SqlConnection is hiding underneath some wrapper). What you could do is implement IDynamicParameters manually (or just borrow the existing DynamicParameters class) to add this functionality:

void SqlMapper.IDynamicParameters.AddParameters(System.Data.IDbCommand command,
                                                SqlMapper.Identity identity)
{
    ...
    // and then whatever the code is...
    ((SqlCommand)command).Parameters
        .AddWithValue(...,...).SqlDbType = System.Data.SqlDbType.Structured;
    ...
}

我也将努力制作具体的 DynamicParameters 类在这里更具多态性，因此在将来的构建中，您只需覆盖单个方法，即可检测到TVP-ish类型，然后手动添加参数。

I will also endeavor to make the concrete DynamicParameters class more polymorphic here, so that in a future build you can just override a single method, detect a TVP-ish type, and add the parameter manually.

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