Dapper:帮助我运行具有多个用户定义表类型的存储过程

本文关键字：定义 用户 类型 存储过程 帮助 运行 Dapper | 更新日期: 2023-09-27 18:02:47

我有一个有3个输入参数的存储过程。

... PROCEDURE [dbo].[gama_SearchLibraryDocuments]
@Keyword nvarchar(160), 
@CategoryIds [dbo].[IntList] READONLY, 
@MarketIds [dbo].[IntList] READONLY ...

其中IntList是用户定义的表类型。

CREATE TYPE [dbo].[IntList]
AS TABLE ([Item] int NULL);

我的目标是用dapper调用这个存储过程。

我发现了一些关于用dapper传递用户定义类型的例子。其中之一就是在Dapper.Microsoft.Sql的nuget包中实现的TableValuedParameter类。

var list = conn.Query<int>("someSP", new
{
Keyword = (string)null,
CategoryIds = new TableValuedParameter<int>("@CategoryIds", "IntList", new List<int> { }),
MarketIds = new TableValuedParameter<int>("@MarketIds", "IntList", new List<int> { 541 })
}, commandType: CommandType.StoredProcedure).ToList();
上面写的代码抛出 
An exception of type 'System.NotSupportedException' occurred in Dapper.dll but was not handled in user code
Additional information: The member CategoryIds of type Dapper.Microsoft.Sql.TableValuedParameter`1[System.Int32] cannot be used as a parameter value

我用一个用户定义的表类型测试了我的存储过程，它工作得很好。

conn.Query<int>("someSP", new TableValuedParameter<int>("@MarketIds", "IntList", new List<int> { 541 }), commandType: CommandType.StoredProcedure).ToList();

我需要帮助运行原始存储过程。谢谢你。

Dapper:帮助我运行具有多个用户定义表类型的存储过程

Dapper具有处理表值参数的扩展方法。

public static SqlMapper.ICustomQueryParameter AsTableValuedParameter(this DataTable table, string typeName = null)

你可以这样使用dapper:

var providersTable = new DataTable();
providersTable.Columns.Add("value", typeof(Int32));
foreach (var value in filterModel.Providers)
{
    providersTable.Rows.Add(value);
}
var providers = providersTable.AsTableValuedParameter("[dbo].[tblv_int_value]");
var filters =
    new
    {
        campaignId = filterModel.CampaignId,
        search = filterModel.Search,
        providers = providers,
        pageSize = requestContext.PageSize,
        skip = requestContext.Skip
    };
using (var query = currentConnection.QueryMultiple(StoredProcedureTest, filters, nhTransaction, commandType: CommandType.StoredProcedure))
{
    var countRows = query.Read<int>().FirstOrDefault();
    var temp = query.Read<CategoryModel>().ToList();
    return new Result<IEnumerable<CategoryModel>>(temp, countRows);
}

将被翻译成SQL:

declare @p3 dbo.tblv_int_value
insert into @p3 values(5)
insert into @p3 values(34)
insert into @p3 values(73)
insert into @p3 values(14)
exec [dbo].[StoredProcedureTest] @campaignId=123969,@search=NULL,@providers=@p3,@pageSize=20,@skip=0

我回答了一个类似的帖子，从dapper调用存储过程，它接受用户定义的表类型列表，我认为这也可能对你有所帮助!

我知道这有点老了，但我想无论如何我都会把它贴出来，因为我想让它更容易一些。我希望我已经用我创建的NuGet包做到了这一点，它将允许这样的代码:

public class IntList
{
     public int Item { get; set; }
}
var list1 = new List<IntList>{new IntList{ Item= 1 }, new IntList{ Item= 2}};
var list2 = new List<IntList>{new IntList{ Item= 3 }, new IntList{ Item= 4}};
var parameters = new DynamicParameters();
parameters.Add("@Keyword", "stringValue");
parameters.AddTable("@CategoryIds", "IntList", list1);
parameters.AddTable("@MarketIds", "IntList", list2);
 var result = con.Query<int>("someSP", parameters, commandType: CommandType.StoredProcedure);

NuGet包:https://www.nuget.org/packages/Dapper.ParameterExtensions/0.2.0仍处于早期阶段，因此可能无法适用于所有内容!

请阅读README，并随时在GitHub上贡献:https://github.com/RasicN/Dapper-Parameters

这是一个有点死亡的帖子，但我相信有更好的方法来做到这一点。具体来说，是一种允许插入简单和复杂类型并执行单个更新的方式，如果您有很多索引，这可能很重要。

在这个例子中，我使用一个名为ProductAdd的存储过程和一个名为ProductList的用户定义表类型添加一个具有名称、ImageUrl和年份的产品。您可以将其简化为数字或字符串列表。

DataTable productList = new DataTable();
productList.Columns.Add(new DataColumn("Name", typeof(string)));
productList.Columns.Add(new DataColumn("ImageUrl", typeof(string)));
productList.Columns.Add(new DataColumn("Year", typeof(Int32)));
foreach (var product in products)
{
    // The order of these must match the order of columns for the Type in SQL!
    productList.Rows.Add(product.Name, product.ImageUrl, product.Year);
}
using (var connection = new SqlConnection(_appSettings.ConnectionString))
{
    await connection.OpenAsync();
    var result = new List<Product>();
    try
    {
        result = await connection.QueryAsync<Product>("[dbo].[ProductAdd]",
            new { product = productList },
            commandType: CommandType.StoredProcedure
        );
    }
    catch (SqlException ex)
    {
        // Permissions and other SQL issues are easy to miss, so this helps
        throw ex;
    }
}
return result;

用户定义类型是这样定义的:

CREATE TYPE [dbo].[ProductList] AS TABLE(
    [Name] [nvarchar](100) NOT NULL,
    [ImageUrl] [nvarchar](125) NULL,
    [Year] [int] NOT NULL,
)

存储过程使用传入的List作为表。如果您对GET/SELECT使用相同的逻辑，并试图使用IN，您将使用相同的逻辑。SQL的IN实际上是一个JOIN，所以我们通过在表上使用JOIN来代替IN

来更明确地说明这一点
CREATE PROCEDURE ProductAdd
    @product ProductList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [Product] ([Name], [ImageUrl], [Year])          
    SELECT paramProduct.[Name]
        , paramProduct.[ImageUrl]
        , paramProduct.[Year]
    FROM @product AS paramProduct
        LEFT JOIN [Product] AS existingProduct
            ON existingProduct.[Name] = paramProduct.[Name] 
                AND existingProduct.[Year] = paramProduct.[Year]
    WHERE existingProduct.[Id] IS NULL
END
GO
相关文章：