Executing ADO.NET Commands to Retrieve Data - Beginning C# 5.0 Databases - page 215

Figure 13-6. Using a data reader
How It Works
In this example, you use the ExecuteReader() method to retrieve and then output the Name and
ProductNumber values of all the products in the Production.Product table. As with ExecuteScalar() ,
ExecuteReader() takes the CommandText property and sends it to the database using the connection from
the Connection property.
When you use the ExecuteScalar method, you produce only a single scalar value. In contrast, using
ExecuteReader() returns a SqlDataReader object.
// execute query
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
txtReader.AppendText(rdr.GetValue(1) + "\t\t" + rdr.GetValue(0));
}
The SqlDataReader object has a Read() method that gets each row in turn and a GetValue method
that gets the value of a column in the row. The particular column whose value it retrieves is given by the
integer parameter indicating the index of the column. Note that GetValue uses a zero-based index, so the
first column is column 0, the second column is column 1, and so on. Since the query asked for two
columns, Name and ProductNumber, these are the columns numbered 0 and 1 in this query result.
Executing Nonquery Statements
ExecuteNonQuery is the method that is used to execute SQL statements that consist of DML statements.
Such statements consist of INSERT , UPDATE , and DELETE functionality of SQL Server. Hence,
ExecuteNonQuery() is used to provide the DML statements to the command and execute it. As you might
have noticed in previous chapters, INSERT , UPDATE , and DELETE statements do not return any records.
Now let's see how the ExecuteNonQuery( ) method works with such a SQL query.