Introduction

Once you've created a SqlCommand object and populated the Parameters collection, you're ready to ask SQL Server to execute the T-SQL it contains. In this chapter, I discuss how a SqlCommand can be executed either synchronously or asynchronously. I'll show you how to choose what kind of information you want returned to your applicationa rowset, a scalar^[1], the rows affected, or an XML stream. You can also choose whether or not to return additional keyset or schema information from a querywhich can be handy if you're building tools or data-driven routines. Choosing the "right" execute function is really pretty importantif you choose the wrong one, you can waste considerable time and resources processing data that you'll never use (or be able to use), so I make sure you know how to choose the right tool for the job.

^[1] A "scalar" is simply a single value returned as an object.

Regardless of what your code executes, the login account you use must have sufficient (at least, read-only) rights to execute the query. If you plan to update the data, you'll need read/write rights. While this might seem obvious, if your DBA has protected the database (as he or she should), you won't be granted rights to any base tables at all and only limited rights to the specific stored procedures you need to run the application and perform any needed updates.

The primary purpose of a SqlCommand object is to provide a mechanism to execute the CommandText and get the server to do something (like an action command) or return one or more resultsets and the rowsets they contain. ADO.NET 2.0 does not expand on the list of synchronous SqlCommand execute functions, but it does add an entire suite of asynchronous functions to execute your queries. These new functions can dramatically alter the way you approach query problems and help deal with queries that take an inordinate length of time to execute (at least, as far as users are concerned). First, I'll deal with the synchronous execute functions as listed in Table 11.1. Later in this chapter, I'll discuss the new asynchronous methods.

Table 11.1. The SqlCommand Synchronous Functions

Execute Function	Returns	Description
ExecuteNonQuery	Integer	Executes SqlCommand CommandText and returns the rows affected integer
ExecuteReader	SqlDataReader	Executes SqlCommand CommandText and returns SqlDataReader (two overloads)
ExecuteScalar	Object	Executes SqlCommand CommandText and returns first column from first row from first rowset returned from query or Null
ExecuteXmlReader	XmlReader	Executes SqlCommand CommandText and returns System.Xml.XmlReader