A Simple ADO.NET and XML Sample

Building a User Interface Quickly Using Data Binding

So you want to build a user interface. You can write code that retrieves data from your database and submits changes, but you need to display data on a form and allow users to interact with that data by adding, modifying, and deleting rows of data. You also want to develop this user interface quickly.

Obviously, you can write code to read the contents of a DataRow and display that data in TextBox controls on a form. You can also write code that gives the user the ability to navigate through different rows of data, as well as the ability to add, modify, and delete rows in your DataSet. If you wrote a series of applications that all shared these same basic goals but really only differed in the type of data they handled, you'd find yourself writing the same basic routines in each application.

The Windows Forms package that's part of the Microsoft .NET Framework includes support for data binding. Data binding offers functionality similar to the routines I described in earlier chapters for displaying the contents of a DataSet in various controls and includes features that allow users to modify that data. In short, data binding allows you to build data access applications more quickly and easily by reducing the amount of code required to develop the user interface.

note

Data binding actually supports more than just DataSet objects. You can bind controls to ADO.NET structures such as DataSet objects and DataTable objects, to arrays, or to any object that implements the IList interface. Because this book is dedicated to ADO.NET, I'll focus on using data binding with ADO.NET structures. For more information on using data binding with other structures, see the corresponding documentation in the .NET Framework SDK.

Enough preliminary talk about data binding. Let's use data binding to develop a fairly simple order entry application. The application, shown in Figure 13-1, allows users to view and modify orders for a customer. We'll build the application in phases to demonstrate the various data binding features needed to develop an application.

Figure 13-1

A sample order entry application

On this book's companion CD, you'll find the completed version of the sample application as well as a version corresponding to each step described in this chapter. You'll find versions of the application built in Microsoft Visual Basic .NET as well as Microsoft Visual C# .NET.

note

The sample application is designed to work with the sample Northwind database. In Chapter 3, you'll find instructions on how to install the version of the Microsoft Desktop Engine (MSDE) that's included with the .NET Framework SDK. The instructions also explain how to install the sample databases that accompany the .NET Framework SDK. The Northwind database is one of the sample databases installed.

Step 1: Creating Your DataAdapter and DataSet

Because this is a Windows-based application, you start by creating a new Windows project in your language of choice. Name the application Chapter13. Rename the default form frmEditOrders, and change the caption to Edit Orders. In the sample application, I've set the MaximizeBox property on the form to False and set the FormBorderStyle property to Fixed3D. These settings prevent the user from resizing the form. Setting these properties isn't really necessary, but I don't like having users resize a form that wasn't designed to be resized.

The application will display order information for a particular customer, so we'll need a DataAdapter to fetch order information from the Northwind database. On the Data tab of the Toolbox, select an OleDbDataAdapter and drag it onto the form. This will launch the Data Adapter Configuration Wizard, which is described in detail in Chapter 5.

On the Connection screen of the wizard, select an existing connection to the Northwind database. If one does not exist, use the New Connection button to create one. On the Query Type screen, stick with the Use SQL Statements default option. Then, on the SQL Statement screen, enter the following SQL statement:

SELECT OrderID, CustomerID, EmployeeID, OrderDate     FROM Orders WHERE CustomerID = ?

I chose this query for two reasons. First, we're creating a very basic application, so I want to limit the number of columns we retrieve. Second, rather than retrieve all orders from the database, I want to retrieve only the orders for a particular customer. Remember that your applications will run faster if you limit the amount of data your queries return.

When you exit the wizard, you'll see an OleDbDataAdapter and an OleDbConnection object in the components tray for your form. Rename them daOrders and cnNorthwind, respectively. Right-click in the components tray, and choose Generate Dataset from the shortcut menu. In the resulting dialog box, change the name of the new DataSet class to xsdChapter13 and then click OK. You'll see a new item in Solution Explorer—xsdChapter13.xsd—and an instance of the DataSet class in the components tray. Rename this instance dsChapter13.

The OrderID column in the Orders table is an autoincrement column. In Chapter 6, I recommended setting both the AutoIncrementSeed and AutoIncrementStep properties to -1 on autoincrement columns in a DataSet. Let's set these properties on the OrderID column in the strongly typed DataSet class that we just created. Double-click on the strongly typed DataSet schema file in Solution Explorer (xsdChapter13.xsd). Select the OrderID column. Set the AutoIncrementSeed and AutoIncrementStep properties to -1. Then close the window and save the changes.

Step 2: Adding Bound Textboxes

You now have a DataAdapter and a DataSet for your form. Let's add some TextBox controls to the form that we'll use to display data for a particular order. To make the user interface more intuitive, we'll also add a label for each TextBox to indicate what type of data the TextBox contains.

First let's add a label and the TextBox for the OrderID column. From the Toolbox, drag a Label onto the form. Set the Label's name to lblOrderID and its Text property to Order ID:. Now click the TextBox on the Toolbox and drag it onto the form. Change its name to txtOrderID, and clear its Text property.

