GridView with an AJAX rating control, DropDownLists, JavaScript to enable/disable rows - an exercise

• Download source code - 821 KB

Introduction

While browsing for answers through various ASP.NET blogs and forums, I often come across certain topics which repeat in one way or another.

This article is an exercise which answers some common GridView questions. In particular, here I am dealing with:

1. MS Access database connectivity.
2. MS AjaxControlToolkit's Rating extender, which is put on every row allowing the user to rate each row in real time. In other words, as soon as the user rates the row (chooses the number of stars), an asynchronous (AJAX) call to the server is made and the database is updated.
3. Data bound dropdownlists in each GridView row. Each row has a supplier, which is displayed in a dropdown list in order to facilitate record modification.
4. JavaScript client-side row manipulation. In particular, on checkbox click, the Product textbox is locked/unlocked.

The project configuration and requirements

In order to achieve what I require, I needed to create an AJAX Enabled Web Application in Visual Studio 2005. I also needed the AjaxControlToolkit installed. If the AJAX Enabled Web Application option does not appear among your project types or you are missing AJAX controls, you need to go to http://asp.net/ajax/ and set yourself up. Don't forget to click on "Learn"; there you will find a great deal of tutorials on how to implement AJAX from A to Z.

If you are using Visual Studio 2008, then AJAX should be already there.

Database connectivity

In this project, I use the standard MS Access Northwind database. In order to be able to connect to it, I first put a connection string in the web.config file:

<add name="db1" 
     connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
                       Data Source=App_Data\db1.mdb"/>

Then, wherever I need to use the database in the code, I will:

1. Add "using System.Data.OleDb;" at the top of each class;
2. Reference my connection string by calling ConfigurationManager.ConnectionStrings["db1"].ToString().

The main data that I retrieve and display is stored in the Products table. I retrieve the data by doing the following:

/// <summary>
/// Selects products from the database and displays them in DataView
/// </summary>
private void ShowData()
{ 
    // I select TOP 20 only for the purpose of speedy page response in this exersize
    // OVerall, I wouldn't recommend having more than 20 rows when implementing 
    // UpdatePanel in each row - it's better to page your grid in this case
    // However in this exercise I am not going to overomplicate it with paging
    using (OleDbDataAdapter da = new OleDbDataAdapter(
        "SELECT TOP 20 Products.ProductID, Products.ProductName," + 
        " Products.UnitPrice, Products.SupplierID, " + 
        "Products.CustomerRating FROM Products", 
        new OleDbConnection(
        ConfigurationManager.ConnectionStrings["db1"].ToString())))
    {
        DataTable dt = new DataTable();
        da.SelectCommand.Connection.Open();
        da.Fill(dt);
        this.GridView1.DataSource = dt;
        this.GridView1.DataBind();
    }
// NOTE the "using" keyword. 
// It became available with .NET 2.0 and is quite handy
// especially when dealing with Database connectivity objects. 
// Now we don't need to worry about connection closing
// and disposing (disregarding whether there was an error or not);
// In short, whatever is set in and within "Using"
// will be destroyed as soon as it goes out including in case of an Exception;
// It makes for smaller nicer structured code and easier object manager;
    
}

AJAX Rating in each GridView row

Say, our user needs to rate each row. We provide the Rating control on each row. As soon as the user rates a given row, there is an AJAX call to the server and the database is updated with the new rating.

To achieve this, we create a template column in our GridView where we put an UpdatePanel which will host this Rating control:

<asp:TemplateField HeaderText="Rating">
    <ItemTemplate>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <cc1:Rating ID="Rating1" runat="server"
                  CurrentRating='<%# Bind("CustomerRating") %>'
                  StarCssClass="ratingStar"
                  WaitingStarCssClass="savedRatingStar"
                  FilledStarCssClass="filledRatingStar"
                  EmptyStarCssClass="emptyRatingStar"
                  OnChanged="Rating1_Changed"
                >
        </cc1:Rating>
            </ContentTemplate>
        </asp:UpdatePanel>
    </ItemTemplate>
</asp:TemplateField>

As you can see, the Rating control has all its properties set including CurrentRating, which is databound in order to show the rating already stored in the database.

Note: By default, the Rating control has the BehaviourId property - remove it altogether (it will add it dynamically anyways); otherwise, you won't be able to use multiple Rating controls on your page.

For the stars CSS which is required, I copied these standard ones:

