Bar Graph Column in a Sorted DataGrid

Introduction

This is my first attempt at an article for CodeProject. I've used enough of other people's, so thought I better give something back. I've been developing websites for the last 6 years or so, doing everything from PHP to C#.NET. For other examples, please visit the Development section at http://www.chemlock.co.uk/. The project below is ASP.NET in C#. There was a need to display a simple bar per row in a DataGrid to quickly illustrate an amount. This is very simple, so I've set it to Beginner level. Any questions, please mail me.

Graph display in DataGrids

This morning, I had the pleasure of re-writing a consultant's code into something which worked and was open to development. For some strange reason, this guy wrote all his code in classic ASP, hard coding the database connection strings into a file and creating all the SQL on the fly. This was slow. Not only because it was Classic ASP and the SQL strings were constantly changing so the server couldn't cache the query, but because it was badly written. One page got passed two variables in the HTTP request stream, and then used those variables to pull the same two values back from the database. Pointless and time consuming. Anyway, the results where thrown into tables, and one column on all the tables was a graph to show the amount of time booked to a project per week. This was out of a maximum of 60 hours (don't know which fool is doing those hours). My friend, the highly paid consultant, had done this by making a table within a table cell with a width equal to the amount of hours. This meant that if you have booked 34 hours to a project, the graph would be 34 pixels long.

I was asked to fix some of the SQL as the results were not very relevant for one of the reports, but thought it would be a fairly quick and simple job to simply move the lot to .NET and Stored Procedures. So, here is my quick and dirty guide to creating a graph column in a DataGrid. It's all nice and basic (C# actually), certainly not rocket science.

First of all, create a nice clean WebForm. Here is the code-behind:

private void Page_Load(object sender, System.EventArgs e)
{
    if(!IsPostBack)
    {
        if(Request["EmployeeNo"] != null)
        {
            Db db = new Db();
            ArrayList parameters = new ArrayList();
            parameters.Add(new DbParameter("@EmployeeNo",DbType.String, 
                           10,"EmployeeNo",Request["EmployeeNo"].ToString()));
            Session["dataset"] = db.GetDataSet("GetEmployeeBookings",parameters);
            Session["SortOn"] = "EndDate";
            Session["SortDirection"] = " DESC";
        }
    }
    BindGrid();
}

Notice that I'm using a class called DB to get at my database and create a DataSet. Use what you like here; the main point is to end up with a DataSet of your results. I also set up initial sort parameters for my DataGrid and the initial direction they will be sorted in. Every postback gets the grid bound with data, but only the initial pass gets the data from the database. Obviously, if this data was likely to be changed as a result of any page action, I would move this database call to separate methods, but as it's a static report and I'm in a hurry, I've left it as I initially did it.

private void BindGrid()
{
    if (Session["dataset"] != null)
    {
        DataTable dt = ((DataSet)Session["dataset"]).Tables[0];
        DataView dv = dt.DefaultView;
        dv.Sort = Session["SortOn"].ToString() + Session["SortDirection"].ToString();
        this.DataGrid1.DataSource = dv;
        this.DataGrid1.DataBind();
    }

Here is the BindGrid method. It first checks that something exists in the session variable. If it does, it extracts it, creates a DataView sorted correctly, and then binds that to our DataGrid control. Notice how very lazy I've been with my DataGrid naming. Remember, this was quick and dirty.

private void DataGrid1_SortCommand(object source, 
        System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
    Session["SortOn"] = e.SortExpression.ToString();
    if (Session["SortDirection"] == null || 
        Session["SortDirection"].ToString() == " ASC")
    {
        Session["SortDirection"] = " DESC";
    }
    else
    {
        Session["SortDirection"] = " ASC";
    }
    this.DataGrid1.CurrentPageIndex = 0;
    BindGrid();
}

Here is a nice and standard sort event method. Basically, if you select a column heading that has already been selected, it will change the direction of that column. Any other column and the grid gets sorted on the new column heading.

private void DataGrid1_ItemDataBound(object sender, 
             System.Web.UI.WebControls.DataGridItemEventArgs e)
{
    if(e.Item.ItemType == ListItemType.Item || 
       e.Item.ItemType == ListItemType.AlternatingItem)
    {
        decimal hours = decimal.Parse(e.Item.Cells[3].Text);
        decimal percentage = (100m / 60m) * hours;
        e.Item.Cells[4].Text = "<div style= "WIDTH: 100%"><div " + 
                               "style= 'BACKGROUND-COLOR:RED;HEIGHT: 10px;WIDTH:"+ 
                               percentage + "%'></div></div> ";}
    }

This is where the graph is built. In my on-going quest to get shot of tables where tables shouldn't be, I've gone for a "DIV" approach. Firstly, I create a Div that fills the entire cell. Then, inside this, I create a Div that takes up a percentage of the parent Div. Please note that without the height command, the graph will not show in Firefox. So, I've got my 60 hours and then worked out a percentage of this for the width of the child Div. The code is set to apply this when each line of the DataGrid gets data bound. Each time, the hours is worked out from another column in the database (in this instance, column 3) and parsed to a decimal. The resultant HTML code is injected into cell 4. All this is set to only occur when the DataGrid item (or row) is an Item or AlternatingItem type; after, all we don't want to create a graph for the header or pager section.

So, there we have it, very quick and very dirty, but it does the trick.

As I said at the start, fairly simple; if my description has complicated things, then please let me know. Other examples are available at www.chemlock.co.uk.