ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


Liberty on Whidbey

ASP.NET 2.0 Databinding

by Jesse Liberty
11/08/2004

In ASP.NET 2.0, a great deal of the ADO.NET object model has been incorporated into controls that let you interact with data declaratively, and that spare you from writing boilerplate code to create data sets, extract tables, bind tables or views to controls, and so forth. In this article, you'll create a simple Windows form that lets you page through the details of the Orders table from the Northwind database. To begin, create a new Windows application called DataBinding.

Open the Data Sources Window (the menu choice Data -> ShowDataSources) and click on Add New Data Source... This opens the Data Source Configuration Wizard. You are free, using this wizard, to connect to a database, a local database file, a web service, or an n-tier design data object. In this case, we'll connect to a database.

The second step in the Wizard lets you choose a data connection. You can choose an existing connection, or you can create a new connection. If you create a new connection, you pick your server and decide whether you'd like to use Windows Integrated Security (Trusted_Connection=True) or you'd like to add your user ID and password. You then pick the database you want to connect to, and you can test the connection you've created, as shown in Figure 1.

Figure 1

Figure 1. Connection dialog

The next step allows you to save the connection string in the application's configuration file, which is a handy convenience. Once your connection is established, you are asked to select which objects you'd like to include in your DataSet, and to name that DataSet (a name is suggested for you). We'll include the Orders table, and take the proposed name, as shown in Figure 2.

Figure 2

Figure 2. DataSet configuration

Click Finish and a DataSet is created that contains a single table, Orders, with the fields you chose (in this case, all of the fields from the table in the database). You can now drag that table onto your form. The default is to create a GridView with each of the fields from the table. You can also click on the table (before dragging it onto the form) and an arrow appears that lets you change how the table will be presented on the form, as shown in Figure 3.

Figure 3

Figure 3. Adding detail data from the DataSource

After choosing Details, drag the table onto the form. A label and appropriate control is created for each field. A DataNavigator is added to the top of the form (see the arrow in Figure 4). In addition, the Component tray now has a DataConnector and a TableAdapter added.

Figure 4
Figure 4. Auto-created controls. Click image for full-size screen shot.

Table adapters are designer-generated components that connect your DataSet to the underlying data source. Table adapters are similar to DataAdapters, but are strongly typed and can contain multiple queries to support multiple tables.

DataConnectors act as a data source for controls; they are an intermediary between the DataSet and the control itself. The typical use for a DataConnector is to connect a group of data-bound-controls to a single table in a data source. This avoids the need to write the code to extract the table from the DataSet.

In addition to navigation, the DataNavigator provides CRUD operations (Create, Read, Update, Delete). You can delete the DataNavigator if you want to navigate through a UI you create yourself.

Finally, a line of code was added to the Form1_Load event handler:


private void Form1_Load(object sender, EventArgs e)
{
	// TODO: Delete this line of code to remove the default 
	// AutoFill for 'northwindDataSet.Orders'.
	this.ordersTableAdapter.Fill(this.northwindDataSet.Orders);
}

This calls the Fill method on the designer-generated TableAdapter, passing in the table from which you want to fill. When you run the program, the data is filled, and the navigator can be used to move through the records. The data entry fields are all "alive" and appropriate to the data, as shown in Figure 5.

Figure 5

Figure 5. Live controls

Creating Parameterized Queries

If you want to provide your own navigation (perhaps from a different page), you can delete the DataNavigator and pass in a parameter to identify the record you want to see. To do so, hover over the ordersDataConnector; a smart tag will become visible. Drop the smart tag and choose Parameterize Data Source, a shown in Figure 6.

Figure 6

Figure 6. Parameterize data source

This opens the Search Criteria Builder. Choose the Source table (Orders) and decide if you want to create a new query or use an existing one. The dialog suggests that your name begin with the prefix "FillBy." Name your new query FillByCustomerID, and modify the query to add a where statement, as shown in Figure 7.

Figure 7

Figure 7. Criteria Builder

The designer adds a fillByCustomerIDToolStrip to the top of the form, as shown in Figure 8.

Figure 8

Figure 8. ToolStrip

The designer has added an event handler for the fillByCustomerIDToolStripButton_Click method:


private void fillByCustomerIDToolStripButton_Click(
	object sender, EventArgs e)
{
	try
	{
		this.ordersTableAdapter.FillByCustomerID(
			this.northwindDataSet.Orders, 
			customerIDToolStripTextBox.Text);
	}
	catch (System.Exception ex)
	{
		System.Windows.Forms.MessageBox.Show(ex.Message);
	}

}

