Enough Talk, Let s Look at Some Code

Enough Talk, Let's Look at Some Code

We've been discussing the DataSet object and how it is the cornerstone of ADO.NET. However, the DataSet has a smaller and faster cousin known as the DataReader object. This object is optimized to retrieve a read-only, forward-only stream of data from a database and is used for displaying data. It's fast and increases your application's performance, but you can't edit the data it returns and it handles only one record at a time. In spite of these limitations, however, the DataReader is much easier to understand than the DataSet, so we'll use the DataReader in our first example. The concepts are the same.

Many times in our applications we might simply want to grab data from a data source and fill a data grid for display. The DataReader is the perfect choice for this task, and the DataReader is a member of a .NET managed provider. You need to understand and perform three operations when working with ADO.NET:

• A connection to a data source. This is a physical connection to a data source such as SQL Server, Oracle, an Access database, an XML file, a flat file, an e-mail store, or whatever.

• A command that represents a directive to retrieve (select) or manipulate (insert, update, or delete) the data in the data source.

• A DataSet or DataReader object to hold the data that is retrieved from the data source.

Connecting to Our Data Source

To move data between a data source and your application, you must first have a connection to that data source. In ADO.NET, you can create and manage a connection using one of the two connection objects: the SqlConnection object, which manages a connection to a data source running SQL Server version 7 or later, or the OleDbConnection object, which manages a connection to any data source accessible via OLE DB.

The SqlConnection object is optimized for use with SQL Server 7 or later by bypassing the OLE DB layer, among other things. The OleDbConnection object interacts with OLE DB to expose a consistent API for a variety of data sources, everything from simple text files to spreadsheets and, of course, full-featured databases.

As I mentioned earlier, both of these connection objects expose roughly the same members. However, the specific members available with a given OleDbConnection object depend on what data source it is connected to. In other words, not all data sources support all members of the OleDbConnection class.

The primary property associated with a connection object is the ConnectionString property. This property consists of a string with attribute-value pairs holding the information required to point to a specific database and log on to it. A typical ConnectionString property looks something like the following:

Dim sqlConn As SqlConnection = New _ SqlConnection("server=(local)\NetSDK;uid=QSUser; " & _ "pwd=QSPassword;database=northwind")

The most common attribute-value pairs used by an SqlConnection object are the server, the user ID, the password, and the data source to connect to. When working with a connection object, you can either set the ConnectionString property as a single string, as shown in the previous code example, or you can set individual connection properties. (If your data source requires connection string values that are not represented by individual properties, you must set the ConnectionString property.)

The two primary methods for managing connections are Open and Close. The Open method uses the information in the ConnectionString property to contact the data source and establish an open connection. The Close method tears the connection down. Closing connections is essential because most data sources support only a limited number of open connections, and open connections take up valuable system resources.

Commands to Manipulate Data from the Data Source

After establishing a connection to a data source with a connection object, you can execute commands and return results from the data source using a Command object. A Command object can be created using the Command constructor, as we do in the following code example, or by calling the CreateCommand method of the connection object. If you are connecting to an SQL Server database, an SqlCommand object might be configured to look like this.

Dim cmdSqlCommand As SqlCommand = _ New SqlCommand("SELECT CustomerID, " & _ "CompanyName FROM Customers", sqlConn)

When creating a Command object using the Command constructor, you specify an SQL statement to execute at the data source as the first parameter and a connection object as the second parameter.

Now that we've built our connection string to specify which data source we are interested in and constructed our command on the data we want to retrieve, we simply execute the Open method of the connection object to establish the connection.

sqlConn.Open()

Creating the DataReader Object

Now we need someplace to hold the data that is retrieved from the database, which is how we'll use the DataReader object. After creating an instance of a Command object, you create a DataReader by calling the ExecuteReader method on the command. This method retrieves rows from a data source as specified in the Command object. At this point the DataReader is filled with records from the database.

Dim drSqlDataReader As SqlDataReader = _ cmdSqlCommand.ExecuteReader()

Now that our DataReader is filled with records, we can display them. In this example, we get the first and second columns from each record (remember the first column is 0, the next 1, and so on) and send them to the output box.

You can get at the data in each column of the returned row in two ways. You can either provide the name of the field you want to retrieve, or you can use the ordinal reference of the column, as we do in the code example that follows. To get the best performance from the DataReader, you can use a series of methods to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). We use GetString so that the system doesn't need to look up the data type and then handle displaying the strings. This is an example of a lean-and-mean, no-frills data output machine.

When the DataReader is first filled, it is positioned to a Null record until its Read method is called for the first time. This approach is different from classic ADO logic in which the first record in a result set is pointed to by default. In this example, we loop through each record and display the two fields in the output console.

The DataReader object does not have a MoveNext method. Not surprisingly, many developers often forget to add the MoveNext statement and then complain about performance because their code never terminates. The Data Reader.Read method now automatically advances the cursor to the next record and returns False if there is no more data to read. Notice that the next example simply uses a Do While Loop construct to read each record, one at a time.

While the DataReader permits direct, high-performance access to the database, it provides only read-only and forward-only access to the data. The results it returns are not memory resident, and it can access only one record at a time. While these limitations put less load on the server's memory requirements, you can use the DataReader only to display data. Remember, though, that many applications need just this display of data, so when you can, use the DataReader because it provides the best performance.

Do While drSqlDataReader.Read() Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _ drSqlDataReader.GetString(0), drSqlDataReader.GetString(1)) Loop

Finally we close both the DataReader object and the connection to the database.

drSqlDataReader.Close() sqlConn.Close()

Putting the Pieces of Our DataReader Together

Now that we've looked at the pieces, let's put them together in a simple program. Start a new Visual Basic Windows Application project and add the following statements.

Imports System.Data Imports System.Data.SqlClient Imports System

The rest of the code could easily be placed in the Click event handler of a button if you are interested in running it. The first step we'll take is to dimension an SqlConnection object to define the database we are interested in and an SqlCommand object that defines the table and records from that table we want to retrieve. Then we dimension an SqlDataReader object that will hold the data. Finally the records are iterated and written to the console. As good practice, we close both the DataReader and the connection. That's all there is to it. Here's the code:

'Note that you must adjust the following connection 'parameters for your Microsoft SQL Server connection Dim sqlConn As SqlConnection = New _ SqlConnection("Initial Catalog=Northwind;" & _ "Data Source=localhost;Integrated Security=SSPI") Dim cmdSqlCommand As SqlCommand = New _ SqlCommand("SELECT CustomerID, CompanyName " & _ "FROM Customers", sqlConn) sqlConn.Open() Dim drSqlDataReader As SqlDataReader = _ cmdSqlCommand.ExecuteReader() Console.WriteLine("---------") Do While drSqlDataReader.Read() Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _ drSqlDataReader.Item(0).ToString(), _ drSqlDataReader.GetString(1)) Loop drSqlDataReader.Close() sqlConn.Close()

When we run the program, the results are piped to the output box (which is the console during design mode). In these few lines of code, we've examined the key elements of retrieving data and displaying it, as you can see in Figure 10-5.

Figure 10-5

Data retrieved through a DataReader object.

This program has no frills, but it got our feet wet with the ADO.NET. DataReader. While the DataReader is fast and useful in certain situations, many times we just need more functionality. Our application might need to permit a user to scroll through or edit the data that's retrieved. And when the user makes changes, we want to update the data source from which the original data was retrieved. In these cases, we need to use the DataSet object.