<style>
    /* The following styles are for the Rating */
        .ratingStar {
        font-size: 0pt;
        width: 13px;
        height: 12px;
        margin: 0px;
        padding: 0px;
        cursor: pointer;
        display: block;
        background-repeat: no-repeat;
    }

    .filledRatingStar {
        background-image: url(Images/FilledStar.png);
    }

    .emptyRatingStar {
        background-image: url(Images/EmptyStar.png);
    }

    .savedRatingStar {
        background-image: url(Images/SavedStar.png);
    }
    
    /******************************************/    
</style>

What happens when the user clicks on the star?

First, the stars are lit and set to the new value; this is handled by the code prepared for you by the AjaxControlToolkit.

Second, Rating1_Changed kicks in and is handled on the server as follows (read comments):

/// <summary>
/// When user rates each line this one fires via AJAX call
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Rating1_Changed(object sender, 
          AjaxControlToolkit.RatingEventArgs e)
{
    // cast rating control which has initiated the call:
    AjaxControlToolkit.Rating myRating = 
              (AjaxControlToolkit.Rating)sender;
    // regular expression which will help identifying row number: 
    System.Text.RegularExpressions.Regex rexLineNo = 
      new System.Text.RegularExpressions.Regex("ctl\\d+");

    // update the record based on the recodrd id
    this.updateRating(this.ProductId(
       rexLineNo.Match(myRating.UniqueID).ToString()), e.Value);
    // the above line does the following: 
    // rexLineNo.Match(myRating.UniqueID).ToString()
    //     - finds "ctl and line number  
       
}

/// <summary>
/// Goes through the rows of the products gridview and
/// locates the lblProduct Id with the same
/// line number (ctl) part in the name;
/// </summary>
/// <param name="LineNo">A ctl thing</param>
/// <returns></returns>
private string ProductId(string LineNo)
{
    foreach( GridViewRow r in this.GridView1.Rows)
    {
        Label lblProductId = (Label)r.FindControl("lblProductID");

        // if label's uniqueID (the one created by ASP
        // and rendered as a long thing with prefixes and line numbers)
        // contains the same number (and prefix) as the one passed in
        if (lblProductId.UniqueID.Contains(LineNo))
        {
            // return label's text, which is your product ID:
            return lblProductId.Text;
        }
    }

    // you need this "dummy" return so it compiles with no warnings:
    return string.Empty;
}

/// <summary>
/// Updates given product with new rating
/// </summary>
/// <param name="ProductId">Product Id</param>
/// <param name="Rating">New rating</param>
private void updateRating(string ProductId, string Rating)
{
    // put your values into parameters:
    OleDbParameter paramRating = new OleDbParameter("@Rating", Rating);
    OleDbParameter paramProductId = 
       new OleDbParameter("@ProductId", ProductId);


    using(OleDbCommand cmd = new OleDbCommand(
        "UPDATE Products SET CustomerRating " + 
        "= @Rating WHERE Products.ProductID=@ProductId", 
        new OleDbConnection(
         ConfigurationManager.ConnectionStrings["db1"].ToString())))
    {
        cmd.Parameters.Add(paramRating);
        cmd.Parameters.Add(paramProductId);
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();

    }
}

The above code is quite self explanatory. The only thing to pay attention to is the idea of finding the ProductId value, which is done using Regular Expressions and .NET's way of naming controls when they are in a GridView. This code will retrieve your product ID, and based on it, it will update your database. You don't need to rebind your GridView in this case, because no data except the rating is changed, and the rating is set on the client side.

Data bound DropDownList in each GridView row

Only for the purposes of an exercise, we will have each product's supplier displayed in a DropDownList.

For that, we need to put the dropdowns in first. Again, we'll create a template column and put the drowdowns in there:

<asp:TemplateField HeaderText="Supplier">
    <ItemTemplate>
       <asp:DropDownList ID="ddlSupplier" runat="server" Width="180px">
        </asp:DropDownList> 
     </ItemTemplate>
</asp:TemplateField>

Now, when each GridView row is databound, we populate the supplier dropdown and set its selection to the appropriate one based on the supplier ID (read code comments):

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    // on each row which is DataRow populate Suppliers dropdown list 
    // and select the supplier according to the id in the Data:
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // find and assign the dropdown list:
        DropDownList ddlSuppliers = 
          (DropDownList)e.Row.FindControl("ddlSupplier");
        // populate and select: 
        this.BindAndSetSupplier(DataBinder.Eval(e.Row.DataItem, 
           "SupplierID").ToString(), ref ddlSuppliers);
    }
    
}

