Beginning Visual Basic 2005 (2006)

Chapter 16

the data, but it also increases the complexities of your programs, because you must write the code to bind the data to the controls and also write the code to navigate between records. This section takes a look at what is involved in binding data to simple controls and also how to manage the data bindings.

In this discussion, the term simple controls refers to controls that can display only one item of data at a time, such as TextBox, a Button, a CheckBox, or a RadioCheck. Controls such as ComboBox, ListBox, and DataGridView can contain more than one item of data and are not considered simple controls when it comes to data binding. Generally speaking, nonsimple controls have particular properties intended for binding to a data object such as a DataTable or Array. When binding to simple controls, you are actually binding a particular item of data to a particular property. This is usually the Text property, but it does not need to be.

BindingContext and CurrencyManager

Each form has a built-in BindingContext object that, which manages the bindings of the controls on the form. Since the BindingContext object is already built into each form, you don’t need to do anything to set it up.

The BindingContext object manages a collection of CurrencyManager objects. The CurrencyManager is responsible for keeping the data-bound controls in sync with its data source and with other databound controls that use the same data source. This ensures that all controls on the form are showing data from the same record. The CurrencyManager manages data from a variety of objects such as DataSet, DataView, DataTable, and DataSetView. Whenever you add a data source to a form, a new CurrencyManager is automatically created. This makes working with data-bound controls very convenient and simple.

The CurrencyManager gets its name because it keeps the controls current with respect to the data in the data source. The controls do not have to represent currency (money) amounts.

If you have multiple data sources in your form, you can declare a CurrencyManager variable and set it to refer to the appropriate CurrencyManager object for a given data source in the collection managed by the BindingContext object. You then have the capability to manage the data in the data-bound controls explicitly.

The following code fragment, using the DataSet object that you have been using in the previous example, defines and sets a reference to the CurrencyManager that manages the data source that contains the local authors table. First, the code declares a variable using the CurrencyManager class. Then it sets this CurrencyManager variable to the currency manager for the DataSet object (objDataSet) contained in the BindingContext object. The CType function is used to return an object that is explicitly converted. The CType function accepts two arguments: the expression to be converted and the type to which the expression is to be converted. Since the expression is to evaluate to a CurrencyManager object, CurrencyManager is specified for the type argument:

Dim objCurrencyManager As CurrencyManager objCurrencyManager = _

CType(Me.BindingContext(objDataSet), CurrencyManager)

516

Database Programming with SQL Server and ADO.NET

After you have a reference to the data source object, you can manage the position of the records using the Position property, as shown in the following example. This example advances the current record position in the objDataSet object by one record:

objCurrencyManager.Position += 1

If you wanted to move backward one record, you would use the following code:

objCurrencyManager.Position -= 1

To move to the first record contained in the DataSet object, you would use the following code:

objCurrencyManager.Position = 0

The Count property of the CurrencyManager contains the number of records in the DataSet object managed by the CurrencyManager. Therefore, to move to the very last record, you would use the following code:

objCurrencyManager.Position = objCurrencyManager.Count - 1

Notice that this code specified the Count value minus one. Since the Count property contains the actual number of records and the DataSet object has a base index of zero, you must subtract one from the Count value to get the index to the last record.

Binding Controls

When you want to bind a data source to a control, you set the DataBindings property for that control. This property accesses the ControlBindingsCollection class. This class manages the bindings for each control, and it has many properties and methods. The method of interest here is Add.

The Add method creates a binding for the control and adds it to the ControlBindingsCollection. The Add method has three arguments, and its syntax is shown here:

object.DataBindings.Add(propertyname, datasource, datamember)

In this syntax, note the following:

object represents a valid control on your form.

The propertyname argument represents the property of the control to be bound.

The datasource argument represents the data source to be bound and can be any valid object, such as a DataSet, DataView, or DataTable, that contains data.

The datamember argument represents the data field in the data source to be bound to this control.