At this point, we still have a simple TextBox. To bind the TextBox to the OrderID column in the DataSet, go to the Properties window. Look for the section marked (DataBindings). This section appears in the Data category if you have the properties arranged by category, the default setting. I prefer sorting the properties alphabetically, in which case the section will appear close to the top of the list. We want the contents of the OrderID column bound to the Text property for the control. Expand the section, select the entry marked Text, and click the down arrow to show the list of available columns. You'll see the DataSet in the list. Expand the DataSet, and you'll see the list of DataTable objects in the DataSet. In this case, there's only the Orders DataTable. Expand the DataTable, and you'll see the DataColumn objects available in that DataTable. Select the OrderID column, as shown in Figure 13-2.

Figure 13-2

Binding the Text property of a TextBox to a DataColumn in a DataSet

Follow the same steps to create Label and TextBox controls for the CustomerID, EmployeeID, and OrderDate columns. Use the same prefixes for the control names and the same format for the Text property of each control. Bind the TextBox controls to the appropriate columns in the DataSet. Arrange the controls so that they look similar to those shown in Figure 13-3.

Figure 13-3

Adding labels and TextBoxes to your form

As you can see, binding a TextBox to a column in a DataSet at design time in Visual Studio .NET is easy. You can also accomplish this same task using code, as shown here:

Visual Basic .NET

txtOrderID.DataBindings.Add("Text", dsChapter13, "Orders.OrderID")

Visual C# .NET

txtOrderID.DataBindings.Add("Text", dsChapter13, "Orders.OrderID");

The code binds the Text property on the control to the OrderID column of the Orders table in the dsChapter13 DataSet.

note

In the sample application, I set the ReadOnly property for the OrderID TextBox to True so that the user can't edit the contents of the OrderID column. By default, text in a read-only TextBox is gray rather than black. Personally, I don't like that default, so I set the ForeColor property for the control to Black. Out of habit, I also set the TextAlignment property of the OrderID and EmployeeID TextBoxes to Right because the corresponding columns contain numerical data.

Step 3: Retrieving Data

You now have a DataSet and some TextBox controls bound to columns in that DataSet, but you don't yet have any data to display. If you run your project, you'll see your form with its Label and TextBox controls, but the TextBox controls will be empty. You've done nothing wrong. The DataSet simply doesn't contain any orders.

If you used previous versions of Visual Basic, you might recall that displaying a bound control automatically forced the application to execute the associated query and retrieve its results. This behavior does not occur with .NET.

You'll have to add some code so that the application will fetch some orders from the database when it starts up. Double-click on the form to bring up the code window for the form. You'll see the procedure that will handle the Load event for the form. Just before the procedure for the Load event, add the following line of code:

Visual Basic .NET

Dim strCustomerID As String = "ALFKI"

Visual C# .NET

string strCustomerID = "ALFKI";

Then add the following code in the procedure for the Load event:

Visual Basic .NET

daOrders.SelectCommand.Parameters(0).Value = strCustomerID daOrders.Fill(dsChapter13.Orders)

Visual C# .NET

daOrders.SelectCommand.Parameters[0].Value = strCustomerID; daOrders.Fill(dsChapter13.Orders);

We need the first line of code because the query we specified for the DataAdapter is parameterized. Once you've added this code, you can run your project and you'll see the contents of an order on your form.

In the sample application, I also added a Close button to the lower right of the form. In the Click event of the button, I call the form's Close method to end the application. To do the same in your application, drag the button from the Toolbox, set its Name property to btnClose, and set its Text property to Close. Double-click on the button to access a procedure for its Click event, and then add the following code:

Visual Basic .NET

Me.Close()

Visual C# .NET

this.Close();

Step 4: Adding Navigation Buttons

Right now, our application can display only the contents of a single order. You could add code to check the Count property of the Orders DataTable in our DataSet and find that we retrieved multiple orders. The sample application isn't terribly useful if it can show only a single order. Let's add functionality to the application so that the user can see how many orders are available and can move from one order to the next.

In the sample application, I've added four buttons and a label just below the order information on the form, as shown in Figure 13-4. The buttons let the user navigate through the available orders. The Label control displays the current position and the number of available orders.

Figure 13-4

Adding navigation controls to the form

The controls use an instance of the CurrencyManager class to provide this functionality. Although the class's name implies that the class acts as a financial consultant, it's really the class that makes data binding work. A Windows Form exposes a BindingContext property that you can use to access the CurrencyManager objects that manage the bound controls on the form.

The TextBox controls are bound to a single row in the Orders DataTable. To change which row those controls display, you can change the Position property on an instance of the CurrencyManager class. To move to the next row, you can add 1 to the value of the Position property. To move to the previous row, you subtract 1.

Rather than try to explain each line of code and where to place it, I've clipped code from the sample and pasted it here. The code includes a form-level CurrencyManager variable. The code in the procedure that handles the form's Load event initializes this variable and adds handlers for the CurrencyManager object's ItemChanged and PositionChanged events. The procedures that handle these events set the text for the label that displays the position of the current order. You'll also find procedures that handle the click events for the various buttons and set the CurrencyManager object's Position property accordingly.

Visual Basic .NET