/// <summary>
///  Selects suppliers from database and select
/// supplier according to the id which is passed in
/// </summary>
/// <param name="SupplierID">Obtained from
/// the product data for each row</param>
/// <param name="Suppliers">Found in each row</param>
private void  BindAndSetSupplier( string  SupplierID , 
                                  ref DropDownList Suppliers)
{
    using (OleDbDataAdapter da = new OleDbDataAdapter(
       "SELECT Suppliers.SupplierID, " + 
       "Suppliers.CompanyName FROM Suppliers", 
       new OleDbConnection(
         ConfigurationManager.ConnectionStrings["db1"].ToString())))
    {
        DataTable dt = new DataTable();
        da.SelectCommand.Connection.Open();
        da.Fill(dt);
        Suppliers.DataValueField = "SupplierID";
        Suppliers.DataTextField = "CompanyName";
        Suppliers.DataSource = dt;
        Suppliers.DataBind();
        Suppliers.SelectedValue = SupplierID;
    }
}

That's it for DropDownLists. The key here is:

GridView1_RowDataBound

the event which allows you to do virtually anything you want to each row and its cells.

JavaScript client-side row manipulation

This one is short and simple. Many people ask, "how do I open my fields for editing on the checkbox OnClick event". Here is how we do it:

First, create a JavaScript function which will handle your checkbox OnClick event (read code comments):

function GridViewRowChecked(what)
{
    var Ctl = new RegExp("ctl\\d+")
    // this regular expression will get
    // the line nubmer with the ctl prefix 
    //which is put automatically by ASP.NET
    // for GridViews and DataGrids ;
    var LineNumber = Ctl.exec(what.name).toString();
        
            
    //get the control names to operate on:
    var txtProduct = document.getElementById( "GridView1_" + 
                     LineNumber + "_txtProduct");
    
    //set Product textbox to readonly 
    //or not accroding to the checkbox check:         
    if (what.checked)
    {
        //in order to accomodate for IE and other 
        //browsers I set the readonly attribute in two ways: 
        txtProduct.setAttribute("readonly", "none");
        txtProduct.readOnly = false;
        //it's not worth it to use Ifs to determine
        //which browser it is cuz it will only slow id down
        
    }
    else
    {
        txtProduct.setAttribute("readonly", "readonly");
        txtProduct.readOnly = true;
    }
}

And, put it into the <head> section of the page.

Note that the same Regular Expression is used here as in the previous section in order to get to the control within the same GridView row; only now, we're doing client-side stuff.

In order for the above JavaScript function to kick in when your checkbox is clicked, you need to put the handler somewhere. I chose to put it directly into the page's XHTML:

<asp:TemplateField HeaderText="Edit">
    <ItemTemplate>
        <asp:CheckBox ID="chkEdit" 
          runat="server" 
          OnClick="GridViewRowChecked(this)" />
    </ItemTemplate>
</asp:TemplateField>

disregarding the Designer's "Warning 1 Validation (ASP.NET): Attribute 'OnClick' is not a valid attribute of element 'CheckBox'." Who cares? It works, and it's better placed there. However, if you want to get fancy or more flexible, you would add this event handler in your GridView1_RowBound event.

Now, each time the user clicks on a checkbox, GridViewChecked(what) kicks in and enables/disables the textbox in the same row where the checkbox is.

That's it.

A little extra - currency format

In fact, I wasn't going to put this here, but... Anyways, here is a small example of "on-the-fly" string formatting within a GridView databound field:

<asp:BoundField DataField="UnitPrice" 
    DataFormatString="{0:C2}" HeaderText="Price"  />

Other formats are:

The value after the format character (xx, in the general example) specifies the number of significant digits or decimal places to display. For example, the formatting string "{0:F2}" displays a fixed-point number with two decimal places.

For more on string formats, refer to this MS article.

Compatibility

This project was tested with IE 7, Firefox 2.0013, and Opera 9.23.

Things to consider

1. Don't make your GridView pages too big because extensive use of JavaScript (both by AJAX and your own) will considerably slow down the client browser; implement paging.
2. Remember to take care of the BehaviourID attribute in the Rating control.
3. Don't consider this article as a practical example - it's simply an exercise which demonstrates certain uses of GridView and other controls.
4. Don't over use dropdowns in your GridView as the page becomes too heavy.
5. The "using" thing. I noticed that not many people make use of the "using" keyword which became available in .NET 2.0. In this example, it is used a number of times and it is self-explanatory. It makes less code, and provides better control over objects and what's most important in our case - database connections. Especially, it becomes crucial if you deal with legacy DBs where connections are left dangling on the server if you don't close them.