.NET Data Secrets, Part II

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:

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:

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.