'Form-level variable Dim cmOrders As CurrencyManager Private Sub frmEditOrders_Load...     cmOrders = CType(BindingContext(dsChapter13, "Orders"), _                      CurrencyManager)     AddHandler cmOrders.ItemChanged, AddressOf cmOrders_ItemChanged     AddHandler cmOrders.PositionChanged, AddressOf cmOrders_PositionChanged     DisplayOrdersPosition() End Sub Private Sub DisplayOrdersPosition()     lblOrdersPosition.Text = "Order " & cmOrders.Position + 1 & _                              " of " & cmOrders.Count End Sub Private Sub cmOrders_ItemChanged(ByVal sender As Object, _                                  ByVal e As ItemChangedEventArgs)     DisplayOrdersPosition() End Sub Private Sub cmOrders_PositionChanged(ByVal sender As Object, _                                      ByVal e As System.EventArgs)     DisplayOrdersPosition() End Sub Private Sub btnOrdersMoveFirst_Click...     cmOrders.Position = 0 End Sub Private Sub btnOrdersMovePrevious_Click...     cmOrders.Position -= 1 End Sub Private Sub btnOrdersMoveNext_Click...     cmOrders.Position += 1 End Sub Private Sub btnOrdersMoveLast_Click...     cmOrders.Position = cmOrders.Count - 1 End Sub

Visual C# .NET

//Form-level variable CurrencyManager cmOrders; private void frmEditOrders_Load... {     cmOrders = (CurrencyManager) BindingContext[dsChapter13, "Orders"];     cmOrders.ItemChanged +=                           new ItemChangedEventHandler(cmOrders_ItemChanged);     cmOrders.PositionChanged += new EventHandler(cmOrders_PositionChanged);     DisplayOrdersPosition(); } private void DisplayOrdersPosition() {     lblOrdersPosition.Text = "Order " + (cmOrders.Position + 1) +                               " of " + cmOrders.Count; } private void cmOrders_ItemChanged(object sender, ItemChangedEventArgs e) {     DisplayOrdersPosition(); } private void cmOrders_PositionChanged(object sender, EventArgs e) {     DisplayOrdersPosition(); } private void btnOrdersMoveFirst_Click(object sender, System.EventArgs e) {     cmOrders.Position = 0; } private void btnOrdersMovePrevious_Click(object sender, System.EventArgs e) {     cmOrders.Position--; } private void btnOrdersMoveNext_Click(object sender, System.EventArgs e) {     cmOrders.Position++; } private void btnOrdersMoveLast_Click(object sender, System.EventArgs e) {     cmOrders.Position = cmOrders.Count - 1; }

Step 5: Adding Add and Delete Buttons

The user now has the ability to view all the orders that the DataAdapter retrieved. The user can also change the contents of an order by editing the contents of the bound TextBox controls.

Run the form, and change the EmployeeID for the first order. Remember the original and new EmployeeID values? Once you've changed the EmployeeID for the first order, click the button to move to the next row and then click the back button to move back to the first order. You'll see that the value you specified for the EmployeeID is still there. I'm fighting the urge to use a vaudeville magician voice and ask, "Is this your EmployeeID?"

Although you can modify the contents of an order with the form, you can't add or delete orders...yet. The CurrencyManager object exposes methods that you can use to add or remove items from the structure to which your controls are bound. The AddNew method adds a new item, and the RemoveAt method removes an existing item. When you call either of these methods, the controls bound to the CurrencyManager object react accordingly. If you remove add an item using the AddNew method, the controls show the contents of the new item. Calling the RemoveAt method causes the controls to display the contents of the next available item.

Visual Basic .NET

Private Sub btnOrdersAdd_Click...     cmOrders.AddNew() End Sub Private Sub btnOrdersDelete_Click...     If cmOrders.Count > 0 Then         cmOrders.RemoveAt(cmOrders.Position)     Else         MessageBox.Show("No Order to Delete!", "Delete Order", _                         MessageBoxButtons.OK, MessageBoxIcon.Error)     End If End Sub

Visual C# .NET

private void btnOrdersAdd_Click(object sender, System.EventArgs e) {     cmOrders.AddNew();     SetOrdersEditMode(true); } private void btnOrdersDelete_Click(object sender, System.EventArgs e) {     if (cmOrders.Count > 0)         cmOrders.RemoveAt(cmOrders.Position);     else         MessageBox.Show("No Order to Delete!", "Delete Order",                          MessageBoxButtons.OK, MessageBoxIcon.Error); }

Step 6: Submitting Changes

Our sample application can now modify, add, and delete orders. However, you might have noticed that the application does not submit these changes to the database. I hope you remember enough information from Chapter 5 and Chapter 10 to realize why. The bound controls modify the contents of the DataSet, but we do not have code that calls the DataAdapter object's Update method.

Because we built the DataAdapter using the Data Adapter Configuration Wizard, we do not need to define updating logic. The wizard created the updating logic for us. All we need to do is add a Submit Changes button to the form and then call the DataAdapter object's Update method in the procedure that handles the button's Click event in order to submit the changes. The code traps for the return value on the call to the DataAdapter object's Update method, which indicates the number of updates submitted to the database.

I've added a couple lines of code to display the number of modified orders, trap the exception if the update attempt fails, and throw a dialog box if the DataSet contains no changes. The meat of the code is still the call to the DataAdapter object's Update method, as shown here:

Visual Basic .NET

