This article, the second of two parts, explains how ADO.NET uses classes from the .NET Framework to provide access to the data in a database. It is excerpted from chapter two of the book VB.NET Database Programming with ADO.NET, written by Anne Prince and Doug Lowe (Murach Publishing; ISBN: 1890774197).
A data reader provides an efficient way of reading the rows in a result set returned by a database query. In fact, when you use a data adapter to retrieve data, the data adapter uses a data reader to read through the rows in the result set and store them in a dataset.
A data reader is similar to other types of readers you may have encountered in the .NET Framework, such as a TextReader, a StreamReader, or an XMLReader. Like these other readers, a data reader lets you read rows but not modify them. In other words, a data reader is read-only. In addition, it only lets you read rows in a forward direction. Once you read the next row, the previous row is unavailable.
Figure 2-5 lists the most important properties and methods of the SqlDataReader class. You use the Read method to read the next row of data in the result set. In most cases, you’ll code the Read method in a loop that reads and processes rows until the end of the data reader is reached.
To access a column of data from the current row of a data reader, you use the Item property. To identify the column, you can use either its index value like this:
drVendors.Item(0)
or its name like this:
drVendors.Item("VendorName")
Since Item is the default property, you can also omit it like this:
drVendors("VendorName")
The SqlDataAdapter class
As you know, the job of a data adapter is to provide a link between a database and a dataset. The four properties of the SqlDataAdapter class listed in figure 2-5 identify the four SQL commands that the data adapter uses to transfer data from the database to the dataset and vice versa. The SelectCommand property identifies the command object that’s used to retrieve data from the database. And the DeleteCommand, InsertCommand, and UpdateCommand properties identify the commands that are used to update the database based on changes made to the data in the dataset.
To execute the command identified by the SelectCommand property and place the data that’s retrieved in a dataset, you use the Fill method. Then, the application can work with the data in the dataset without affecting the data in the database. If the application makes changes to the data in the dataset, it can use the data adapter’s Update method to execute the commands identified by the DeleteCommand, InsertCommand, and UpdateCommand properties and post the changes back to the database.
Figure 2-5. The SqlDataReader and SqlDataAdapter classes
Common properties and methods of the SqlDataReader class
Common properties and methods of the SqlDataAdapter class
Description
A data reader provides read-only, forward-only access to the data in a database. Because it doesn’t require the overhead of a dataset, it’s more efficient than using a data adapter. However, it can’t be used to update data.
When the Fill method of a data adapter is used to retrieve data from a database, the data adapter uses a data reader to load the results into a dataset.
Now that you have a general idea of how the data provider classes provide access to a database, you need to learn more about the disconnected part of ADO.NET processing: the ADO.NET dataset. So in the topics that follow, you’ll first learn how a dataset is organized. Then, you’ll see an overview of the classes you use to define dataset objects. Finally, you’ll learn how ADO.NET handles concurrency issues that arise when you work with disconnected data.
How a dataset is organized
Figure 2-6 illustrates the basic organization of an ADO.NET dataset. The first thing you should notice in this figure is that a dataset is structured much like a relational database. It can contain one or more tables, and each table can contain one or more columns and rows. In addition, each table can contain one or more constraints that can define a unique key within the table or a foreign key of another table in the dataset. If a dataset contains two or more tables, the dataset can also define the relationships between those tables.
Although a dataset is structured much like a relational database, it’s important to realize that each table in a dataset corresponds to the result set that’s returned from a Select statement, not necessarily to an actual table in a database. For example, a Select statement may join data from several tables in a database to produce a single result set. In this case, the table in the dataset would represent data from each of the tables involved in the join.
You should also know that each group of objects in the diagram in this figure is stored in a collection. All of the columns in a table, for example, are stored in a collection of columns, and all of the rows are stored in a collection of rows. You’ll learn more about these collections in the next figure and in later chapters.
The basic dataset object hierarchy
Figure 2-6. How a dataset is organized
Description
A dataset object consists of a hierarchy of one or more data table and data relation objects.
A data table object consists of one or more data column objects and one or more data row objects. The data column objects define the data in each column of the table, including its name, data type, and so on, and the data row objects contain the data for each row in the table.
A data table can also contain one or more constraint objects that are used to maintain the integrity of the data in the table. A unique key constraint ensures that the values in a column, such as the primary key column, are unique. And a foreign key constraint determines how the rows in one table are affected when corresponding rows in a related table are updated or deleted.
The data relation objects define how the tables in the dataset are related. They are used to manage constraints and to simplify the navigation between related tables.
All of the objects in a dataset are stored in collections. For example, the data table objects are stored in a data table collection, and the data row objects are stored in a data row collection. You can refer to these collections through properties of the containing objects.
Figure 2-7 presents some of the properties and methods of the four main classes that you use to work with a dataset: DataSet, DataTable, DataColumn, and DataRow. As you saw in the previous figure, the objects you create from these classes form a hierarchy where each dataset can contain one or more tables and each table can contain one or more rows and one or more columns. Because of that, a dataset contains a Tables property that provides access to the collection of tables in the dataset. Similarly, a data table contains a Columns property and a Rows property that provide access to the collections of columns and rows in the table. These are the properties you’re most likely to use as you work with these objects.
Although they’re not shown in this figure, the collections you refer to through the Tables property of a dataset and the Columns and Rows properties of a data table have properties and methods of their own. For instance, each collection has a Count property that you can use to determine how many items are in the collection. To get the number of tables in a dataset named dsPayables, for example, you could use code like this:
dsPayables.Tables.Count()
To access a specific item in a collection, you use the Item property. On that property, you specify the index value or name of the item you want to access. To access the Vendors table in the dsPayables dataset, for example, you can use code like this:
dsPayables.Tables.Item("Vendors")
Since Item is the default property of the collection class, however, you typically omit it like this:
dsPayables.Tables("Vendors")
The code in this figure shows how you can use a For Each…Next statement to loop through the items in a collection. Here, the statement loops through the rows in the Vendors table. To do that, it uses a variable that’s declared as a DataRow object. Then, the For Each…Next statement uses this variable to retrieve the value of the VendorName column in each row. You can use similar code to loop through the columns in a table or the tables in a dataset.
Figure 2-7.The DataSet, DataTable, DataColumn, and DataRow classes
Common properties and methods of the DataSet class
Common properties and methods of the DataTable class
Common properties and methods of the DataColumn class
Common properties and methods of the DataRow class
Code that refers to the rows collection in the tables collection of a dataset
Dim sMsg As String, dr As DataRow For Each dr In dsVendors.Tables("Vendors").Rows sMsg &= dr.Item("VendorName") & ControlChars.CrLf Next MessageBox.Show(sMsg)
Description
You’ll use the properties and methods of the dataset classes most often when you work with ADO.NET objects through code, as described in chapter 6.
Each collection of objects has properties and methods that you can use to work with the collection.
Although the disconnected data architecture has advantages, it also has some disadvantages. One of those is the conflict that can occur when two or more users retrieve and then try to update data in the same row of a table. This is called a concurrency problem. This is possible because once a program retrieves data from a database, the connection to that database is dropped. As a result, the database management system can’t manage the update process.
To illustrate, consider the situation shown in figure 2-8. Here, two users have retrieved the Vendors table from a database, so a copy of the Vendors table is stored on each user’s PC. These users could be using the same program or two different programs. Now, suppose that user 1 modifies the address in the row for vendor 123 and updates the Vendors table in the database. And suppose that user 2 modifies the phone number in the row for vendor 123 and then tries to update the Vendors table in the database. What will happen? That will depend on the concurrency control that’s used by the programs.
When you use ADO.NET, you have two choices for concurrency control. By default, a program uses optimistic concurrency, which checks whether a row has been changed since it was retrieved. If it has, the update or deletion will be refused and a concurrency exception will be thrown. Then, the program should handle the error. For example, it could display an error message that tells the user that the row could not be updated and then retrieve the updated row so the user can make the change again.
In contrast, the "last in wins" technique works the way its name implies. Since no checking is done with this technique, the row that's updated by the last user overwrites any changes made to the row by a previous user. For the example above, the row updated by user 2 will overwrite changes made by user 1, which means that the phone number will be right but the address will be wrong. Since errors like this corrupt data in a database, optimistic concurrency is used by most programs, which means that your programs have to handle the concurrency exceptions that are thrown.
If you know that concurrency will be a problem, you can use a couple of programming techniques to limit concurrency exceptions. If a program uses a dataset, one technique is to update the database frequently so other programs can retrieve the current data. The program should also refresh its dataset frequently so it contains the recent changes made by other programs.
Another way to avoid concurrency exceptions is to retrieve and work with just one row at a time. That way, it’s less likely that two programs will update the same row at the same time. In contrast, if two programs retrieve the same table, they will of course retrieve the same rows. Then, if they both update the same row in the table, even though it may not be at the same time, a concurrency exception will occur when they try to update the database.
Of course, you will understand and appreciate this more as you learn how to develop your own database applications. As you develop them, though, keep in mind that most applications are multi-user applications. That’s why you have to be aware of concurrency problems.
Two users who are working with copies of the same data
Figure 2-8.Concurrency and the disconnected data architecture
What happens when two users try to update the same row
When two or more users retrieve the data in the same row of a database table at the same time, it is called concurrency. Because ADO.NET uses a disconnected data architecture, the database management system can’t prevent this from happening.
If two users try to update the same row in a database table at the same time, the second user’s changes could overwrite the changes made by the first user. Whether or not that happens, though, depends on the concurrency control that the programs use.
By default, ADO.NET uses optimistic concurrency. This means that the program checks to see whether the database row that’s going to be updated or deleted has been changed since it was retrieved. If it has, a concurrency exception occurs and the update or deletion is refused. Then, the program should handle the exception.
If optimistic concurrency isn’t in effect, the program doesn’t check to see whether a row has been changed before an update or deletion takes place. Instead, the operation proceeds without throwing an exception. This is referred to as “last in wins” because the last update overwrites any previous update. And this leads to errors in the database.
How to avoid concurrency errors
For many applications, concurrency errors rarely occur. As a result, optimistic concurrency is adequate because the users will rarely have to resubmit an update or deletion that is refused.
If concurrency is likely to be a problem, a program that uses a dataset can be designed so it updates the database and refreshes the dataset frequently. That way, concurrency errors are less likely to occur.
Another way to avoid concurrency errors is to design a program so it retrieves and updates just one row at a time. That way, there’s less chance that two users will retrieve and update the same row at the same time.
/ 13 
An introduction to ADO.NET
