Datasheet

"[Products].[UnitsInStock] FROM [Products]";
System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
Once the command details are set, we can open the database connection:
dbConnection.Open();
Even though the database connection has been opened for us when using a DataSet, we still have to
open it manually because we are using an
OleDbCommand and a data reader.
Next, we declare the data reader. It is of type
IDataReader and the object is created by the return value
of the
ExecuteReader() method of the command:
System.Data.IDataReader dataReader =
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
Remember that the command has the SQL statement, so ExecuteReader() tells ADO.NET to run the
command and return a data reader. The argument indicates that as soon as the data is finished with the
connection to the database, the connection should be closed. When using
ExecuteReader(), you
should always add this argument to make sure the connection is closed as soon as it is no longer
required.
Finally, we return the reader object:
return dataReader;
}
To bind to the grid, we simply use this function as the DataSource for the grid. Since the function
returns a stream of data, the grid just binds to that data:
void Page_Load(Object sender, EventArgs e) {
DataGrid1.DataSource = GetProductsReader();
DataGrid1.DataBind();
}
DataReader Methods and Properties
The DataReader exists as SqlDataReader (for SQL Server) and OleDbDataReader (for other
databases), as well as a common
IDataReader interface. If you are not using generic code, you can
create the reader as follows:
System.Data.OleDbDataReader dataReader = new _
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
Using data readers is the most efficient way of fetching data from a database, but you don't have to bind
to a grid. You can use the properties and methods to fetch the data directly. If you do this, it's best to use
the
OleDbDataReader rather than the interface, as the OleDbDataReader contains more properties that
make it easier to use. For example, consider the following code:
280
Chapter 8
57084_08.qxp 30/01/2004 8:03 PM Page 280