Alternate syntax for order by in SQL Server

The order by clause is very flexible in SQL Server. You can pass it nearly anything and it will work.

In this section I show how to sort a results based on a runtime value. This works well for a stored procedure that is used for sorting and the user interface has the ability to sort by different columns. Instead of using Dynamic SQL, creating multiple stored procedures, or have a long list of if-else and then copy and pasting the same basic code (just changing the order by column), this solution is simple and easily maintainable.

Declare @Orderby as varchar(20)
Set @Orderby = 'CREATED DESC'
SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY -- add columns to sort by here
 CASE @Orderby WHEN 'NAME DESC' THEN [NAME] END DESC,
 CASE @Orderby WHEN 'NAME ASC' THEN [NAME] END ASC,
 CASE @Orderby WHEN 'SEARCH_CODE DESC' THEN SEARCH_CODE END DESC,
 CASE @Orderby WHEN 'SEARCH_CODE ASC' THEN SEARCH_CODE END ASC,
 CASE @Orderby WHEN 'CREATED DESC' THEN CREATED END DESC,
 CASE @Orderby WHEN 'CREATED ASC' THEN CREATED END ASC

You can refer to columns by number instead of column name. This can make columns that have complex subqueries easier to reference in the order by.

SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 3 DESC, 1 ASC

Instead of column name or column number, you can use the column alias

SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY FullName ASC

Here are some variations on the previous example

SELECT [NAME] as 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY FullName ASC

SELECT [NAME] as 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 'FullName' ASC

SELECT [NAME] 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 'FullName' ASC

Override any CSS style (even inline styles) no matter where it is defined

Cascading Style Sheets (CSS) are very nice for formatting HTML. In general, I think it is a very bad idea for an ASP.NET control to emit inline styles because it prevents the developer from overriding the formatting. This is of course unless there is a corresponding property or programmatic way provided of changing it.

The ASP.NET GridView is a great example of this. It emits the following inline style for the table tag it emits.

style="border-collapse:collapse"

If I want to change that *they* provide no way to do so.

However, CSS does provide a way to that I recently found here http://home.tampabay.rr.com/bmerkey/cheatsheet.htm.

So, all I have to do is create a CSS class called something like GridViewStyle by including the following between the head tags on the .aspx page.

<style>
.GridViewStyle { border-collapse:separate ! important; }
</style>

This is the key to this entire solution. Notice the ! important; This means that border-collapse will be set to separate even if the inline style says differently. This is *very* powerful.

To apply this CSS class to the table tag that is emitted by the GridView all we have to do is set the CssClass property of the GridView to GridViewStyle.

Add TBody and THead to GridView

If you want to play nice with standard (for many reasons), you will notice that GridView does a fair job. However, it does not use tbody, thead, or tfoot tags for the table that is generated when rendered in the browser.

This technique for this entry was learned from this article. However, I found that it was really correct or functional in all cases. For example, if you click on a column i the GridView to sort or do anything else on the page to cause the GridView to do its databinding again the html is regenerated. Which means that after DataBind() is called (implicitly using ObjectDataSource or SqlDataSource) you need to make the changes again. You could technically do this in the PageLoad event, but you would also have to do it in the other places that cause the data binding to fire again. The easiest way I no to have the code in one place is to do it in the PreRender event of the GridView.

Luckily, there is a simple fix

protected void GridView1_PreRender(object sender, EventArgs e)
{

   // You only need the following 2 lines of code if you are not 
   // using an ObjectDataSource of SqlDataSource
   GridView1.DataSource = Sample.GetData();
   GridView1.DataBind();

   if (GridView1.Rows.Count > 0)
   {
      //This replaces <td> with <th> and adds the scope attribute
      GridView1.UseAccessibleHeader = true;

      //This will add the <thead> and <tbody> elements
      GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;

      //This adds the <tfoot> element. 
      //Remove if you don't have a footer row
      GridView1.FooterRow.TableSection = TableRowSection.TableFooter;
   }

}

The easiest way to wire up this event is to in the Designer, get properites on the GridView, and then click the Events icon (lightning icon) to get the list of events. Now, double-click the PreRender space. This will create a method calle something like the one above. Copy the above code and you are ready to go.

Or

If you prefer the no GUI way. Just add it to your GridView tag. That would look something like this:

<asp:GridView ID="GridView1" runat="server" 
    OnPreRender="GridView1_PreRender">
</asp:GridView>