If dsChapter13.HasChanges Then     Try         Dim intOrdersModified As Integer         intOrdersModified = daOrders.Update(dsChapter13.Orders)         Dim strOutput As String         strOutput = "Modified " & intOrdersModified & " order(s)"         MessageBox.Show(strOutput, "Update succeeded!", _                         MessageBoxButtons.OK, MessageBoxIcon.Information)     Catch ex As Exception         MessageBox.Show(ex.Message, "Update failed!", _                         MessageBoxButtons.OK, MessageBoxIcon.Error)     End Try Else     MessageBox.Show("No changes to submit!", "SubmitChanges", _                     MessageBoxButtons.OK, MessageBoxIcon.Information) End If

Visual C# .NET

if (dsChapter13.HasChanges()) {     try     {         int intOrdersModified;         intOrdersModified = daOrders.Update(dsChapter13.Orders);         string strOutput;         strOutput = "Modified " + intOrdersModified + " order(s)";             MessageBox.Show(strOutput, "Update succeeded!",                          MessageBoxButtons.OK, MessageBoxIcon.Information);     }     catch (Exception ex)     {         MessageBox.Show(ex.Message, "Update failed!",                         MessageBoxButtons.OK, MessageBoxIcon.Error);     } } else     MessageBox.Show("No changes to submit!", "SubmitChanges",                     MessageBoxButtons.OK, MessageBoxIcon.Information);

Step 7: Adding Edit, Update, and Cancel Buttons

The application we've built is simple. The form has only a few controls on it. However, the application is not as intuitive as you might think.

Start the application, and modify the value of the EmployeeID for the first order. Then click the Submit Changes button. You'll see a dialog box stating that there are no changes to submit. What happened?

According to our code, the DataSet object does not contain any changes. So what happened to the change you just made? The CurrencyManager object is still managing that change and has not yet written it to the DataSet. If you navigate to the next order and then click the Submit Changes button, you'll successfully submit the change.

The CurrencyManager object will not commit the pending changes to the DataSet until one of two events occurs—the CurrencyManager moves to another row or the CurrencyManager object's EndCurrentEdit method is called. In some ways, this behavior is similar to the BeginEdit method of the DataRow object. The changes aren't really written to the DataRow object until you call the EndEdit method.

We could include a call to the CurrencyManager object's EndCurrentEdit method just before calling the DataAdapter object's Update method to submit our changes. Adding that line of code would definitely handle this particular scenario. But I prefer a different approach.

In step 7 of creating the sample application, I've added buttons marked Edit, Cancel, and Update. When the application starts up, the data in the TextBox controls are all read-only. You have to click the Edit button to edit the contents of an order. Once you click the Edit button, you can edit the TextBox contents (except for the OrderID column). However, the navigation buttons as well as the Add, Edit, Delete, and Submit Changes buttons are all disabled. You must click either the Update or Cancel button to continue, as shown in Figure 13-5. Clicking the Update button commits the change to the current order; clicking the Cancel button cancels the changes to the current order.

Figure 13-5

Forcing users to commit or cancel changes to the current order

Adding this functionality requires just a few minutes of coding, but I think it's time well spent. As a result of these changes, the application's behavior is much more intuitive. Users won't encounter problems submitting changes due to uncommitted changes. Plus, you won't be left scratching your head trying to figure out how and when the CurrencyManager object commits changes to the DataSet.

I've added a procedure to the form called SetOrdersEditMode that accepts a Boolean value to indicate whether the user can edit the current order. The procedure then sets the ReadOnly property of the TextBox controls and the Enabled property of the buttons as appropriate. I call this procedure from the click events of the Add, Edit, Update, and Cancel buttons.

Visual Basic .NET

Private Sub SetOrdersEditMode(ByVal blnEdit As Boolean)     txtCustomerID.ReadOnly = Not blnEdit     txtEmployeeID.ReadOnly = Not blnEdit     txtOrderDate.ReadOnly = Not blnEdit     btnOrdersMoveFirst.Enabled = Not blnEdit     btnOrdersMovePrevious.Enabled = Not blnEdit     btnOrdersMoveNext.Enabled = Not blnEdit     btnOrdersMoveLast.Enabled = Not blnEdit     btnOrdersCancel.Enabled = blnEdit     btnOrdersUpdate.Enabled = blnEdit     btnOrdersEdit.Enabled = Not blnEdit     btnOrdersAdd.Enabled = Not blnEdit     btnOrdersDelete.Enabled = Not blnEdit     btnSubmitChanges.Enabled = Not blnEdit End Sub

Visual C# .NET

private void SetOrdersEditMode(bool blnEdit) {     txtCustomerID.ReadOnly = !blnEdit;     txtEmployeeID.ReadOnly = !blnEdit;     txtOrderDate.ReadOnly = !blnEdit;     btnOrdersMoveFirst.Enabled = !blnEdit;     btnOrdersMovePrevious.Enabled = !blnEdit;     btnOrdersMoveNext.Enabled = !blnEdit;     btnOrdersMoveLast.Enabled = !blnEdit;     btnOrdersCancel.Enabled = blnEdit;     btnOrdersUpdate.Enabled = blnEdit;     btnOrdersEdit.Enabled = !blnEdit;     btnOrdersAdd.Enabled = !blnEdit;     btnOrdersDelete.Enabled = !blnEdit;     btnSubmitChanges.Enabled = !blnEdit; }

