linq存储过程转List, Linq存储过程转DataTable

    之前将ISingleResult<XXX>转化为IMultipleResults,此做法虽然能实现用存储过程返回多个结果集,但此做法是修改自动生成的dbml文件,一但开发者日后将其它表拖入dbml文件之后,微软生成代码机置又将IMultipleResults转化为ISingleResult<XXX>类型,也就是说以前的方法不合适,今又有一新方法可将存储过程返回多个结果集,也不用修改微软生成的dbml文件, 以下的方法可将结果集转化为List<XXX>对象,也可转化成DataTable类型.

存储过程:

 

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

dbml中自动生的代码:

  [Function(Name="dbo.MeterTaskStat")]
  public ISingleResult<MeterTaskStatResult> 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<MeterTaskStatResult>)(result.ReturnValue));
  }

业务层方法:

    public class StatTaskControl : ControlBase
    {
        public List<MeterTaskStatResult> GetStatInfo(TaskType type, DateTime startDate, DateTime endDate)
        {
            ISingleResult<MeterTaskStatResult> info = Context.MeterTaskStat((int)type, startDate, endDate);
                       
            return info.ToList();
        }

UI层调用:

        private List<MeterTaskStatResult> GetData(ref DateTime startDate, ref DateTime endDate, TaskType type)
        {
             List<MeterTaskStatResult> result = _control.GetStatInfo(type, startDate, endDate);
                        
            return result;
        }

 

       private void BindWeekCheck(ref DateTime startDate, ref DateTime endDate)
        {
            TaskType type = TaskType.WeekCheck;
            List<MeterTaskStatResult> data = GetData(ref startDate, ref endDate, type);

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

步骤也就是以上几步,可以返回多个结果集,即然能转化为List<XXX>对象类型,那么如果用户想将其转化为DataTable类型,也相当简单了,转化DataTable的方法比较简单,不再多说.

如果转载请注明出处.