The developers of previous versions of Visual Basic pioneered the concept of data binding, in which a data connection object (known as a data control) gave designers the ability to create simple, data-driven user interfaces with a minimum of programming. The good news is that the concept of data binding still exists in Visual Basic.NET. The even better news is that many of the aspects of data binding that previously frustrated designers have been improved or done away with in .NET.
In the past, a designer managed the connection between a Visual Basic form and a database with data controls. These controls also provided basic data browsing functionality, enabling an application to navigate through a set of data and add and update records.
In .NET, maintaining a connection to a database and perusing records is handled by automatically generated code. This feature has a number of advantages, including the following.
If you followed the demonstrations earlier in this chapter, you should now have a functional database with some data in it. It should be sufficient for you to experiment with building various kinds of data-bound user interfaces. In the next several sections we demonstrate how to build Windows Forms applications that connect to your database.
Connecting to a Database and Working with Records
Creating a Windows Forms application that accesses data is quite simple in fact, if all you're interested in doing is browsing the database, you don't even have to write a single line of code. It's a matter of first creating a connection to the database and then binding user interface controls to the data source generated by VS.NET. You can do so as follows.
These are two of the three objects required to retrieve and display data the SqlConnection1 object creates a connection to the database, and the SqlDataAdapter1 object is responsible for retrieving data from the database. The third object is the DataSet object, which actually stores the data retrieved by the data adapter. To create a DataSet to bind to data, do the following.
To view data in the form, you'll next need to create a user interface control on the form and bind it to the DataSet object that you just created. To do so, take the following steps.
You may notice one thing in particular about this application: Although it appears that you can make changes to the data, any changes that you do make won't be committed to the database in other words, they won't be saved. To save data, you'll need to write code to call a method of the DataAadapter object in your proj ect. We discuss this task in the Updating Records section later in this chapter.
Creating a Data Browser Application
The preceding demonstration shows the easiest type of data binding retrieving an entire table and displaying it in a DataGrid control. But what about displaying data one record at a time? To do that, you'll have to use a combination of TextBox controls, Button controls, and code.
To create a data browser application to view records in the customer table one record at a time, do the following.
This application is limited at this point, you can view only a single record, and once again, you can't change data or create new customer records. But this application is a good start. We build on it in the next few demonstrations, adding capabilities that transform the simple data browser into a real database application with the ability to manipulate data.
Even though this application isn't complete yet, you can already see the power of data binding in .NET it's much more flexible and granular than the data-binding options provided in VB6. For example, the ability to manage the process of binding entirely in code offers a great deal of flexibility.
Next you'll need to add code to enable you to navigate from one record to the next. To do so, do the following.
The BindingContext object provides navigational capabilities for a data-bound application. If you've created data-bound applications in previous versions of Visual Basic, you know that the Data control was responsible for navigating from one record to the next. In the .NET framework, however, the BindingContext object has been factored out of the process of data binding. (Put simply, factoring out an object entails taking one large object and breaking its functionality into two or more simpler objects.) In object design, a software designer typically factors out functionality when an object becomes too complex or in cases where more granular access to programmatic functionality would provide more flexibility for the developer.
So in the case of the data browser application, rather than providing one giant Data object that is responsible for querying, updating, navigating, and binding fields to user interface controls, Windows Forms and ADO.NET provide separate objects for each of these capabilities. The way that ADO.NET factors out data access functionality is a key theme of the .NET framework, and it's one that we return to repeatedly throughout this book.
The BindingContext object is a member of the Windows Forms family of objects (specifically, a member of the System.Windows.Forms namespace in the .NET framework). It has a number of useful properties and methods. For example, you can use the BindingContext property to determine how many records exist in the data source, as follows.
Performing Binding Programmatically
In Windows Forms, you can perform data binding programmatically. Doing so gives you added flexibility in situations where the arrangement of fields isn't known at design time, or if you simply want to express the relationship between bound controls and data fields explicitly in code rather than in the properties window.
To create a binding to a UI control, use the Add method of the DataBindings collection contained by each Windows Forms control. For example, to assign bindings programmatically in the data browser application, amend Form1's Load event to read as shown in Listing 1.1.
Listing 1.1 Programmatically clearing and resetting the data bindings of the data browser application
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load txtFirstName.DataBindings.Clear() txtLastName.DataBindings.Clear() txtFirstName.DataBindings.Add("Text", DsCustomer1, "tblCustomer.LastName") txtLastName.DataBindings.Add("Text", DsCustomer1, "tblCustomer.FirstName") SqlDataAdapter1.Fill(DsCustomer1) ShowDataStatus() End Sub
Note that the calls to the Clear method of the controls' DataBindings collections aren't necessary in every application you create; they're necessary only in this case because you defined data bindings by using the Properties window previously. As an alternative to clearing the data bindings by using the Clear method, you could have instead removed the DataBindings assignments you originally made in the Properties window.
The Add method of the DataBindings collection takes three parameters a property of the control to bind to, a data source object (typically, but not necessarily, a DataSet), and a reference to a member of the data source object that provides the data. When you run the application after making this change to the Load event of the form, the bindings for the data browser application should have been reversed each customer's last name now appears first, and the first name appears in the second text box.
About Data-Aware Controls in .NET
A data-aware control is any control that has a DataBindings collection. The DataBindings property refers to any one of a number of data types, including (but not limited to) a relational data source.
The DataBindings property connects (or "binds") the user-interface control to the data source. The user-interface control is therefore said to be bound to the database through the data control.
In previous versions of Visual Basic, a relatively limited subset of user-interface controls could be bound to data sources. For those controls that could be bound to data, the options were very limited a developer could generally bind only to data sources for which an ADO provider existed. For non-data-aware controls, a developer had to write tedious, repetitive code to perform data binding manually. In .NET, nearly every Windows Forms control can be data-bound, including complex controls such as the Windows Forms TreeView control. Even better, the developer isn't limited to relational data sources, or even to data sources that Visual Studio and ADO.NET know about. Any object that implements the .NET IList interface can be bound to data, including DataSets, as we've already shown, and more mundane constructs such as many types of arrays and collections.
Updating Records in the Data Browser Application
So far, you've easily been able to retrieve and browse data from the database examples presented. You've also been able to make changes to the data in the user interface after a fashion. However, the changes you've made haven't been saved (or committed) to the database. It seems intuitive that, in a data-bound user interface, updates would happen automatically the various data controls provided in previous versions of Visual Basic certainly had no trouble making them. Why doesn't data binding in .NET Windows Forms work this way?
Committing updates back to a data source in a bound user interface requires a single line of code in Windows Forms for several good reasons mainly flexibility and performance. To understand why, it's helpful to have an understanding of how the DataSet object works in .NET.
Take a look at Figure 1.15. This diagram shows the relationship between the form, the DataSet object, and the database in a data-bound Windows Forms application.
Figure 1.15. A diagram of the relationship between a bound form, the DataSet object it contains, and the database
In the data browser application we've created, data initially resides in a database. It is then extracted from the database and stored in memory in the DataSet object. The form, which is bound to fields in the data table contained in the DataSet object, detects that new data has appeared and automatically displays the contents of data fields in bound controls.
In a data-bound application, changing the contents of a bound control affects the DataSet changing the contents of a text box, changing the value of the row in the data table contained in the DataSet object. But the changes in the DataSet aren't copied back to the database and stored persistently until you explicitly tell the DataSet to do so (by calling the DataSet's Update method). Although this instruction might seem like a needless extra step (you never had to do it with the data controls provided by previous versions of Visual Basic), it's actually a powerful feature of .NET. Why? Because you don't need to update until it's appropriate to do so and while the user is editing data, the application doesn't maintain a connection to the database.
Listing 1.2 shows a pair of modified event procedures that enable editing in the data browser application.
Listing 1.2 Saving data by updating the DataSet object as the user navigates in the data browser application
Private Sub btnNext_Click(ByVal sender As Object, ByVal e As EventArgs) _ Handles btnNext.Click Me.BindingContext(DsCustomer1, "tblCustomer").Position += 1 SqlDataAdapter1.Update(DsCustomer1) ShowDataStatus() End Sub Private Sub btnPrevious_Click(ByVal sender As Object, ByVal e As _ EventArgs) Handles btnPrevious.Click Me.BindingContext(DsCustomer1, "tblCustomer").Position = 1 SqlDataAdapter1.Update(DsCustomer1) ShowDataStatus() End Sub
Of course, updating each record as the user navigates from one record to the next isn't necessary. Because you have programmatic control of when the DataSet is updated, you could instead choose to commit changes back to the database when a user clicks a Save button or menu command. Or you can put off updating entirely until several records have been changed this proce dure is known as batch updating. In ADO.NET writing extra code to perform batch updates isn't necessary. It's all handled by the DataSet object (which stores the data in memory) and the SQLDataAdapter object (which is responsible for performing the necessary database commands to ensure that the correct view of data is displayed and that data is inserted, updated, and deleted properly). We consider further the relationship between these objects in Chapters 5 and 6.
Creating New Records in a Data-Bound Form
To create a new record in a data-bound Windows Forms application, use the AddNew method of the form's BindingContext object. When you execute this method, any bound controls are cleared, allowing new data to be entered. When new data has been entered, you commit the new record back to the database by executing the Update method of the DataAdapter object (as in the preceding example).
To add the ability to create new records in the data browser application, do the following.
Note that, although the Next and Previous navigation buttons perform an update on the DataSet, you don't need to update the DataSet explicitly after you create a new record navigating off the new record is sufficient. However, if you exit the application after creating a new record, but before committing it back to the database (either implicitly, by navigating to a new record, or explicitly, by calling the Update method of the DataAdapter object), the data in the new record will be lost.
Generally, you should provide a way to cancel an edit if a new record is created or an existing record is changed. To do so, use the CancelCurrentEdit method of the BindingContext object.
Deleting Records from a Data-Bound Form
To delete records from a data-bound Windows Forms application, use the RemoveAt method of the BindingContext object. This method takes a single parameter, the index number of the record you want to delete. In a bound data browser application like the one used as an example in this book, you'll nearly always want to delete the current record. To do so, pass the Position property of the BindingContext object as the parameter. Listing 1.3 shows an event procedure that deletes data.
Listing 1.3 Deleting data in the data browser application, using the RemoveAt method of the BindingContext object
Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As _ EventArgs) Handles btnDelete.Click If MsgBox("Whoa bubba, you sure?", MsgBoxStyle.YesNo, _ "Delete record") = MsgBoxResult.Yes Then With Me.BindingContext(DsCustomer1, "tblCustomer") .RemoveAt(.Position) End With End If End Sub
This code is based on the earlier creation of a button called btnDelete. Note that this procedure asks users if they really want to delete a record; this query is a good practice, especially if your user interface is constructed in such a way that users easily could accidentally click on the Delete button. (Note, however, that displaying a message box isn't the only way to handle the problem of accidental deletions. A more sophisticated application might provide "undo" functionality that lets users back up if they make a mistake. Constructing this kind of feature is beyond the scope of this chapter, but it's something to consider.)
Note that the RemoveAt method is smart enough not to throw an error when you call it in inappropriate situations (as when there's no data or after the bound controls are cleared following a call to AddNew). This capability is a vast improvement over the data controls provided by previous versions of Visual Basic, which forced you to write tedious code to catch the many errors that could occur when a user did something unexpected.
Validating Data Entry in a Data-Bound Form
In database programming, validation ensures that data entered into the system conforms to rules defined by the design of your application. These rules are called validation rules. One way to implement validation when you're programming with a bound Windows Forms application is to write code in the RowUpdating event of the DataAdapter object. This event is triggered just before a row is updated (a corresponding event, RowUpdated, is triggered immediately after a row is updated). By placing validation code in the RowUpdating event, you can be sure to catch any change made to the data, no matter which part of the application is responsible for it.
The key to using the RowUpdating event effectively is to work with the properties and methods of its event argument. This object is an instance of System.Data.SqlClient.SqlRowUpdatingEventArgs. In addition to inspecting the command associated with the row update (through the object's Command property), you can inform the data adapter to skip an update and roll back changes to bound controls. Listing 1.4 shows this approach for the data browser application we've been creating throughout this chapter.
Listing 1.4 Performing row-level validation by handling the RowUpdating property of the DataAdapter object
Private Sub SqlDataAdapter1_RowUpdating(ByVal sender As Object, _ ByVal e As System.Data.SqlClient.SqlRowUpdatingEventArgs) _ Handles SqlDataAdapter1.RowUpdating If e.Row.Item("FirstName").Length = 0 Or e.Row.Item("LastName").Length = 0 Then MsgBox("Change not saved; customer must have a first and last name.") e.Status = UpdateStatus.SkipCurrentRow() e.Row.RejectChanges() End If End Sub
Passing the enumerated value UpdateStatus.SkipCurrentRow to the Status property of the event argument tells the data adapter to abort the operation that is, to abort the update to the data, because it didn't pass the validation rule. But simply aborting the data operation isn't enough at this point, you have a blank text box in the user interface (and a corresponding blank field in the DataSet object). To resolve this problem, call the RejectChanges method of the Row object contained in the event argument. Doing so refreshes the bound user interface and tells the DataSet object that this row no longer needs to be reconciled with the database. All is well; you can now go on editing, and the data is safe.
Validation at the Database Engine Level
In addition to performing validation at the time data is entered, remember that you can also perform validation at the database engine level. Such validation is usually more reliable than doing it when entering data because the validation is applied regardless of the kind of client process responsible for changing the data. You don't have to remember to implement the validation rule in every software application that accesses a particular table. But validation at the database engine level is less flexible it's nearly impossible to override, and it's also much more primitive (typically limited to preventing empty values from being entered in fields). Additionally, you can generally perform database engine validation only at the field level; you can't have database engine validation rules that, for example, are based on a comparison between two fields (unless the comparison is a primary/foreign key constraint or implemented with a server-side procedure such as a trigger).
Database engine validation is a function of database design. For example, suppose that you want to ensure that a customer is never entered into tblCustomer without a first and last name. To do so, set up a database engine level validation rule, as follows.
From this point on, no software process that uses this database can enter a customer record that lacks either a first or last name. (Any attempt to do so will cause an exception to be thrown.)