Step 8: Viewing Child Data

Our application now allows users to view and modify data from the Orders table. However, that functionality isn't terribly helpful unless we also allow the user to view and modify the line items for those orders.

Figure 13-6 shows the user interface for the next phase of the sample application. As you can see, I've added a grid that displays data from the Order Details table. As you move from one order to the next, the grid displays just the related rows.

Figure 13-6

Displaying an order and its line items

To add this functionality to the application, do the following:

  1. Add a DataAdapter that fetches all rows from the Order Details table that correspond to a customer's orders.

  2. Regenerate the strongly typed DataSet to add a DataTable for this new data, and then add a DataRelation to the DataSet to easily locate just the line items for a particular order.

  3. Add to the form a DataGrid control that's bound to the DataSet in such a way that the grid shows only the line items for the current order.

  4. Add logic to the procedure for the Submit Changes button to submit changes from both DataTable objects.

Retrieving Just the Order Details for a Customer

It was easy to create a DataAdapter that retrieves just the orders for a particular customer because the Orders table has a CustomerID column. The Order Details table does not. So, to retrieve just the order details for a particular customer, we need to reference both the Order Details and Orders tables in our query.

You can structure such a query in a number of ways. Here are three examples:

SELECT D.OrderID, D.ProductID, D.UnitPrice, D.Quantity     FROM Orders O INNER JOIN [Order Details] D         ON O.OrderID = D.OrderID     WHERE O.CustomerID = ? SELECT OrderID, ProductID, UnitPrice, Quantity FROM [Order Details]     WHERE OrderID IN (SELECT OrderID FROM Orders WHERE CustomerID = ?) SELECT D.OrderID, D.ProductID, D.UnitPrice, D.Quantity     FROM [Order Details] D, Orders O     WHERE D.OrderID = O.OrderID AND O.CustomerID = ?

According to SQL Query Analyzer, which displays estimated execution plans, SQL Server creates the same execution plan for each query. I prefer the syntax in the third query for two reasons. First, it seems like the most intuitive syntax. And second, the Data Adapter Configuration Wizard is unable to handle the second query syntax because of the parameter in the subquery. The wizard seems to prefer the INNER JOIN syntax. Even though I used the syntax for the first query when I built the sample application, the wizard changed the query to the first syntax. To each his, her, or its own.

After you create a new OleDbDataAdapter using this query, rename the new DataAdapter daDetails.

Adding an Order Details DataTable to the Strongly Typed DataSet Class

Once you've added the DataAdapter to fetch rows from the Order Details table, you can use the Generate Dataset dialog box to add a new DataTable to the strongly typed DataSet class. Figure 13-7 shows how. Select the existing DataSet from the list of available DataSet objects. Then make sure the table that corresponds to the new DataAdapter is the only one that's checked in the list of tables in the middle of the dialog box.

When you click OK, Visual Studio .NET will add the new DataTable to the existing strongly typed DataSet class. If you leave both items in the list of available tables checked, Visual Studio .NET will overwrite the existing Orders DataTable in the strongly typed DataSet, which means you'll lose the settings for the AutoIncrementSeed and AutoIncrementStep properties. Resetting those properties isn't difficult, just annoying.

Figure 13-7

Adding a new DataTable to an existing strongly typed DataSet

Now that the strongly typed DataSet class has DataTable objects for the Orders and Order Details tables, you can add a DataRelation between the two DataTable objects based on the OrderID DataColumn. Double-click on the class's .xsd file in Solution Explorer to launch the XML Schema Designer. Drag the CustomerID column from the Orders DataTable to the Order Details DataTable. Accept the defaults in the dialog box that the designer displays.

The sample application also adds a calculated column to show the total cost of a line item. You can do the same by adding a new column to the Order Details DataTable. Set its data type to Decimal, and then set the Expression property to UnitPrice * Quantity.

Close the designer and save the changes.

Adding a DataGrid That Displays Child Data

Binding a DataGrid is simple. You need to set two properties—DataSource and DataMember.

Add a DataGrid to your form, and set its Name property to gridDetails. Select the DataSource property in the Properties window. Open the drop-down list on the right to see the list of available data sources. You'll see the DataSet as well as the individual DataTable objects. Select the DataMember property. You'll find a list of possible values for this property as well, as shown in Figure 13-8. Expand the Orders DataTable in the list, and you'll see the DataRelation you created earlier. Setting the DataMember property to the DataRelation will make the DataGrid show just the child rows using the DataRelation.

Figure 13-8

Setting the DataMember property for a DataGridthat will display child rows

If you wanted to bind the DataGrid at run time, you could use the following code:

Visual Basic .NET

gridDetails.DataSource = dsChapter13 gridDetails.DataMember = "Orders.Order_x0020_Details"

Visual C# .NET

gridDetails.DataSource = dsChapter13; gridDetails.DataMember = "Orders.Order_x0020_Details";

