Background

Yearns ago I developed an electronic’s black-book. It is very basic and rudimentary.  It allows me to jot down names and contact details.

Here is what the screen looks like:

A couple of days ago, I wanted to find someone I met a few months ago. Unfortunately, I could not remember the person’s name, but I remembered how I met the person and noted that it is a very recent acquaintance.

And, so I know sorting by name is the way to go, but I also know that the sorted by column never quite worked.

So this is a good opportunity to clear that up.

Forward

When adding the ability to sort a DataGrid via exposing individual Column sorting, there are a few things to keep in mind.

1. Datagrid
   • Set AllowSorting to true i.e. AllowSorting=”True”
   • Set OnSortCommand to an event function i.e. OnSortCommand = “gridSortEvent”
   • On each sort-able column, be sure to set
     • HeaderText
     • DataField
     • SortExpression
2. In the code behind
   • From Database, feed in SQL Query and get unsorted RecordSet
     • Persist Query Result in DataSet
     • Capture DataSet’s default view in a variable ( objDefaultView )
     • Save default view in a Session Variable
   • Have a helper function that we called getSortDirection. It receives the sortColumn and compares that sortColumn with the previous callback’s sortColumn.
     • Determine Sort Direction
       • If previous sortColumn is empty, then set to default of ASC
       • If previous sortColumn is the same as current, then toggles sortDirection
       • If previous sortColumn is different than current, then assumes ASC
     • Preserve Sort Column and Sort Direction
       • We used viewState to preserve the passed-in sortColumn and derived sortDirection
     • Return sortDirection
   • Have the event handling function ( gridContactSortEvent )
     • It gets passed the DataGridSortCommandEventArgs argument
     • We extract the sortExpression from the argument
     • We call the helper function mentioned above ( getSortDirection )
     • We concatenate our two sort arguments (sortColumn and sortDirection) and create a new variable sortColumnAndDir
     • Retrieve the session variable gridContact and casts it as a DataView; saved as dv
     • Set the Sort property of the dataview (dv) to the variable sortColumnAndDir
     • Set the grid DataSource to the saved variable dv
     • Invoke grid’s DataBind method

 

Code

ASPX Code

   <form id='frmCover'
         method='post'
         runat='server'
         action='Cover.aspx'
         defaultFocus='txtContactSearchTag'
         defaultButton='idAnchorContactSearch'
    >

   <asp:dataGrid
      id='gridContact'
      BorderColor='black'
      BorderWidth='1'
      CellPadding='3'
      AutoGenerateColumns='false'
      runat='server'
      width='100%'
      AllowSorting='True'
      onSortCommand='gridContactSortEvent'
>

   <HeaderStyle BackColor='#9FBCE3'>
   </HeaderStyle>

   <ItemStyle BackColor='#EEF2F7'>
   </ItemStyle>

   <AlternatingItemStyle BackColor='#F5F9FD'>
   </AlternatingItemStyle>;

   <Columns>

     <asp:HyperLinkColumn>

         HeaderText='Contact'
         Text='Contact'
         DataTextField='Contact'
         DataNavigateUrlField='ContactIdentifier'
         DataNavigateUrlFormatString='ContactBrowse.aspx?ContactID={0}'
	 SortExpression='Contact'
      >       

      </asp:HyperLinkColumn>   		                        

      <asp:BoundColumn
          HeaderText='Affiliate'
          DataField='affiliate'
          SortExpression='affiliate'
       > 		

       </asp:BoundColumn>

       <asp:BoundColumn
	  HeaderText='Profession'
	  DataField='Profession'
	  SortExpression='Profession'>; 	

      </asp:BoundColumn>		                            

      <asp:BoundColumn
          HeaderText='Created On'
          DataField='dateCreatedAsDay'
          SortExpression='dateCreated'>

      </asp:BoundColumn>

      <asp:TemplateColumn
	  HeaderText='Created On'
	  SortExpression='dateCreated'
	  Visible='False'
      >

        <ItemTemplate>

         <asp:label 

             id='dateCreatedFormattedAsDay'
             runat='server'
             text='<%# Eval('dateCreated', '{0:d}') %>' 

          >

        </ItemTemplate>

      </asp:TemplateColumn>									

   </Columns>                                                   		

  </asp:DataGrid>

 </form>

Code Behind – C#

namespace addressBook
{

    using System;
    using System.Collections;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using System.Web.Security;
    using System.Text;    //StringBuilder
    using System.Configuration;    

    public class frmCover : System.Web.UI.Page
    {

