Tuesday, March 27, 2012

GridView: sort with OnSorting

A lot of posts that I've made are about ASP.Net's GridView.  I use it all the time, its the backbone of reporting in ASP.Net.  It does a lot of things very well and easily.  One of the things it completely handles is sorting the rows of the grid by an column just by clicking on that column header.  All you have to do is set AllowSorting=true, set the SortExpression of each column to the name of the field for that column, and you get sorting....but there's one catch.  You have to be using a data source that supports automatic sorting, like SqlDataSource, otherwise you'll have to handle sorting on your own.  I have a GridView that uses a DataSet as the data source, so I had to write something myself.

It *still* isn't that hard if you just want to be able to sort Ascending anytime someone clicks the header.  But the sort functionality that GridView provides with SqlDataSource is that the first click on a header sorts Ascending, but another click on the header will sort Descending.  To duplicate that takes a little more work using some ViewState or Session variables.

Here's a solution that I came up with that I thought was pretty clean.  The basic ideas are:

  • Create your unsorted SQL statement in your Page_Load and assign it to the member variable of the class.  That way you can use it in the initial population of the grid in Page_Load and in your OnSorting handler that is called in the PostBack's 
  • Create your ORDER BY clause based on the previous ORDER BY clause, which you will save in the ViewState of the page.  You could save it in a Session variable, but ViewState is better since you only need it on this one page

Here's the code for the function to handle your grid's OnSorting event.  Just set  OnSorting="reportGrid_Sorting" in your grid



protected void reportGrid_Sorting(object sender, GridViewSortEventArgs e)
{
    // m_unsortedSql has already been created in Page_Load
    string orderByClause = e.SortExpression;
    string previousOrderByClause = "";
    if (this.ViewState["orderClause"] != null)
    {
        previousOrderByClause = this.ViewState["orderClause"].ToString();
    }
    if (previousOrderByClause == e.SortExpression + " ASC")
    {
        orderByClause += " DESC";
    }
    else
    {
        orderByClause += " ASC";
    }
    // now we have the ORDER BY clause that we want.
    // save it to the ViewState and add it to our unsorted SQL
    this.ViewState["orderClause"] = orderByClause;
    string sortedSql = m_unsortedSql + " ORDER BY " + orderByClause;

    SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
    SqlDataAdapter myCommand = new SqlDataAdapter(sortedSql, myConnection);
    DataSet ds = new DataSet();
    myCommand.Fill(ds);

    reportGrid.DataSource = ds;
    reportGrid.DataBind();
}


Monday, March 26, 2012

Google Docs: Hiding toolbar for viewers

One of my clients had a spreadsheet that they wanted to let the public view, but not edit.  I've done a lot with sharing Google Docs, so I knew that all you had to do was go to Share and specify " Anyone who has the link can view ".  The problem with that is that it still shows the menu bar and the tool bar to the viewer.  They are disabled / grayed out, but they are still there taking up space, and might be confusing to some people.  My client asked if we could hide the toolbar from those who only had permission to view.  It really seemed like Google Docs should be able to hide them for all viewers, but it took me a while to find the solution.

The solution was in "Publishing" the Google Doc.  What publishing does is create a static online non-Google Doc version of the document.  This can be a web page, a PDF, or other types of files.  By default, every time you change the Google Doc, the document will be re-published so that your published version always has the latest edits.  There are lots of options for what kind of file to publish to, but we wanted a web page, so that's what we chose.  And just as we wanted, the web page has no menu or toolbar controls on it.

To publish a Google Doc, just go to the File menu and choose "Publish to the Web".  Then you can select what type of file you want to publish to and get the link to provide to your viewers.