In the sample application, you'll find a line of code toward the end of the procedure for the form's Load event that calls a procedure named FormatDetailsGrid. This procedure adds a new DataGridTableStyle to the DataGrid to control the DataGrid's display. Supplying a DataGridTableStyle allows you to select which columns to display as well as control the size, format, and alignment of each column.

Submitting Changes to Both Tables

Chapter 11 discusses the complexities involved in submitting hierarchical changes to a database. Basically, you need to submit new rows starting at the top of the hierarchy (orders before order details) but deleted rows starting at the bottom (order details before orders). As a result, you can't simply supply the entire DataTable when you call the DataAdapter objects' Update methods.

First you submit the new and modified orders. Then you can submit all the changes to the Order Details table. After that, you can submit the deleted orders. If you look at the code in the procedure for the Submit Changes button's Click event, you'll see that it uses that same logic. I've included that code here, though I've omitted the Try/Catch block to make the code a little more readable:

Visual Basic .NET

Dim intOrdersModified, intDetailsModified As Integer Dim aRowsToUpdate As DataRow() Dim dvrs As DataViewRowState 'Submit new or modified orders. dvrs = DataViewRowState.Added Or DataViewRowState.ModifiedCurrent aRowsToUpdate = dsChapter13.Orders.Select("", "", dvrs) intOrdersModified = daOrders.Update(aRowsToUpdate) 'Submit all changes to the Order Details DataTable. intDetailsModified = daDetails.Update(dsChapter13.Order_Details) 'Submit deleted orders. dvrs = DataViewRowState.Deleted aRowsToUpdate = dsChapter13.Orders.Select("", "", dvrs) intOrdersModified += daOrders.Update(aRowsToUpdate) Dim strOutput As String strOutput = "Modified " & intOrdersModified & " order(s)" & vbCrLf & _             "Modified " & intDetailsModified & " detail(s)" MessageBox.Show(strOutput, "Update succeeded!", _                 MessageBoxButtons.OK, MessageBoxIcon.Information)

Visual C# .NET

int intOrdersModified, intDetailsModified; DataRow[] aRowsToUpdate; DataViewRowState dvrs; //Submit new or modified orders. dvrs = DataViewRowState.Added _ DataViewRowState.ModifiedCurrent; aRowsToUpdate = dsChapter13.Orders.Select("", "", dvrs); intOrdersModified = daOrders.Update(aRowsToUpdate); //Submit all changes to the Order Details DataTable. intDetailsModified = daDetails.Update(dsChapter13.Order_Details); //Submit deleted orders. dvrs = DataViewRowState.Deleted; aRowsToUpdate = dsChapter13.Orders.Select("", "", dvrs); intOrdersModified += daOrders.Update(aRowsToUpdate); string strOutput; strOutput = "Modified " + intOrdersModified + " order(s)\n\r" +             "Modified " + intDetailsModified + " detail(s)"; MessageBox.Show(strOutput, "Update succeeded!",                  MessageBoxButtons.OK, MessageBoxIcon.Information);

Step 9: Binding a Second Form to the Same Data Source

Trying to bind controls on multiple forms to the same data source is challenging but possible. Before I explain how to accomplish this, I want to make a quick detour to show you how to improve the user interface to help even novice users edit data easily.

The DataGrid is a helpful and powerful tool. I regularly use DataGrid controls to display the contents of multiple rows. However, I'm not a big fan of having users modify data in a DataGrid. You might be aware that you can undo a change in the DataGrid by pressing the Escape key or by pressing Ctrl+Z, but few novice users will realize this.

Rather than hoping that users will discover and remember such functionality, I prefer making the process more intuitive even if it forces the user to go through an extra step or two. So, in step 9 of the sample application, I made the DataGrid that shows the line items for a particular order read-only. To add, modify, or delete a line item, the user must click a button—the same requirement for editing an order. If the user chooses to add or modify a line item, the sample application will launch a modal form that lets the user edit the desired row, as shown in Figure 13-9.

Figure 13-9

Editing a line item in a new form

The user can modify the information for the line item using this form. Just as when he or she edits an order, the user must click Update or Cancel to exit edit mode.

Now let's get back to binding controls on multiple forms to the same data source. The TextBox controls on the new form are bound to the same row of data that's currently shown in the DataGrid for the main form. Although you can't do this at design time, you can do it at run time. Here's how.

If you look at the code that's executed when you click the Edit button below the details DataGrid, you'll see that the code creates an instance of the details form and then calls the EditDetail method on the form. The EditDetail method accepts a CurrencyManager object as a parameter.

The code for the EditDetail method follows. As you can see, the code uses the CurrencyManager object to bind the TextBox controls on the new form to the same row of data. The Current property of the CurrencyManager object returns a DataRowView object cast as an object. You can bind the TextBox controls to a DataView object, so the code uses the DataView property of the DataRowView object to access the DataView to which the DataRowView belongs.

Visual Basic .NET