An example of how the Add method works is shown in the following code. This example binds the column name au_fname in the objDataView object to the Text property of a text box named txt FirstName:

txtFirstName.DataBindings.Add(“Text”, objDataView, “au_fname”)

517

Chapter 16

Sometimes, after a control has been bound, you may want to change the bindings for that control. To do this, you can use the Clear method of the ControlBindingsCollection. The Clear method clears the collection of all bindings for this control. Then you can make the change you need. An example of this method is shown in the following code fragment:

txtFirstName.DataBindings.Clear()

Now that you have had a look at the BindingContext, CurrencyManager, and ControlBindingsCollection objects, learn how all of these pieces fit and work together in a practical hands-on exercise.

Binding Example

The following Try It Out will demonstrate not only how to use the BindingContext, Currency Manager and ControlBindingsCollection objects but also how to use the DataView, SqlCommand, and SqlParameter classes.

You will be using the query from the previous example as the base for your new query and will display all authors’ first and last names, as well as their book titles and the prices of their books. However, this example differs from the last one in that this will display only one record at a time.

You will use the CurrencyManager object to navigate the records in the DataView object and provide the functionality to move forward and backward as well as to the first and last records.

Try It Out

Binding Simple Controls

1.Create a new Windows Application project called BindingExample.

2.Drag a ToolTip control from the toolbox and drop it on your form to add it to the designer. Set the various form properties as follows:

Set FormBorderStyle to FixedDialog.

Set MaximizeBox to False.

Set MinimizeBox to False.

Set Size to 430, 360.

Set StartPosition to CenterScreen.

Set Text to Binding Controls.

3.You are going to add objects to the form, so that the form ends up looking like Figure 16-6.

The steps that follow provide property settings to produce an exact replica of this form. However, the “cosmetic” properties are not as important; if you wish, you can approximate the layout visually. It is crucial, however, to use the same control names as those used here in your own application.

4.Add a GroupBox control to the form. Set the GroupBox1 properties according to the following list:

Set Location to 8, 8.

Set Size to 408, 128.

Set Text to Authors && Titles.

518

Database Programming with SQL Server and ADO.NET

Figure 16-6

Note that to have an ampersand (&) displayed in the GroupBox title you have to write &&; because a single & causes the character following it to be underlined.

5.Using this list, add the required controls to GroupBox1 and set their properties:

Add a Label control. Name it Label1 and set its Location to 8, 26; Size to 64, 16; Text to

Last Name; AutoSize to False.

Add a Label control. Name it Label2 and set Location to 8, 50; Size to 64, 16; Text to

First Name; AutoSize to False.

Add a Label contol. Name it Label3 and set Location to 8, 74; Size to 56, 16; Text to

Book Title; AutoSize to False.

Add a Label control. Name it Label4 and set Location to 8, 98; Size to 64, 16; Text to Price; AutoSize to False.

Add a TextBox control. Name it txtLastName and set Location to 72, 24; Size to 88, 20; ReadOnly to True.

Add a TextBox control. Name it txtFirstName and set Location to 72, 48; Size to 88, 20; ReadOnly to True.

Add a TextBox control. Name it txtBookTitle and set Location to 72, 72; Size to 328, 20.

Add a TextBox control. Name it txtPrice and set Location to 72, 96; Size to 48, 20.

6.Now add a second GroupBox and set its properties according to this list:

Set Location to 8, 144.

Set Size to 408, 168.

Set Text to Navigation.

519

Chapter 16

7.In GroupBox2, add the following controls:

Add a Label control. Name it Label5 and set Location to 8, 23; Size to 64, 16; Text to Field; AutoSize to False.

Add a Label control. Name it Label6 and set Location to 8, 48; Size to 80, 16; Text to Search Criteria; AutoSize to False.

Add a ComboBox control. Name it cboField and set Location to 88, 21; Size to 88, 21; DropDownStyle to DropDownList.

