CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
cmd.Parameters.Add(param);
}
}
cmd.ExecuteNonQuery();
Again, notice that your SQL query consists of four embedded placeholder symbols, each of which is
prefixed with the @ token. You can use the SqlParameter type to map each placeholder using the
ParameterName property and specify various details (e.g., its value, data type, and size) in a strongly typed
matter. After each parameter object is hydrated, it is added to the command object’s collection through
a call to Add().
Note This example uses various properties to establish a parameter object. Note, however, that parameter
objects support a number of overloaded constructors that allow you to set the values of various properties (which
will result in a more compact code base). Also be aware that Visual Studio provides many graphical designers that
will generate a good deal of this grungy parameter-centric code on your behalf (see Chapters 22 and 23).
While building a parameterized query often requires more code, the end result is a more convenient
way to tweak SQL statements programmatically, as well as to achieve better overall performance. While
you are free to use this technique whenever a SQL query is involved, parameterized queries prove most
helpful when you want to trigger a stored procedure.
Executing a Stored Procedure
Recall that a stored procedure is a named block of SQL code stored in the database. You can construct
stored procedures so they return a set of rows or scalar data types or do anything else that makes sense
(e.g., insert, update, or delete); you can also have them take any number of optional parameters. The end
result is a unit of work that behaves like a typical function, except that it is located on a data store rather
than a binary business object. Currently, your AutoLot database defines a single stored procedure named
GetPetName, which you formatted as follows:
GetPetName
@carID int,
@petName char(10) output
AS
SELECT @petName = PetName from Inventory where CarID = @carID
Now consider the following final method of the InventoryDAL type, which invokes your stored
procedure:
public string LookUpPetName(int carID)
{
string carPetName = string.Empty;
// Establish name of stored proc.
using (SqlCommand cmd = new SqlCommand("GetPetName", this.sqlCn))
{
845