What Are Databases?

We can begin this discussion on databases by asking just what they are. It's probable that you've already worked with databases and so know well what they are and what they do, but for the sake of readers who have less expertise, a brief introduction to the topic will be useful.

Databases have become more complex over the years, as have many other programming concepts, but the fundamental concept is still a simple one. Say, for example, that you are in charge of teaching a class and are supposed to allot a grade for each student. You might make up a table much like the one in Figure 20.1 to record the grades.

Grade	Name
A	Mark
B	Ann
A-	Barbara
B+	Sam
A-	Franklin
A+	Tamsen

Figure 20.1: A table of data.

In fact, you've already created a database—or more specifically, a database table. (We'll even put this particular table from Figure 20.1 to work when we see how to connect MS Jet databases of the kind created by Microsoft Access to Visual Basic applications; see "Connecting to an MS Jet Database" later in this chapter). The transition from a table on paper to one in a computer is natural—with a computer, you can sort, index, update, and organize large tables of data in an easy way (and without a great waste of paper).

Each individual data entry in a table, such as a student's name, goes into a field in the table. Here are the data types you can use for fields in Visual Basic: Boolean, Byte, Char, DateTime, Decimal, Double, Int16, Int32, Int64, SByte, Single, and String. An entry in a table is made up of a set of fields, such as the Name and Grade fields for a particular student; this is called a record. Each record gets its own row in a table, and each column in that row represents a different field.

A collection of records—that is, rows of records, where each column is a field— becomes a table. What, then, is a database? In its most common form, a database is just a collection of one or more tables. A simple collection of tables such as this is a certain type of database—a flat or flat-file database. There is a second type of database as well— relational database, so called because they are set up to relate the data in multiple tables together. To make a table relational, you choose certain fields to be primary keys and foreign keys.

The primary key in a table is usually the most important one—the one you might use to sort by, for instance. The foreign key usually represents the primary key in another table, which gives you access to that table in an organized way. For example, we might add a field called student ID to our student grade table. That same field, student ID, may be the primary key in the school registrar's database table, which lists all students. In our table, then, the student ID field is a foreign key, allowing us to specify individual records in the registrar's table. For more on relational databases, see "Using Relational Databases" later in this chapter.

Now that you've set up a database, how do you work with the data in that database? One popular way is to use Structured Query Language (SQL), which we'll see more about later in the Immediate Solutions (see "Using Basic SQL" in this chapter). You use SQL to set up a query, which, when applied to a database, typically returns a dataset of records that matched your SQL query—for example, you may have asked for all students that got a grade of B or better. You can do a great many things with databases using SQL—you can insert new records, create new tables, get datasets that match specific criteria (such as all your customers in Hawaii, or philosophers who lived more than 1,000 years ago, and so on).

Tip

To get the actual documents that define SQL, as standardized by the International Organization for Standardization (ISO), see www.iso.org/iso/en/prods-services/catalogue/intstandards/CatalogueListPage.CatalogueList?ICS1=35&ICS2=60, which lists the ISO's catalogue for SQL documents—they're not free, though. (Note that this URL may have changed by the time you read this—in that case, go to the ISO site, click the link for Information Technology, followed by the link for "Languages Used in Information Technology.")

So that's how the process works—you use SQL to work with the data in a database, filtering out the records you don't want, and working on the records you do. If you don't know SQL, don't panic; Visual Basic has a built-in tool (the Query Builder) that lets you create SQL statements visually. We'll see that tool at work later; see "Using Relational Databases" in this chapter (also see "Using Basic SQL" in this chapter).

Main Page

Connections, Data Adapters, and Datasets

Visual Basic .NET uses ADO.NET (ADO stands for ActiveX Data Objects) as its primary data access and manipulation protocol. We'll be getting familiar with ADO.NET in this and the next few chapters. There are plenty of objects available in ADO.NET, but at root, they're not difficult to use in practice.

Here's what happens—you first get a connection to a data source, which means using a data provider to access a database. The default data provider that Visual Basic .NET works with is Microsoft's SQL Server, version 7.0 or later, and I'll use that data provider in this book. However, Visual Basic also can work with any data provider that can support Open Database Connectivity (ODBC), such as Oracle. To work with SQL server, you use ADO.NET SQLConnection objects, and to work with any other data provider, you use ADO.NET OleDbConnection objects. We'll see how to create connection objects such as these visually in just a moment.

Tip

Studies have shown that data access with Visual Basic .NET is up to 70 percent faster with SQL Server connections than with standard OLE-DB connections.

After you have a connection to a data source, you create a data adapter to work with that data. You need a data adapter because datasets do not maintain any active connection to the database—they are disconnected from the database. The data adapter is what actually applies your SQL statements to a database and causes your datasets to fill with data. Data adapters are new in Visual Basic, but don't let them throw you. They're just there to apply your commands to the database— because datasets are disconnected from that database—and they're fundamental to the whole process. To work with the SQL Server data provider, you use SQLDataAdapter objects, and to work with ODBC data providers, you use OleDbAdapter objects.

Once you have a data adapter, you can generate a dataset using that adapter. Datasets are what you actually work with in your code when you want to use data from databases. (Although, note that in addition to datasets, there are also data readers, which are fast, read-only mini-datasets that you can only move through records with in ascending order; see "Using a Data Reader" in Chapter 22.) For example, if I wanted to get access to the data in the table in Figure 20.1, I would first create a connection to the database the table was stored in, then create an adapter with the SQL to retrieve that table (for example, if the table was named students, that SQL might be "SELECT * FROM students"), and then fill a DataSet object using that adapter. Note that each data adapter can handle only one SQL query at a time, but DataSet objects can store multiple tables, and to place multiple tables in a dataset, you can use multiple data adapters—see "Adding Multiple Tables to a Dataset" in this chapter.

Tip

The names of tables and fields in datasets are case-insensitive, so the students table is the same as the Students table or the STUDENTS table.

So those are the three objects that it's essential to know about: data connections to connect to the database, data adapters to execute SQL with, and datasets to store the data—as returned from data adapters—that your code will actually work on. That's a simplified view, because there are many other objects, but it fits the majority of scenarios. In Figure 20.2, you can see an overview these and a few other data objects we'll come across.

Figure 20.2: ADO.NET data objects.

Tip

In this chapter, we're going to work with the ADO.NET objects visually, using the tools that VB .NET offers for that purpose. We'll work with ADO.NET connection, adapter, and dataset objects, and others, in code in Chapter 22.

Of course, the easiest way to understand all this is by looking at an example. Visual Basic has a number of visual tools to make working with databases easier, and that gives us a natural place to start, because Visual Basic will create all the objects we need automatically. The easiest way to do all this is with the Server Explorer, and I'll use that tool first to display the data in a database table. This example is called EasyAccess on the CD-ROM; to follow along, create a new Windows forms application with that name now.