Add a TextBox control. Name it txtSearchCriteria and set Location to 88, 48; Size to 200, 20.

Add a TextBox control. Name it txtRecordPosition and set Location to 152, 130; Size to 85, 20; TabStop to False; TextAlign to Center.

Add a Button control. Name it btnPerformSort and set Location to 304, 16; Size to 96, 24; Text to Perform Sort.

Add a Button control. Name it btnPerformSearch and set Location to 304, 48; Size to 96, 24; Text to Perform Search.

Add a Button control. Name it btnNew and set Location to 40, 88; Size to 72, 24; Text to New.

Add a Button control. Name it btnAdd and set Location to 120, 88; Size to 72, 24; Text to Add.

Add a Button control. Name it btnUpdate and set Location to 200, 88; Size to 72, 24; Text to Update.

Add a Button control. Name it btnDelete and set Location to 280, 88; Size to 72, 24; Text to Delete.

Add a Button control. Name it btnMoveFirst and set Location to 88, 128; Size to 29, 24; Text to |<; ToolTip on ToolTip1 to Move First.

Add a Button control. Name it btnMovePrevious and set Location to 120, 128; Size to 29, 24; Text to <; ToolTip on ToolTip1 to Move Previous.

Add a Button control. Name it btnMoveNext and set Location to 240, 128; Size to 29, 24; Text to >; ToolTip on ToolTip1 to Move Next.

Add a Button control. Name it btnMoveLast and set Location to 272, 128; Size to 29, 24; Text to >|; ToolTip on ToolTip1 to Move Last.

8.Finally, add a StatusStrip control. Leave its name as the default StatusStrip1, and its default location and size. Click the new StatusStrip1, and you will have an option to add a StatusLabel control in the menu. Select StatusLabel from the menu and leave the default settings.

9.When you are done, your completed form should look like the one shown in Figure 16-6.

10.Again, you need to add imports to the namespaces needed. First, remember to add references to System.Data and System.Xml. To do this, switch to Code Editor view and then insert the following lines of code at the very top:

‘Import Data and SqlClient namespaces...

Imports System.Data

Imports System.Data.SqlClient

520

Database Programming with SQL Server and ADO.NET

11.Next you need to declare the objects that are global in scope to this form, so add the following highlighted code:

Public Class Form1

‘ Declare objects...

Dim objConnection As New SqlConnection _ (“server=bnewsome;database=pubs;user id=sa;password=!p@ssw0rd!;”)

Dim objDataAdapter As New SqlDataAdapter( _

“SELECT authors.au_id, au_lname, au_fname, “ & _ “titles.title_id, title, price “ & _

“FROM authors “ & _

“JOIN titleauthor ON authors.au_id = titleauthor.au_id “ & _ “JOIN titles ON titleauthor.title_id = titles.title_id “ & _ “ORDER BY au_lname, au_fname”, objConnection)

Dim objDataSet As DataSet Dim objDataView As DataView

Dim objCurrencyManager As CurrencyManager

Be sure to update the connection string to match your settings for the user id and password, and also set the Server to the machine where SQL Server is running if it is not your local machine.

12.The first procedure you need to create is the FillDataSetAndView procedure. This procedure, along with the following ones, will be called in your initialization code. Add the following code to the form’s class, just below your object declarations:

Private Sub FillDataSetAndView()

‘Initialize a new instance of the DataSet object...

objDataSet = New DataSet()

‘Fill the DataSet object with data...

objDataAdapter.Fill(objDataSet, “authors”)

‘Set the DataView object to the DataSet object...

objDataView = New DataView(objDataSet.Tables(“authors”))

‘Set our CurrencyManager object to the DataView object...

objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)

End Sub

13.The next procedure you need to create will actually bind the controls on your form to your

DataView object:

Private Sub BindFields()

‘Clear any previous bindings...

txtLastName.DataBindings.Clear()

