Datasheet

Dim dbCommand As System.Data.IDbCommand = _
New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Once the command details are set, we can then 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:
Dim dataReader As System.Data.IDataReader = _
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 no longer required.
Finally we return the reader object:
Return dataReader
End Function
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:
Sub Page_Load()
DataGrid1.DataSource = GetProductsReader()
DataGrid1.DataBind()
End Sub
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:
Dim dataReader As System.Data.OleDbDataReader = _
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:
285
Reading From Databases
57076_Ch 8 SAN.qxd 01/12/2003 6:43 PM Page 285