A Simple Example

A Simple Example

Most any production program requires code that lets users or the program itself navigate through records. This navigational code is fairly easy to write, but as I mentioned, in the .NET world navigation is quite different from how navigation was accomplished in classic ADO. Here's a simple program that loads the Employees table from the Northwind database and permits a user to scroll through the records. The finished program is shown in Figure 12-2.

Figure 12-2

The BindingContext program in action.

To start, create a new Windows application project named BindingContext. Draw four text boxes, two buttons, and a label on the default form, as shown in Figure 12-3.

Figure 12-3

The BindingContext form.

Add a SqlDataAdapter object, use the Northwind connection we created in Chapter 10, "Data Access with ADO.NET," and select all the fields from the Employees table. SqlConnection1 will automatically be added for us. Right-click the SqlDataAdapter object, select Generate Dataset, and then click OK in the Generate Dataset dialog box, which will insert the DataSet11 object for us. Now set the properties for the controls you added to the form as indicated in Table 12-1.

Table 12-1  Properties and Values for the BindingContext Controls

Object	Property	Value
Text box	Name	tbFirstName
	Text	""
Text box	Name	tbLastName
	Text	""
Text box	Name	tbTitle
	Text	""
Text box	Name	tbNotes
	MultiLine	True
	Text	""
Button	Name	btnPrevious
	Text	<<
Button	Name	btnNext
	Text	>>
Label	Name	lblPosition
	Backcolor	Lime
	Text	""
	TextAlign	MiddleCenter

Add the Code

We need to check the current position as well as the number of records in various routines. To accomplish this, we'll define two class-level variables in the program. Here's the code to add:

Public Class Form1 Inherits System.Windows.Forms.Form Dim iRecordCount As Integer Dim iCurrentPosition As Integer

