Running Queries

for RuBoard

After you have a connection to a data source, you can retrieve data from it by executing a query. The results of a query are stored in one of the ADO.NET data objects, typically either a DataReader or a DataSet object. The type of object can differ , depending on how you intend to work with the data.

This is in contrast to ADO.old, which invariably returned data in the form of a Recordset object. In ADO.NET, several objects can contain data. Table 11.2 summarizes these objects.

Table 11.2. Summary of Data-Containing Objects in ADO.NET

Object	Description
DataSet	Read/write; connectionless; contains one or more DataTable objects with relationships defined in a collection of DataRelation objects; supports filtering and sorting; supports automatic serialization to XML; contains an object (the DataView) that can be bound to data-aware ASP.NET Web forms controls
DataReader	Read-only; connection-based; supports scrolling forward only
DataTable	Contained by the DataSet; contains sets of rows and columns
DataColumn	Contained by the DataSet object; defines the schema (structure) of a DataTable
DataRow	Contained by the DataSet object; stores the data in an individual row

As you can see, the DataSet object supports far more features than the DataReader. But as with all benefits in technology, there are trade-offs in terms of programming complexity and performance. The key is to understand when it's most appropriate to use the various options available to you; these issues will be discussed in the next few sections of this chapter.

About the SqlDataReader Object

You can use the SqlDataReader object to execute queries and retrieve data from a data source. You typically use the SqlDataReader object in situations where you want to get a small quantity of data and display it quickly. If you're interested in retrieving and manipulating larger sets of data or you want to perform actions such as updating and deleting records, you will need to use a data command object or the DataSet object, discussed later in this chapter.

NOTE

The SqlDataReader class discussed in this section is a member of the System.Data.SqlClient namespace. The OLE DB provider equivalent of this class is called OleDbDataReader; it is found in the System.Data.OleDb namespace. A full list of members of the SqlDataReader class can be found in the reference section at the end of this chapter.

The SqlDataReader is the rough equivalent of the read-only, forward-only Recordset object found in ADO.old. To create a SqlDataReader, you begin by creating a connection to the database and then executing a command that returns data. Using command objects to execute commands against data sources is discussed in the next section.

Executing Commands Using Command Objects

ADO.old provides the ADO Command object to execute commands against data sources. The ADO Command object can execute queries that retrieve data; it can also execute commands that insert, update, or delete data (these commands are referred to as data-manipulation commands ). Finally, command objects can also execute stored procedures, which are bits of data-access code stored on the server.

ADO.NET, in contrast, gives you two ways to execute commands against data sources: the command object and the data adapter. The ADO.NET Command object is conceptually similar to the ADO.old Command object; it's used when you want to execute a quick command against a data source. The ADO.NET data adapter, in contrast, is used when you need to perform more involved operations against the back end ”those operations that might have required a cursor in ADO.old.

Data adapters are also useful when you need to marshal a complex or hierarchical set of data remotely. Because they are based on XML, you could (in theory) remote the DataSet to another platform (one that does not contain a .NET implementation), manipulate the data ”including making inserts and updates ”and then remote the DataSet back to the .NET server. Putting this to work in practice would pose challenges (it would depend on the quality of tools available on the non-.NET platform), but the promise of standard XML makes it possible.

Ultimately, think of a command object as a way to manipulate data on the server or to return a data reader. The data adapter, in contrast, is the way to create a DataSet object, which establishes communication between the DataSet object and a specific data source.

In this chapter, we'll start simply, using the data reader and command objects first, and then move on to the more complex and feature-rich data adapter and DataSet objects.

Executing Text-Based Select Commands Using a Data Reader Object

A text-based command is a command that is constructed in your code at runtime. Commands are expressed in a query language that your data source can understand; this is typically (although not necessarily ) Structured Query Language, or SQL.