Dim drvDetail As DataRowView Dim vueDetail As DataView Public Sub EditDetail(ByVal cm As CurrencyManager)     drvDetail = CType(cm.Current, DataRowView)     vueDetail = drvDetail.DataView     Me.BindingContext(vueDetail).Position = cm.Position     txtOrderID.DataBindings.Add("Text", vueDetail, "OrderID")     txtProductID.DataBindings.Add("Text", vueDetail, "ProductID")     txtUnitPrice.DataBindings.Add("Text", vueDetail, "UnitPrice")     txtQuantity.DataBindings.Add("Text", vueDetail, "Quantity")     txtItemTotal.DataBindings.Add("Text", vueDetail, "ItemTotal")     If Me.ShowDialog = DialogResult.OK Then         cm.EndCurrentEdit()     Else         cm.CancelCurrentEdit()     End If End Sub

Visual C# .NET

DataRowView drvDetail; DataView vueDetail; public void EditDetail(CurrencyManager cm) {     drvDetail = (DataRowView) cm.Current;     vueDetail = drvDetail.DataView;     this.BindingContext[vueDetail].Position = cm.Position;     txtOrderID.DataBindings.Add("Text", vueDetail, "OrderID");     txtProductID.DataBindings.Add("Text", vueDetail, "ProductID");     txtUnitPrice.DataBindings.Add("Text", vueDetail, "UnitPrice");     txtQuantity.DataBindings.Add("Text", vueDetail, "Quantity");     txtItemTotal.DataBindings.Add("Text", vueDetail, "ItemTotal");     if (this.ShowDialog() == DialogResult.OK)         cm.EndCurrentEdit();     else         cm.CancelCurrentEdit(); }

Step 10: Improving the User Interface

We now have an application that lets users view and edit order information for a customer, but we can do a couple more things to improve the overall user experience.

Given the choice, most users would rather see descriptive information than cryptic key information on a form. For example, the child form that allows the user to modify a line item forces the user to know the key value rather than the name of the product. Also, the formatting for the unit price and item total for each line item looks unprofessional.

Let's see how we can present the data in a more user-friendly format, as shown in Figure 13-10.

Figure 13-10

Presenting data in a more user-friendly format

Adding Lookup Functionality Using a ComboBox Control

If you look at Figure 13-10, you'll see that I replaced the TextBox that listed the EmployeeID for the current order with a ComboBox control that shows the employee's name. Adding this functionality is really quite simple. In fact, you need to set only four properties on the ComboBox.

Before you add a ComboBox to your form to try to use this functionality, you need to add a DataAdapter to retrieve information from the Employees table. I created one with the following query:

SELECT EmployeeID, LastName + ', ' + FirstName AS EmployeeName     FROM Employees

Once you've added a DataAdapter to fetch employee information, you regenerate the DataSet to add an Employees DataTable to the strongly typed DataSet.

note

The application will not modify data in the Employees table, so there's no need to generate updating logic for the DataAdapter that queries the Employees table. You can click the Advanced Options button on the SQL Statement screen of the wizard and tell the wizard to not generate updating logic.

We want the ComboBox to display values from the EmployeeName column in the Employees DataTable. When the user selects an employee name from the list, we want the ComboBox to take the value from the EmployeeID column for that employee and store it in the EmployeeID column for the current order. We'll actually bind the ComboBox to two separate data sources at once—the Employees DataTable and the current row of order information.

First let's look at how to bind the ComboBox to the Employees DataTable. The ComboBox has a DataSource property that you set just like you set the DataSource property of the DataGrid. Select the DataSet object from the list of available data sources. To control which column the ComboBox will use to populate its list, you set the DisplayMember property—in this case, we want to display the EmployeeName column in the Employees DataTable. Then set the ValueMember property to the EmployeeID column in the Employees DataTable.

All that's left to do is associate the ComboBox with the EmployeeID property for the current row of order information. This last part of the process is similar to binding the Text property of a TextBox control to a column in a DataSet. Locate the (DataBindings) area in the Properties window. Expand the area, locate the SelectedValue property, and bind it to the EmployeeID column in the Orders DataTable.

You can accomplish these same steps at run time by executing the following code:

Visual Basic .NET

cboEmployee.DataSource = dsChapter13 cboEmployee.DisplayMember = "Employees.EmployeeName" cboEmployee.ValueMember = "Employees.EmployeeID" cboEmployee.DataBindings.Add("SelectedValue", dsChapter13, _                              "Orders.EmployeeID")

Visual C# .NET

cboEmployee.DataSource = dsChapter13; cboEmployee.DisplayMember = "Employees.EmployeeName"; cboEmployee.ValueMember = "Employees.EmployeeID"; cboEmployee.DataBindings.Add("SelectedValue", dsChapter13,                               "Orders.EmployeeID");

Now, as you navigate through the orders, you'll see the EmployeeName rather than the value of the EmployeeID column. You can also edit an order and change the value of the EmployeeID column by changing the employee listed in the ComboBox control.

Controlling the Format of Bound Data

The data type of the UnitPrice column in the DataSet is Decimal. As a result, the TextBox on the details form that's bound to the UnitPrice column will display the contents of that column using the standard numerical formatting. If the unit price for an item is $4.50, the TextBox will display 4.5.

We can write code to manually change the display of this data to a format that's more appropriate.

Earlier in the chapter, I showed an example of code that binds the Text property of a TextBox to a column in a DataView.

Visual Basic .NET

txtOrderID.DataBindings.Add("Text", dsChapter13, "Orders.OrderID")

Visual C# .NET

