One of the primary reasons to create programmable Web pages at all is to work with data. Web Forms pages allow you to access, display, and edit data from a variety of sources.
As with other aspects of Web programming, data access in Web Forms is somewhat different than it is in Windows Forms or in other forms packages you might be familiar with. The most interesting differences are listed here:
Wide range of sources You can bind controls in a Web Forms page to everything from an array or a collection, to a database table, to an XML document. A data source needs only to implement the IEnumerable interface for it to be valid for data binding in Web Forms. For example, you can bind the contents of a control to a DropDownList or ListBox control elsewhere in the page. The most common source of data is databases, of course, and for that you typically use ADO.NET.
One-way data binding Data binding in Web Forms pages is read-only—that is, the data binding mechanism will fetch data from the source for you, but it doesn’t automatically write changes back. You can certainly update data sources from Web Forms pages, but you have to provide code to do this yourself. The rationale for this initially surprising strategy is, once again, the differences in how Web applications work. A large majority of Web pages that work with data simply display it. Many fewer pages allow users to edit or enter data that needs to be saved. (Think about browsing commercial Web sites and how rarely you enter data.) Therefore, Web Forms pages don’t incur the overhead of generating the extra code for updates when the code is used comparatively rarely.
Disconnected data access The inherently disconnected nature of Web Forms pages extends to data access as well. The page interacts with an external data source such as a database only long enough to fetch data or make an update, which promotes efficient use of database resources. But remember that the page is re-created with each round-trip. This could potentially mean having to refetch data each time. In particular, this can make the use of ADO.NET datasets a little problematic. We’ll look at this topic in more detail in the sections that follow.
To begin our examination of data binding, let’s create the simplest possible scenario. Create a new Web Forms page named DataBinding_Simple. Use the Web Forms tab of the Toolbox to add a ListBox control and a Button control to the page. Name the Button control Submit. Create a Page_Load handler that looks like this:
private void Page_Load(object sender, System.EventArgs e) { if (!this.IsPostBack) { ArrayList animals = new ArrayList(); animals.Add("Dog"); animals.Add("Cat"); animals.Add("Goldfish"); ListBox1.DataSource = animals; ListBox1.DataBind(); } }
When you run the page, the list box is filled in. Click the Submit button, and notice that the page makes its usual round-trip.
A number of data-binding features are illustrated here. First is that the data source here is simply an ArrayList object that you happen to create at run time. Note also that you bind the ListBox control to the data source by setting its DataSource property.
The call to the control’s DataBind method is important. In Web Forms pages, you specify data binding by setting various control properties (such as DataSource in this instance). But the actual data binding doesn’t occur until the control’s DataBind method is called. It’s only then that the control performs whatever internal logic is required to read the data into the control. If the method isn’t called, nothing happens. The DataBind method is part of the System.Web.UI.Control base class and is available for all Web server controls. You can call the method for an individual control or, because it applies to child controls, call the method for the page (this.DataBind), and the call will be cascaded to all controls on the page.
Finally, notice that the binding is set within a test for a postback—in this case, the data binding occurs only the first time the page is called. When you click the Submit button, this code is not being executed. If you didn’t make this test, the data source would be re-created with each round-trip. For this trivial sample, it would make no difference, but when you’re accessing a database, it can be very inefficient to perform a query every time the user clicks a button. After the first time the data source has been created and the ListBox control is bound to it, you don’t need to rebind. The first time the page runs, the list items are created from the data. Thereafter, the control saves its state (the items) in ViewState.
To bind to a database, you generally use ADO.NET. In a Windows Form, this means that you create a dataset, read data into it from the database, and then bind the controls to dataset tables and columns. That way, you can read and write data while disconnected from the data source. For details, see Chapter 18.
You can use datasets in Web Forms pages as well, but there are some subtle differences. To demonstrate how a dataset is used, create a new page named DataBinding_Dataset1 or reuse the example page containing the ListBox control from the previous section. From the Data tab of the Toolbox, drag a data adapter (SqlDataAdapter or OleDbDataAdapter) control onto the page, and use the Data Adapter Configuration Wizard to configure the adapter to read a small table from a database you have access to. The following example assumes that you have access to the Categories table from the sample Northwind database included with Microsoft Access and SQL Server. If you’re using a different database, you’ll need to adjust accordingly. After the adapter has been configured, choose Generate Dataset from the Data menu and create a new dataset class named dsCategories. This will add an instance of the dataset named dsCategories1 to your page.
Create a Page_Load handler that looks like this:
private void Page_Load(object sender, System.EventArgs e) { if(!this.IsPostBack) { oleDbDataAdapter1.Fill(dsCategories1); ListBox1.DataSource = dsCategories1; ListBox1.DataMember = "Categories"; ListBox1.DataTextField = "CategoryName"; ListBox1.DataValueField = "CategoryID"; ListBox1.DataBind(); } }
This is similar to the DataBinding_Simple example, except that you first fill the dataset and then bind the ListBox control to the dataset table. As before, you must call the DataBind method to get the control to read its data source.
The way the page in the DataBinding_Simple example is currently coded, the dataset is disposed of along with the page itself when the page has finished processing. This isn’t a particularly intelligent way to use a dataset, which is after all designed to be an offline cache. In this example, the cache is thrown away as soon as it’s filled.
There are two solutions to this dilemma. One is to bypass the dataset altogether and work directly against the database. The other is to save the dataset between round-trips.
If the only thing you need to do is read data from the database and display it, you can often bypass the overhead of filling a dataset and use a data command instead. The data command allows you to directly execute a SQL statement—typically a Select command or a stored procedure—and read the results directly. If your command returns multiple records, you can read it with a data reader, which provides you with very fast, read-only, forward-only access to a result set.
The following example shows how you can use a data reader to get the same data that you got from the dataset in the DataBinding_Dataset1 example. The process requires more code because you have to create the reader, execute the command, and use the reader to fetch records, all manually. You also have to explicitly open and close the connection. (For the example, it’s assumed that you have a connection in the page that’s already configured to open the database you want to use.) All things considered, however, this code is more efficient.
private void Page_Load(object sender, System.EventArgs e) { if (!this.IsPostBack) { System.Data.OleDb.OleDbDataReader dreader; oleDbConnection1.Open(); oleDbCommand1.CommandText = "Select CategoryId, CategoryName From Categories"; dreader = oleDbCommand1.ExecuteReader(); while(dreader.Read()) { ListItem li = new ListItem(); li.Text = dreader[1].ToString(); li.Value = dreader[0].ToString(); ListBox1.Items.Add(li); } dreader.Close(); oleDbConnection1.Close(); } }
Even though a data reader is more efficient in many cases, there are still times when you want to use a dataset, including the following:
When the page needs access to data in multiple tables
When you want to reuse the same data, such as in paging or filtering
When you’re exchanging data with a Web service
If you do use a dataset, it’s generally efficient to fill the dataset the first time the page runs and then store the dataset. The next time the page runs, you retrieve the dataset instead of requerying the database and refilling it. If this sounds familiar, it’s because it’s the same state management issue that comes up for other values you want to maintain between round-trips. You can store the dataset in ViewState on the client—in which case, the entire dataset is encoded in the page and sent to the browser and then sent back to you when the page is posted again. Alternatively, you can use a server-based state option for the dataset, such as session state. As before, there’s no single recommended technique.
The following example shows how you can store a dataset in session state:
private void Page_Load(object sender, System.EventArgs e) { if(this.IsPostBack) { dsCategories1 = (dsCategories) Session["dsCategories"]; } else { oleDbDataAdapter1.Fill(dsCategories1); Session["dsCategories"] = dsCategories1; ListBox1.DataSource = dsCategories1; ListBox1.DataMember = "Categories"; ListBox1.DataTextField = "CategoryName"; ListBox1.DataValueField = "CategoryID"; ListBox1.DataBind(); } }
Because objects stored in session state are typed as objects, you have to cast them when you get them out. Here we’re using the generated dataset class dsCategories, which is the type for the instance dsCategories1.
The following code shows how to save the same dataset in ViewState. You can’t store objects in ViewState that aren’t serializable. Datasets aren’t serializable, so you must go through the extra step of converting the dataset into a usable format. The usual solution is to call a dataset method that creates an XML representation of the dataset and store that. When the page is posted back, the XML is read back out of ViewState and converted back to dataset format. As usual, ViewState objects have to be cast from the object type to their true data type.
private void Page_Load(object sender, System.EventArgs e) { if(this.IsPostBack) { System.IO.StringReader sr = new System.IO.StringReader((string)(ViewState["dsCategories"])); dsCategories1.ReadXml(sr); } else { oleDbDataAdapter1.Fill(dsCategories1); System.IO.StringWriter sw = new System.IO.StringWriter(); dsCategories1.WriteXml(sw); ViewState["dsCategories"] = sw.ToString(); ListBox1.DataSource = dsCategories1; ListBox1.DataMember = "Categories"; ListBox1.DataTextField = "CategoryName"; ListBox1.DataValueField = "CategoryID"; ListBox1.DataBind(); } }
You can bind any server control to a data source, and in fact, you can bind any property of any control to data. For example, you can bind a TextBox control’s Text property to a database column, but you can also bind its BackColor, Width, and other properties to suitable data sources. That aside, a number of Web server controls are specifically designed to help you display data: Repeater, DataList, and DataGrid. All three controls display multiple records—that is, lists of data. For detailed information about these controls, refer to the MSDN Library. We’ll look at the DataList and DataGrid controls in more detail in the sections that follow.
To give you an idea of the benefits of using templates to display data, let’s work with the DataList control. Create a new Web Forms page named DataList. For the data, add a data adapter from the Data tab of the Toolbox and use the Data Adapter Configuration Wizard to point to a database and table. This example assumes that you’re using the Categories table of the Northwind database, fetching the columns CategoryID and CategoryName. Drag a DataSet control onto the page, and reuse the dsCategories dataset class you defined earlier in this chapter, in the section “Binding to an ADO.NET Dataset.” If the dataset isn’t available in your project, generate a new dataset class and name it dsCategories, which will create an instance of the dataset named dsCategories1 in the page.
Now drag a DataList control onto the page from the Web Forms tab of the Toolbox. You’ll see only a gray rectangle that acts as a placeholder for the control. Because the DataList control has no inherent user interface, it’s up to you to define what its output looks like.
Select the control, and in the Properties window, set the control’s DataSource property to dsCategories1 (or whatever your dataset is named) and its DataMember property to Categories, which is the name of the data table in your dataset. You have now specified where the control will get the entire list of data from. In a moment, you’ll specify individual data items.
Right-click the control, choose Edit Template from the shortcut menu, and then choose Item Templates to put the control into “template editing” mode. To define how the data is displayed, you drag controls into the Item Templates boxes. You can also type static text into the boxes. For example, from the Web Forms tab of the Toolbox, drag two Label controls into the ItemTemplate box and set their Text property to an empty string. Type parentheses around the second control so that it looks like Figure 20-7.
Although the ItemTemplate box appears somewhat cramped, you’re free to drag as many controls into it as you need. You can also simply type carriage returns to make the box bigger. (You can also resize the DataList control if needed.) Basically, you’re creating a region with whatever layout you want to use for each record. When the page runs, your layout will be repeated once for each record in the data source.
To display a specific data column, you data-bind the controls. Right-click the first label, and choose Properties on the shortcut menu to open the control’s Properties window. Click the button in the (DataBindings) box to open the DataBindings dialog box. Under Bindable Properties, make sure Text is selected. Under Simple Binding, open the node for Container, and for DataItem, select CategoryName.
You’ve created a data-binding expression that will be resolved at run time. (You can see the expression in the disabled Custom Binding Expression box.) The expression is shown here:
DataBinder.Eval(Container, "DataItem.CategoryName")
This expression specifies that the control is getting its data from its container (the DataList control). As the DataList control is being processed, it fetches each data record in turn and makes the data available in its DataItem property. The expression indicates that this Label control should be bound at run time to the current DataItem object, extracting its CategoryName value.
Repeat the data binding process for the second label, binding it to the CategoryID value instead. Right-click the DataList control, and choose End Template Editing from the shortcut menu. Now the control finally looks like something— namely, the layout you specified in ItemTemplate.
Let’s pretty things up a little, primarily to demonstrate the design-time features of the DataList control. Open the Properties window, choose the Auto Format link, and then choose a scheme.
Click the Property Builder link, which opens a builder that greatly simplifies setting the many properties supported by the DataList control. On the General tab, under Repeat Layout, set Columns to 2 to indicate that you want to display the data in two columns. On the Format tab, open the Items node and select Normal Items. Set the font to Verdana and the size to X-Small.
That’s it for the layout. As with all data binding in Web Forms, you need to get your data and then explicitly call the control’s DataBind method. Add code to the Page_Load handler that looks like the following, adjusting the names of your data objects as required:
private void Page_Load(object sender, System.EventArgs e) { if(this.IsPostBack) { dsCategories1 = (dsCategories)Session["dsCategories"]; } else { oleDbDataAdapter1.Fill(dsCategories1); Session["dsCategories"] = dsCategories1; DataList1.DataBind(); } }
Run the page, and you’ll see that the data has been displayed exactly according to your layout. You can create much more sophisticated layouts by specifying a different layout such as the Alternating ItemTemplate (displayed every other record), a header and footer template, or a SeparatorTemplate. We won’t cover these templates in this chapter. Refer to the MSDN Library for more information about these properties. To get some insight into editing data in a Web Forms page, let’s move on to the DataGrid control.
DataGrid is the most sophisticated of the Web server controls for working with data. Paradoxically, for simple data display, it’s easier to work with than the DataList and Repeater controls, primarily because you don’t have to define templates. In this section, you’ll first learn how to use the DataGrid control as a read-only control for data. After that, you’ll see what’s involved in using it as a control for editing data. To some extent, the DataGrid control is the model of any kind of data update you do from a Web Forms page.
To see how a DataGrid control works on a Web page, first create a new Web Forms page named DataGrid. Add a data connection, a data adapter, and a dataset as you did for the DataList control in the previous section. If you followed that example, this will give you a dataset that contains the Categories table from the Northwind database, with the columns CategoryID and CategoryName. The dataset class name is dsCategories, and the instance of the class in the page is named dsCategories1.
From the Web Forms tab of the Toolbox, drag a DataGrid control onto the page. In the Properties window, click the Auto Format link and specify a format for the grid. Then click the Property Builder link in the Properties window to configure the control for data access.
On the General tab, specify dsCategories1 for the DataSource property and Categories for the DataMember property. In the Data Key Field box, choose CategoryID. This value will help you locate the appropriate record later when you want to update it.
On the Columns tab, clear the Create Columns Automatically At Run Time check box. If you leave this check box selected, the grid will render columns based on the data columns it finds in the data source. That would work fine in this case, but you want to customize the display a little bit, which requires that you define explicit columns instead. Under Available Columns, open the Data Fields node. Select CategoryID, and click the > button to copy the column to the Selected Columns list. Do the same for CategoryName. Your grid now has two columns. In the Selected Columns list, select CategoryID, and in the BoundColumn Properties section, select the Read Only check box. This setting will apply later, when you add editing to the grid.
Add a Page_Load handler using the same logic that you used for the DataList control. The handler will look something like this:
private void Page_Load(object sender, System.EventArgs e) { if(this.IsPostBack) { dsCategories1 = (dsCategories)Session["dsCategories"]; } else { oleDbDataAdapter1.Fill(dsCategories1); Session["dsCategories"] = dsCategories1; DataGrid1.DataBind(); } }
As usual, you’re storing the dataset after filling it and restoring it with each round-trip. Run the page, and you’ll see that the grid is filled with information from the Categories table.
There’s no inherent way to edit the data displayed in the grid. The DataGrid control does offer editing capability, which works like this: You add an Edit button column to the grid, which then displays an Edit hyperlink in each row of the grid, as shown in Figure 20-8. When the user clicks one of these Edit links, that row is redisplayed with text boxes for the editable data and with Update and Cancel links in place of the original Edit link. When the user clicks Update, the data in that row is updated and the row is redisplayed in its normal mode.
The rub is that you have to do most of the data editing work yourself. The DataGrid control displays the links automatically, and it raises events when users click the buttons. But you must code the switch to edit mode and, especially, the actual update. Fortunately, this isn’t a particularly difficult task.
To add the edit buttons, select the grid again, open the Property Builder again, and go to the Columns tab. Under Available Columns, open the Button Column node and add an Edit, Update, and Cancel column to the Selected Columns list. If you want, use the arrows to the right to move the Edit button column to the first (that is, leftmost) position in the grid. Close the Property Builder, and notice that the DataGrid control now shows the Edit button column at design time.
When the user clicks a particular Edit button, an EditCommand event is raised for the grid. To put a specific row into edit mode, you need to set the DataGrid control’s EditItemIndex property to the index of that row. How do you know which row the user clicked? The DataGridComandEventArgs object e for the EditCommand event contains the entire current row in the form of a DataGridItem object. You can work backward from this object and extract its ItemIndex property to get the row offset in the grid. This sounds complicated, but it’s actually quite simple. The code for the EditCommand handler almost always looks like this:
private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = e.Item.ItemIndex; DataGrid1.DataBind(); }
After setting the grid’s EditItemIndex property, you have to rebind it to cause it to be redisplayed in edit mode.
When the row is in edit mode, the Edit Button column contains Update and Cancel links. To cancel editing, you handle the CancelCommand event, set the grid’s EditItemIndex to -1, and then rebind, as shown here:
private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; DataGrid1.DataBind(); }
If you run the page now and try clicking the Edit button, you should see the row displayed with a TextBox control for the CategoryName field. (The CategoryID property shouldn’t be displayed in a text box because you set it to be read-only when you first defined the column.) Clicking Cancel returns the row to display mode.
There’s just one task left—namely, to handle an update. When the user clicks the Update button in edit mode, an UpdateCommand event is raised for the grid. You need to perform the following tasks:
Get the new or changed values of the TextBox controls in the editable row.
Use the new values to update the data source. In this case, you need to find the appropriate row in the dataset and copy the changed values into it. You then need to send the dataset changes back to the database.
Take the row out of edit mode and, as usual, rebind the grid. In this example, you also need to resave the dataset to session state because it has changes.
The code for the UpdateCommand handler is shown here:
private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { // Get the value of the editable TextBox in the third cell. TextBox tb = (TextBox) e.Item.Cells[2].Controls[0]; String NewCategoryName = tb.Text; int categorykey = (int) DataGrid1.DataKeys[e.Item.ItemIndex]; DataRow dr = dsCategories1.Categories.FindByCategoryID(categorykey); dr[1] = NewCategoryName; oleDbDataAdapter1.Update(dsCategories1); Session["dsCategories"] = dsCategories1; DataGrid1.EditItemIndex = -1; DataGrid1.DataBind(); }
When the row is in edit mode, it displays text boxes. To get their values, you have to dig fairly deeply into the DataGrid control’s object model. Each row is an item containing a cell for each column. Each cell can contain controls, which you can access via the cell’s Controls collection. For a simple scenario such as this one, the TextBox control is typically the first (and only) control in a given cell. The elements in the Controls collection are typed as objects, so you have to cast the object to a TextBox control. Then you can get the control’s value.
The next task is to find and update the row in the dataset table that corresponds to the one being edited. It’s usually not a good idea to assume that a row offset in the grid is the same as the row offset in a data table. (In this case, that’s true, but in most cases it won’t be.) The only reliable way to find the row in the data table is to find a match with the key of the row being edited. You can get the key of the current grid row by extracting it from the DataGrid control’s DataKeys property, which contains a collection of the keys. This collection is available automatically as long as you set the grid’s DataKeyField property (as you did when you first configured the grid), even if you don’t display the key value in the grid. Once you have a key, you can use it to locate the data table row. You could loop through the table looking for a match, for example. In this case, you’re using a typed dataset, in which each table exposes a FindBy<key> method. In the preceding code, the data table row is found by calling the FindByCategoryID method of the Categories table, passing it the key. Once you’ve got the data table row, you can set its columns to the new values you got earlier.
After updating the dataset, you propagate changes back to the database by calling the data adapter’s Update method, which executes the appropriate SQL statements. Finally, because the dataset has changed, you need to make a new copy of it in session state. Then take the row out of edit mode and rebind the grid to show the new data in display mode.