Sorting: ASP.net (C#) and SQL 2005/8
Source code for the following post can be downloaded
here.
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 ('Julie', 'Truter', getdate() - 10)
INSERT INTO friends(firstname, lastname, created) VALUES ('Eugene', '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, " ")));
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.
Posted by - Christoff Truter
Date - 2010-01-18 15:23:14
Post comment