linq存储过程返回多条结果集

曾经被linq存储过程返回多个结果所困扰,今晚加班,问题解决,分享一下思路:

linq默认生成的代码是ISingleResult的,也就是只能返回一条结果集,我们先动手脚,将其改成IMultipleResults 的.实体类根据不同情况更改.

更改前:

  [Function(Name="dbo.MeterTaskStat")]
  public ISingleResult<XXXX> MeterTaskStat([Parameter(Name="MeterTaskType", DbType="Int")] System.Nullable<int> meterTaskType, [Parameter(Name="StartDate", DbType="DateTime")] System.Nullable<System.DateTime> startDate, [Parameter(Name="EndDate", DbType="DateTime")] System.Nullable<System.DateTime> endDate)
  {
   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), meterTaskType, startDate, endDate);
   return ((ISingleResult<XXXXXX>)(result.ReturnValue));
  }

更改后:

  [Function(Name="dbo.MeterTaskStat")]
        [ResultType(typeof(TaskStatData))]
        public IMultipleResults MeterTaskStat([Parameter(Name = "MeterTaskType", DbType = "Int")] System.Nullable<int> meterTaskType, [Parameter(Name = "StartDate", DbType = "DateTime")] System.Nullable<System.DateTime> startDate, [Parameter(Name = "EndDate", DbType = "DateTime")] System.Nullable<System.DateTime> endDate)
  {
   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), meterTaskType, startDate, endDate);
            return ((IMultipleResults)(result.ReturnValue));
  }

注意到多一条: [ResultType(typeof(TaskStatData))] 的记录吧,简单介绍一下,必须得为存储过程的结果返回一个实体类型,而TaskStatData就是自己定义的类,[ResultType(typeof(TaskStatData))]必须加上,加存储过程返回值.

存储过程:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ===================================================
-- Author: MaHong
-- Create date: 2008-09-11
-- Description: 根据口径统计某段时间内水表复装任务信息
-- ===================================================
ALTER PROCEDURE [dbo].[MeterTaskStat]
@MeterTaskType INT,
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN
SET NOCOUNT ON;

SELECT MeterCaliberName,SUM(Requisition) AS RequisitionCount,SUM(Approve) AS ApproveCount,
SUM(Disapprove) AS DisapproveCount,SUM(WaitWork) AS WaitWorkCount,
SUM(CompleteY) AS CompleteYCount, SUM(CompleteN) AS CompleteNCount,
SUM(Requisition+Approve+Disapprove+WaitWork+CompleteY+CompleteN) AS Subtotal
FROM (SELECT MeterCaliberName
,CASE WHEN MeterTaskStatus=0 THEN 1 ELSE 0 END Requisition
,CASE WHEN MeterTaskStatus=1 THEN 1 ELSE 0 END Approve
,CASE WHEN MeterTaskStatus=11 THEN 1 ELSE 0 END Disapprove
,CASE WHEN MeterTaskStatus=2 THEN 1 ELSE 0 END WaitWork
,CASE WHEN MeterTaskStatus=4 THEN 1 ELSE 0 END CompleteY
,CASE WHEN MeterTaskStatus=5 THEN 1 ELSE 0 END CompleteN
FROM View_MeterTaskMaintain WHERE [MeterTaskType] = @MeterTaskType AND StartDate BETWEEN @StartDate AND @EndDate) tempTable
GROUP BY MeterCaliberName
END

然后在business中间层直接调用:

    public class StatTaskControl : ControlBase
    {
        public IEnumerable<TaskStatData> GetStatInfo(TaskType type, DateTime startDate, DateTime endDate)
        {
            IMultipleResults info = Context.MeterTaskStat((int)type, startDate, endDate);
            IEnumerable<TaskStatData> data = info.GetResult<TaskStatData>();

            return data;
        }
    }

ui层获取:

        protected void StatButton_Click(object sender, EventArgs e)
        {
            DateTime startDate = DateTime.Parse(StartDate.Text);
            DateTime endDate = DateTime.Parse(EndDate.Text);
            TaskType type = TaskType.Remove;

IEnumerable<TaskStatData> info = _control.GetStatInfo(type, startDate, endDate);
List<TaskStatData> data = info.ToList();

            RemoveGridView.DataSource = data;
            RemoveGridView.DataBind();
        }

整个过程大概就是这么几步.也不是太困难!