The Hoju Saram

Tuesday, January 23, 2007

Update of Drag and Drop, Paging,Sorting,Filtering Gridview.

kick it on

The Drag and Drop, Paging,Sorting ,Filtering Gridview is one of the most popular pages on my blog (1000+ page impressions since it was put up ).

So given the interest I have decided to update it. I have now added the ability to filter date columns as well as text/number columns. A couple of additonal screen shots are on this post (others can be seen on the original post) and the source of the new vs2005 solution is available here.

You will again need to follow the same instructions to set it up as the previous version. These instructions are:

You need to create a new empty database on your SQL server and run the SQL code file that is in the DatabaseSetup folder of the solution this will:

  • Create a customer table

  • Create a stored procedure to query the customer table as required

  • Fill in the customer table with some test data.

You will then need to grant access to those SQL elements so that you can access them from your .net solution.

The solution file contains a single aspx page that holds a standard gridview and code that does all of the logic. The data is accessed from the database via DataClass. I have created an Interface called IpagingFilterDataSource that matches what is required if you want to write your own class to do the datahandling. This will obviously be required if you are going to extend this in anyway.

int GetDataSize();
int GetDataSize(string FilterField, string FilterVal);
int GetDataSize(string FilterField, DateTime SearchDateStart, DateTime SearchDateEnd);

DataSet GetPage(int Pagesize, int CurrPage, string SortCol, string SortDirection);
DataSet GetPage(int Pagesize, int CurrPage, string SortCol, string SortDirection, string FilterField, string FilterVal);
DataSet GetPage(int Pagesize, int CurrPage, string SortCol, string SortDirection, string FilterField, DateTime SearchDateStart, DateTime SearchDateEnd);

In this solution I put together a class MyCustomSQLPageFilterDataSource which supports the IpagingFilterDataSource and retrieves data from the customers table of the test database via a stored proc called up_Customers_list.

In the page_load event in the default.aspx.cs file there is a line

MyCustomSQLPageFilterDataSource MyDataSource = new MyCustomSQLPageFilterDataSource("SQLServer", "CustomerTestDatabaseName", "Uid", "passwd");

You will need to change this to suit your setup based on the database that you created and the userid and passwd for it.

The solution provides a page with a gridview that supports the following features:

  • Click on headers to sort columns

  • Drag & Drop headers to re-order columns, changes persist over pages.

  • Right click on Column headers and filter on entered values ( Version 2 now supports filtering dates in a selected Month)

  • Page through data and page size is adjustable in the URL (Count Parameter)

  • Exporting to excel and a highly experimental mail merge via word using the “client siding” techniques I have blogged about before.

Labels: ,


Post a Comment

Links to this post:

Create a Link

<< Home