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

问题描述

我有一个存储过程返回一组多个结果（两个表）。我调用这样的存储过程：

  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（）; 
  

我的RefererStatisticResult包含两个List<>属性，对于结果集，但列表在呼叫。如何处理结果集？

解决方案

cp> 支持实现多个结果集。但是，通过下拉到 ObjectContext 并使用 Translate 方法将结果从一个 DbDataReader 转换为域模型中的实体。

这是一些示例代码。这假设你的 ReferrerStatisticResult 只是一个名为 Set1 和 Set2 。显然可以根据您的实际域模型进行调整。

  //为结果集创建容器
 var result = new RefererStatisticResult （）; 
 
 using（var myContext = new MyContext（））
 {
 //从上下文创建命令，以执行
 // GetReferrer` proc 
 var command = myContext.Database.Connection.CreateCommand（）; 
 command.CommandType = System.Data.CommandType.StoredProcedure; 
 command.CommandText =[dbo]。[GetReferrer]; 
 //添加命令参数
 //（未显示）
 
 try 
 {
 myContext.Connection.Open（）; 
 var reader = command.ExecuteReader（）; 
 
 //下拉到包装的ObjectContext以访问
 //Translate方法
 var objectContext =（（IObjectContextAdapter）myContext）.ObjectContext; 
 
 //从第一个结果集读取Entity1 
 result.Set1 = objectContext.Translate< Entity1>（reader，Set1，MergeOptions.AppendOnly）; 
 
 //从第二个结果集读取Entity2 
 reader.NextResult（）; 
 result.Set2 = objectContext.Translate< Entity2>（reader，Set2，MergeOptions.AppendOnly）; 
} 
 finally 
 {
 myContext.Database.Connection.Close（）; 
} 
} 
  

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 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?

解决方案

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.

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屋！