Reading Data with the DataReader | Beginning Visual C#supAND#174;/sup 2005

In the following Try It Out, you just get some data from one table, the Customers table in the SQL Server/ MSDE Northwind sample database (you look at this database again in Chapter 25). the Customers table contains rows and columns with data about the customers of Northwind traders. The first example here uses a DataReader to retrieve the CustomerID and CompanyName columns from this table.

Try It Out – Reading Data with the DataReader

Follow these steps to create the example in Visual Studio 2005:

Create a new console application called DataReading in the directory C:\BegVCSharp\ Chapter24.

Begin by adding the using directives for the ADO.NET classes you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.SqlClient;  // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion

Now add the following code to the Main() method:

static void Main(string[] args)  { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" +  "Database=northwind"); // Open connection thisConnection.Open(); // Create command for this connection SqlCommand thisCommand = thisConnection.CreateCommand(); // Specify SQL query for this command thisCommand.CommandText =  "SELECT CustomerID, CompanyName from Customers"; // Execute DataReader for specified command SqlDataReader thisReader = thisCommand.ExecuteReader(); // While there are rows to read while (thisReader.Read()) { // Output ID and name columns Console.WriteLine("\t{0}\t{1}",  thisReader["CustomerID"], thisReader["CompanyName"]); } // Close reader thisReader.Close(); // Close connection thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

Compile and execute this program. You will see a list of customer IDs and company names, as shown in Figure 24-2. If you don't see the output below, don't worry, you come to the possible problems in a moment.

Figure 24-2

How It Works

The first step is to reference the System.Data namespace and your provider as described before. You're going to use the SQL Server .NET provider in these examples, so you need the following lines at the start of your program:

using System.Data; using System.Data.SqlClient;

There are five steps to retrieving the data from the program:

Connect to the data source.
Open the connection.
Issue a SQL query.
Read and display the data with the DataReader.
Close the DataReader and the connection.

You look at each of these steps in turn.

First, you need to connect to your data source. This is done by creating a connection object using a connection string. The connection string is just a character string containing the name of the provider for the database you want to connect to, the login information (database user, password, and so on), and the name of the particular database you want to use. Let's look at the specific elements of this connection string; however, keep in mind that these strings differ significantly between data providers, so you need to look up the specific connection information for your data provider if it is different from this example (the connection information for Access is shown a little later in the chapter).

The line where you create the connection object looks like this:

SqlConnection thisConnection = new SqlConnection(      @"Server=(local)\sqlexpress;Integrated Security=True;" +      "Database=northwind");

SqlConnection is the name of the connection object for the SQL .NET data provider; if you were using OLE DB you would create an OleDbConnection, but you see this in the next example. The connection string consists of named entries separated by semicolons; let's look at each one. The first is:

Server=(local)\sqlexpress;

This is just the name of the SQL Server you are accessing, in the form computername\instancename. The computer name (local) is a handy SQL Server shorthand name that refers to the server instance running on the current machine. You can also substitute the actual network name of your computer; for example, my laptop computer is \\roadrunner, so I could also use roadrunner\sqlexpress as my server name.

sqlexpress is the SQL Server instance name. There can be multiple copies of SQL Server installed on one machine, and the instance name, set at installation time, is a how you tell SQL Server which one you want. Sqlexpress is the default instance name used when you install SQL Express. If you have another version of SQL Server installed, the instance name will differ — there may be no instance name (in which case you would use (local) or your machine name by itself), or it may be a different name, such as (local)\NetSDK used for the MSDE installations with previous versions .NET Framework.

Note that the @ sign prefacing the connection string indicates a string literal, making the backslash in this name work; otherwise double backslashes (\\) are necessary to escape the backslash character inside a C# string.

If you installed the SQL Server you are working with, you know the name of the SQL Server instance. Otherwise, you have to check with your SQL Server or network administrator to find out what name to use.

The next part of the connection string specifies how to log in to the database; here, you use the integrated security of the Windows login so no separate user and password need to be specified:

Integrated Security=True;

This clause specifies the standard built-in security for SQL Server and Windows. Alternatively, instead of the Integrated Security clause you could specify a username and password clause, as in User=sa; PWD=secret. Using the built-in security of your Windows login is preferable to using hard-coded user- names and passwords in connection strings!

Finally, the particular database you want to use is specified, in this case, the Northwind sample:

Database=northwind

You must have installed the Northwind sample for this database to be found; it may already be present if you are using an existing SQL Server installation, though many database administrators choose to omit it to save space.

Anyway, you now have a connection object that is configured for your machine and database (but the connection is not yet active; to do this you must open it).

Once you have a connection object, you can move on to the second step. The first thing you want to do with the connection object is open it, which establishes the connection to the database:

thisConnection.Open();

If the Open() method fails, for example if the SQL Server cannot be found, a SqlException exception will be thrown and you will see a message like that shown in Figure 24-3.

Figure 24-3

This particular message indicates that the program couldn't find the SQL Server database or server. Check that the server name in the connection string is correct and that the server is running.

The third step is to create a command object and give it a SQL command to perform a database operation (such as retrieving some data). The code to do this is as follows:

SqlCommand thisCommand = thisConnection.CreateCommand(); thisCommand.CommandText = "SELECT CustomerID, CompanyName from Customers";

The connection object has a method called CreateCommand() to create a command associated with this connection, so you will use this to get your command object. The command itself is assigned to the CommandText property of the command object. You're going to get a list of the customer IDs and the company names from the Northwind database, so that is the basis for your SQL query command:

SELECT CustomerID, CompanyName from Customers

The SELECT command is the SQL command to get the data from one or more tables. A common error is to mistype the name of one of the tables, resulting in another exception:

thisCommand.CommandText = "SELECT CustomerID, CompanyName from Customer";

Whoops! I forgot the "s" in Customers — I get the exception shown in Figure 24-4.

Figure 24-4

Fix the typo, rebuild, and move on.

The fourth step is to read and display the data. First, you have to read the data — you do this with a DataReader. The DataReader is a lightweight, fast object for quickly getting the results of a query. It is read-only, so you can't use it to update data — you get to that after you finish this example. As you saw in the previous section, you use a method from the last object you created, the command object, to create an associated instance of the object you need next — in this case, the DataReader:

SqlDataReader thisReader = thisCommand.ExecuteReader();

ExecuteReader() executes the SQL command at the database, so any database errors are generated here; it also creates the reader object for reading the generated results — here you assign it to thisReader.

There are several methods for getting the results out of the reader, but the following is the usual process. the Read() method of DataReader reads a single row of the data resulting from the query, and returns true while there is more data to read, false if there is not. So, you set up a while loop to read data with the Read() method and print out the results as you get them on each iteration:

while (thisReader.Read()) {    Console.WriteLine("\t{0}\t{1}",                       thisReader["CustomerID"], thisReader["CompanyName"]); }

So, while Read() returns true, Console.WriteLine("\t{0}\t{1}", ...) writes out a line with two pieces of data separated by tab characters (\t). the DataReader object provides an indexer property (see Chapter 11 for a discussion of indexers). The indexer is overloaded, and allows you to reference the columns as an array reference by column name: thisReader["CustomerID"], thisReader["CompanyName"], or by an integer: thisReader[0], thisReader[1].

When Read() returns false at the end of the results, the while loop ends.

The fifth and final step is to close the objects you opened, which include the reader object and the connection object. Each of these objects has a Close() method, which you call before exiting the program:

thisReader.Close(); thisConnection.Close();

That's all there is to accessing a single table!

The same program can be written with just a few simple changes to use the Microsoft Access version of this database (nwind.mdb). This can be found in the C:\Program Files\Microsoft Office\Office\ Samples directory (if you have Office). Make a copy of the file (in a temporary directory such as C:\ tmp\nwind.mdb), so you can always go back to the original.

If you do not have Office already, download Nwind.exe from:

www.microsoft.com/downloads/details.aspx?familyid=c6661372-8dbe-422b-8676- c632d66c529c&displaylang=en

Execute Nwind.exe to install the sample database; it prompts you for a directory, so you can place it wherever you want to, such as in c:\tmp. Except for the details of the connection string, the changes you make in the following Try It Out will work for any other OLE DB data source.

Try It Out – Reading from an Access Database

Create a new console application called ReadingAccessData in the directory C:\BegVCSharp\ Chapter24.

Begin by adding the using directives for the OLE DB provider classes you will be using:

#region Using Directives using System; using System.Data;            // Use ADO.NET namespace using System.Data.OleDb;      // Use namespace for OLE DB .NET Data Provider using System.Collections.Generic; using System.Text; #endregion

Now add the following code to the Main() method:

static void Main(string[] args)     { // Create connection object for Microsoft Access OLE DB Provider; // note @ sign prefacing string literal so backslashes in path name; // work OleDbConnection thisConnection = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\tmp\nwind.mdb"); // Open connection object thisConnection.Open(); // Create SQL command object on this connection  OleDbCommand thisCommand = thisConnection.CreateCommand(); // Initialize SQL SELECT command to retrieve desired data thisCommand.CommandText =  "SELECT CustomerID, CompanyName FROM Customers"; // Create a DataReader object based on previously defined command object OleDbDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { Console.WriteLine("\t{0}\t{1}", thisReader["CustomerID"], thisReader["CompanyName"]); } thisReader.Close(); thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); }

How It Works

Instead of the SqlConnection, SqlCommand, and SqlDataReader objects, you create OleDbConnection, OleDbCommand, and OleDbDataReader objects. These objects work essentially the same way as their SQL Server counterparts.

Accordingly, you change the using directive that specifies the data provider from

using System.Data.SqlClient;

using System.Data.OleDb;

The only other difference is in the connection string, which you need to change completely. The first part of an OLE DB connection string, the Provider clause, specifies the name of the OLE DB provider for this type of database. For Microsoft Access databases, this is always the following name (Jet is the name of the database engine included in Access):

Provider=Microsoft.Jet.OLEDB.4.0;

If you are using a different OLE DB provider for a different database or data format, then specify the name of that provider in the Provider clause.

The second part of the connection string is the Data Source clause, and in the OLE DB/Microsoft Access case, this simply specifies the name of the Microsoft Access database file (.mdb file) you are going to open:

Data Source=C:\tmp\nwind.mdb

Once again, you have the @ sign preceding the connection string to specify a string literal, so that the backslashes in the path name work; otherwise, double backslashes (\\) would be necessary to escape the filename in C#.