Custom paging and clientside sorting

• Download demo project - 14.6 Kb

Introduction

I have seen a lot of samples on custom paging and client side sorting but they all seem to complicate the issues, by either trying to make them fit all situations or creating them as custom controls. Well, like DataGrids, custom controls almost never fit the layout or functionality I'm working on, so I'm always ending up writing a template and custom functionality. So why scan the net for a control that will not properly fix your problem anyway when a few lines of code can do the same. Microsoft has put a lot of effort in their controls and by using them wisely they can solve a lot of problems.

The solution will fetch only the data from the database needed for the current page in the DataGrid. The JavaScript will sort the DataGrid on the first click on the column header ASC and on second DESC. This is meant to be used as template code so you will properly have to customize it to fulfill your needs. "But that's why you are a programmer, right?".

The JavaScript part has been tested to work in IE 6, FireFox 1.0.4 and Opera 8.0. If you need further documentation or explanation don't hesitate to drop me a mail and I'll update the article.

C# Code

public class CustomPageing : System.Web.UI.Page
{
  protected System.Web.UI.WebControls.DataGrid DataGrid1;
  // used to set which column number

  // a linkbutton is in. used by javascript

  protected int colNumber = 0;
  // used to set which row to start sorting eg width

  // pager and header it will be nr 2. used by javascript

  protected int rowstart = 2;
  OdbcCommand cmd;
  OdbcDataAdapter ad;
  DataSet data = new DataSet();
  OdbcConnection con = new OdbcConnection("DRIVER={MySQL ODBC" + 
         " 3.51 Driver};SERVER=localhost;DATABASE=minannonce;");

  private void Page_Load(object sender, System.EventArgs e)
  {
    if(!IsPostBack)
    {
      // for simplicity i have kept this in pageload

      // but should be in a search method

      // set virtual count so Pager know how many pages to prepare

      // we keep it in a Session so we only have to do this once pr search

      cmd = new OdbcCommand("Select Count(*) From Advertisment",con);
      cmd.Connection.Open();
      Session["Count"]= Convert.ToInt32(cmd.ExecuteScalar());
      DataGrid1.VirtualItemCount = Convert.ToInt32(Session["Count"]);
      cmd.Connection.Close();
      fillGrid();
    }
}

public void fillGrid()
{
  // fill grid width the number of advertisments from db

  // this is from a MySql db so rewrite if you use different Db 

  cmd = new OdbcCommand("Select advertismentId, HeadLine," + 
        " Price From Advertisment LIMIT " + 
        DataGrid1.PageSize*DataGrid1.CurrentPageIndex + 
        ","+DataGrid1.PageSize*(DataGrid1.CurrentPageIndex+1)+";",con);
  ad = new OdbcDataAdapter();
  ad.SelectCommand = cmd;
  ad.Fill(data);
  DataGrid1.DataSource = data;
  DataGrid1.DataBind();
}

public void Sort(object sender, System.EventArgs e)
{
  // add javascript eventhandler to columnheader

  ((LinkButton)sender).Attributes.Add("onclick", 
              "sort('"+colNumber+"','"+rowstart+ "','" 
              +DataGrid1.ClientID+"'); return false;");
  colNumber++;
}

private void DataGrid1_PageIndexChanged(object source, 
        System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
  // pageindex changer

  DataGrid1.CurrentPageIndex = e.NewPageIndex;
  fillGrid();
}