使用 SqlQuery 处理来自存储过程的多个结果 [英] Handle multiple result from a stored procedure with SqlQuery

问题描述

我有一个存储过程，它返回多组结果(两个表).我这样调用存储过程:

I have a stored procedure which returns a multiple set of results (two tables). I call the stored procedure like this:

var result = context.Database.SqlQuery<RefererStatisticResult>(
"exec [dbo].[GetReferrer] @StartDate, @EndDate, @Source",
this.CreateInParam("@StartDate", SqlDbType.DateTime, startDate),
this.CreateInParam("@EndDate", SqlDbType.DateTime, endDate),
this.CreateInParam("@Source", SqlDbType.SmallInt, eventSourveVal)).ToArray();

My RefererStatisticResult 包含两个 List<> 属性，用于结果集，但调用后列表为空.如何处理结果集?SqlQuery 可以吗?

My RefererStatisticResult contains two List<> properties, for the result set, but the lists are empty after the call. How can I handle the result set? Is it possible with the SqlQuery?

推荐答案

DbContext 没有对实现多个结果集的本机支持.但是，通过下拉到 ObjectContext 并使用 Translate 方法将结果从 DbDataReader 复制到你的领域模型.

The DbContext has no native support for materialising multiple resultsets. However, it is reasonably straight forward to achieve by dropping down to the ObjectContext and using the Translate method to copy results from a DbDataReader into entities in your domain model.

这是一些示例代码.这假设您的 ReferrerStatisticResult 只是名为 Set1 和 Set2 的两个列表的容器.显然根据您的实际领域模型进行调整.

Here's some example code. This assumes your ReferrerStatisticResult is just a container for the two lists called Set1 and Set2. Obviously adjust according to your actual domain model.

// Create container ready for the resultsets
var result = new RefererStatisticResult();

using (var myContext = new MyContext())
{
    // Create command from the context in order to execute
    // the `GetReferrer` proc
    var command = myContext.Database.Connection.CreateCommand();
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.CommandText = "[dbo].[GetReferrer]";
    // add in command parameters
    // (not shown)

    try
    {
        myContext.Connection.Open();
        var reader = command.ExecuteReader();

        // Drop down to the wrapped `ObjectContext` to get access to
        // the `Translate` method
        var objectContext = ((IObjectContextAdapter)myContext).ObjectContext;

        // Read Entity1 from the first resultset
        result.Set1 = objectContext.Translate<Entity1>(reader, "Set1", MergeOptions.AppendOnly);

        // Read Entity2 from the second resultset
        reader.NextResult();
        result.Set2 = objectContext.Translate<Entity2>(reader, "Set2", MergeOptions.AppendOnly);        
    }
    finally
    {
        myContext.Database.Connection.Close();
    }
}

这篇关于使用 SqlQuery 处理来自存储过程的多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！