oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

ASP.NET 2.0 Databinding
Pages: 1, 2

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)
	catch (System.Exception ex)


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.