When the form class is loaded, we want to bind our controls as well as add two event handlers. The routine setUpData is called once do to this, and then the updateLabel routine is called to display the current number of records present.

Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the 'InitializeComponent() call setUpData() updateLabel() End Sub Private Sub setUpData() 'Fill our dataset SqlDataAdapter1.Fill(DataSet11, "Employees") '--Bind the text boxes tbLastName.DataBindings.Add("Text", DataSet11, _ "Employees.LastName") tbFirstName.DataBindings.Add("Text", DataSet11, _ "Employees.FirstName") tbTitle.DataBindings.Add("Text", DataSet11, _ "Employees.Title") tbNotes.DataBindings.Add("Text", DataSet11, _ "Employees.Notes") Dim tbBackground() As Control = {tbLastName, tbFirstName, _ tbTitle, tbNotes} Dim tbCtrl As Control For Each tbCtrl In tbBackground With tbCtrl .BackColor = System.Drawing.Color.BlanchedAlmond .ForeColor = System.Drawing.Color.DarkGray .Enabled = False End With Next 'Add handlers to check button navigation AddHandler btnNext.Click, AddressOf Me.ValidateRecords AddHandler btnPrevious.Click, AddressOf Me.ValidateRecords End Sub Private Sub ValidateRecords(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnPrevious.Click '-- Get status iRecordCount = Me.BindingContext(DataSet11, _ "Employees").Count iCurrentPosition = Me.BindingContext(DataSet11, _ "Employees").Position + 1 '-- Reasonability check for buttons -- If iRecordCount <= 1 Then btnPrevious.Enabled = False btnNext.Enabled = False updateLabel() Exit Sub End If 'Back button pressed If (sender.Equals(btnPrevious)) Then 'Can we go backwards? If iCurrentPosition > 1 Then Me.BindingContext(DataSet11, _ "Employees").Position -= 1 Else btnPrevious.Enabled = False End If End If 'Forward button pressed If (sender.Equals(btnNext)) Then If iRecordCount > iCurrentPosition Then Me.BindingContext(DataSet11, _ "Employees").Position += 1 Else btnNext.Enabled = False End If End If '-- Get Status after move -- iCurrentPosition = Me.BindingContext(DataSet11, _ "Employees").Position + 1 If iRecordCount = iCurrentPosition Then btnNext.Enabled = False Else btnNext.Enabled = True End If If iCurrentPosition = 1 Then btnPrevious.Enabled = False Else btnPrevious.Enabled = True End If updateLabel() End Sub Private Sub updateLabel() iRecordCount = Me.BindingContext(DataSet11, _ "Employees").Count iCurrentPosition = Me.BindingContext(DataSet11, _ "Employees").Position + 1 If iRecordCount <= 1 Then lblPosition.Text = "No Records" Else lblPosition.Text = "Record " & iCurrentPosition & _ " of " & iRecordCount End If End Sub

How the Code Works

The setUpData routine is called only once, when the form is loaded. The DataSet object is filled, and the text boxes are bound to their respective columns.

Private Sub setUpData() 'Fill our dataset SqlDataAdapter1.Fill(DataSet11, "Employees") '-Bind the text boxes tbLastName.DataBindings.Add("Text", DataSet11, _ "Employees.LastName") tbFirstName.DataBindings.Add("Text", DataSet11, _ "Employees.FirstName") tbTitle.DataBindings.Add("Text", DataSet11, _ "Employees.Title") tbNotes.DataBindings.Add("Text", DataSet11, _ "Employees.Notes")

I added this setUpData routine to give you some ideas for your own applications. We dimension the tbBackground array of type Control. The four text boxes are then added as elements of the array. Then we iterate through the text box controls in the array and set three properties for each. We could have set these properties in the property box in the IDE, but seeing this example might give you ideas for other applications. In classic Visual Basic, I always used control arrays to streamline code that had to perform similar operations on several visual controls. Because Visual Basic .NET no longer provides a control array, we can easily build our own.

Dim tbBackground() As Control = {tbLastName, tbFirstName, _ tbTitle, tbNotes} Dim tbCtrl As Control For Each tbCtrl In tbBackground With tbCtrl .BackColor = System.Drawing.Color.BlanchedAlmond .ForeColor = System.Drawing.Color.DarkGray .Enabled = False End With Next

Next we want to add two event handlers, one for the Click event of each button. When the user clicks either the btnPrevious or btnNext button, the ValidateRecords event will fire.

'Add handlers to check button navigation AddHandler btnNext.Click, AddressOf Me.ValidateRecords AddHandler btnPrevious.Click, AddressOf Me.ValidateRecords

As I noted, the ValidateRecords routine is called when either button is clicked. We want to let the ValidateRecords routine handle record navigation so that we don't have to duplicate code in both of the buttons' Click events. Notice that the ValidateRecords routine's signature is the same as a Click event. The first operation the routine performs is to update the class-level variables that hold the number of records that are in the table and indicate which is the current record.

Notice also that we retrieve both the number of records as well as the current position within the table from the BindingContext object. We tell the BindingContext object which data source we are interested in. The BindingContext object, and not the data set in our case, manages all of the positional information.

Private Sub ValidateRecords(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnPrevious.Click '-- Get Status iRecordCount = Me.BindingContext(DataSet11, _ "Employees").Count iCurrentPosition = Me.BindingContext(DataSet11, _ "Employees").Position + 1

If the table doesn't have any records, the buttons are disabled, the label is updated, and the routine is exited.

'-- Reasonability check for buttons -- If iRecordCount <= 1 Then btnPrevious.Enabled = False btnNext.Enabled = False updateLabel() Exit Sub End If

Either button's Click action can call the ValidateRecords routine. We can easily tell which button fired the event by calling the sender.Equals method and passing in the button as a parameter. If the user clicked the previous record button, the code block determines whether the current position is greater than the first record. If it is, the button is enabled and we go back a record. If the current record is the first record, however, we have nowhere to go, so the button is disabled to give the user a visual cue of the position. When we move between records, BindingContext is the object that moves the internal cursor to the next record and performs the synchronization.

'Back button pressed If (sender.Equals(btnPrevious)) Then 'Can we go backwards? If iCurrentPosition > 1 Then Me.BindingContext(DataSet11, _ "Employees").Position -= 1 Else btnPrevious.Enabled = False End If End If

The same logic checks are made to determine whether the btnNext button was clicked. If the current record is less than the total records, the BindingContext object moves to the next record within the table.

'Forward button pressed If (sender.Equals(btnNext)) Then If iRecordCount > iCurrentPosition Then Me.BindingContext(DataSet11, "Employees").Position += 1 Else btnNext.Enabled = False End If End If

After the navigation, the current position is checked, and if we are at the first record, the btnPrevious button is disabled. Likewise, if the last record is current, the btnNext button is disabled. Finally the updateLabel routine is called to provide visual feedback about where we are within the table.

'-- Get status after move -- iCurrentPosition = Me.BindingContext(DataSet11, _ "Employees").Position + 1 If iRecordCount = iCurrentPosition Then btnNext.Enabled = False Else btnNext.Enabled = True End If If iCurrentPosition = 1 Then btnPrevious.Enabled = False Else btnPrevious.Enabled = True End If updateLabel()

The updateLabel routine simply updates the label to show the position within the table as the navigation buttons are clicked.

Private Sub updateLabel() iRecordCount = Me.BindingContext(DataSet11, _ "Employees").Count iCurrentPosition = Me.BindingContext(DataSet11, _ "Employees").Position + 1 If iRecordCount <= 1 Then lblPosition.Text = "No Records" Else lblPosition.Text = "Record " & iCurrentPosition & _ " of " & iRecordCount End If End Sub

Now we know how the mechanics of binding controls to data sources work in a bit more detail. Another piece of information you need to understand is how to programmatically read and write from and to tables, columns, and fields in a data source. While we've seen some examples of updating data using the DataGrid control, we will certainly need to update data from a program as well.