.NET Data Secrets, Part II

Welcome to the second part of .NET Data Secrets!

I'm Karl Moore and today we'll be exploring even more secrets to make your data access applications run as smoothly as possible—especially the Web variety:

• Nine Steps to a Quick, Editable Web Grid
• Little-Known Technique for Confirming Web Deletes
• Selecting Multiple Web Form Grid Items, Hotmail-Style
• Click Anywhere and Select, with a Web Grid

If you missed the secrets from last time, you can find them here. Let's get on with all those juicy secrets!

Nine Steps to a Quick, Editable Web Grid

As demonstrated in the last article, setting up your own editable Windows DataGrid may be an absolute doddle—however, creating the equivalent grid for the Web is a little more complicated. Most articles simply show you how to display data—and conveniently manage to skip the editing, deleting, adding, and updating stages. Not here.

Here, you're going to create a template page that will allow you to display data from a table. You'll be able to add new records. Delete records. Edit existing records. Update the backend database. It'll handle most of your simple table operations. I've written all the code for you—and if there's any piece of functionality you don't want, just don't add it.

Let's get started:

1. Open the Server Explorer (View > Server Explorer). If you're connecting into an SQL Server database, expand the Servers node, locate your target machine (if not available, click the 'Connect to Server' icon and specify the machine name), and then drill down to your database. If you're connecting into another type of database, right-click on the Data Connections node, select Add Connection, and connect into your database.

2. Drag the table you want to the grid to be based upon onto your Web Form. Two components will be added—a Connection object, which connects into the database, and a DataAdapter object, which acts as the "phone line" between your Connection object and your actual set of data (your "DataSet").

   Top Tip: To preview the data coming back from your DataAdapter, right-click on your DataAdapter object and select 'Preview Data'. To change what is returned (for example, to remove certain columns or add calculated fields), right-click and select 'Configure Data Adapter'; then use the designer to prepare a customized SQL statement. Do not remove primary keys: instead, make them invisible (see the next 'Top Tip').

3. Right-click the DataAdapter and choose Generate DataSet. A dialog box will appear, about to create the template upon which your DataSet will be based (a DataSet based on a template like this is called a "typed DataSet", whilst the template itself is a customizable "XML schema", sometimes referred to as XSD [XML Schema Definition]). Ensure New is selected; replace the suggested name with some more sensible, such as Customer. Ensure 'Add this DataSet to the designer' is checked. Click OK when finished. Two things will happen: a Customer.xsd (or similar) template will be added to your Solution; an invisible DataSet object will be added to your Form, based on the template. Rename the DataSet template to, say, dsCustomer.

4. Drag-and-drop a DataGrid control from the Toolbox onto your Form. Resize as appropriate, and then right-click and select Auto Format. Choose a new style, such as "Professional 3" or "Colorful 3".

5. Add the following code template to respond to the page Load event. This retrieves your table data from the database and binds it to your DataGrid. Be sure to replace MyDataAdapter and MyDataGrid with names of your own DataAdapter and DataGrid objects:

   If Not IsPostBack Then
       MyDataAdapter.Fill(MyDataSet)
       MyDataGrid.DataSource = MyDataSet
       MyDataGrid.DataBind()
       DataSave(MyDataSet)
       ' The DataSave function will be added later.
       ' Remove this line and stop here if you want
       ' a read-only DataGrid
   End If
   

6. Right-click on your DataGrid and select Property Builder. Select the Columns property sheet and ensure 'Create columns automatically at run time' is checked. This means the columns are dynamically created from your table. Next, we're going to add one button to allow you to select a record, perhaps for editing or deleting. Under the 'Available columns' list, expand Button Column, highlight Select and use the '>' button to move it across to the 'Selected columns'. The properties of this new column button will appear below. Change its Text property to a square bracket (>) and its Button type to a PushButton. Click OK when finished. You should be able to see the new record select button in your grid.

   Top Tip: If you only want to display certain columns in the DataGrid, you can selectively choose those required through the Property Builder. Firstly, in the General property sheet, select your data by choosing your DataSet and table for the DataSource and DataMember properties. Next, in the Columns property sheet, uncheck 'Create columns automatically at run time', then move individual fields from the list of available columns (under 'Data Fields') over to the selected columns list. Click OK when finished and continue the instructions.

7. Add the following code to respond when the SelectedIndexChanged event of your DataGrid occurs. This event fires off when a record is selected. This code simply highlights the row, making your selection more obvious:

   Dim intCount As Integer
   For intCount = 1 To MyDataGrid.Items.Count
       MyDataGrid.Items(intCount - 1).BorderStyle = _
                                      BorderStyle.Groove
   Next
   MyDataGrid.SelectedItem.BorderStyle = BorderStyle.Dashed
   

