Linq To Sql - GridView Paging - Multi record sets - IMultipleResults

 

Town Table

    CREATE TABLE [dbo].[Town](
	    [TownID] [int] IDENTITY(1,1) NOT NULL,
	    [StateProvinceID] [int] NULL,
	    [Name] [varchar](255) NOT NULL,
	    [Prv_Id_Sigla] [char](2) NULL,
	    [Com_Cod_Fisc] [varchar](4) NOT NULL,
	    [Com_Cod_Catastale] [varchar](4) NULL,
	    [BeginDate] [datetime] NULL,
	    [FinishDate] [datetime] NULL,
	    [FlagDeleted] [dbo].[Flag] NULL,
     CONSTRAINT [PK_Town_TownID] PRIMARY KEY CLUSTERED 
    (
	    [TownID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

TownPaging Store Procedure

    create PROCEDURE dbo.TownPaging
       (
	      @PageIndex INT,
              @PageSize INT
       )

    AS
    BEGIN
           SELECT TownID, [Name], StateProvinceID
           FROM (
                  SELECT TownID, [Name], StateProvinceID, ROW_NUMBER()  
                  OVER (ORDER BY TownID) AS RowNumber
                  FROM Town        
           ) AS Results
           WHERE RowNumber BETWEEN @PageIndex + 1 AND @PageIndex + @PageSize
           SELECT COUNT(*) FROM Town 
    END
    
    

Dbml (DataContext)
TownPaging store Procedure can be dragged and dropped on the right hand side of the designer. This will then add the stored procedure as a method on your Data Context.

The store procedure accepts parameters (pageIndex and pageSize), then these will be translated into parameters on the method.

The method code for TownPaging stored procedure would be:

       
    [global::System.Data.Linq.Mapping.FunctionAttribute(Name = "dbo.TownPaging")]
    public ISingleResult TownPaging([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "PageIndex", DbType = "Int")] System.Nullable pageIndex, [global::System.Data.Linq.Mapping.ParameterAttribute(Name = "PageSize", DbType = "Int")] System.Nullable pageSize)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), pageIndex, pageSize);
        return ((ISingleResult)(result.ReturnValue));
    }
    

The result is ISingleResult, which implements IEnumerable, and returned to the calling client.

Our TownPaging stored procedure returns 2 record sets:

    SELECT TownID, [Name], StateProvinceID .... from Town
    and 
    SELECT COUNT(*) FROM Town
    

IMultipleResult

Then there will be a problem with a method returning ISingleResult. This is where IMultipleResult comes in handy. We have to manually modify the dbml designer code.

     [Function(Name="dbo.TownPaging")]
    [ResultType(typeof(Town))]
    [ResultType(typeof(int))]
    
    public IMultipleResults TownPaging([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "PageIndex", DbType = "Int")] System.Nullable pageIndex, [global::System.Data.Linq.Mapping.ParameterAttribute(Name = "PageSize", DbType = "Int")] System.Nullable pageSize)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), pageIndex, pageSize);
        return ((IMultipleResults)(result.ReturnValue));
    }
        
    

Access to the IMultipleResult

By code:

            

        DataClassesDataContext dc = new DataClassesDataContext();

        IMultipleResults results = dc.TownPaging(100,10);

        IEnumerable towns = results.GetResult().ToList();

        int totrecord = results.GetResult().Single();
        string s;
        foreach (Town t in towns)
        {
             s = t.Name;

        }
        s = totrecord.ToString();
    

using System.Data.Linq; (IMultipleResults)

 

In your gridview:

And LinqDataSource1_Selecting:

    protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        DataClassesDataContext dc = new DataClassesDataContext();

        IMultipleResults results = dc.TownPaging(e.Arguments.StartRowIndex, e.Arguments.MaximumRows);

        e.Result = results.GetResult().ToList();

        e.Arguments.TotalRowCount = results.GetResult().Single();

    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }