Database Programming with ADO.NET | Microsoft Visual Basic 2005 Step by Step (Step by Step (Microsoft))

Database Programming with ADO.NET

A database is an organized collection of information stored in a file. You can create powerful databases by using any of a variety of database products, including Microsoft Access, Microsoft SQL Server, Oracle, and Paradox. You can also store and transmit database information by using XML, a file format designed for exchanging structured data over the Internet and in other settings.

Creating and maintaining databases has become an essential task for all major corporations, government institutions, non-profit agencies, and most small businesses. Rich data resources—for example, customer addresses, manufacturing inventories, account balances, employee records, donor lists, and order histories—have become the lifeblood of the business world.

Visual Studio 2005 isn't designed for creating new databases, but rather for displaying, analyzing, and manipulating the information in existing databases. Although previous versions of Visual Studio and Visual Basic have also provided this capability, Visual Studio 2005 offers an enhanced data model called ADO.NET 2.0 that you can use to work with an even greater number of database formats and access scenarios. In particular, ADO.NET has been designed for Internet use, meaning that it uses the same method for accessing local, client-server, and Internet-based data sources. As a testimony to its goal of making ADO.NET a great technology for manipulating databases over the Internet, Microsoft has made XML—a standard defined by the World Wide Web Consortium—the internal data format of ADO.NET. Using XML in this way makes ADO.NET easier to utilize with existing Internet data sources, and it makes it easier for software vendors to write adapters, or “providers,” that convert third-party database formats to be compatible with ADO.NET.

Database Terminology

When working with databases and ADO.NET, it's important to understand some basic database terminology.

A field (also called a column) is a category of information stored in a database. Typical fields in a customer database might contain customer names, addresses, phone numbers, and comments. All the information about a particular customer or business is called a record (less commonly called a row). When a database is created, information is entered in a table of fields and records. Records correspond to rows in the table, and fields correspond to columns, as shown here:

A relational database can consist of multiple linked tables. In general, most of the databases that you connect to from Visual Studio will probably be relational databases that contain multiple tables of data organized around a particular theme.

In ADO.NET, various objects are used to retrieve and modify information in a database. The following illustration shows an overview of the approach that will be covered in more detail in this chapter:

First a connection is made, which specifies connection information about the database and creates something for other controls and components to bind to. Next the Data Sources Configuration Wizard creates a dataset, which is a representation of one or more database tables you plan to work with in your program. (You don't manipulate the actual data, but rather a copy of it.) The Data Sources Configuration Wizard also adds an XML schema file to your project and associates a table adapter and data navigator with the dataset to handle retrieving data from the database, posting changes, and moving from one record to the next in the dataset. You can then bind information in the dataset to controls on a form by using the Data Sources window or DataBindings property settings

Working with an Access Database

In the following sections, you'll learn how to use the ADO.NET 2.0 data access technology in Visual Basic 2005. You'll get started by using the Data Source Configuration Wizard to establish a connection to a database named Students.mdb that I created in Microsoft Access 2002/2003 format. Students.mdb contains various tables of academic information that would be useful for a teacher who is tracking student coursework or a school administrator who is scheduling rooms, assigning classes, or building a time schedule. You'll learn how to create a dataset based on a table of information in the Students database, and you'll display this information on a Windows form. When you've finished, you'll be able to put these skills to work in your own database projects.

TIP
Although the sample in this chapter uses a Microsoft Access database, you don't have to have Access installed. Visual Studio and ADO.NET include the necessary support to understand the Access file format, as well as other formats. If you decide to open the database in Access, you'll find that Students.mdb is in Access 2002/2003 format. I have also included the file in Access 2000 format (Students_2000format.mdb) and Access 1997 format (Students_97format.mdb) so that you can experiment with the sample database in Access even if you have an older version.

Establish a connection by using the Data Source Configuration Wizard

Start Visual Studio, and create a new Visual Basic Windows Application project named My ADO Form.

A new project appears in the IDE.
On the Data menu, click the Add New Data Source command.

The Data Source Configuration Wizard appears in the development environment, as shown here:

The Data Source Connection Wizard is a new feature within the Visual Studio 2005 IDE that automatically prepares your Visual Basic program to receive database information. The wizard prompts you for the type of database that you will be connecting to (a local or remote database, Web service, or custom data object that you have created), establishes a connection to the data, and then creates a dataset within the program to hold specific database tables and fields. The end result is that the wizard opens the Data Sources window and fills it with a visual representation of each database object that you can use in your program.
Click the Database icon in the Data Source Configuration Wizard, and then click Next.

The wizard displays a screen that helps you establish a connection to your database by building a statement called a connection string. A connection string contains the information that Visual Studio needs to open and extract information from a database file. This includes a path name and file name, but also potentially sensitive data such as a username and password. For this reason, the connection string is treated carefully within the Data Source Connection Wizard, and you should take care to protect it from unauthorized access as you copy your source files from place to place.
Click the New Connection button.

The first time that you click the New Connection button, the Choose Data Source dialog box appears, prompting you to select the database format that you plan to use. If you see the Add Connection dialog box instead of the Choose Data Source dialog box, it simply means that your copy of Visual Studio has already been configured to favor a particular database format. No problem; simply click the Change button in the Add Connection dialog box, and you'll see the same thing that first-time wizard users see, except that the title bar reads Change Data Source, as shown in the following illustration:

The Change/Choose Data Source dialog box is the place where you select your preferred database format, which Visual Studio uses as the default format. In this chapter, you'll select the Microsoft Access format, but note that you can change the database format to one of the other choices at any time. You can also establish more than one database connection—each to a different type of database—within a single project.
Click Microsoft Access Database File, and then click OK.

The Add Connection dialog box appears, as shown in the following illustration:

Now you'll specify the location and connection settings for your database, so that Visual Studio can build a valid connection string.
Click Browse.

The Select Microsoft Access Database File dialog box appears, which functions like an Open dialog box.
Browse to the c:\vb05sbs\chap18 folder, click the Students database, and then click Open.

You have selected the Access database in 2002/2003 format that I built to demonstrate how database fields and records are displayed within a Visual Basic program. The Add Connections dialog box appears again with the path name recorded. I don't restrict access to this file in any way, so a username and password are not necessary with Students.mdb. However, if your database requires a username and/or password for use, you can specify it now in the User Name and Password boxes. These values are then included in the connection string.
Click the Test Connection button.

Visual Studio attempts to open the specified database file with the connection string that the wizard has built for you. If the database is in a recognized format and the username and password entries (if any) are correct, you see the following message:
Click OK to close the message box, and then click OK to close the Add Connection dialog box.

Visual Studio displays the Data Source Configuration Wizard again.
Click the plus sign (+) next to the Connection String item in the dialog box to display your completed connection string.

Your wizard page looks similar to the following:

The connection string identifies a provider (also called a managed provider) named Microsoft.Jet.OLEDB.4.0, which is an underlying database component that understands how to connect to a database and extract data from it. The two most popular providers offered by Visual Studio are Microsoft Jet OLE DB and Microsoft SQL, but third-party providers are available for many of the other popular database formats.
Click the Next button.

The wizard displays an alert message indicating that a new local database has been selected, and you are asked if the database should be copied to your project folders. (This message only appears the first time that you make a connection to a local database file. If you are repeating this exercise, you probably won't see the message.)
Click No to avoid making an extra copy of the database at this time.

You are not commercially distributing this project; it is only a sample program, and an extra copy is not needed.
Click the Next button.

The Data Source Configuration Wizard now asks you the following question: “Do you want to save the connection string to the application configuration file?” Saving the connection string is the default selection, and in this example, the recommended string name is “StudentsConnectionString”. You usually want to save this string within your application's default configuration file, because then if the location of your database changes, you can edit the string in your configuration file (which is listed in Solution Explorer), as opposed to tracking down the connection string within your program code and recompiling the application.
Click Next to save the default connection string.

You are now prompted to select the subset of database objects that you want to use for this particular project, as shown in the following dialog box:

NOTE
Visual Studio allows you to use just part of a database or to combine different databases—useful features when you're working to build datacentric applications.

The items you select in this dialog box are referred to within the project as database objects. Database objects can include tables of fields and records, database views, stored procedures, functions, and other items unique to your database. The collective term for all the database objects that you select is a dataset. In this project, the dataset is assigned the default name StudentsDataSet, which you can adjust in the DataSet Name box.

TIP
Note that the dataset you create now only represents the data in your database—if you add, delete, or modify database records in the dataset, you don't actually modify the underlying database tables until you issue a command that writes your changes back to the original database. Database programmers call this kind of arrangement a disconnected data source, meaning that there is a layer of abstraction between the actual database and your dataset.
Click the plus sign (+) next to the Tables node to expand the list of the tables included in the Students.mdb database.

The list of the tables that appears in the wizard includes Assignments, Classes, Departments, and Instructors. Each table relates to some aspect of academic scheduling. The table we'll use in this example is Instructors.
Click the plus sign (+) next to the Instructors node, and then select the check boxes for the Instructor and PhoneNumber fields.

You'll add these two fields to the StudentsDataSet dataset. The wizard page looks like this:
Click Finish to complete and close the Data Source Configuration Wizard.

Visual Studio finishes the tasks of adding a database connection to your project and configuring the dataset with the selected database objects. (Depending on how the Visual Studio IDE has been used and configured, you might or might not see a Data Sources tab or window now.)
Click the Save All button on the Standard toolbar to save your changes. Specify the c:\vb05sbs\chap18 folder as the location.
If Solution Explorer is not currently visible, open it now to display the major files and components contained in the ADO Form project.

Your screen looks like this:

In addition to the standard Solution Explorer entries for a project, you see a new file named StudentsDataSet.xsd. This file is an XML schema that describes the tables, fields, data types, and other elements in the dataset that you have just created. The presence of the schema file means that you have added a typed dataset to your project. (Typed datasets have a schema file associated with them, but un-typed datasets don't.) Typed datasets are advantageous because they enable the statement-completion feature of the Visual Studio Code Editor, and they give you specific information about the fields and tables you're using.
Click the schema file in Solution Explorer, and then click the View Designer button.

You see a visual representation of the tables, fields, and data adapter commands related to your new dataset in a visual tool called the Dataset Designer. The Dataset Designer contains tools for creating components that communicate between your database and your application—what database programmers call data access layer components. You can create and modify table adapters, table adapter queries, data tables, data columns, and data relationships with the Dataset Designer. You can also use the Dataset Designer to review and set important properties related to objects in a dataset, such as the length of database fields and the data types associated with fields.
Click the Instructor field, and then press F4 to highlight the Properties window.
Click the MaxLength property.

Your screen looks similar to the graphic on the next page.

Here the Dataset Designer is shown with an active dataset named StudentsDataSet, and the Properties window shows that the MaxLength property is set to allow for a maximum of 50 characters in the Instructor field. Although this length seems sufficient, you can adjust this property (and others, too) if you find that the underlying database settings are inadequate for your application.

Setting the Dataset Designer aside for a moment, let's continue building the sample database application in the Data Sources window.