       private DataView getRecordSetNames
       (
	      String strContactFilter
       )
       {

	   if (
                    (strMemberID == null)
                 || (strMemberID == string.Empty)
              )
	    {
		return null;
	    }

            OleDbDataAdapter objDbAdapter = null;
	    DataSet objDataSet;
	    DataView objDataView;
	    OleDbCommand objOleDBCommand;

	    strSQLQuery = "dbo.usp_GetContactsForMember";

	    objOleDBCommand = new OleDbCommand();
	    objOleDBCommand.Connection = objConnection;
	    objOleDBCommand.CommandType
                 = CommandType.StoredProcedure;
	    objOleDBCommand.CommandText = strSQLQuery;

            OleDbParameter objDBParametemItemMemberID =
                             new OleDbParameter();
            objDBParametemItemMemberID.OleDbType
                             = OleDbType.VarChar;
            objDBParametemItemMemberID.Size = 88;
            objDBParametemItemMemberID.Value =
                 strMemberID;
            objOleDBCommand.Parameters.Add(
                 objDBParametemItemMemberID);			

            OleDbParameter objDBParametemItemContactFP =
                            new OleDbParameter();
            objDBParametemItemContactFP.OleDbType
                           = OleDbType.VarChar;
            objDBParametemItemContactFP.Size = 88;
            objDBParametemItemContactFP.Value =
                   strContactFilter;

            objOleDBCommand.Parameters.Add(
                   objDBParametemItemContactFP);			            

            objDbAdapter = new OleDbDataAdapter();
            objDbAdapter.SelectCommand = objOleDBCommand;

            objDataSet = new DataSet("namedetails");

            objDbAdapter.Fill(objDataSet);						

	    objDataView =
               objDataSet.Tables[0].DefaultView;

	    objDataView.RowFilter = String.Empty;

	    return objDataView;

	}              

	private void loadNamesGrid(
                                      String strContactFillter
                                  )
	   {

		//ICollection objNames;
		DataView    objNames;

		//get Names
		objNames = getRecordSetNames
                       (
                          strContactFilter
                       );

		//set data source
		gridContact.DataSource = objNames;

		//data Bind
		gridContact.DataBind();

                //save Grid's default view as a
                //session var
		Session["gridContact"] = objNames;

	   }       

	private string getSortDirection(string column)
	{

	   // By default, set the sort direction to asc
	   string sortDirection = "ASC";

	   // Retrieve the last column that was sorted.
	   string sortExpression = (string)
               ViewState["gridContactSortExpression"];

           string lastDirection = null;

	   if (sortExpression != null)
	   {
	      // Check if the same column is being sorted
	      // Otherwise, the default value can be
	      if (sortExpression == column)
	      {
		lastDirection = (string)
                   ViewState["gridContactSortDirection"];

		if ((lastDirection != null) && (lastDirection == "ASC"))
		{
		   sortDirection = "DESC";
		}

	      } // if sortExpression == column

	  } // if sortExpression is not null

	  // Save new values in ViewState.
	  ViewState["gridContactSortDirection"] = sortDirection;
	  ViewState["gridContactSortExpression"] = column;

	  return sortDirection;

	} // getSortDirection

       protected void gridContactSortEvent(
                                             Object sender
					   , DataGridSortCommandEventArgs e
					   )
       { 

 	   DataView dv = (DataView)Session["gridContact"];
	   String strSortColumn = e.SortExpression;

	   String strSortDir = getSortDirection(strSortColumn);

	   String strSortColumnANDDir = strSortColumn + " " + strSortDir;

	   // The DataView provides an easy way to sort.
	   // Simply set the Sort property with
	   // the name of the field to sort by.
	   // dv.Sort = e.SortExpression;
	   dv.Sort = strSortColumnANDDir;

	   // Re-bind the data source and specify that it should be sorted
	   // by the field specified in the SortExpression property.
	   gridContact.DataSource = dv;
	   gridContact.DataBind();

 	} // gridContactSortEvent

    } // public class frmCover : System.Web.UI.Page

} //namespace

 

Post Code Changes

Our code changes bore dividend, as here is the result:

Grid Sorted by Name – Ascending

Grid Sorted by Name – Descending

 

Summary

Couple of things.  We increased session state usage by maintaining the results of the original query in a session variable.

As that data was saved in a session variable, we did not have to pass along the user’s intended sort column and direction to the database.

We needed a bit of code to determine sort direction as the DataGrid does not appear to keep that as part of it’s session state.

Please be sure to maintain database column’s datatype fidelity as much as possible. That is, keep data and formatting different.  This is achievable by exposing two data columns, or using the core column in the DB and using TemplateColumn and ItemTemplate in the ASPX code to perform formatting.

The big realization for me is that I actually need to handle the event for sorting, as it is not handled intrinsically by setting “AllowSorting“.

References

DataGrid

Microsoft Reference

• DataGrid OnSort Event
  https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.datagrid.onsortcommand(v=vs.110).aspx

 

GridView

Microsoft Reference

• GridView Sorting Event
  https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting(v=vs.110).aspx

Q/A

• ASP.Net GridView Sorting Custom Data Source
  http://stackoverflow.com/questions/12538578/asp-net-gridview-sorting-custom-datasource
• Change date Format or String length of table
  http://stackoverflow.com/questions/16236646/change-date-format-or-string-length-of-lable-itemtemplate