8. Add the following functions behind your Web form. They provide a clean and easy way of saving and retrieving the DataSet containing our table data. Here, our application uses the page ViewState (encrypted HTML sent back and forth between posts); however, you could easily change it to use the Session or Application object if you're dealing with large tables:

   Public Sub DataSave(ByVal DataSet As DataSet)
       If DataExists() Then
           ViewState.Item("__Data") = DataSet
       Else
           ViewState.Add("__Data", DataSet)
       End If
   End Sub
   Public Function DataRetrieve() As DataSet
       Return CType(ViewState.Item("__Data"), DataSet)
   End Function
   Public Function DataExists() As Boolean
       If Not ViewState.Item("__Data") Is Nothing _
       Then Return True
   End Function
   

9. Add six buttons to your Web Form, above the grid: Add, Delete, Edit, OK, Cancel, and Update. These will be action buttons. You will click Add to add a new record, Delete to remove a record, Edit to edit an existing record, OK to accept an edit, Cancel to cancel an edit, and Update to save all changes to the backend database. If you don't want to implement any of these features, simply leave it out. Behind each of those buttons, add the relevant snippet of code:

   ' Code to respond to the Click event of the ADD button:
   ' Desc: Adds a new row to the DataSet, rebinds to the
   ' DataGrid, then makes the row editable
   If DataExists() = False Then Exit Sub
   Dim LocalDS As DataSet = DataRetrieve()
   Dim rowNew As System.Data.DataRow = LocalDS.Tables(0).NewRow
   ' Enter sample values for non-null fields here
   ' ie, rowNew.Item("uniqueTag") = "sample"
   LocalDS.Tables(0).Rows.Add(rowNew)
   MyDataGrid.EditItemIndex = MyDataGrid.Items.Count
   MyDataGrid.DataSource = LocalDS
   MyDataGrid.DataBind()
   DataSave(LocalDS)
   
   ' Code to respond to the Click event of the DELETE button:
   ' Desc: Deletes the selected row, updates the DataSet,
   ' then rebinds
   If DataExists() = False Then Exit Sub
   If MyDataGrid.SelectedIndex = -1 Then Exit Sub
   Dim LocalDS As DataSet = DataRetrieve()
   LocalDS.Tables(0).Rows(MyDataGrid.SelectedIndex).Delete()
   MyDataGrid.EditItemIndex = -1
   MyDataGrid.SelectedIndex = -1
   MyDataGrid.DataSource = LocalDS
   MyDataGrid.DataBind()
   DataSave(LocalDS)
   
   ' Code to respond to the Click event of the EDIT button:
   ' Desc: Makes the selected row editable, then rebinds
   If DataExists() = False Then Exit Sub
   If MyDataGrid.SelectedIndex = -1 Then Exit Sub
   Dim LocalDS As DataSet = DataRetrieve()
   MyDataGrid.DataSource = LocalDS
   MyDataGrid.EditItemIndex = MyDataGrid.SelectedIndex
   MyDataGrid.DataBind()
   
   ' Code to respond to the Click event of the OK button:
   ' Desc: Cycles through the TextBox controls used during a
   ' standard edit, puts the values back in the DataSet,
   ' then rebinds. Add error handling as appropriate.
   ' NOTE: This code relies on the first column being a
   ' selection (>) button (it starts counting the cells
   ' from position 1, not 0). If you remove that button,
   ' you may have to change this code.
   If DataExists() = False Then Exit Sub
   If MyDataGrid.EditItemIndex = -1 Then Exit Sub
   Dim intCount As Integer
   Dim LocalDS As DataSet = DataRetrieve()
   With MyDataGrid
       For intCount = 1 To .Items(.EditItemIndex).Cells.Count
           If intCount = .Items(.EditItemIndex).Cells.Count _
                         Then Exit For
           ' Check that a control exists in this position
           If .Items(.EditItemIndex).Cells(intCount).Controls. _
                                     Count Then
               ' Check for a standard TextBox
               If TypeOf (.Items(.EditItemIndex)._
                                  Cells(intCount). _
                                  Controls(0)) _
                   Is TextBox Then
                   If CType(.Items(.EditItemIndex). _
                      Cells(intCount).Controls(0), _
                       TextBox).Text = "" Then
                       ' Insert a null, if no data
                       LocalDS.Tables(0).Rows(.EditItemIndex)._
                                               Item( _
                           intCount - 1) = System.DBNull.Value
                   Else
                       LocalDS.Tables(0).Rows(.EditItemIndex). _
                                                   Item( _
                           intCount - 1) = CType(.Items( _
                           .EditItemIndex).Cells(intCount). _
                                           Controls(0), _
                           TextBox).Text
                   End If
               End If
           End If
       Next
       .SelectedIndex = -1
       .EditItemIndex = -1
       DataSave(LocalDS)
       .DataSource = LocalDS
       .DataBind()
   End With
   
   ' Code to respond to the Click event of the CANCEL button:
   ' Desc: Used to cancel an edit. Deselects an selected rows and
   ' exists the edit mode, then rebinds.
   If DataExists() = False Then Exit Sub
   MyDataGrid.SelectedIndex = -1
   MyDataGrid.EditItemIndex = -1
   MyDataGrid.DataSource = DataRetrieve()
   MyDataGrid.DataBind()
   
   ' Code to respond to the Click event of the UPDATE button:
   ' Desc: Updates the underlying database, then rebinds.
   ' Add error handling code as appropriate.
   If DataExists() = False Then Exit Sub
   MyDataAdapter.Update(DataRetrieve)
   MyDataGrid.DataSource = DataRetrieve()
   MyDataGrid.DataBind()
   

From this code base, you can do practically anything using the Web DataGrid and a little imagination. You could create a form that allows you to add items through regular input boxes, then in code creates a new row in the DataGrid and fills out the relevant fields. You could modify it so the DataSet actually contains items in a user's shopping basket, with an update feature to change quantities. You could simply use it to create a power user system that allows a privileged few to access and edit data in key administration tables within your company database.

In short, there are many different methods of displaying data on the Web—and everyone wants to do something slightly different. Here, I've provided a standard code framework that will allow you to perform the most commonly requested—adding, deleting, editing, and updating of records in a table. It's now up to you to customize and take this base model to new heights.

Top Tip: Although you may be working with a DataGrid here, don't be afraid of working directly with your data through the DataSet object. In the Essentials, we looked at sample code to do this—and you can easily mix that code in with the above templates for a more personalized, powerful solution.

The techniques that follow this introduction will be particularly useful to those who have become acquainted with the Web DataGrid and how it works. They gradually get more advanced, assuming a working knowledge of the DataGrid and ADO.NET technologies.

Click here for a larger image.

Figure 1: A selected record in my final, editable Web grid!

Little Known Technique for Confirming Web Deletes

This next code technique is a real gem—one of those tips you rarely find printed, but when you figure out how it works, you won't be able to get enough of it. This tip shows you how to run a snippet of JavaScript to confirm an action, such as the deletion of a record, all on the client side. Best of all, it takes just one line of code.

Simply add the following code behind your delete button:

MyDeleteButton.Attributes("onclick") = _
  "return confirm('Are you sure you wish to _ 
                     delete this record?');"

This adds a small piece of JavaScript to run when the client side 'onclick' event occurs for the rendered HTML button. The 'Are you sure?' prompt then appears and if it the user confirms, a True is returned and the click is processed. Otherwise, it simply cancels itself out.

However, this doesn't accommodate those developers using actual delete buttons in DataGrid columns. In such a situation, you need to catch the individual delete buttons as the data bind occurs and add this 'onclick' attribute.

The following snippet of code assumes you have a Web DataGrid with Delete buttons in the first column (index zero). Note that it currently explicitly checks for a Button control, not a PushButton (ensure the 'Button type' is set correctly in the Property Builder, or alter the code). Simply add the following to respond to the ItemDataBound event of the DataGrid control, ready to fire when any data bind occurs:

If e.Item.ItemType = ListItemType.Item Or _
  e.Item.ItemType = ListItemType.AlternatingItem Then
  If e.Item.Cells(0).Controls.Count > 0 Then
    If TypeOf (e.Item.Cells(0).Controls(0)) Is Button Then
        Dim btnDelete As Button = CType(e.Item.Cells(0). _
                                  Controls(0), Button)
        btnDelete.Attributes("onclick") = _
            "return confirm('Are you sure you wish to _
                             delete this record?');"
    End If
  End If
End If

And that's it: simple, effective, and pretty hush-hush.

Figure 2: Confirm your Web deletes in a Web grid, easily!

Selecting Multiple Web Form Grid Items, Hotmail-Style

Selecting and, say, deleting items one-by-one can be a real pain. E-mail services such as Hotmail and Yahoo! Mail recognize that and allow you to select multiple messages through a little checkbox, then zap them all at once. Well, anything Hotmail can do, we can do ... quicker.

To create a selection checkbox, first set up your Web DataGrid as normal—displaying anything from order information through to mail messages. Next, right-click and select Property Builder. Choose the Columns property sheet and add a 'Template Column' to the list of selected columns, moving it to the top of the list (the first column). Set the 'Header text' value if you wish. Click OK when finished.

Back on your Web page, right-click your DataGrid again, this time choosing Edit Template, then choosing your new Template Column. This is your template for this particular field. Drag-and-drop a CheckBox control into the ItemTemplate portion, changing its ID property to 'chkSelect'. When finished, right-click on the DataGrid again and select 'End Template Editing'. You should be able to see the difference on your DataGrid.

Next, add a button to your Web Form. This will be the button your user clicks after selecting records to delete (or perform some other action upon). Add code behind the button Click event, similar to the following:

Dim objItem As DataGridItem
For Each objItem In MyDataGrid.Items
  ' Ignore invalid items
  If objItem.ItemType <> ListItemType.Header And _
    objItem.ItemType <> ListItemType.Footer And _
  objItem.ItemType <> ListItemType.Pager Then
    ' Retrieve the value of the check box
    Dim blnDelete As Boolean
    blnDelete = CType(objItem.Cells(0).FindControl("chkSelect"), _
      CheckBox).Checked
    If blnDelete = True Then
      ' Delete this row from the underlying DataSet, ie.
      ' LocalDS.Tables(0).Rows(MyDataGrid.SelectedIndex).Delete
      ' You can also retrieve the value of a field on the row, ie.
      ' MyVariable = objItem.Cells(5).Text
      ' ... then rebind.
    End If
  End If
Next

Here, our code walks through each valid item in the DataGrid, searching for our control in the first cell (zero index) and analyzing whether it's checked. If it is, that's where your code can step in to take action—probably deleting the record in the underlying DataSet, then rebinding, as per the examples in the "Nine Steps to a Quick, Editable Web Grid" tip.

And that's it. You now should be able to select multiple cells and perform an en masse operation, such as a delete, in seconds! You may even want to merge this tip with the next for even more power over your data...

Figure 3: Selecting multiple items in our Web grid, Hotmail-style

Click Anywhere and Select, with a Web Grid

Web applications are not like Windows applications. We know that. But, by using tricks such as the SmartNavigation property we covered in the last chapter, you can give your sites more intelligence, allowing them to be much more responsive, to work better.

This next tip adds to that repertoire. By using the following code, you can click anywhere in a DataGrid and have the record you were over selected (or, rather, have your code behind the SelectedIndexChanged event run). Especially useful for those with a speedy Internet connection, or using an Intranet site, where postbacks are hardly noticed.

Anyway, here's the code. It presumes the very first column contains a Select button of the PushButton variety (though you can make this column invisible through the Property Builder, if you wish to do so). It finds this Select button in code and, through the highly hush-hush GetPostBackClientHyperlink function, returns the name of the script which runs when pressed. This is then set to run when the 'onclick' event of the row is fired. Just add the following to respond to the ItemDataBound event of the DataGrid control:

If e.Item.ItemType = ListItemType.Footer Or _
  e.Item.ItemType = ListItemType.Footer Or _
  e.Item.ItemType = ListItemType.Pager Then Exit Sub
If e.Item.Cells(0).Controls.Count > 0 Then
  If TypeOf (e.Item.Cells(0).Controls(0)) Is Button Then
    Dim btnSelect As Button = CType(e.Item.Cells(0).Controls(0), _
        Button)
    e.Item.Attributes("onclick") = _
      Page.GetPostBackClientHyperlink(btnSelect, "")
  End If
End If

Figure 4: Click anywhere and select, with this crafty code

Next Week

Coming up in part three of .NET Data Secrets:

• Quick and Easy Data Paging, with Your Web Grid
• Sorting in Seconds, with Your Web Grid
• Amazingly Simple Method for Exporting Your Web Grid to Excel
• Get Microsoft to Write Your Code—Amazing Undocumented SQL Server Tool!

Also, don't forget that you can contribute to this very series yourself. Simply send your favorite little-known .NET snippets to me—karl@karlmoore.com—and I'll publish the best with full credits.

See you then!

About the Author

Karl Moore is a technology author living in Yorkshire, England. He runs his own consultancy group, White Cliff Computing Ltd, and is author of two best-selling books exposing the secrets behind Visual Basic .NET. When he's not writing for magazines, speaking at conferences, or making embarrassing mistakes on live radio, Karl enjoys a complete lack of a social life. Check out Karl's newest book, Ultimate VB .NET and ASP.NET Code Book.

# # #