txtFirstName.DataBindings.Clear()

txtBookTitle.DataBindings.Clear()

txtPrice.DataBindings.Clear()

‘Add new bindings to the DataView object...

txtLastName.DataBindings.Add(“Text”, objDataView, “au_lname”) txtFirstName.DataBindings.Add(“Text”, objDataView, “au_fname”) txtBookTitle.DataBindings.Add(“Text”, objDataView, “title”) txtPrice.DataBindings.Add(“Text”, objDataView, “price”)

‘Display a ready status...

521

Chapter 16

ToolStripStatusLabel1.Text = “Ready”

End Sub

14.Now you need a procedure that will display the current record position on your form:

Private Sub ShowPosition()

‘Always format the number in the txtPrice field to include cents

Try

txtPrice.Text = Format(CType(txtPrice.Text, Decimal), “##0.00”)

Catch e As System.Exception

txtPrice.Text = “0”

txtPrice.Text = Format(CType(txtPrice.Text, Decimal), “##0.00”) End Try

‘ Display the current position and the number of records txtRecordPosition.Text = objCurrencyManager.Position + 1 & _ “ of “ & objCurrencyManager.Count()

End Sub

15.You’ve added some powerful procedures to your form. But at the moment, there is no code to call them. You want these procedures, as well as some other code, to execute every time the form loads. So return to the Form Designer, double-click the Form Designer, and add the following highlighted code to the Form_Load method. (Note that you must click an area outside of the GroupBox controls.)

Private Sub Form1_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load

‘Add items to the combo box...

cboField.Items.Add(“Last Name”) cboField.Items.Add(“First Name”) cboField.Items.Add(“Book Title”) cboField.Items.Add(“Price”)

‘Make the first item selected...

cboField.SelectedIndex = 0

‘Fill the DataSet and bind the fields...

FillDataSetAndView()

BindFields()

‘Show the current record position...

ShowPosition()

End Sub

16.Next, you add the code for your navigation buttons. You need to switch back and forth between the design and code views, double-clicking each button and then adding the code, or you can select the buttons in the Class Name combo box and then select the Click event in the Method Name combo box. Add the code as highlighted to the procedure for the btnMoveFirst button first:

Private Sub btnMoveFirst_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles btnMoveFirst.Click ‘ Set the record position to the first record...

objCurrencyManager.Position = 0

522

Database Programming with SQL Server and ADO.NET

‘ Show the current record position...

ShowPosition()

End Sub

17.Add code as highlighted to the btnMovePrevious button next:

Private Sub btnMovePrevious_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles btnMovePrevious.Click

‘Move to the previous record...

objCurrencyManager.Position -= 1

‘Show the current record position...

ShowPosition()

End Sub

18.The next procedure you want to add code to is the btnMoveNext procedure:

Private Sub btnMoveNext_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles btnMoveNext.Click

‘Move to the next record...

objCurrencyManager.Position += 1

‘Show the current record position...

ShowPosition()

End Sub

19.The final navigation procedure that you need to code is the btnMoveLast procedure:

Private Sub btnMoveLast_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles btnMoveLast.Click

‘Set the record position to the last record...

objCurrencyManager.Position = objCurrencyManager.Count - 1

‘Show the current record position...

ShowPosition()

End Sub

20.At this point, you have entered a lot of code and are probably anxious to see the results of your work. Run the project to see how your DataView object gets bound to the controls on the form and to see the CurrencyManager object at work as you navigate through the records.

After your form displays, you should see results similar to Figure 16-7. The only buttons that work are the navigation buttons, which change the current record position. Test your form by navigating to the next and previous records and by moving to the last record and the first record. Each time you move to a new record, the text box between the navigation buttons will be updated to display the current record.

While you are on the first record, you can try to move to the previous record, but nothing will happen, because you are already on the first record. Likewise, you can move to the last record and try to navigate to the next record, and nothing will happen, because you are already on the last record.

If you hover your mouse pointer over the navigation buttons, you will see a ToolTip indicating what each button is for. This just provides a nicer interface for your users.

523

Chapter 16

Figure 16-7

Note that error handling has been omitted from the exercise to preserve space. You should always add the appropriate error handling to your code. Please review Chapter 9 for error-handling techniques.

How It Works: Namespaces and Object Declaration

As usual, you import the System.Data and System.Data.SqlClient namespaces. Next, you declare the objects on your form. The first three objects should be familiar to you, because you used them in your last project.

Take a closer look at the initialization of the SqlDataAdapter object. You use a constructor that initializes this object with a string value for the SelectCommand property and an object that represents a connection to the database. This constructor saves you from writing code to manipulate the SqlData Adapter properties; it’s already set up.

The SELECT statement that you use here is basically the same as in the previous project, except that you add a couple more columns in the select list (the list of columns directly following the word SELECT).

The au_id column in the select list is prefixed with the table name authors, because this column also exists in the titleauthor table. Therefore, you must tell the database which table to get the data from for this column. This is the same for the title_id column, except that this column exists in the titles and titleauthor tables:

Dim objConnection As New SqlConnection _ (“server=bnewsome;database=pubs;user id=sa;password=!p@ssw0rd!;”)

Dim objDataAdapter As New SqlDataAdapter( _

“SELECT authors.au_id, au_lname, au_fname, “ & _ “titles.title_id, title, price “ & _

“FROM authors “ & _

“JOIN titleauthor ON authors.au_id = titleauthor.au_id “ & _ “JOIN titles ON titleauthor.title_id = titles.title_id “ & _ “ORDER BY au_lname, au_fname”, objConnection)

524

Database Programming with SQL Server and ADO.NET

Dim objDataSet As DataSet

Dim objDataView As DataView

Dim objCurrencyManager As CurrencyManager

The last two objects are new but were discussed in the section on binding. You use the DataView to customize your view of the records returned from the database, and stored in the dataset. The Currency Manager object controls the movement of your bound data, as you saw in the previous section.

How It Works: FillDataSetAndView

The first procedure you create is the FillDataSetAndView procedure. This procedure will be called several times throughout your code and will get the latest data from the database and populate your

DataView object.

The first thing you need to do is initialize a new instance of the DataSet object. You do this here because this procedure might be called more than once during the lifetime of the form. If it is, you do not want to add new records to the records already in the dataset; you always want to start afresh:

Private Sub FillDataSetAndView()

‘ Initialize a new instance of the DataSet object...

objDataSet = New DataSet()

Next, you invoke the Fill method on objDataAdapter to populate the objDataSet object. Then you specify that your DataView object will be viewing data from the authors table in the DataSet object. Remember that the DataView object allows you to sort, search, and navigate through the records in the dataset:

‘Fill the DataSet object with data...

objDataAdapter.Fill(objDataSet, “authors”)

‘Set the DataView object to the DataSet object...

objDataView = New DataView(objDataSet.Tables(“authors”))

After you initialize your DataView object, you want to initialize the CurrencyManager object. Remember that the BindingContext object is built into every Windows form and contains a collection of CurrencyManagers. The collection contains the available data sources, and you choose the DataView object:

‘ Set our CurrencyManager object to the DataView object...

objCurrencyManager = _ CType(Me.BindingContext(objDataView), CurrencyManager)

How It Works: BindFields

The next procedure that you create (BindFields) actually binds the controls on your form to your DataView object. This procedure first clears any previous bindings for the controls and then sets them to your DataView object.

It is important to clear the bindings first because, after you modify the DataView object by adding, updating, or deleting a row of data, the DataView object will show only the changed data. Therefore, after you update the database with your changes, you must repopulate our DataView object and rebind your controls. If you didn’t do this, the data that would actually be in the database and the data in the DataView may not be the same.

525