Montag, 8. November 2010

Ext.NET GridPanel: Paging against large tables using Linq-To-SQL with Stored Procedures

This Blog moved to http://webapps-in-action.com/

This is just a rought entry and addressed to advanced Ext.Net developers.

In this example I want to show you one possiblity of the ajax paging from the GridPanel against large tables using the performance of MS SQL Servers stored procedures, but still having the Linq-to-SQL DataContext features for OR Mapping.

So, first we need a Linq-to-SQL DataContext for our entities. Let's call it Demo_DC.
Now we need to created the entity, we want to use for binding to the Grid.

In my case, i drop a table called "MAKT" onto the designer in VS2010 (Demo_DC.dbml).

Now it's time for a suitable paging stored procedure. I name it "sp_paging".


ALTER PROCEDURE dbo.sp_paging
@PageStart INT, 
@PageLimit INT, 
@TotalCount INT OUTPUT
AS
BEGIN

-- get record count
WITH AllRecords AS ( 
SELECT * FROM MAKT WHERE SPRAS = 'D'
) SELECT @TotalCount = Count(*) From AllRecords;

-- now get the records
WITH AllRecords AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY MATNR) 
AS Row, *
FROM MAKT WHERE SPRAS = 'D'
) SELECT * FROM AllRecords 
WHERE Row between @PageStart and @PageStart + @PageLimit;

END

You can also extent this with an additional string for dyamic searches, or
even dyamic generated SQL where conditions. But for these you need to put
the select into a string an exec it within the stored procedure. I might add a sample later.

After saving on the MSSQL, do a little refresh in the VS2010 Server Explorer
and drag it also to the Linq-to-Sql Designer. Looks then like this

select the dragged stored procedure and choose the entity "MAKT" as return type.



Right. Now, its time to extent our DataContext to return multiple Results.
We create a partial class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace LinqDataContext
{
    public partial class Demo_DC
    {
        [Function(Name = "sp_paging")]
        [ResultType(typeof(int))]
        [ResultType(typeof(MAKT))]
        public IMultipleResults sp_paging(
        [Parameter(Name = "@PageStart", DbType = "int")] int PageStart,
        [Parameter(Name = "@PageLimit", DbType = "int")] int PageLimit,
        [Parameter(Name = "@TotalCount", DbType = "int")] int TotalCount
        )
        {
            IExecuteResult result = this.ExecuteMethodCall(this,
            ((MethodInfo)(MethodInfo.GetCurrentMethod())), PageStart, PageLimit, TotalCount);
            return (IMultipleResults)result.ReturnValue;
        }
    }
}
We almost there. The only thing missing is the GridPanel DataBinding, or better binding the Store of the Grid.
Therefore we use the default DirectEvent Refesh Method of a store, like this:

protected void Store_RefreshData(object sender, StoreRefreshDataEventArgs e)
{
    int pagestart = e.Start;
    int pagelimit = e.Limit;
    int totalCount = 0;

    IMultipleResults results = this.dataContext.sp_paging(pagestart, pagelimit, totalCount);
    ISingleResult<MAKT> data = (ISingleResult<MAKT>)results.GetResult<MAKT>();

    e.Total = (int)results.ReturnValue;

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

Thats it. We receive now only the a page of our total resultset from
the database within nice clean entity objects and also know the total recordcount, without extra query from our web application.

1 Kommentar: