.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:
- 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.
- 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').
- 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.
- 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".
- 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
- 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.
- 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
- 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
- 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.
Figure 1: A selected record in my final, editable Web grid!