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.
- 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
- 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
- Determine Sort Direction
- 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
- From Database, feed in SQL Query and get unsorted RecordSet
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
Reblogged this on Dinesh Ram Kali..