txtOrderID.DataBindings.Add("Text", dsChapter13, "Orders.OrderID");

The Add method returns a Binding object, which responds to the CurrencyManager object's events and moves data back and forth between the TextBox and the column to which the TextBox is bound.

The Binding object exposes two events—Format and Parse. The Format event fires when the Binding object loads data from the data source into the property to which it's bound. The Parse event fires when the Binding object reads data from the bound property and assigns this data to the data source. We can use these two events to change the format of the data displayed in the bound TextBox controls.

The following code snippet from the sample shows how it formats the data in the TextBox for the UnitPrice column as currency. The code uses an overloaded ToString method of the Decimal class to format the decimal as currency.

Visual Basic .NET

Public Sub EditDetail(ByVal cm As CurrencyManager)      Dim b As Binding     b = txtUnitPrice.DataBindings.Add("Text", vueDetail, "UnitPrice")     AddHandler b.Format, AddressOf DecimalToCurrencyString     AddHandler b.Parse, AddressOf CurrencyStringToDecimal  End Sub Private Sub DecimalToCurrencyString(ByVal sender As Object, _                                     ByVal cevent As ConvertEventArgs)     If Not cevent.DesiredType Is GetType(String) Then         Exit Sub     End If     If cevent.Value Is DBNull.Value Then         cevent.Value = CDec(0).ToString("c")     Else         cevent.Value = CDec(cevent.Value).ToString("c")     End If End Sub Private Sub CurrencyStringToDecimal(ByVal sender As Object, _                                     ByVal cevent As ConvertEventArgs)     If Not cevent.DesiredType Is GetType(Decimal) Then         Exit Sub     End If     cevent.Value = Decimal.Parse(cevent.Value.ToString, _                                  Globalization.NumberStyles.Currency, _                                  Nothing) End Sub

Visual C# .NET

public void EditDetail(CurrencyManager cm) {      Binding b;     b = txtUnitPrice.DataBindings.Add("Text", vueDetail, "UnitPrice");     b.Format += new ConvertEventHandler(DecimalToCurrencyString);     b.Parse += new ConvertEventHandler(CurrencyStringToDecimal);  } private void DecimalToCurrencyString(object sender,                                       ConvertEventArgs cevent) {     if (!cevent.DesiredType.Equals(typeof(string)))         return;     if (cevent.Value == DBNull.Value)         cevent.Value = ((Decimal) 0).ToString("c");     else         cevent.Value = ((Decimal) cevent.Value).ToString("c"); } private void CurrencyStringToDecimal(object sender,                                       ConvertEventArgs cevent) {     if (!cevent.DesiredType.Equals(typeof(Decimal)))         return;     cevent.Value = Decimal.Parse(cevent.Value.ToString(),                                  System.Globalization.NumberStyles.Currency,                                  null); }

Step 11: If You Want Something Done (Just) Right...

Let's take a short break and look at the sample application we've built. Thanks to data binding features, it took very little code to let users view and edit data from two related DataTable objects through bound controls. That's the whole point of the data binding features—providing basic functionality so that you can build user interfaces with minimal code.

When we initially bound the controls, we had little control over how the controls interacted with the data in our DataSet. In step 10, we added code to control the format of data in bound TextBox controls. Step 10 of the sample application on the CD also includes code to format and accept Null values. You can add more code to gain more control over the bound controls, but remember that the more code you write, the less benefit you'll really get from using data binding in the first place.

Here's an example. I finished step 10 and started using the sample application, looking for ways to improve the application. I discovered that on the Edit Detail form, changing the contents of the Quantity and UnitPrice TextBox controls did not update the contents of the ItemTotal TextBox. I therefore started looking for a way to automatically update the ItemTotal TextBox when the contents of the Quantity or UnitPrice TextBox controls changed. I tried setting the Text property for the ItemTotal TextBox in the Leave event of the Quantity and UnitPrice TextBox controls. I tried calling the Refresh method of the CurrencyManager object. I tried relying on the SuspendBinding and ResumeBinding methods of the CurrencyManager object. No matter what I tried, I couldn't get the functionality I was looking for. Although I think it's possible to accomplish this task with data binding, this isn't the type of scenario that data binding was designed to address.

The more functionality we add to this application through code, the more we relegate the data binding features to three simple tasks—navigation through the available rows, displaying the contents of the current row in a series of controls, and saving user input to the current row. That's not terribly complex code to write.

If you rely on your own code to display data in controls and write changes back to the data structure, you take control and responsibility for the interaction between the user interface and your data structures. Step 11 of the sample application no longer relies on data binding to display data in TextBox controls. If you look at the code for the Edit Orders form, you'll find a ShowCurrentOrder procedure as well as code to determine which order and which line items to display using DataView objects. The forms also include code to validate input into the various TextBox controls. If the user enters invalid data, such as entering Thursday as the unit price for a line item, the user receives a descriptive error message.

Data Binding Summary

As you've seen in the various steps in the sample application, the data binding features in the Windows forms package allow you to create a powerful and robust user interface with minimal code. However, as you try to take more control of the user interface and add more and more code to your application, you might find that your code is battling data binding rather than complementing it. In such cases, you might be better off writing your own code to manage the interaction between your data and the user interface.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net