Data Readers

for RuBoard

To make these concepts concrete, let us use some of these classes to access a database. This example is found in this chapter's Connected subdirectory.

We will need a connection to the database, a command to issue against the database, and a reader to retrieve the data, so we declare three objects: ^[6]

^[6] The objects are declared outside of the try/catch block so that they can be used in the finally block. They are set to null because their use in the finally block could theoretically occur before they are initialized inside the try block (and so the compiler indicates with a warning).

 SqlConnection conn = null;  SqlCommand command = null;  SqlDataReader reader = null; 

The connection string that is used to connect to the database is set next . You will have to replace the server element with the appropriate value for your machine. You will also have to specify the appropriate user and password for the database. ^[7] You can also set the connect string as an SqlConnection property. A simple select statement will be the command run against the database.

^[7] For Northwind the usual uid=sa;pwd=; will probably work.

 String ConnString =      "server=localhost;uid=sa;pwd=;database=Northwind";  string cmd = "select CustomerId, CompanyName from                                         Customers";  [8]  

^[8] Here and several other places long SQL statement strings are broken up and placed on multiple lines to allow for book formatting.

Figure 9-2 shows the tables and stored procedures for the Northwind database.

Figure 9-2. Server Explorer showing Northwind database tables and stored procedures.

We create an SqlConnection object and then a create an SqlCommand object that is attached to the connection just created. A connection to the database must be opened before any command can be executed.

 conn = new SqlConnection(ConnString);  command = new SqlCommand(cmd, conn);  conn.Open(); 

When the command is executed using the ExecuteReader method on the SqlCommand object, an SqlDataReader instance is returned. This reader can be used to iterate through the result set. The column names can be used to fetch the data elements from the current result set row.

 reader = command.ExecuteReader();  if (reader != null)  {     Console.WriteLine("CustomerId\tCompanyName");     while (reader.Read())      Console.WriteLine("{0}\t\t{1}", reader["CustomerId"],                                    reader["CompanyName"]);  } 

The reader and the connection are closed in the finally block.

 if (reader != null) reader.Close();  if (conn.State == ConnectionState.Open) conn.Close(); 

If the connection is not closed explicitly, the finalizer on the SqlConnection object will eventually get called and the connection will be closed. Since the garbage collector is not deterministic, there is no way to know when this will happen. Therefore always close your connections. If you do not, you will use more connections than you need (even with connection pooling), and this could interfere with your applications scalability. You could also run out of connections.

Here is the output the program produces:

 CustomerId      CompanyName  ALFKI           Alfreds Futterkiste  ANATR           Ana Trujillo Emparedados y helados  ANTON           Antonio Moreno Taquera  AROUT           Around the Horn  BERGS           Berglunds snabbkp  BLAUS           Blauer See Delikatessen  BLONP           Blondesddsl pre et fils  BOLID           Blido Comidas preparadas  BONAP           Bon app'  BOTTM           Bottom-Dollar Markets  BSBEV           B's Beverages  ... 

You use the Visual Studio.NET Server Explorer to check the results of the program. Select the Customers table under the Northwind database explorer and right-click to get a context menu. Select "Retrieve Data from Table," and you can retrieve the data associated with the table and compare it with the results of the program. You will see that they are the same. Figure 9-3 shows this.

Figure 9-3. Server Explorer showing Customers table, fields, and data.

The Connected Database Scenario

This scenario of working with a database is referred to as connected. The program connects to the database, does the work it needs to do, and then disconnects. You can run through the returned data only in the forward direction. This corresponds to the classic ADO forward-only cursor/recordset. In the connected mode you must open and close the database connection explicitly.

Keeping a connection continually open is not the best way to work in an environment where you want to minimize the resources consumed (connections are expensive) to allow for scalability. A DataSet allows you to work disconnected from a data source. Nonetheless, as will be discussed later, depending on your concurrency assumptions, using a DataReader instead of a DataSet might still be the right approach.

As will be discussed later, the SqlConnection is used with the DataAdapter to establish connections with the database in the same way as illustrated here with the SqlCommand . SqlConnection also controls database properties such as transactions and isolation levels. A root transaction is issued by invoking the BeginTransaction method on the SqlConnection class. ^[9] If in the previous example we connected SQL Server through the OleDbConnection class, the connection string would be:

^[9] Since OLEDB allows for nested transactions, nested transactions can be started by invoking the Begin method on the OleDbTransaction class.

 "Provider=SQLOLEDB.1;server=localhost;uid=sa;pwd=;                                      database=Northwind"; 

You would have to provide the correct server, user, and password. While the SqlCommand executes a command against a database in the same way whether you use a DataAdapter or an SqlDataReader , the mechanics of doing so is different. This will become clearer when we discuss the SqlDataAdapter class.

You specify the type of SqlCommand with the CommandType property. For the Sql data provider this can be either Text (the default) or StoredProcedure. The CommandText can also be specified as a property. We will soon show how parameters can be applied to database commands.

An SqlDataReader instance is returned by the ExecuteReader method on an SqlCommand instance. If you wanted to program in a way that was independent of a data provider, you could use the IDataReader interface instead. You could then invoke methods on the interface instead of an object.

 IDataReader idr = command.ExecuteReader(); 

Similar techniques can be used with the other data-provider classes that implement interfaces used by multiple data providers. Until the SqlDataReader instance is closed, the SqlCommand object cannot be used for any purpose other than for executing its Close method.

Executing SQL Statements

The ExecuteReader method on the SqlCommand returns a DataReader instance. Data is returned when the command is a select statement or a stored procedure that returns results. When you know there will be no results returned it is more efficient to use the ExecuteNonQuery method. The SqlCommand.ExecuteReader method uses the stored procedure sp_executesql . Some commands that use "SET" statements may not work properly. Other providers might have different restrictions when their ExecuteReader method is used.

In general, for commands that do not return data, use the SqlCommand.ExecuteNonQuery method. The NonQuery example shows how this works. For illustrative purposes this example connects to SQL Server through the OleDb data provider.

 string cmd = "update Customers set CompanyName =           'Maria Inc' where ContactName = 'Maria Anders'";  ...  command = new OleDbCommand(cmd, conn);  int NumberRows = command.ExecuteNonQuery(); 

The number of rows returned should be 1. Figure 9-4 shows the results of the change to the first row.

Figure 9-4. Rows in Customers table in the Server Explorer showing the changed ContactName. Compare with Figure 9-3 to see the original value of the first row.

For insert, update, and delete statements, the number of rows affected is returned. SQL Server returns -1 for all other statements (Native or OLEDB provider). Other providers might return 0 or -1.

To fetch a single value (such as an aggregate computation) use the ExecuteScalar method. Against SQL Server 2000 you can use the SqlCommand.ExecuteXmlReader to retrieve XML results directly from the server.

DataReader

When created, the SqlDataReader is positioned before the first record returned of the first result set. You must invoke the Read method before accessing any data. As the DataReader example demonstrates , the item property can be used to access the individual fields or column values in the current row:

All the fields in a row can be accessed with the GetValues method.

 object[] fields = new object[NumberFields];  ...  int NumberFields = reader.GetValues(fields); 

GetValue returns the column value in its native format. You can also access the column values as particular datatypes: GetBoolean , GetDecimal , GetString , etc. The GetName method returns the column name of a particular column.

To reinforce what was mentioned earlier, only one record at a time is accessible with a DataReader. Make sure you close the DataReader when you are done with it.

Multiple Result Sets

The SqlDataReader class can handle multiple result sets, as the DataReader example demonstrates. Two queries separated by a semicolon represent two SQL statements that will cause two results sets to be generated, one for each statement.

 string ConnString =      "server=localhost;uid=sa;pwd=;database=Northwind";  string cmd = "select CustomerId, CompanyName from                 Customers where CustomerId like 'T%';select                 CustomerId, CompanyName from Customers                 where CustomerId like 'W%'";  ...  int ResultSetCounter = -1;  int NumberFields = 0;  ...  reader = command.ExecuteReader();  if (reader != null)  {   NumberFields = reader.FieldCount;   object[] fields = new object[NumberFields];   Console.WriteLine("Result Set\tCustomerId\tCompanyName");   do   {     ResultSetCounter++;     while(reader.Read())     {        NumberFields = reader.GetValues(fields);       Console.Write("\t{0}", ResultSetCounter);         for (int i = 0; i < NumberFields; i++)         {           Console.Write("\t\t{0}", fields[i]);         }       Console.Write("\n");       }     }while(reader.NextResult());  }  ... 

The FieldCount method returns the number of columns in the result set. Since the GetValues method returns the native format of the data, an array of objects is passed to it. The NextResult method navigates to the next result set.

for RuBoard