Again, you are free to delete both the DataNavigator and the new fillByCustomerIDToolStrip. Instead, you would and pass an ID into the form, and then call ordersTableAdapter.FilByCustomerID programmatically, passing in the Orders table (as shown in the code above) and the ID.

Adding Master/Details

The Orders table is in a master/details relationship with the Orders Details table. It would be great if, for every order, you could display all of the details of that order on the same form.

To do so, you'll need to modify the DataSet to add the Orders Details table. Right-click on the DataSet in the DataSources window, and choose Edit Data Source with Designer as shown in Figure 9.

Figure 9

Figure 9. Editing the DataSource

This opens the designer for NorthwindDataSet.xsd. Right-click anywhere in the designer (except on the Orders table) and choose Add -> Data Component to bring up the DataComponent Configuration Wizard. Pick the existing database connection, and in the next step choose the default to use a SQL statement to create the new component (rather than a stored procedure). On the third step, click on Query Builder. The Query Builder dialog opens, and over that, the Add Table dialog opens. Choose Order Details and click Add, as shown in Figure 10.

Figure 10

Figure 10. Query Builder

Click Close and in the query builder, click All Columns on the Order Details table to select all the columns in that table. Click OK to return to the Query Builder. Before you close this dialog, click Advanced Options... and notice that the default is that the designer will create all the CRUD methods for you, and will use optimistic concurrency methods.

Note: As I explain in both Programming .Net Windows Applications and Programming ASP.NET, the preferred method for handling concurrency is to insert a where clause that checks to make sure that each field is unchanged from when you selected the record. In .NET 1.1, this involved creating parameters for the new values of each field and the old values of each field, but with the new controls, all of this work is done for you.

Click through the rest of the wizards, reviewing and accepting the default selections. When you click Finish, you are returned to the designer window, the new table is shown, and the designer has recognized the relationship between the two tables, as shown in Figure 11.

Figure 11

Figure 11. DataSet Editor

You can right-click on the relation to open the Relation dialog, which lets you rename the relation, examine the keys involved, change the update, delete and accept/reject rules, as well as modify the relation and foreign key constraints.

Before you leave this dialog, click on the Order Details DataTable and change its name from Order Details to OrderDetails. At least in Beta, failure to do this will cause an exception later, and make it impossible to proceed.

That done, return to your form, and widen it. Move about half of the controls into a second column to make room at the bottom of the form for the details. Before you drag the Order Details table onto the form, you must establish the Master/Details relationship in the Data Connector. To do so, click on the ordersDataConnector and choose Configure Master Details, as shown in Figure 12.

Figure 12

Figure 12. Configuring master detail

This brings up the Add Related Databound UI dialog. In the drop-down menu, choose the parent table (Orders) and the child tables are shown in the list box below (in this case, only Order Details). Click on Order Details and decide which style you'd like the details presented in (Grid or Details view). In this case, choose the Grid style and click OK.

Hey! Presto! The Data Grid is added to your form, along with two new objects in the component tray: fk_Order_Details_OrdersDataConnector (the data connector for the data grid) and orderDetailsTableAdapter (the TableAdapter that will fill the grid). Right-click on the Smart Tag of the grid, and choose Auto Format to improve the look and feel of the grid, as shown in Figure 13.

Figure 13
Figure 13. Formatting the Grid. Click image for full-size screen shot.

While the smart tag is open, click on Edit Columns to open the Edit Columns dialog box. Set the Header Text as you desire and look through the other properties to make an attractive data grid. Close the smart tag, and widen the grid. While you're at it, resize the form to fit, and give the form a new name and title.

Open your database and find an order with multiple order details. Copy the customerID. Run the application and paste the CustomerID into the FillByCustomer tool strip, and click on the FillByCustomerID button. Now click through on the data navigator, investigating the various orders for that customer. Note that all of the controls are coupled, so that the data navigator only navigates within that customer's orders, and the details page changes to reflect the specific order's details, as shown in Figure 14.

Figure 14
Figure 14. Orders and details. Click image for full-size screen shot.

You have created a fairly advanced data-centric application, and you've not written one line of code.


Read more Liberty on Whidbey columns.

Copyright © 2009 O'Reilly Media, Inc.