Datasheet

Once the query string is set, we define our command to run the query, as follows:
System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
Now we come to the definition of the parameter. Like many of the other examples, this uses a database
specific object – an
OleDbParameter, which defines what is being passed into the query:
System.Data.IDataParameter dbParam_categoryID =
new System.Data.OleDb.OleDbParameter();
We then set the properties of the parameter. The ParameterName indicates the name of the parameter,
and we set the value to be the same as the placeholder. The
Value property stores the value for the
parameter, and is set to the
CategoryID passed into the procedure from the button click event – it's the
ID of the category selected from the list. The
DbType property indicates the database type – Int32 is the
database equivalent of an
Integer:
dbParam_categoryID.ParameterName = "@CategoryID";
dbParam_categoryID.Value = categoryID;
dbParam_categoryID.DbType = System.Data.DbType.Int32;
At this point, even though we have a Parameter object, it's not associated with the command, so we
add it to the
Parameters collection of the command:
dbCommand.Parameters.Add(dbParam_categoryID);
When ADO.NET processes the command, it matches parameters in the collection with the placeholders
in the query and substitutes the placeholder with the value in the parameter.
The rest of the code is as we've seen it before. We create a
DataAdapter to run the command, and use
the
Fill() method to fetch the data into our DataSet:
System.Data.IDbDataAdapter dataAdapter =
new System.Data.OleDb.OleDbDataAdapter();
dataAdapter.SelectCommand = dbCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}
As you can see, there really isn't that much code; even though we've introduced a new object, much of
the code remains the same.
Filtering Queries
There's a very important point to know about filtering data, as you may see code elsewhere that uses a
bad method of doing it – it simply builds up the SQL string (as we've done), but instead of using
parameters, it just appends the filter value to the SQL string. For example, you might see this:
277
Reading from Databases
57084_08.qxp 30/01/2004 8:03 PM Page 277