GridView Redux

• Download source - 18.5 Kb

Introduction

The ASP.NET 2.0 GridView is a wonderful beast that can allow you to create new database rows, edit in place, even provide DropDownLists and CheckBoxes to aid you in the entry process. It can even do data validation.

I have added the following additional functionality over and above what you get "out of the box":

• Insert new rows.
• Edit via DropDownLists.
• Validation.
• Delete confirmation.
• Checkbox input.
• Current row highlighting.
• Selective cell highlighting based on value.

None of the functionality that I will show you how to build is rocket science, and most of it is around the internet someplace (although I have never seen the "Insert new row before"). This is my humble attempt to consolidate it all in one article.

Background

I was presented with a project to convert an Access single-user application into a Web-based multi-user SQL Server application. The Access application had about 10 tables that needed to be managed through an Add/Edit/Delete interface. This table maintenance was to be done by a single individual, so concurrency issues would not be a problem. I decided that with some tweaking, the GridView could perform what needed to be done. Out of the box, the GridView will not do row inserts, nor will it provide DropDownLists where the data to be edited is really an ID representing a row in another table. This example uses the NorthWind Products table to add and update data rows. It is a step-by-step guide to producing a web page which will accomplish this task. It is long and arduous, but this is what it takes. When you have completed this exercise, you should be ready to tackle your own tables with confidence. You may want to print out the steps and keep them handy so you don't have to be constantly switching screens (unless you have two monitors ;-)

Using the code

Download the project, unzip it to somewhere on your hard disk, and then create a new web project from an existing folder. Build the project and test drive it so that you see what it does. Now, create a new ASPX file, Default2.aspx, and do it yourself manually.

Steps to create the project manually

• Create a blank project.
• Create an Images folder.
• Right click on Images.
• Choose "Add Existing Item".
• Go to the folder you unzipped the download to, and select all the .GIF files and click "Add".
• In the design view, add a DataSource.
• Configure the DataSource to select each individual field.
• Create a GridView and select the previously created DataSource.
• Click the GridView SmartTag to open up its Tasklist.
• Enable Paging, Sorting, Editing and Deleting.
• Select Edit Columns.
• In the lower right pane, the top item is CommandField.
• Click it, and the top right pane shows the attributes.
• Change Appearance > Button Type to Image.
• Set CancelButtonUrl to Images/Cancel.gif.
• Set the rest of the ButtonUrls appropriately.
• Select ProductID from the lower left pane.
• Press the red X to the right to remove it as it is not an editable field.
• Build and run the project.

You now have a working editable/delete-able table maintenance program.

Editing using DropDownLists