Text-based commands are in contrast to stored procedures, which are defined ahead of time and reside on the server. (We'll discuss how to call stored procedures later in this chapter.) Text-based commands can perform selects (commands that retrieve data) or data manipulation (commands that insert, update, or delete data).

You can use a text command to retrieve a data reader object. Listing 11.4 shows an example of a text-based command that executes a select query (using a SqlCommand object) and returns a SqlDataReader object.

Listing 11.4 Executing a Text Command That Returns a SqlDataReader Object

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender,EventArgs e)   {     SqlConnection cn;     SqlCommand cm;     SqlDataReader dr;     String strSQL;     cn = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs;");     strSQL = "SELECT TOP 10 au_fname, au_lname FROM authors";     cm = new SqlCommand(strSQL, cn);     // ** Open connection     try     {       cn.Open();     }     catch(SqlException sx)     {       Response.Write("Connection failed: " + sx.Message);     }     // ** Execute command     dr = cm.ExecuteReader();     while(dr.Read())     {         Response.Write(dr["au_fname"] + " " + dr["au_lname"] + "<BR>");     }     cn.Close();   } </SCRIPT> 

This code example builds on the ADO.NET connection object examples used in Listing 11.1. It adds a SqlCommand object that retrieves the first and last names of the first 10 authors in the pubs database. It also includes a SqlDataReader object that provides access to the information retrieved by the query.

NOTE

The SqlCommand class discussed in this section is a member of the System.Data.SqlClient namespace. The OLE DB provider equivalent of this class is called OleDbCommand; it is found in the System.Data.OleDb namespace. A full list of members of the SqlCommand class can be found in the reference section at the end of this chapter.

You can see from the code that the SqlDataReader object is initially created with a call to the ExecuteReader method of the SqlCommand object. If you're familiar with ADO.old, this pattern may be familiar to you ”you can return an ADO Recordset object through a call to the Execute method of the ADO.old Command object as well.

After you have successfully created the SqlDataReader object, you can traverse the reader by executing its Read method in a loop. The Read method returns a true or false value depending on whether any data is readable; when you reach the end of data, the object returns false .

NOTE

The way that data reader objects in ADO.NET retrieve data can be contrasted to the somewhat awkward method that ADO.old employed to retrieve field values from a Recordset object ”in ADO.old, you typically set up a while loop with a call to the MoveNext method inside the loop. If you forgot to include a call to the MoveNext method, your loop became infinite, and you kicked yourself for making a silly mistake. (You may have guessed that one of the authors of this book commits this error more frequently than he would like.)

As you work with data reader objects, remember that the data they store is read-only and forward-only. You can't make changes to the data returned by the data reader object (at least not through the data reader itself), and you can't jump around in the resultset like you can with a cursor-based resultset. However, the data reader is among the best-performing methods of all the data access methods in ADO.NET. As a result, data reader objects are useful in situations where you want to display or export data quickly without complex code or repeated interaction with the data source.

Executing Stored Procedures That Return Data

A stored procedure is a small chunk of code that is embedded in a database server. Stored procedures are commonly used in database applications because they execute more efficiently than database commands constructed on-the-fly . They also serve to separate data access code from business logic, which can be helpful for managing change and complexity in a database application.

NOTE

This section describes how to call stored procedures, but does not cover how to create them (that would require another whole book). Fortunately, such books have already been written. Two good books on writing stored procedures in SQL Server are Transact-SQL Programming (O'Reilly) and The Guru's Guide to Transact-SQL (Addison-Wesley). However, note that as of this writing, neither of these books covers recent changes in Transact-SQL introduced in SQL Server 2000. They're still great for learning both basic and advanced T-SQL programming and contain examples that demonstrate at least 90% of the operations you're ever likely to perform as a T-SQL programmer.

You can call stored procedures from ADO.NET using an ADO.NET command object (OleDbCommand in the OLE DB provider or SqlCommand in the SQL Server provider). You can also specify a stored procedure as one of the four commands in a data adapter object. The four commands that are possible as data adapter object properties are SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand.

Most stored procedures take parameters; for example, a stored procedure that performs a reporting and aggregation function might take a begin date and an end date as parameters. Similarly, a procedure to return information about a given customer or a given order would typically take a customer ID or order ID as one of its parameters. Parameterization enables you to limit the amount of data returned by the stored procedure.

We'll discuss stored procedures that take parameters later in this chapter. For now, we'll start simply and call a parameter-free stored procedure in the Northwind database. The name of this procedure is "Ten Most Expensive Products" and, not surprisingly, it is a SELECT procedure that should always return 10 rows of data.

In the SQL Server provider, the connection object is called SqlConnection; in the OLE DB provider it is called OleDbConnection. Both objects work essentially the same way, but for our examples in this section, we'll use the SQL Server command object. The most common SqlConnection constructor takes two arguments: a command in the form of a string and a connection object. Executing a command that selects records produces a SqlDataReader object that you can then use to display information.

Calling a stored procedure that returns data is similar to executing a text command that returns data. To call a stored procedure, you first create a connection and a command object, and then set the command object's CommandType property to the enumerated value CommandType.StoredProcedure.

Listing 11.5 shows an example of how to execute a query and retrieve data by calling a stored procedure using a SqlCommand and SqlDataReader object.

Listing 11.5 Calling a Simple Stored Procedure Using the SqlCommand Object

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender, EventArgs e)   {     SqlConnection cn;     SqlCommand cm;     SqlDataReader dr;     cn = new SqlConnection("server=localhost;uid=sa;" +                 "pwd=;database=Northwind;");     cm = new SqlCommand("Ten Most Expensive Products", cn);     cm.CommandType = CommandType.StoredProcedure;     // ** Open connection     try     {       cn.Open();     }     catch(SqlException sx)     {       Response.Write("Connection failed: " + sx.Message);     }     // ** Execute command     dr = cm.ExecuteReader();     while(dr.Read())     {         Response.Write(dr.GetString(0) + " " + dr.GetDecimal(1) + "<BR>");     }     dr.Close();     cn.Close();  } </SCRIPT> 

This code again modifies Listing 11.4, which demonstrates executing a text command and retrieving a SqlDataReader object. In addition to setting the CommandType property of the SqlCommand object to CommandType.StoredProcedure, we also replace the command text with the name of the stored procedure ("Ten Most Expensive Products"). This user -defined stored procedure returns two columns of data: the name of the product and a column called UnitPrice. The data type of the UnitPrice column is of the currency data type (called "money" in SQL Server parlance) but is expressed as a decimal data type in .NET because that's how .NET deals with currency data. As a result, we use the GetDecimal method of the SqlDataReader object to extract prices from each row.

Passing Parameters to Stored Procedures

Just like function calls and subroutines in your code, most stored procedures take parameters. When you call a stored procedure that has one or more parameters, you must supply values for those parameters in code before you execute the stored procedure.

In ADO.NET, you can pass parameters to stored procedures in two ways. To pass parameter values, you can either include the parameter as part of the command itself (this is known as the inline method of passing parameters), or you can use the Parameters collection of the connection object. (This technique is syntactically closer to the ADO.old method of passing stored procedure parameters, but requires a bit more code than the inline approach.)

Like the previous examples, calling a command requires a connection object and a command object that are appropriate to the provider you're using. As we've been doing throughout this chapter, we'll stick with the SQL Server objects, SqlConnection and SqlCommand.

For our stored procedure, we'll use the SalesByCategory stored procedure in the Northwind database. This is a fairly simple select procedure that requires a single parameter: a product category (a string value found in the list of product categories in the Categories table), examples of which include Beverages , Produce , and Seafood .

Listing 11.6 shows how to pass a parameter to a stored procedure using the Parameters collection contained by the Command object.

Listing 11.6 Calling a Parameterized Stored Procedure Using the Parameters Collection of the SqlCommand Object

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender, EventArgs e)   {     SqlConnection cn;     SqlCommand cm;     SqlDataReader dr;     SqlParameter sp;     cn = new SqlConnection("server=localhost;uid=sa;" +                     "pwd=;database=Northwind;");     cm = new SqlCommand();     cm.Connection = cn;     cm.CommandType = CommandType.StoredProcedure;     cm.CommandText = "SalesByCategory";     // ** Add parameter and parameter value     sp = cm.Parameters.Add(new SqlParameter("@CategoryName",                             SqlDbType.NVarChar, 15));     sp.Value = "Condiments";     // ** Open connection     try     {       cn.Open();     }     catch(SqlException sx)     {       Response.Write("Connection failed: " + sx.Message);     }      // ** Execute command      dr = cm.ExecuteReader();      Response.Write("<B>" + sp.Value + "</B><BR>");      while(dr.Read())     {       Response.Write(dr.GetString(0) + " " + dr.GetDecimal(1) + "<BR>");     }     dr.Close();     cn.Close();   } </SCRIPT> 

This version of the code uses the Add method of the Parameters collection contained by the SqlCommand object, cm. By adding this object to the command object's Parameters collection and then setting its Value property to the value you want to supply to the parameter, you tell the stored procedure which category of data to retrieve.

Note that the Add method used in the example takes a SqlParameter object as an argument. You can use several other constructors for the SqlParameter, depending on how much information you want to specify about the parameter. However, in most cases, the constructor we used (specifying the parameter name, data type, and size ) will be the one you use most frequently. Note that the values for SQL Server data types are found in the enumeration System.Data.SqlClient.SqlDbType. All the SQL Server 2000 data types are found in this enumeration (including SqlDbType.BigInt, the 64-bit integer, and the Unicode types NChar, NText, and NVarChar).

With SQL Server, it's actually not necessary to use the Parameters collection to supply a parameter to a stored procedure. In fact, you can call any SQL Server stored procedure using the EXEC keyword. The syntax of EXEC is

 EXEC procname [param1], [param2] ... 

where procname is the name of the stored procedure. Parameters, if any, are passed in a comma-delimited list following the name of the stored procedure.

Listing 11.7 shows an example of using EXEC to call a parameterized stored procedure. As in the previous example, the resultset of the stored procedure is returned in the form of a SqlDataReader object.

Listing 11.7 Calling a Parameterized Stored Procedure

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender, EventArgs e)   {     SqlConnection cn;     SqlCommand cm;     SqlDataReader dr;     cn = new SqlConnection("server=localhost;uid=sa;" +                         "pwd=;database=Northwind;");     cm = new SqlCommand();     cm.Connection = cn;     cm.CommandText = "EXEC SalesByCategory 'Beverages'";     // ** Open connection     try     {       cn.Open();     }     catch(SqlException sx)     {       Response.Write("Connection failed: " + sx.Message);     }     // ** Execute command     dr = cm.ExecuteReader();     while(dr.Read())     {      Response.Write(dr.GetString(0) + " " + dr.GetDecimal(1) + "<BR>");     }     cn.Close();   } </SCRIPT> 

It's important to note here that an EXEC call is technically a text command that calls a stored procedure, rather than a "pure" stored procedure call (as in the previous code example). Because an EXEC call is a text command, we get rid of the line of code that specifies that the command is a stored procedure (by setting the CommandType property of the command object).

Also, when using EXEC to call a stored procedure, parameters must be passed in the order they're declared in the stored procedure itself (a potential deal-killer if you don't have access to the stored procedure source code). Also, make sure to delimit non-numeric values (including date/time values) with single quotes; numeric values don't need to be delimited.

Executing Commands That Do Not Return Data

ADO.NET command objects have a special method for calling commands that do not retrieve data. This is an ADO.NET concept that does not specifically exist in ADO.old; it presumably exists for performance reasons, because a procedure that does not return data can be managed slightly more economically than one that does.

Examples of commands that don't return data include most data manipulation commands (including inserting, deleting, and updating records) as well as certain types of administrative commands in SQL Server.

You call a stored procedure that does not return data using the ExecuteNonQuery method of the connection object. Listing 11.8 shows an example of this.

Listing 11.8 Executing a Nonquery Command

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender, EventArgs e)   {     SqlConnection cn;     SqlCommand cm;     SqlDataReader dr;     String strSQL;     cn = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs;");     strSQL = "INSERT INTO authors " +              "(au_id, au_fname, au_lname, contract) " +              "VALUES ('123-45-6789', 'Chris', 'Kinsman', 0)";     cm = new SqlCommand(strSQL, cn);     // ** Open connection     try     {       cn.Open();       // ** Execute command       cm.ExecuteNonQuery();     }     catch(SqlException sx)     {       Response.Write(" Exception occurred: " + sx.Message);     }     finally     {       if (cn.State == ConnectionState.Open)         cn.Close();     }   } </SCRIPT>     try     {       // ** Open connection       cn.Open();       // ** Execute command       cm.ExecuteNonQuery();     }     catch(SqlException sx)     {       Response.Write("Connection failed: " + sx.Message);     }     finally     {       if (cn.State == ConnectionState.Open)         cn.Close(); } 

Note that executing this code will not display anything in the browser; you'll need to run a query against the Authors table to determine whether the insert actually worked. Later in this chapter, we'll construct a user interface that enables you to easily do this.

This code inserts a new author into the Authors table in the pubs database. It starts by constructing a SQL INSERT command, storing it in the string variable strSQL. This command contains the minimum amount of information required to insert a new author (the contract and au_id fields require non-null values). Aside from the syntax of the SQL command itself and the call to the ExecuteNonQuery method of the SqlCommand object, this code is nearly identical to the examples of commands we've demonstrated earlier in this chapter.

Executing Stored Procedures That Return Scalar Values

Most queries return resultsets, which are similar to arrays comprising one or more rows and one or more columns. In ADO.old these were called Recordsets; in ADO.NET, results typically are stored in an object such as a data reader or DataTable.

It is possible, however, to run a query that returns a single value. Such values are known as scalars, and they can be retrieved using the ExecuteScalar method of the ADO.NET command object.

As with the ExecuteNonQuery method discussed in the previous section, the idea behind ExecuteScalar is to give you additional options for executing commands that presumably perform better than returning the results in the form of a data reader or other object.

NOTE

You'll notice that when describing ADO.NET methods such as ExecuteNonQuery and ExecuteScalar, we use the word presumably a lot when describing their performance benefits. Doing a scientific performance analysis of the relative performance benefits of these various methods isn't our intention and isn't really within the scope of this book. At any rate, you'll want to examine the various methods for retrieving data in real-world scenarios before committing to a particular technique.

Listing 11.9 shows an example of using the ExecuteScalar method to retrieve a single value from the Northwind database.

Listing 11.9 Using ExecuteScalar to Retrieve a Single Value from the Database

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender, EventArgs e)   {     SqlConnection cn;     SqlCommand cm;     SqlDataReader dr;     String strSQL;     cn = new SqlConnection("server=localhost;uid=sa;" +             "pwd=;database=Northwind;");     strSQL = "SELECT Count(CustomerID) FROM Customers";     cm = new SqlCommand(strSQL, cn);     // ** Open connection     try     {       cn.Open();     }     catch(SqlException sx)     {       Response.Write("Connection failed: " + sx.Message);     }     //** Execute command     Response.Write("The number of customers is: " + cm.ExecuteScalar());    cn.Close();  } </SCRIPT> 

This code prepares a SQL command that returns a single value and submits it to the server using the ExecuteScalar method of the SqlCommand object. The ExecuteScalar method returns whatever value was returned by the command.

for RuBoard