CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
public DataTable GetAllInventoryAsDataTable()
{
// This will hold the records.
DataTable inv = new DataTable();
}
// Prep command object.
string sql = "Select * From Inventory";
using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
SqlDataReader dr = cmd.ExecuteReader();
// Fill the DataTable with data from the reader and clean up.
inv.Load(dr);
dr.Close();
}
return inv;
Working with Parameterized Command Objects
Currently, the insert, update, and delete logic for the InventoryDAL type uses hard-coded string literals
for each SQL query. As you might know, you can use a parameterized query to treat SQL parameters as
objects, rather than as a simple blob of text. Treating SQL queries in a more object-oriented manner
helps reduce the number of typos (given strongly typed properties); plus, parameterized queries
typically execute much faster than a literal SQL string because they are parsed exactly once (rather than
each time the SQL string is assigned to the CommandText property). Parameterized queries also help
protect against SQL injection attacks (a well-known data access security issue).
To support parameterized queries, ADO.NET command objects maintain a collection of individual
parameter objects. By default, this collection is empty, but you can insert any number of parameter
objects that map to a placeholder parameter in the SQL query. When you want to associate a parameter
within a SQL query to a member in the command object’s parameters collection, you can prefix the SQL
text parameter with the @ symbol (at least when using Microsoft SQL Server; not all DBMSs support this
notation).
Specifying Parameters Using the DbParameter Type
Before you build a parameterized query, you need to familiarize yourself with the DbParameter type
(which is the base class to a provider’s specific parameter object). This class maintains a number of
properties that allow you to configure the name, size, and data type of the parameter, as well as other
characteristics, including the parameter’s direction of travel. Table 21-7 describes some key properties of
the DbParameter type.
Table 21-7. Key Members of the DbParameter Type
Property
Meaning in Life
DbType
Gets or sets the native data type of the parameter, represented as a CLR data type.
Direction
Gets or sets whether the parameter is input-only, output-only, bidirectional, or a
return value parameter.
843