Visual Studio provides tools that make getting started with databases remarkably easy. Although the process is relatively straightforward, it does involve a lot of steps. The steps also allow several variations, so describing every possible way to build a database connection takes a long time. To make the process more manageable, the following two sections group the steps in two pieces: connecting to the data source and adding data controls to the form.

Connecting to the Data Source

To build a simple database program, start a new application and select the Data menu's Add New Data Source command to display the Data Source Configuration Wizard shown in Figure 20-1.

Figure 20.1. Select the data source type for a new connection.

Visual Studio allows you to use databases, web services, and objects as data sources for your application. The most straightforward choice is Database.

Select the type of data source you want add and click Next to select a data model on the page shown in Figure 20-2.

Figure 20.2. Pick the type of data model you want to use.

Select the type of data model that you want to use (this example assumes you pick Dataset) and click Next to select a data connection on the page shown in Figure 20-3.

Figure 20.3. Pick the data connection or click New Connection to create a new one.

If you have previously created data connections, you can select one from the drop-down list. If you have not created any data connections, click the New Connection button to open the Choose Data Source dialog shown in Figure 20-4. This dialog lets you pick the type of data source you will use. For example, you can use it to select Microsoft Access databases, ODBC data sources, Microsoft SQL Server, and Oracle databases.

Figure 20.4. Select the data source type for a new connection.

DOWNLOADING DATABASES

You don't need to have Access to use an Access database in Visual Basic. However, if you want to give Access a try, you can download a 60-day trial version at us20.trymicrosoftoffice.com.

Another popular database choice is SQL Server. You can download the free Express Edition at www.microsoft.com/express/sql.

You can also download the open source MySQL database at www.mysql.com.

Note

If the dialog shown in Figure 20-5 appears first, you can skip the dialog in Figure 20-4 as long as it has selected the correct data source type. If you want to change the data source, click the Change button shown in Figure 20-5 to open the dialog shown in Figure 20-4.

After you select a data source type, the drop-down lists available providers for that type. The data provider acts as a bridge between the application and the data source, providing methods to move data between the two. Pick a provider and click Continue to show the dialog in Figure 20-5.

Figure 20.5. Use the Add Connection dialog box to create a data connection.

Depending on the type of database you selected in Figure 20-4, this dialog box may not look exactly like Figure 20-5. For example, if you selected the SQL Server database type, the Add Connection dialog asks for a data source and server name rather than a database file name.

Enter the necessary data in the Add Connection dialog box. For a SQL Server database, select the server name, authentication method, database name, and other information. For a Microsoft Access database, enter the file name or click the Browse button shown in Figure 20-5 and find the database file. Enter a user name and password if necessary.

After you enter all of the required information, click the Test Connection button to see if the wizard can open the database. If the test fails, recheck the database path (if the database is on a network, make sure the network connection is available), user name, and password and try again.

Once you can test the database connection, click OK.

When you return to the Data Source Configuration Wizard shown in Figure 20-3, the new connection should be selected in the drop-down list. If you click the plus sign next to the "Connection string" label, the wizard shows the connection information it will use to connect the data source to the database. For example, this information might look like the following:

Provider=Microsoft.Jet.OLEDB.4.0;
DataSource=|DataDirectory|ClassRecords.mdb

When you click Next, the wizard tells you that you have selected a local database file that is not part of the project and it asks if you want to add it to the project. If you click Yes, the wizard adds the database to the project so it shows up in Project Explorer. If you plan to distribute the database with the application, you may want to do this to make it easier to manage the database and the Visual Basic source code together.

Next, the wizard asks whether you want to save the connection string in the project's configuration file. If you leave this check box selected, the wizard adds the configuration string to the project's app.config file.

The following shows the part of the configuration file containing the connection string:

<connectionStrings>
    <add name="StudentTest.My.MySettings.ClassRecordsConnectionString"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=|DataDirectory|ClassRecords.mdb; Persist Security Info=True;
Jet OLEDB:Database Password=MyPassword" providerName="System.Data.OleDb" />
</connectionStrings>

Later, the program uses the Settings.Default.ClassRecordsConnectionString values to get this value and connect to the database. You can easily make the program connect to another data source by changing this configuration setting and then restarting the application.

Note

You should never save real database passwords in the configuration file. The file is stored in plain text and anyone can read it. If you need to use a password, store a connection string that contains a placeholder for the real password. At runtime, load the connection string and replace the placeholder with a real password entered by the user.

Adding Data Controls to the Form

At this point you have defined the basic connection to the database. Visual Studio knows where the database is and how to build an appropriate connection string to open it. Now you must decide what data to pull out of the database and how to display it on the form.

Click Next to display the dialog box shown in Figure 20-6. This page shows the objects available in the database. In this example, the database contains two tables named Students and TestScores. By clicking the plus signs next to the objects, you can expand them to see what they contain. In Figure 20-6, the tables are expanded so you can see the fields they contain.

Figure 20.6. Select the database objects that you want included in the data source.

Select the database objects that you want to include in the data source. In Figure 20-6, both of the tables are selected.

When you click Finish, the wizard adds a couple objects to the application. The Solution Explorer, (which lists all of the solution's files, now lists the new file ClassRecordsDataSet.xsd. This is a schema definition file that describes the data source.

When you double-click the schema file, Visual Basic opens it in the editor shown in Figure 20-7. This display shows the tables defined by the schema and their fields.

Figure 20.7. The Schema Editor shows the tables defined by the schema and their relationships.

The line between the files with the little key on the left and the infinity symbol on the right indicates that the tables are joined by a one-to-many relationship. In this example, the Students.StudentId field and TestScores.StudentId field form a foreign key relationship. That means every StudentId value in the TestScores table must correspond to some StudentId value in the Students table. When you double-click the relationship link or right-click it and select Edit Relation, the editor displays the dialog box shown in Figure 20-8. You can use this editor to modify the relation.

Figure 20.8. Use this dialog box to edit relationships among data source tables.

At the bottom of the tables shown in Figure 20-7, you can see two table adapter objects containing the labels Fill, GetData(). These represent data adapter objects that the program will later use to move data from and to the data source.

In addition to adding the schema file to Solution Explorer, the Data Source Configuration Wizard also added a new DataSet object to the Data Sources window shown in Figure 20-9. (If this window is not visible, select the Data menu's Show Data Sources command.)

Figure 20.9. The Data Sources window lists the new data source.

You can use the plus and minus signs to expand and collapse the objects in the DataSet. In Figure 20-9, the DataSet is expanded to show its tables, and the tables are expanded to show their fields. Notice that the TestScores table is listed below the Students table because it has a parent/child relationship with that table.

It takes a lot of words and pictures to describe this process, but using the wizard to build the data source is actually quite fast. After you have created the data source, you can build a simple user interface with almost no extra work. Simply drag objects from the Data Sources window onto the form.

When you click and drag a table from the Data Sources window onto the form, Visual Basic automatically creates BindingNavigator and DataGridView controls, and other components to display the data from the table. Figure 20-10 shows the result at runtime.

Figure 20.10. Drag and drop a table from the Data Sources window onto the form to create a simple DataGridView.

Instead of dragging an entire table onto the form, you can drag individual database columns. In that case, Visual Basic adds controls to the form to represent the column. Figure 20-11 shows the columns from the Students table dragged onto a form.

Figure 20.11. Drag and drop table columns onto a form to create a record-oriented view instead of a grid.

If you select a table in the Data Sources window, a drop-down arrow appears on the right. Open the drop-down to give the table a different display style, as shown in Figure 20-12. For example, if you set a table's style to Details and drag the table onto a form, Visual Basic displays the table's data using a record detail view similar to the one shown in Figure 20-11 instead of the grid shown in Figure 20-10.

Figure 20.12. Use the drop-dwon in the Data Source window to give a table a different display style.

Similarly, you can change the display styles for specific database columns. Select a column in the Data Sources window and click its drop-down arrow to make it display in a text box, label, link label, combo box, or other control. Now, when you drag the column onto a form, or when you drag the table onto the form to build a record view, Visual Basic uses this type of control to display the column's values.

When you drag database tables and columns from the Data Sources window onto a form, Visual Basic does a lot more than simply placing a DataGridView control on a form. It also creates about two dozen other controls and components. Five of the more important of these objects are the DataSet, TableAdapter, TableAdapterManager, BindingSource, and BindingNavigator.

The program stores data in a DataSet object. A single DataSet object can represent an entire database. It contains DataTable objects that represent database tables. Each DataTable contains DataRow objects that represent rows in a table, and each DataRow contains items representing column values for the row.

The TableAdapter object copies data between the database and the DataSet. It has methods for performing operations on the database (such as selecting, inserting, updating, and deleting records). Hidden inside the TableAdapter is a connection object that contains information on the database so that the TableAdapter knows where to find it.

The TableAdapterManager coordinates updates among different TableAdapters. This is most useful for hierarchical data sets, a topic that is outside the scope of this book. The wizard-generated code also uses the TableAdapterManager to update the single data set it creates.

The BindingSource object encapsulates all of the DataSet object's data and provides programmatic control functions. These perform such actions as moving through the data, adding and deleting items, and so forth.

The BindingNavigator provides a user interface so the user can control the BindingSource.

Figure 20-13 shows the relationships among the DataSet, TableAdapter, BindingSource, and BindingNavigator objects. The BindingNavigator is the only one of these components that has a presence on the form. It is connected to the BindingSource with a dotted arrow to indicate that it controls the BindingSource but does not actually transfer data back and forth. The other arrows represent data moving between objects.

Figure 20.13. Visual Basic uses DataSet, TableAdapter, BindingSource, and BindingNavigator objects to display data.

Even all these objects working together don't quite do everything you need to make the program display data. When it creates these objects, Visual Basic also adds the following code to the form:

public Class Form1
    Private Sub StudentsBindingNavigatorSaveItem_Click(
    ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles StudentsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.StudentsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ClassRecordsDataSet)

End Sub

    Private Sub Form1_Load(ByVal sender As System.Object,
    ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ClassRecordsDataSet.Students'
        ' table. You can move, or remove it, as needed.
        Me.StudentsTableAdapter.Fill(Me.ClassRecordsDataSet.Students)
    End Sub
End Class
                                                  

The StudentsBindingNavigatorSaveItem_Click event handler fires when the user clicks the BindingNavigator object's Save tool. This routine makes the TableAdapter save any changes to the Students table to the database.

The Form1_Load event handler makes the TableAdapter copy data from the database into the DataSet when the form loads.

Visual Basic builds all this automatically, and if you ran the program at this point, it would display data and let you manipulate it. It's still not perfect, however. It doesn't perform any data validation, and it will let you close the application without saving any changes you have made to the data. It's a pretty good start for such a small amount of work, however.

If you want a simple program that can display and modify data, then the solution described in the previous sections may be good enough. In that case, you can let Visual Basic do most of the work for you, and you don't need to dig into the lower-level details of database access.

You can also use objects similar to those created by Visual Basic to build your own solutions. You can create your own DataSet, TableAdapter, BindingSource, and BindingNavigator objects to bind controls to a database. (You can even modify the controls supplied by Visual Basic by overriding their properties and methods, although that's a very advanced topic so it isn't covered here.)

If you need to manipulate the database directly with code, it doesn't necessarily make sense to create all these objects. If you simply want to modify a record programmatically, it certainly doesn't make sense to create DataGridView, BindingNavigator, and BindingSource objects.

For cases such as this, Visual Basic provides several other kinds of objects that you can use to interact with databases. These objects fall into the following four categories:

Data container and adapter classes are generic and work with different kinds of databases, whereas different types of connection and command objects are specific to different kinds of databases. For example, the connection objects OleDbConnection, SqlConnection, OdbcConnection, and OracleConnection work with Object Linking and Embedding Database (OLE DB); SQL Server, including SQL Server Express; Open Database Connectivity (ODBC); and Oracle databases, respectively. The SQL Server and Oracle objects work only with their specific brand of database, but they are more completely optimized for those databases and may give better performance.

Aside from the different database types they support, the various objects work in more or less the same way. The following sections explain how an application uses those objects to move data to and from the database. They describe the most useful properties, methods, and events provided by the connection, transaction, data adapter, and command objects.

Later sections describe the DataSet and DataView objects and tell how you can use them to bind controls to display data automatically.

An application uses three basic objects to move data to and from a database: a connection, a data adapter, and a data container such as a DataSet.

The connection object defines the connection to the database. It contains information about the database's name and location, any user name and password needed to access the data, database engine information, and flags that determine the kinds of access the program will need.

The data adapter object defines a mapping from the database to the DataSet. It determines what data is selected from the database, and which database columns are mapped to which DataSet columns.

The DataSet object stores the data within the application. It can hold more than one table and can define and enforce relationships among the tables. For example, the database used in the earlier examples in this chapter has a TestScores table that has a StudentId field. The values in this field must be values listed in the Students table. This is called a foreign key constraint. The DataSet can represent this constraint and raise an error if the program tries to create a TestScores record with a StudentId value that does not appear in the Students table. The section "Constraints" later in this chapter says more about constraints.

When the connection, data adapter, and DataSet objects are initialized, the program can call the data adapter's Fill method to copy data from the database into the DataSet. Later it can call the data adapter's Update method to copy any changes to the data from the DataSet back into the database. Figure 20-14 shows the process.

Figure 20.14. An application uses connection, data adapter, and DataSet objects to move data to and from the database.

If you compare Figure 20-14 to Figure 20-13, you'll see several similarities. Both approaches use an adapter to copy data between the database and a DataSet. At first glance, it may seem that Figure 20-13 doesn't use a connection object, but actually the TableAdapter contains a connection object internally that it uses to access the database.

One major difference is that Figure 20-13 uses a BindingSource to provide an extra layer between the DataSet and the program's controls. It also includes a Binding Navigator object that lets the user control the BindingSource to move through the data.

As in the previous example, a program using the objects shown in Figure 20-14 could call the data adapter's Fill method in a form's Load event handler. Later it could call the Update method when the user clicked a Save button, in the form's FormClosing event handler, or whenever you wanted to save the data.

The connection object manages the application's connection to the database. It allows a data adapter to move data in and out of a DataSet.

The different flavors of connection object (OleDbConnection, SqlConnection, OdbcConnection, OracleConnection, and so on) provide roughly the same features, but there are some differences. Check the online help to see if a particular property, method, or event is supported by one of the flavors. The web page msdn.microsoft.com/32c5dh3b.aspx provides links to pages that explain how to connect to SQL Server, OLE DB, ODBC, and Oracle data sources. Other links lead to information on the SqlConnection, OleDbConnection, and OdbcConnection classes.

If you will be working extensively with a particular type of database (for example, SQL Server), you should also review the features provided by its type of connection object to see if it has special features for that type of database.

Some connection objects can work with more than one type of database. For example, the OleDbConnection object works with any database that has an OLE DB (Object Linking and Embedding Database) provider. Similarly the OdbcConnection object works with databases that have ODBC (Open Database Connectivity) providers such as MySQL.

Generally, connections that work with a specific kind of database (such as SqlConnection and OracleConnection) give better performance. If you think you might later need to change databases, you can minimize the amount of work required by sticking to features that are shared by all the types of connection objects.

The following table describes the most useful properties provided by the OleDbConnection and SqlConnection classes.

The ConnectionString property includes many fields separated by semicolons. The following text shows a typical ConnectionString value for an OleDbConnection object that will open an Access database. The text here shows each embedded field on a separate line, but the actual string would be all run together in one long line.

Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Registry Path=;
Jet OLEDB:Database Locking Mode=1;
Data Source="C:PersonnelDataPersonnel.mdb";
Mode=Share Deny None;
Jet OLEDB:Engine Type=5;
Provider="Microsoft.Jet.OLEDB.4.0";
Jet OLEDB:System database=;

Jet OLEDB:SFP=False;
persist security info=False;
Extended Properties=;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Create System Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
User ID=Admin;
Jet OLEDB:Global Bulk Transactions=1"

Many of these properties are optional and you can omit them. Remembering which ones are optional (or even which fields are allowed for a particular type of connection object) is not always easy. Fortunately, it's also not necessary. Instead of typing all these fields into your code or in the connection control's ConnectString property in the Properties window, you can let Visual Basic build the string for you.

Simply follow the steps described in the section "Connecting to the Data Source" earlier in this chapter. After you build or select the database connection, click the plus sign in Figure 20-3 to see its connection string. Use the mouse to highlight the connection string and then press Ctrl+C to copy it to the clipboard.

The following code fragment shows how a program can create, open, use, and close an OleDbConnection object. The code assumes the database name is in the text box txtDatabase.

' Make the connect string.
Dim connect_string As String =
    "Provider=Microsoft.Jet.OLEDB.4.0;" &
    "Data Source=""" & txtDatabase.Text & """;" &
    "Persist Security Info=False"

' Open a database connection.
Dim conn_people As New OleDb.OleDbConnection(connect_string)
conn_people.Open()

' Do stuff with the connection.
'...

' Close the connection.
conn_people.Close()
conn_people.Dispose()
                                                  

Example program CommandInsert, which is available for download on the book's web site, uses similar code to open a connection before inserting new data into the database.

The following table describes the most useful methods provided by the OleDbConnection and SqlConnection classes.

The connection object's most useful events are InfoMessage and StateChange. The InfoMessage event occurs when the database provider issues a warning or informational message. The program can read the message and take action or display it to the user. The StateChange event occurs when the database connection's state changes.

Note that the method for using a connection object shown in Figure 20-13 relies on the data adapter's Fill and Update methods, not on the connection object's Open and Close methods. Fill and Update automatically open the connection, perform their tasks, and then close the connection so that you don't need to manage the connection object yourself. For example, when the program calls Fill, the data adapter quickly opens the connection, copies data from the database into the DataSet, and then closes the database. When you use this model for database interaction, the data connections are open only very briefly.

A transaction defines a set of database actions that should be executed "atomically" as a single unit. Either all of them should occur or none of them should occur, but no action should execute without all of the others.

The classic example is a transfer of money from one account to another. Suppose that the program tries to subtract money from one account and then add it to another. After it subtracts the money from the first account, however, the program crashes. The database has lost money — a bad situation for the owners of the accounts.

On the other hand, suppose that the program performs the operations in the reverse order: first it adds money to the second account and then subtracts it from the first. This time if the program gets halfway through the operation before crashing, the database has created new money — a bad situation for the bank.

The solution is to wrap these two operations in a database transaction. If the program gets halfway through the transaction and then crashes, the database engine unwinds the transaction when the database restarts, so the data looks as if nothing had happened. This isn't as good as performing the whole transaction flawlessly, but at least the database is consistent and the money has been conserved.

To use transactions in Visual Basic, the program uses a connection object's BeginTransaction method to open a transaction. It then creates command objects associated with the connection and the transaction, and it executes them. When it has finished, the program can call the transaction object's Commit method to make all the actions occur, or it can call Rollback to cancel them all.

Example program Transactions, which is available for download on the book's web site, uses the following code to perform two operations within a single transaction. This code removes an amount of money from one account and adds the same amount to another account.

' Make a transfer.
Private Sub btnUpdate_Click() Handles btnUpdate.Click
    ' Open the connection.
    Dim connAccounts As New OleDbConnection(MakeConnectString())
    connAccounts.Open()

    ' Make the transaction.
    Dim trans As OleDbTransaction =
        connAccounts.BeginTransaction(IsolationLevel.ReadCommitted)

    ' Make a Command for this connection.
    ' and this transaction.
    Dim cmd As New OleDbCommand(
        "UPDATE Accounts SET Balance=Balance + ? WHERE AccountName=?",
        connAccounts,
        trans)

    ' Create parameters for the first command.
    cmd.Parameters.Add(New OleDbParameter("Balance",
        Decimal.Parse(txtAmount.Text)))
    cmd.Parameters.Add(New OleDbParameter("AccountName",
         "Alice's Software Emporium"))

    ' Execute the second command.
    cmd.ExecuteNonQuery()

    ' Create parameters for the second command.
    cmd.Parameters.Clear()
    cmd.Parameters.Add(New OleDbParameter("Balance",

-Decimal.Parse(txtAmount.Text)))
    cmd.Parameters.Add(New OleDbParameter("AccountName",
         "Bob's Consulting"))

    ' Execute the second command.
    cmd.ExecuteNonQuery()

    ' Commit the transaction.
    If MessageBox.Show(
         "Commit transaction?",
         "Commit?",
        MessageBoxButtons.YesNo,
        MessageBoxIcon.Question) = indows.Forms.DialogResult.Yes _
    Then
        ' Commit the transaction.
        trans.Commit()
    Else
        ' Rollback the transaction.
        trans.Rollback()
    End If

    ' Display the current balances.
    ShowValues(connAccounts)

    ' Close the connection.
    connAccounts.Close()
End Sub

                                                  

The code first creates a connection. It uses the MakeConnectString function to build an appropriate connection string.

Next the code uses the connection's BeginTransaction method to make the transaction object trans.

Next, the code defines an OleDbCommand object named cmd, setting its command text to the following text:

UPDATE Accounts SET Balance=Balance + ? WHERE AccountName=?

Note that it passes the transaction object into the command object's constructor to make the command part of the transaction.

The question marks in the command text represent parameters to the command. The program defines the parameters' values by adding two parameter objects to the command object. It then calls the command's ExecuteNonQuery method to perform the query.

The code clears the command's parameters, adds two parameters with different values, and calls the command's ExecuteNonQuery method again.

Now the program displays a message box asking whether you want to commit the transaction. When you click Yes, the program calls the transaction's Commit method and both of the update operations occur. When you click No, the program calls the transaction's Rollback method and both of the update operations are canceled.

The program finishes by calling ShowValues to display the updated data and by closing the connection.

Instead of clicking Yes or No when the program asks if it should commit the transaction, you can use the IDE to stop the program. When you then restart the program, you will see that neither update was processed.

In addition to the Commit and Rollback methods, transaction objects may provide other methods for performing more complex transactions. For example, the OleDbTransaction class has a Begin method that enables you to create a nested transaction. Similarly, the SqlTransaction class has a Save method that creates a "savepoint" that you can use to roll back part of the transaction. See the online help for the type of transaction object you are using to learn about these methods. The web page msdn.microsoft.com/2k2hy99x.aspx gives an overview of using transactions. Links at the bottom lead to information about the OleDbTransaction, SqlTransaction, and OdbcTransaction classes.

A data adapter transfers data between a connection and a DataSet. This object's most important methods are Fill and Update, which move data from and to the database. A data adapter also provides properties and other methods that can be useful. The following table describes the object's most useful properties.

You can create the command objects in a couple of ways. For example, if you use the Data Adapter Configuration Wizard (described shortly) to build the adapter at design time, the wizard automatically creates these objects. You can select the adapter and expand these objects in the Properties window to read their properties, including the CommandText property that defines the commands.

Another way to create these commands is to use a command builder object. If you attach a command builder to a data adapter, the adapter uses the command builder to generate the commands it needs automatically.

Example program GenerateCommands uses the following code to determine the commands used by a data adapter. The code creates a new OleDbCommandBuilder, passing its constructor the data adapter. It then uses the command builder's GetDeleteCommand, GetInsertCommand, and GetUpdateCommand methods to learn about the automatically generated commands.

' Attach a command builder to the data adapter
' and display the generated commands.
Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapter1)

Dim txt As String = ""

txt &= command_builder.GetDeleteCommand.CommandText & vbCrLf & vbCrLf
txt &= command_builder.GetInsertCommand.CommandText & vbCrLf & vbCrLf
txt &= command_builder.GetUpdateCommand.CommandText & vbCrLf & vbCrLf

txtCommands.Text = txt
txtCommands.Select(0, 0)
                                                  

The following text shows the results of the previous Debug statements. The DELETE and UPDATE statements are wrapped across multiple lines. The command builder generated these commands based on the select statement SELECT * From Books that was used to load the DataSet.

DELETE FROM Books WHERE ((Title = ?) AND ((? = 1 AND URL IS NULL) OR (URL = ?))
AND ((? = 1 AND Year IS NULL) OR (Year = ?)) AND ((? = 1 AND ISBN IS NULL) OR
(ISBN = ?)) AND ((? = 1 AND Pages IS NULL) OR (Pages = ?)))

INSERT INTO Books (Title, URL, Year, ISBN, Pages) VALUES (?, ?, ?, ?, ?)

UPDATE Books SET Title = ?, URL = ?, Year = ?, ISBN = ?, Pages = ? WHERE
((Title = ?) AND ((? = 1 AND URL IS NULL) OR (URL = ?)) AND ((? = 1 AND Year IS
NULL) OR (Year = ?)) AND ((? = 1 AND ISBN IS NULL) OR (ISBN = ?)) AND ((? = 1
AND Pages IS NULL) OR (Pages = ?)))

A data adapter's TableMappings property enables you to change how the adapter maps data in the database to the DataSet. For example, you could make it copy the Employees table in the database into a DataSet table named People. You don't usually need to change the table and column names, however, and you can make these changes interactively at design time more easily than you can do this in code, so you will usually leave these values alone at runtime.

To create a data adapter control at design time, open a form in the Windows Forms Designer, select the Toolbox's Data tab, and double-click the appropriate data adapter control. (If the data adapter you want doesn't appear in the Toolbox, right-click the Toolbox, select Choose Items, and pick the data adapter that you want to use.)

When you create a data adapter, the Data Adapter Configuration Wizard appears. The wizard's first page lets you select or build a data connection much as the Data Source Configuration Wizard does in Figure 20-3. Select or create a connection as described in the section "Connecting to the Data Source" earlier in this chapter.

Click Next to display the page shown in Figure 20-15. Use the option buttons to select the method the adapter should use to work with the data source. This determines how the data adapter will fetch, update, delete, and insert data in the database. Your options are:

In Figure 20-15, only the first option is enabled because it is the only option available to the OleDbDataAdapter that was used in this example.

When you select the "Use SQL statements" option and click Next, the form shown in Figure 20-16 appears. If you are experienced at writing SQL statements, enter the SELECT statement that you want the data adapter to use to select its data.

If you have less experience or are not familiar with the database's structure, click the Query Builder button to use the Query Builder shown in Figure 20-17. The area in the upper left shows the tables currently selected for use by the SQL query. Check boxes indicate which fields in the tables are selected. To add new tables to the query, right-click in this area and select Add Table.

Figure 20-17 shows the Query Builder. The top part shows that the Books table is included in the query and that its Title, Year, and Pages fields are selected.

Below the table and field selection area is a grid that lists the selected fields. Columns let you specify modifiers for each field. A field's Alias indicates the name the field is known by when it is returned by the query. In Figure 20-17, the Year field will be returned with the alias PubYear.

The Output check box determines whether the field is selected. This check box does the same thing as the one in the upper field selection area.

Figure 20.15. Select the method the data adapter will use to manipulate database data.

Figure 20.16. Enter a SQL SELECT statement or click the Query Builder button.

Figure 20.17. You can use the Query Builder to interactively define the data that a data adapter selects.

The Sort Type column lets you indicate that the results should be sorted in either ascending or descending order. Sort Order determines the order in which the fields are sorted. The query shown in Figure 20-17 sorts first by Year in descending order. If more than one book has the same Year, they are ordered by Title in ascending order.

The Filter column lets you add conditions to the fields. The values in Figure 20-17 make the query select records only where the Year is greater than 2005. Additional fields scrolled off to the right in Figure 20-17 let you add more filters combined with OR. For example, you could select books where the Year is greater than 2005 OR less than 1990.

If you place filters on more than one field, their conditions are combined with AND. For example, the values shown in Figure 20-17 select records where Year is greater than 2005 AND Pages is greater than 400.

Below the grid is a text box that shows the SQL code for the query. If you look at the query, you can see that it selects the fields checked in the field selection area at the top, uses an appropriate WHERE clause, and orders the results properly.

Click the Execute Query button to make the Query Builder run the query and display the results in the bottom grid. You can use this button to test the query to verify that it makes some sense before you finish creating the data adapter.

Click OK to close the Query Builder and return to the Data Adapter Configuration Wizard.

When you click Next, the Data Adapter Configuration Wizard displays a summary page indicating what it did and did not do while creating the data adapter. Depending on the query you use to select data, the wizard may not generate all the commands to select, update, insert, and delete data. For example, if the query joins more than one table, the wizard will be unable to figure out how to update the tables, so it won't generate insert, update, or delete commands.

Click Finish to close the wizard and see the new data adapter and its new connection object. You can see the adapter's DeleteCommand, InsertCommand, SelectCommand, and UpdateCommand objects in the Properties window. These objects' CommandText values show the corresponding SQL statements used by the objects. The wizard also generates default table mappings to transform database values into DataSet values.

The command object classes (OleDbCommand, SqlCommand, OdbcCommand, and OracleCommand) define database commands. The command can be a SQL query, or some non-query statement such as an INSERT, UPDATE, DELETE, or CREATE TABLE statement.

The object's Connection property gives the database connection object on which it will execute its command. CommandText gives the SQL text that the command represents.

The CommandType property tells the database the type of command text the command holds. This can be StoredProcedure (CommandText is the name of a stored procedure), TableDirect (CommandText is the name of one or more tables from which the database should return data), or Text (CommandText is a SQL statement).

The command object's Parameters collection contains parameter objects that define any values needed to execute the command text.

Example program CommandInsert, which is available for download on the book's web site, uses the following code to create an OleDbCommand object that executes the bolded SQL statement INSERT INTO PeopleNames (FirstName, LastName) VALUES (?, ?). The question marks are placeholders for parameters that will be added later. The code then adds two new OleDbParameter objects to the command's Parameters collection. When the code invokes the command's ExecuteNonQuery method, the adapter replaces the question marks with these parameter values in the order in which they appear in the Parameters collection. In this example, the value of txtFirstName.Text replaces the first question mark and txtLastName.Text replaces the second.

Private Sub btnAdd_Click() Handles btnAdd.Click
    ' Make the connect string.
    Dim connect_string As String =
        "Provider=Microsoft.Jet.OLEDB.4.0;" &
        "Data Source=""" & txtDatabase.Text & """;" &
        "Persist Security Info=False"

    ' Open a database connection.
    Dim conn_people As New OleDb.OleDbConnection(connect_string)

conn_people.Open()

    ' Make a Command to insert data.
    Dim cmd As New OleDbCommand(
         "INSERT INTO PeopleNames (FirstName, LastName) VALUES (?, ?)",
        conn_people)

    ' Create parameters for the command.
    cmd.Parameters.Add(New OleDbParameter("FirstName", txtFirstName.Text))
    cmd.Parameters.Add(New OleDbParameter("LastName", txtLastName.Text))

    ' Execute the command.
    Try
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

    ' Show the current values.
    ShowValues(conn_people)

    ' Close the connection.
    conn_people.Close()
    conn_people.Dispose()
End Sub
                                                  

The command object's Transaction property gives the transaction object with which it is associated. See the section "Transaction Objects" earlier in this chapter for more information about transactions.

The command object provides three methods for executing its CommandText. ExecuteNonQuery executes a command that is not a query and that doesn't return any values.

ExecuteScalar executes a command and returns the first column in the first row selected. This is useful for commands that return a single value such as SELECT COUNT * FROM Users.

ExecuteReader executes a SELECT statement and returns a data reader object (for example, OleDbDataReader). The program can use this object to navigate through the returned rows of data.

The command object's two other most useful methods are CreateParameter and Prepare. As you may be able to guess, CreateParameter adds a new object to the command's Parameters collection. The Prepare method compiles the command into a form that the database may be able to execute more quickly. It is often faster to execute a compiled command many times using different parameter values than it is to execute many new commands.

Private Sub Form1_Load() Handles MyBase.Load
    ' Make the DataSet.
    Dim scores_dataset As New DataSet("Scores")

    ' Make the Students table.
    Dim students_table As DataTable =
        scores_dataset.Tables.Add("Students")

    ' Add columns to the Students table.
    students_table.Columns.Add("FirstName", GetType(String))
    students_table.Columns.Add("LastName", GetType(String))
    students_table.Columns.Add("StudentId", GetType(Integer))

    ' Make the StudentId field unique.
    students_table.Columns("StudentId").Unique = True '

    ' Make the combined FirstName/LastName unique.
    Dim first_last_columns() As DataColumn = {
        students_table.Columns("FirstName"),
        students_table.Columns("LastName")
    }
    students_table.Constraints.Add(New UniqueConstraint(first_last_columns))

    ' Make the TestScores table.
    Dim test_scores_table As DataTable =
    scores_dataset.Tables.Add("TestScores")

    ' Add columns to the TestScores table.
    test_scores_table.Columns.Add("StudentId", GetType(Integer))
    test_scores_table.Columns.Add("TestNumber", GetType(Integer))
    test_scores_table.Columns.Add("Score", GetType(Integer))

    ' Make the combined StudentId/TestNumber unique.
    Dim studentid_testnumber_score_columns() As DataColumn = {
        test_scores_table.Columns("StudentId"),
        test_scores_table.Columns("TestNumber")
    }

test_scores_table.Constraints.Add(
        New UniqueConstraint(studentid_testnumber_score_columns))

    ' Make a relationship linking the
    ' two tables' StudentId fields.
    scores_dataset.Relations.Add(
        "Student Test Scores",
        students_table.Columns("StudentId"),
        test_scores_table.Columns("StudentId"))

    ' Make some student data.
    students_table.Rows.Add(New Object() {"Art", "Ant", 1})
    students_table.Rows.Add(New Object() {"Bev", "Bug", 2})
    students_table.Rows.Add(New Object() {"Cid", "Cat", 3})
    students_table.Rows.Add(New Object() {"Deb", "Dove", 4})

    ' Make some random test scores.
    Dim score As New Random
    For id As Integer = 1 To 4
        For test_num As Integer = 1 To 10
            test_scores_table.Rows.Add(
                New Object() {id, test_num, score.Next(65, 100)})
        Next test_num
    Next id

    ' Attach the DataSet to the DataGrid.
    grdScores.DataSource = scores_dataset
End Sub
                                                  

<pfx:Scores xmlns:pfx="my_namespace">
  <Students xmlns="my_namespace">
    <FirstName>Art</FirstName>
    <LastName>Ant</LastName>
    <StudentId>1</StudentId>
  </Students>
  <Students xmlns="my_namespace">
    <FirstName>Bev</FirstName>
    <LastName>Bug</LastName>
    <StudentId>2</StudentId>
  </Students>
  ...

<TestScores xmlns="my_namespace">
    <StudentId>1</StudentId>
    <TestNumber>1</TestNumber>
    <Score>78</Score>
  </TestScores>
  <TestScores xmlns="my_namespace">
    <StudentId>1</StudentId>
    <TestNumber>2</TestNumber>
    <Score>81</Score>
  </TestScores>
  ...
</pfx:Scores>

<pfx:Students xmlns:pfx="my_namespace">
  <FirstName xmlns="my_namespace">Art</FirstName>
  <LastName xmlns="my_namespace">Ant</LastName>
  <StudentId xmlns="my_namespace">1</StudentId>
</pfx:Students>

DataRow

A DataRow object represents the data in one record in a DataTable. This object is relatively simple. It basically just holds data for the DataTable, and the DataTable object does most of the interesting work.

The following table describes the DataRow object's most useful properties.

PROPERTY	PURPOSE
HasErrors	Returns True if the row's data has errors.
Item	Gets or sets one of the row's item values. Overloaded versions of this property use different parameters to identify the column. This parameter can be the column's zero-based index, its name, or a DataColumn object. An optional second parameter can indicate the version of the row so, for example, you can read the original value in a row that has been modified.
ItemArray	Gets or sets all of the row's values by using an array of generic Objects.
RowError	Gets or sets the row's error message text.
RowState	Returns the row's current state: Added, Deleted, Modified, or Unchanged.
Table	Returns a reference to the DataTable containing the row.

If a row has an error message defined by its RowError property, the DataGrid control displays a red circle containing a white exclamation point to the left of the row as an error indicator. If you hover the mouse over the error indicator, a tooltip displays the RowError text. In Figure 20-18, the third row has RowError set to "Missing registration."

Figure 20.18. The DataGrid control marks a DataRow that has a nonblank RowError.

Example program MemoryDataSetWithErrors, which is available for download on the book's web site, uses the following code to set errors on the second row's third column (remember, indexes start at zero) and on the third row. The result is shown in Figure 20-17.

students_table.Rows(1).SetColumnError(2,
    "Bad name format")
students_table.Rows(2).RowError =
    "Missing registration"

The following table describes the DataRow object's most useful methods.

METHOD	PURPOSE
AcceptChanges	Accepts all changes to the row that were made since the data was loaded or since the last call to AcceptChanges.
BeginEdit	Puts the row in data-editing mode. This suspends events for the row, so your code or the user can change several fields without triggering validation events. BeginEdit is implicitly called when the user modifies a bound control's value and EndEdit is implicitly called when you invoke AcceptChanges. Although the row is in edit mode, it stores the original and modified values, so you can retrieve either version, accept the changes with EndEdit, or cancel the changes with CancelEdit.
CancelEdit	Cancels the current edit on the row and restores its original values.
ClearErrors	Clears the row's column and row errors.
Delete	Deletes the row from its table.
GetChildRows	Returns an array of DataRow objects representing this row's child rows as specified by a parent/child data relation.
GetColumnError	Returns the error text assigned to a column.
GetParentRow	Returns a DataRow object representing this row's parent record as specified by a parent/child data relation.
GetParentRows	Returns an array of DataRow objects representing this row's parent records as specified by a data relation.
HasVersion	Returns True if the row has a particular version (Current, Default, Original, or Proposed). For example, while a row is being edited, it has Current and Proposed versions.
IsNull	Indicates whether a particular column contains a NULL value.
RejectChanges	Removes any changes made to the row since the data was loaded or since the last call to AcceptChanges.
SetColumnError	Sets error text for one of the row's columns. If a column has an error message, then a DataGrid control displays a red circle containing a white exclamation point to the left of the column's value as an error indicator. In Figure 20-17, the second row's second column has a column error set. If you hover the mouse over the error indicator, a tooltip displays the error's text.
SetParentRow	Sets the row's parent row according to a data relation.

students_table.Columns("FirstName").ColumnMapping = MappingType.Attribute
students_table.Columns("LastName").ColumnMapping = MappingType.Attribute
students_table.Columns("StudentId").ColumnMapping = MappingType.SimpleContent
                                                  

<Students FirstName="Art" LastName="Ant">1</Students>
<Students FirstName="Bev" LastName="Bug">2</Students>
<Students FirstName="Cid" LastName="Cat">3</Students>
<Students FirstName="Deb" LastName="Dove">4</Students>

students_table.Columns("FirstName").ColumnMapping = MappingType.Element
students_table.Columns("LastName").ColumnMapping = MappingType.Element
students_table.Columns("StudentId").ColumnMapping = MappingType.Attribute
                                                  

<Students StudentId="1">
  <FirstName>Art</FirstName>
  <LastName>Ant</LastName>
</Students>
<Students StudentId="2">
  <FirstName>Bev</FirstName>
  <LastName>Bug</LastName>
</Students>
<Students StudentId="3">
  <FirstName>Cid</FirstName>
  <LastName>Cat</LastName>
</Students>
<Students StudentId="4">
  <FirstName>Deb</FirstName>
  <LastName>Dove</LastName>
</Students>

' Make a relationship linking the two tables' StudentId fields.
scores_dataset.Relations.Add(
    "Student Test Scores",
    students_table.Columns("StudentId"),
    test_scores_table.Columns("StudentId"))
                                                  

<Students>
  <FirstName>Deb</FirstName>
  <LastName>Dove</LastName>
  <StudentId>4</StudentId>
  <TestScores>
    <StudentId>4</StudentId>
    <TestNumber>1</TestNumber>
    <Score>81</Score>
  </TestScores>
  <TestScores>
    <StudentId>4</StudentId>
    <TestNumber>2</TestNumber>
    <Score>68</Score>
  </TestScores>
  ...
</Students>

<Students>
  <FirstName>Deb</FirstName>
  <LastName>Dove</LastName>
  <StudentId>4</StudentId>
  <TestScores>
    <TestNumber>1</TestNumber>
    <Score>81</Score>
  </TestScores>
  <TestScores>
    <TestNumber>2</TestNumber>
    <Score>68</Score>
  </TestScores>
  ...
</Students>

scores_dataset.Relations.Add(
    "Student Test Scores",
    students_table.Columns("StudentId"),
    test_scores_table.Columns("StudentId"))
                                                  

students_table.Columns("StudentId").Unique = True

' Make the combined FirstName/LastName unique.
Dim first_last_columns() As DataColumn = {
    students_table.Columns("FirstName"),
    students_table.Columns("LastName")
}
students_table.Constraints.Add(New UniqueConstraint(first_last_columns))

                                                  

A DataView object represents a customizable view of the data contained in a DataTable. You can use the DataView to select some or all of the DataTable's data and display it sorted in some manner without affecting the underlying DataTable.

A program can use multiple DataViews to select and order a table's data in different ways. You can then bind the DataViews to controls such as the DataGrid control to display the different views. If any of the views modifies its data, for example by adding or deleting a row, the underlying DataTable object's data is updated and any other views that need to see the change are updated as well.

Example program DataGrids, which is available for download on the book's web site, uses the following code to demonstrate DataGrid controls:

Private Sub Form1_Load() Handles MyBase.Load
    ' Make a DataTable.
    Dim contacts_table As New DataTable("Contacts")

    ' Add columns.
    contacts_table.Columns.Add("FirstName", GetType(String))
    contacts_table.Columns.Add("LastName", GetType(String))
    contacts_table.Columns.Add("Street", GetType(String))
    contacts_table.Columns.Add("City", GetType(String))
    contacts_table.Columns.Add("State", GetType(String))
    contacts_table.Columns.Add("Zip", GetType(String))

    ' Make the combined FirstName/LastName unique.
    Dim first_last_columns() As DataColumn = {
        contacts_table.Columns("FirstName"),
        contacts_table.Columns("LastName")
    }
    contacts_table.Constraints.Add(New UniqueConstraint(first_last_columns))

    ' Make some contact data.
    contacts_table.Rows.Add(New Object() {"Art", "Ant",

"1234 Ash Pl", "Bugville", "CO", "11111"})
    contacts_table.Rows.Add(New Object() {"Bev", "Bug",
        "22 Beach St", "Bugville", "CO", "22222"})
    contacts_table.Rows.Add(New Object() {"Cid", "Cat",
        "3 Road Place Lane", "Programmeria", "KS", "33333"})
    contacts_table.Rows.Add(New Object() {"Deb", "Dove",
        "414 Debugger Way", "Programmeria", "KS", "44444"})
    contacts_table.Rows.Add(New Object() {"Ed", "Eager",
        "5746 Elm Blvd", "Bugville", "CO", "55555"})
    contacts_table.Rows.Add(New Object() {"Fran", "Fix",
        "647 Foxglove Ct", "Bugville", "CO", "66666"})
    contacts_table.Rows.Add(New Object() {"Gus", "Gantry",
        "71762-B Gooseberry Ave", "Programmeria", "KS", "77777"})
    contacts_table.Rows.Add(New Object() {"Hil", "Harris",
        "828 Hurryup St", "Programmeria", "KS", "88888"})

    ' Attach grdAll to the DataTable.
    grdAll.DataSource = contacts_table
    grdAll.CaptionText = "All Records"

    ' Make a DataView for State = CO.
    Dim dv_co As New DataView(contacts_table)
    dv_co.RowFilter = "State = 'CO'"
    grdCO.DataSource = dv_co
    grdCO.CaptionText = "CO Records"

    ' Make a DataView for FirstName >= E.
    Dim dv_name As New DataView(contacts_table)
    dv_name.RowFilter = "FirstName >= 'E'"
    grdName.DataSource = dv_name
    grdName.CaptionText = "LastName >= E"
End Sub
                                                  

The code builds a DataTable named Contacts containing the fields FirstName, LastName, Street, City, State, and Zip. It places a uniqueness constraint on the FirstName/LastName pair and adds some rows of data to the table. It then binds the DataTable to the DataGrid control named grdAll. Next the program makes a DataView named dv_co based on the table, sets its RowFilter property to make it select rows where the State field has the value CO, and binds the DataView to the DataGrid named grdCO. Finally, the code makes another DataView with RowFilter set to select records where the FirstName field is greater than or equal to E and binds that DataView to the grdName DataGrid.

Figure 20-19 shows the result. The DataGrid on the top is bound to the DataTable and shows all the table's rows. The second DataGrid is bound to the dv_co DataView and displays records where State = CO. The bottom DataGrid is bound to the dv_name DataView, so it displays records where FirstName >= E. If you use any of these DataGrid controls to modify the data, the other grids immediately show the updates.

The DataView class is geared more toward data display than toward storage. It basically refers to data stored in a DataTable object, so it doesn't provide the same features for managing relations and constraints that the DataTable does. It does, however, provide links to the DataRow objects it represents. From those objects, you can get back to the rows' DataTable objects if necessary.

Figure 20.19. Different DataView objects can show different views of the same data.

The following table describes the DataView object's most useful properties.

The following table describes some of the most useful DataView methods.

The DataView object's Sort property determines not only the fields by which the data is sorted but also the key fields used by the Find method. The following code makes the dv_name DataView sort by FirstName and LastName. It then uses the Find method to display the index of a row with FirstName = Hil and LastName = Harris.

dv_name.Sort = "FirstName, LastName"
MessageBox.Show(dv_name.Find(New String() {"Hil", "Harris"}).ToString)

A DataRow object can hold data for more than one state. For example, if a DataTable row has been modified, its DataRow object contains the row's original data and the new modified values.

The DataRowView object represents a view of a DataRow object in a particular state. That state can be Current (the current value), Default (if columns have defined default values), Original (the original values), or Proposed (new values during an edit before EndEdit or CancelEdit is called).

A DataView object holds DataRowView objects representing a view of a DataTable selecting particular rows in a particular state.

The DataRowView object's purpose is to represent a row in a specific state so this object is relatively simple. It basically indicates the chosen state and refers to a DataRow.

The following table describes the DataRowView object's most useful properties.

Binding a simple property such as Text to a data source is relatively easy. First, create a DataSet, DataTable, or DataView to act as the data source. You can create this object at design time using controls or at runtime using object variables.

If you build the data source at design time, you can also bind the property at design time. Select the control that you want to bind and open the Properties window. Expand the (DataBindings) entry and find the property you want to bind (for example, Text). Click the drop-down arrow on the right, and use the pop-up display to select the data source item that you want to bind to the property.

Figure 20-20 shows the pop-up binding the txtTitle control's Text property to the dsBooks DataSet object's Books table's Title field.

Figure 20.20. You can bind a simple control property to a data source at design time.

At runtime, your code can bind a simple property to a data source by using the control's DataBindings collection. This collection's Add method takes as parameters the name of the property to bind, the data source, and the name of the item in the data source to bind.

The following statement binds the txtUrl control's Text property to the dsBooks DataSet object's Books table's URL field:

txtUrl.DataBindings.Add("Text", dsBooks.Books, "URL")

That's all there is to binding simple properties. By itself, however, this binding doesn't provide any form of navigation. If you were to bind the Text properties of a bunch of TextBox controls and run the program, you would see the data for the data source's first record and nothing else. To allow the user to navigate through the data source, you must use a CurrencyManager object.

Some controls such as the DataGrid control provide their own forms of navigation. If you bind a DataGrid to a DataSet, it allows the user to examine the DataSet object's tables, view and edit data, and follow links between the tables. The DataGrid provides its own methods for navigating through the data. For simpler controls, such as the TextBox, which can display only one data value at a time, you must provide some means for the program to navigate through the data source's records.

A data source manages its position within its data by using a CurrencyManager object. The CurrencyManager supervises the list of Binding objects that bind the data source to controls such as TextBoxes.

The following table describes the CurrencyManager object's most useful properties.

The CurrencyManager also provides some methods for manipulating the data. The following table describes the CurrencyManager object's most useful methods.

The CurrencyManager class raises a PositionChanged event when its position in the data changes.

Example program BindSimple, which is available for download on the book's web site, uses the following code to navigate through a DataSet:

Public Class Form1
    Private WithEvents m_CurrencyManager As CurrencyManager

    Private Sub Form1_Load() Handles MyBase.Load
        'TODO: This line of code loads data into the
        'BooksDataSet.Books' table. You can move, or remove it, as needed.
        Me.BooksTableAdapter.Fill(Me.BooksDataSet.Books)

        ' Get the CurrencyManager.
        m_CurrencyManager = DirectCast(Me.BindingContext(
            BooksBindingSource), CurrencyManager)

        ' Display the record number.
        m_CurrencyManager_PositionChanged()
    End Sub

    ' Move to the previous record.
    Private Sub btnPrev_Click() Handles btnPrev.Click
        If m_CurrencyManager.Position = 0 Then
            Beep()
        Else
            m_CurrencyManager.Position -= 1
        End If
    End Sub

    ' Move to the next record.
    Private Sub btnNext_Click() Handles btnNext.Click
        If m_CurrencyManager.Position >= m_CurrencyManager.Count - 1 Then
            Beep()
        Else
            m_CurrencyManager.Position += 1
        End If
    End Sub

    ' Go to the first record.
    Private Sub btnFirst_Click() Handles btnFirst.Click

m_CurrencyManager.Position = 0
    End Sub

    ' Go to the last record.
    Private Sub btnLast_Click() Handles btnLast.Click
        m_CurrencyManager.Position = m_CurrencyManager.Count - 1
    End Sub

    Private Sub m_CurrencyManager_PositionChanged() _
    Handles m_CurrencyManager.PositionChanged
        lblPosition.Text =
            (m_CurrencyManager.Position + 1) & " of " & m_CurrencyManager.Count
    End Sub

    ' Add a record.
    Private Sub btnAdd_Click() Handles btnAdd.Click
        m_CurrencyManager.AddNew()
        txtTitle.Focus()
    End Sub

    ' Delete the current record.
    Private Sub btnDelete_Click() Handles btnDelete.Click
        If MessageBox.Show("Are you sure you want to remove this record?",
            "Confirm?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) =
            Windows.Forms.DialogResult.Yes _
        Then
            m_CurrencyManager.RemoveAt(m_CurrencyManager.Position)
        End If
    End Sub
End Class
                                                  

When the form loads, the program fills its data set and saves a reference to a CurrencyManager object to control the data set's Books table. It then calls subroutine m_CurrencyManager_PositionChanged to display the current record's index (this is described shortly).

The First, Last, Previous, and Next record buttons all work by changing the CurrencyManager's Position property. For example, the previous record button's event handler checks whether the current position is greater than zero and if it is the code decreases the position by one.

Similarly, the Next record button increases the current position by one if the CurrencyManager is not already displaying the last record.

The First and Last record buttons set the position to the indexes of the first and last records, respectively.

Whenever the CurrencyManager's position changes, its PositionChanged event handler executes. This code displays the current record's index in a label for the user to see.

When the user clicks the Add Record button, the code calls the CurrencyManager's AddNew method to make a new record. It also sets focus to the first text box to make filling in the new record easier.

Finally, when the user clicks the delete record button, the code confirms the deletion and then calls the CurrencyManager's RemoveAt method to delete the record.

Figure 20-21 shows the BindSimple program in action.

Figure 20.21. This program's buttons use a CurrencyManager to let the user add, delete, and navigate through a table's records.

Example program BindSimpleMemoryDataSet, which is available for download on the book's web site, is similar to program BindSimple except it uses a DataSet built in memory rather than one loaded from a database.

For some controls (such as the TextBox and Label) binding the Text property is good enough. Other controls, however, do not display a simple textual value.

For example, suppose that you have a database containing a Users table with fields FirstName, LastName, and UserType. The UserTypes table has fields UserTypeId and UserTypeName. The Users.UserType field contains a value that should match UserTypes.UserTypeId. The UserTypes.UserTypeName field contains values such as Programmer, Project Manager, Department Manager, Program Manager, and Lab Director.

When you build a form to display the Users table data, you would like to use a ComboBox to allow the user to select only the allowed choices Programmer, Project Manager, and so on. However, the Users table doesn't store those string values. Instead it stores the UserTypeId value corresponding to the UserTypeName value that the user selects. When the user picks a UserTypes.UserTypeName value, the ComboBox should look up the corresponding UserTypes.UserTypeId value and save it in the Users.UserType field.

Clearly, the simple binding strategy used for TextBoxes won't work here. Binding this control requires two rather complicated steps: defining the DataSet and binding the control. Each piece of the operation is easy, but you must do everything correctly. If you miss any detail, the ComboBox won't work, and Visual Basic's error messages probably won't give you enough information to figure out how to fix the problem.

The first step is building a data connection. Select the Data menu's Add New Data Source command. Use the Data Source Configuration Wizard to make a data source that selects both the Users and UserTypes tables from the database.

Next, create a ComboBox named cboUserType to the form. In the Properties window, select the control's DataSource property and click the drop-down arrow on the right. Select the UserTypes table as shown in Figure 20-22. This tells the ComboBox where to look up values.

When you set this property, Visual Basic also adds a DataSet, BindingSource, and TableAdapter to the form. These components provide access to the UserTypes table.

Set the ComboBox's DisplayMember property to the field in the lookup table (specified by the DataSource property) that the control will display to the user. In this example, the field is UserTypeName.

Set the ComboBox's ValueMember property to the field in the lookup table that represents the value that the ComboBox will need to read and write from the database. In this example, that's the UserTypeId field.

Next, you need to bind the ComboBox to the field that it must read and write in the database. In this example, that's the Users table's UserType field. To simplify this binding, add a new BindingSource to the form. Change its name to UsersBindingSource and set its DataSource property to the ComputerUsersDataSet as shown in Figure 20-23. Then set the BindingSource object's DataMember property to the Users table.

Figure 20.22. Set the ComboBox's DataSource property to the UserTypes table.

Figure 20.23. Set the BindingSource object's DataSource to the ComputerUsersDataSet.

The last ComboBox property you need to set is SelectedValue. Click the ComboBox, open the Properties window, and expand the (DataBindings) entry at the top. Click the drop-down arrow to the right of the SelectedValue property and select the field that the control must read and write in the database. For this example, that's the UsersBindingSource object's UserType field. Figure 20-24 shows the Property window setting this property.

Figure 20.24. Set the BindingSource object's SelectedValue to the UsersBindingSource object's UserType field.

Next, create TextBox controls to display the Users table's FirstName and LastName fields. In the Properties window, open their (Data Bindings) items and set their Text properties to the UsersBindingSource object's FirstName and LastName fields.

Finally, to give the user a way to navigate through the data, add a BindingNavigator to the form. Set this component's BindingSource property to UsersBindingSource and the program is ready to run. Figure 20-25 shows the BindComboBox example program, which is available for download on the book's web site, in action.

Figure 20.25. At runtime, the ComboBox displays the field bound to its DisplayMember property while updating the field bound to its SelectedValue property.

The choices allowed by the ComboBox are taken from the values in the UserTypes table's UserTypeName field. If you select a new user value from the ComboBox, the control automatically makes the appropriate change to the Users table.

The steps for binding a ListBox control are exactly the same as those for binding a ComboBox. Example program BindListBox, which is available for download on the book's web site, works much as program BindComboBox does, except it uses a ListBox instead of a ComboBox. As you move through the records, the ListBox selects the appropriate user type for each user record.

Working with databases in Visual Basic is an enormous topic. This chapter does not cover every detail of database programming, but it does explain the basics. It tells how to build data sources and how to drag and drop tables and fields from the Data Sources window onto a form. It describes the most important database controls and objects, such as connection, data adapter, DataSet, and DataTable objects. It also explains the fundamentals of simple and complex data binding, and using CurrencyManager objects to navigate through data.

For more information on database programming in Visual Basic .NET, see one or more books about database programming. This is a very broad field so you may want to look at several books about database design, database maintenance using your particular database (for example, Access or SQL Server), Visual Basic database programming, and so forth.

Database programming has changed considerably in recent versions of Visual Basic, so be sure to get a book that's reasonably up-to-date. Older books explain many of the fundamental database objects such as DataSet, DataTable, DataRow, and CurrencyManager, but they won't cover new objects such as TableAdapter, DataConnector, and DataNavigator. Books covering Visual Basic 2005 and more recent editions should be the most useful. For example, the book Expert One-on-One Visual Basic 2005 Database Programming by Roger Jennings (Wrox, 2005) provides much greater depth on this topic.

If you must build and maintain large databases, you should also read books about database management. These can tell you how to design, build, and maintain a database throughout the application's lifetime. My book Beginning Database Design Solutions (Rod Stephens, Wrox, 2008) explains how to analyze database needs and build a robust and efficient database design.

You should also read about the particular kinds of databases that you need to use. For example, if you are working with SQL Server databases, get a good book on using SQL Server, such as Professional Microsoft SQL Server 2008 Programming by Robert Viera (Wrox, 2009).

Becoming an expert database developer is a big task, but the techniques described in this chapter should at least get you started.

The controls and other objects described in this chapter help a program manipulate data in a database. They help a program connect to a database, read and update data, and display the data on a form.

Chapter 21, "LINQ," explains another way to load and manipulate data. LINQ allows a program to perform complex queries similar to those provided by SQL to select data from lists, collections, arrays, and other data structures within the program's code.