Sorting: ASP.net (C#) and SQL 2005/8

January 18, 2010 by Christoff Truter C#   ASP.NET  

The following post is a bit of a follow up post on the post I did a while ago about paging.

Equally to paging, sorting is also one of those common tasks when working with data.

Similarly to the paging post we create a table, except we're adding a datetime field in this example, which tells us when an user was added to our "system".

CREATE TABLE [dbo].[friends](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[firstname] [varchar](50) NOT NULL,
	[lastname] [varchar](50) NOT NULL,
	[created] [datetime] NOT NULL,
 CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The default value for the create date:

ALTER TABLE [dbo].[friends] ADD  CONSTRAINT [DF_friends_created]  DEFAULT (getdate()) FOR [created]

You'll also need to create some indexes for the fields you're planning to do sorting on, to avoid table scans:

CREATE NONCLUSTERED INDEX [ix_friends] ON [dbo].[friends] 
(
	[firstname] ASC,
	[lastname] ASC,
	[created] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Add some data:

INSERT INTO friends(firstname, lastname, created) VALUES ('Jurgens', 'Truter', getdate() - 10)
INSERT INTO friends(firstname, lastname, created) VALUES ('Gerhardt', 'Stander', getdate() - 50)
INSERT INTO friends(firstname, lastname, created) VALUES ('Pam', 'Nizar', getdate() - 120)
INSERT INTO friends(firstname, lastname, created) VALUES ('Alexander', 'Mehlhorn', getdate() - 4)
INSERT INTO friends(firstname, lastname, created) VALUES ('Roland', 'Cooper', getdate() - 10)
INSERT INTO friends(firstname, lastname, created) VALUES ('Loren', 'Stevens', getdate() -2)
INSERT INTO friends(firstname, lastname, created) VALUES ('Edward', 'Anderson', getdate() - 5)
INSERT INTO friends(firstname, lastname, created) VALUES ('Wayne', 'Kleynhans', getdate() - 13)

The stored procedure we're going to use to assist us in sorting, will look something like this:

CREATE PROCEDURE [dbo].[sortFriends]
 	@sort AS VARCHAR(20)
AS
BEGIN
	SELECT id, firstname, lastname, created
	FROM friends
	ORDER BY
	CASE @sort
		WHEN 'firstname' THEN firstname
		WHEN 'lastname' THEN lastname
		ELSE NULL
	END ASC,
	CASE @sort
		WHEN 'created' THEN created
		ELSE NULL
	END ASC,
	CASE @sort
		WHEN 'firstname DESC' THEN firstname
		WHEN 'lastname DESC' THEN lastname
		ELSE NULL
	END DESC,
	CASE @sort
		WHEN 'created DESC' THEN created
		ELSE NULL
	END DESC
END

Notice the case statements in the order by clause, I seperated the created field from the other (firstname, lastname) fields - if we process these fields in the same case statement, we'll need to convert the datetime field to a varchar - which I am avoiding for performance reasons.

Also notice the "ELSE NULL", SQL will ignore these order conditions, if our case condition isn't met.

Moving on to the ASP.net side of things..

In the following snippet we create a gridview control, notice the sortexpression attributes, those are the expressions we're going to send to our stored procedure. Note: the control will automatically concatenate our descending command if needed, eg firstname DESC.

<asp:GridView ID="gvFriends" runat="server" DataSourceID="obsFriends" AllowSorting="true"
	AutoGenerateColumns="false" OnRowCreated="gvFriends_RowCreated">
	<Columns>
		<asp:BoundField DataField="firstname" HeaderText="Firstname" SortExpression="firstname" />
		<asp:BoundField DataField="lastname" HeaderText="Lastname" SortExpression="lastname" />
		<asp:BoundField DataField="created" HeaderText="Created" SortExpression="created"
			DataFormatString="{0:d}" />
	</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="obsFriends" runat="server" SelectMethod="sortFriends" TypeName="friends"
	SortParameterName="sort"></asp:ObjectDataSource>

This is what our select method is going to look like:

[DataObject()]
public class friends
{
    [DataObjectMethod(DataObjectMethodType.Select)]
    public static DataTable sortFriends(String sort)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("sortFriends", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@sort", sort);
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

When an user clicks on a field in the header of the gridview, it will send the appropriate command to the stored procedure and sort our results.

Unfortunately M$ didn't provide functionality which displays which field and which direction (descending/ascending) are currently being sorted.

Luckily this is functionality we can add ourselves, we attach the following code to the rowcreated event of the gridview.

protected void gvFriends_RowCreated(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.Header)
    {
        LinkButton linkButton = (from headerCells in e.Row.Cells.OfType<DataControlFieldHeaderCell>()
                                 let lb = (headerCells.HasControls()) ? headerCells.Controls[0] as LinkButton : null
                                 where lb != null
                                     && ((GridView)sender).SortExpression == lb.CommandArgument
                                 select lb).SingleOrDefault();
        if (linkButton != null)
        {
            Image image = new Image();
            image.ImageUrl = (((GridView)sender).SortDirection == SortDirection.Ascending) ? "~/images/asc.gif" : "~/images/desc.gif";
            linkButton.Controls.Add(new LiteralControl(String.Concat(linkButton.Text, "&nbsp;")));
            linkButton.Controls.Add(image);
        }
    }
}

You will notice I wrote a LINQ query to retrieve the linkbutton(s) currently "responsible" for sorting - alternatively one can simply retrieve them using a foreach statement, but I felt lucky ;)

Once we find them, we add the proper picture to these controls, which gives us a visible representation of which columns are currently being sorted.