• Create a new SqlDataSouce (in the Toolbox under Data).
• Configure this DataSource to connect to the Suppliers table, and in the SELECT statement, choose SupplierID and CompanyName.
• Create a new SqlDataSouce (you know where it is).
• Configure this DataSource to connect the the Categories table, and in the SELECT statement, choose CategoryID and CategoryName.
• Click on the GridView SmartTag to bring up the Tasklist.
• Click "Edit Columns...".
• Highlight SupplierID (in the lower left pane), then click on the phrase "Convert this field into a template" (over to the right).
• Do the same for CategoryID.
• Click on OK.
• You should now be looking at the GridView Tasklist.
• Click Edit Templates at the bottom.
• Click the down arrow and you will see a list of templates, choose (under Column [2] - SupplierID) "EditItemTemplate".
• Do not choose "ItemTemplate".
• Select the text box and delete it.
• From the toolbox, drag a DropDownList control and place it where the textbox was.
• Click its SmartTag and click "Choose data source".
• Choose the Supplier DataSource that you set up earlier.
• Bind the display field to CompanyName, and the value to SupplierID.
• Click the GridViews SmartTag.
• Now, pick "EditItemTemplate", but this time, under Column [3] - CategoryID.
• Repeat what you have just done to connect up the CategoryID.
• Now, build and run the application.
• You will see in the SupplierID and CategoryID columns, the Supplier/Category IDs (we'll fix that shortly), but when you click Edit, they magically transform into dropdown lists (well, it's not really magic, but it is pretty impressive).
• Click Cancel (because we're not ready to do an Update yet).
• Close the application.
• Now, we're going to turn those irritating Supplier/Category IDs into more user friendly names.
• You need to modify the first DataSource you created to dig the supplier's name and the category's name out of their respective tables. You could do it the manly (stupid) way by editing the source code for the SELECT statement and doing the INNER JOINs yourself, or just click the DataSource's SmartTag and click "Configure data source".
• Click Next once, and then change the retrieval type to Custom SQL Statement.
• Click Next and make sure Select is the highlighted tab.
• Click Query Builder.
• Right click in the pane showing the Products table and choose Add table.
• Add the following tables: Suppliers, Categories, then close.
• Add CompanyName from Suppliers and CategoryName from Categories to the query result.
• You're now done with the DataSource. Now, click the GridView SmartTag. At some point, an alert box will ask you if you want to clear the work you have already done. Just say no!
• Click Edit Templates in the Tasklist.
• Choose the ItemTemplate for Column [2] - SupplierID.
• Click the SmartTag for the Label control and choose Edit Databindings.
• Select Text in the left pane and change the Bound to column to CompanyName.

  Important: Make sure that the two-way binding is still checked after you do this.

• Close the box.
• Click the SmartTag for GridView1 - Column [2] - SupplierID.
• Choose the ItemTemplate for Column [3] - CategoryID.
• Click the SmartTag for the Label control and choose Edit Databindings.
• Select Text in the left pane and change the Bound to column to CategoryName.

  Important: Make sure that the two-way binding is still checked after you do this.

• Close the box.
• Click the SmartTag for GridView1 - Column [3] - CategoryID.
• Click End Template Editing.
• Build and execute the application.

  Now, you will see that CategoryID and SupplierID numbers have been replaced by names.

• Click around and edit the various lines but don't save - this is the real live NorthWind database that other tutorials depend on. We'll get to adding next, so you can add your own rows and mangle/delete them without injuring the real data.

Follow these steps to make the GridView - Add New Records

• Click the Product DataSource SmartTag.
• Click Configure data source.
• Click Next twice.
• Make sure Select is the highlighted tab.
• Click Query Builder.
• Change the existing statement:

  SELECT Products.ProductID, Products.ProductName, Products.SupplierID, 
    Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, 
    Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, 
    Products.Discontinued, Categories.CategoryName, Suppliers.CompanyName 
    FROM Products INNER JOIN Categories ON 
    Products.CategoryID = Categories.CategoryID 
    INNER JOIN Suppliers ON 
    Products.SupplierID = Suppliers.SupplierID 
    ORDER BY Products.ProductName

  to be:

  SELECT 0 as ProductID, '' as ProductName, 0 as SupplierID, 0 as 
    CategoryID, '' as QuantityPerUnit, 0.00 as UnitPrice, 
    0 as UnitsInStock, 0 as UnitsOnOrder, 0 ReorderLevel, 
    convert(bit, 0) as Discontinued,'' as CategoryName, 
    '' as CompanyName
  
  UNION
  
  SELECT Products.ProductID, Products.ProductName, Products.SupplierID, 
    Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, 
    Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, 
    Products.Discontinued, Categories.CategoryName, Suppliers.CompanyName 
    FROM Products INNER JOIN Categories ON 
    Products.CategoryID = Categories.CategoryID INNER JOIN 
    Suppliers ON Products.SupplierID = Suppliers.SupplierID 
    ORDER BY Products.ProductName

• Insert the following JavaScript code into the <head> of the ASPX file:

  <script>
  function FixGrid(idGrid, PageIndex, EditIndex)
  {
    var Start = 1;
    if(EditIndex != 0)
    {
      if(PageIndex == 0)
      {
        Start = 2;
        // see the actual source for explanation
  
        // replace Edit image with Add image, remove Delete
  
        idGrid.firstChild.childNodes[1].childNodes[0].childNodes[0].src=
          "Images/Add.gif";
        var i = 
          idGrid.firstChild.childNodes[1].
                 childNodes[0].innerHTML.indexOf(" ");
        idGrid.firstChild.childNodes[1].childNodes[0].innerHTML = 
          idGrid.firstChild.childNodes[1].childNodes[0].innerHTML.slice(0, i);
      }
    }
    // put delete confirmations in
  
    for(var i=Start; ; i++)
    {
      try
      {
        var ctl=idGrid.firstChild.childNodes[i].childNodes[0].childNodes[2];
        if(ctl.tagName == "INPUT")
        {
          var onc = ctl.onclick.toString();
          // window.alert(onc);
  
          // uncomment this to see what the onclick actually contains
  
          if(onc.indexOf("Delete$") == -1)
           continue;    // don't want to add confirm to "update cancel"
  
          var j = onc.indexOf("__do");
          var k = onc.indexOf(")", j)+1;
          onc = "if(confirm('Are you sure') == false)" + 
                " return(false); "+onc.slice(j, k);
          ctl.onclick = onc;
          // if you don't do this then the onclick will not work.
  
          // it is probably related to how the onclick is actually
  
          // defined (see window.alert above)
  
          ctl.outerHTML = ctl.outerHTML;
        }
      }
      catch(e)
      {
        // when we land here, we have run the table rows out
  
  
        break;
      }
    }
  }
  </script>

• Put this script after the closing </form> tag:

  <script> 
    FixGrid(document.all.GridView1, 
       <%=GridView1.PageIndex.ToString()%>, 
       <%=EditIndex %>);     
  </script>

• Right click the GridView and select Properties.
• Click the Event button.
• Double click Action > RowUpdating.
• Double click Action > RowEditing.
• This will create the empty event handlers and the required event hookup code.
• Now, replace the code in the empty ASPX.CS file with:

  public partial class YOUR_CLASSNAME_HERE : System.Web.UI.Page
  {
      public int EditIndex = -1;
      protected void Page_Load(object sender, EventArgs e)
      {
  
      }
  
      protected void GridView1_RowUpdating(object sender, 
                     GridViewUpdateEventArgs e)
      {
        if (e.RowIndex > 0 || GridView1.PageIndex > 0)
          return;
          // only RowIndex 0 on Page 0 is the row we want to insert
  
        // find the DataSource for the GridView control
  
        System.Web.UI.WebControls.SqlDataSource ds = 
          (System.Web.UI.WebControls.SqlDataSource)
            this.FindControl(this.GridView1.DataSourceID);
        // Get the DataSource's connection string
  
        System.Data.SqlClient.SqlConnection conn = 
          new System.Data.SqlClient.SqlConnection(ds.ConnectionString);
        // open the connection
  
        conn.Open();
        // get the Insert command string
  
        string s = ds.InsertCommand;
  
        // create the new command
  
        System.Data.SqlClient.SqlCommand c = 
          new System.Data.SqlClient.SqlCommand(s, conn);
        System.Data.SqlClient.SqlParameter p;
        // the NewValues collection contains
  
        // the name value pairs that need to be inserted, 
  
        // make them parameters to the command
  
        foreach (System.Collections.DictionaryEntry x in e.NewValues)
        {
          p = new System.Data.SqlClient.SqlParameter("@" + x.Key, x.Value);
          c.Parameters.Add(p);
        }
        // execute the command
  
        c.ExecuteNonQuery();
        // the GridView framework will execute an update
  
        // on a row where the identity column=0
  
        // since none exists it will silently do nothing, but that is ok
  
      }
  
    protected void GridView1_RowEditing(object sender, 
                   GridViewEditEventArgs e)
    {
      EditIndex = e.NewEditIndex;
    }
  }

• Now the hack:

  The INSERT statement that is generated by VS2005 can not be executed as is. It is originally:

  InsertCommand="INSERT INTO [Products] ([ProductName], 
     [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], 
     [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
     VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, 
     @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)"

  You need to change it to:

  InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], 
     [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], 
     [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
     VALUES (@ProductName, @SupplierID, @CategoryID, 
     @QuantityPerUnit, convert(money, @UnitPrice), 
     @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)"

  You can change it manually in the source code or through the IDE.

Checkboxes

You pretty much get checkboxes for free. The GridView by default will bind a SQL column of type bit (and only bit) to a CheckBoxField (you'll get a runtime conversion exception if you don't). If you look at the SelectCommand that we modified above, I had to add convert(bit, 0) as Discontinued.

Sorting

By default, the SortExpression for a field is the same as the field. In this case, we are displaying CompanyName and CategoryName in the SupplierID and SortExpression fields, so I have set the SortExpression for SupplierID to CompanyName and the SortExpression for CategoryID to CategoryName.

Look in the code behind file, and you will see how sort direction glyphs were added (code courtesy of Dino Esposito's New Grid in Town article). This code makes use of the OnRowCreated event, so make sure you hook it up in the GridView's Property Events sheet.

Other business

Data presentation

There are two ways you can format data.

To statically format the data

• Choose Edit Column from the GridView's SmartTag.
• Highlight the column you want to format in the lower left pane. Scroll the right hand pane to the bottom.
• Expand ItemStyle and make your changes.
• In this sample program, I have set each item's VerticalAlign to top, you'll understand why in the section on validation.
• I also set the HorizontalAlign to right for the numeric columns.
• If the item you have chosen is also an ItemTemplate, you can also make some formatting changes to its constituent controls by navigating to it as described above and making your required changes to the ItemTemplate not the EditItemTemplate.

To format the data at runtime

You must create a RowDataBound event handler. To do this:

• Right click the GridView and select Properties.
• Click the lightning bolt at the top of the Properties window to access the events sheet.
• Double click in the input area to the right of RowDataBound to auto-generate an empty event handler.

I then modified the empty template to look like this:

protected void GridView1_RowDataBound(object sender, 
                                      GridViewRowEventArgs e)
{
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
    if (e.Row.RowIndex != EditIndex)
    {
      // highlight the UnitsInStock cell if it is zero

      int unitsInStock =
        Convert.ToInt32(System.Web.UI.DataBinder.Eval(e.Row.DataItem, 
        "UnitsInStock"));
      if (unitsInStock == 0)
        e.Row.Cells[7].BackColor = System.Drawing.Color.Yellow;
      // highlight the row that the mouse is over

      // save the original attribute in a custom attribute

      // so that you can restore it later

      e.Row.Attributes.Add("onmouseover", 
         "this.originalcolor=this.style.backgroundColor;" + 
         " this.style.backgroundColor='Silver';");
      e.Row.Attributes.Add("onmouseout", 
         "this.style.backgroundColor=this.originalcolor;");
    }
  }
}

The first part of the above code gets the value of the UnitsInStock field from the current row, and if the value is 0, change the cell's background to yellow. If you have seen other examples of RowDataBound functions, you may wonder why not just do Convert.ToInt32(e.Row.Cells[7].Text). Well here is the nasty secret that no-one wants to tell you about (or at least not document anywhere). Once you turn a BoundField into an ItemTemplate, you must access the original data, because at the time that RowDataBound is called, it is not yet in the cell for you to access. But if you think about it, this makes perfect sense!

At the RowDataBound time, the cell still contains the controls which make up the template, and there may be good reasons for you to still want to manipulate the constituent controls before rendering.

The second part shows you how to highlight the row that the mouse is over. The only trick here is to save the underlying background color (in the custom attribute originalcolor) before changing it so that it can be restored on the OnMouseOut event. I have seen other solutions that hard code the background color in the restore, but that technique does not handle alternating background colors.

Validation

I have added validation controls to each EditItemTemplate. Each control is validated as it is edited (i.e.: when it loses focus). If there is an error, the error message is displayed below the control. The reason why I formatted each control to be VerticalAlign = top was so that the line would look nicer when there were errors.

Styles

All elements, whether DataBound, ItemTemplates, or controls within an ItemTemplate, expose a CssClass element to which you can assign one or more classes to affect the look of the element. If you are specifying more than one class, separate them with a space. It is interesting that none of the GridView elements support inline styling via the CssStyle.Add(...) method that most other classes support. This means that you can not add individual inline style elements to a cell.

Images

If anyone has better images that I can use for this example, please send them to me or send the URLs. These ones suck.

Conclusions

I have taken you through the long and complex VS2005 process of what should be the simple task of maintaining a table. While it is a great step forward, it could have gone a lot further. Come on Visual Studio team, we need a button in the GridView's SmartTag TaskList that will automate most of the process I have just described manually!

Errata

This has been a long step-by-step article, and I am sure that there are mistakes. Please be kind. Any corrections will be dutifully recognized and the article corrected.

External links

If you have any other suggestions, please send them along.

• GridView Class (System.Web.UI.WebControls).
• Move Over DataGrid, There's a New Grid in Town!.
• Formatting numbers.
• fredrik.nsquared2.com.
• http://www.gridviewguy.com/.
• http://www.gridviewgirl.com/ (something going on? nudge nudge wink wink ;-).

SmashGrab / Redux series

I have recently started two series of articles here at CodeProject. Smash & Grab is intended as series of short articles on one specific code technique. Redux is intended as a series of longer articles which attempt to reduce a complicated topic (like GridView) into its basic constituent parts and show that once you have all the information, it isn't really that hard after all. To find the Smash&Grab articles, search for the keyword SmashGrab. To find the Redux articles, search for the keyword Redux. I welcome any contributions to either series, but please follow the guidelines when submitting articles to either.