CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
cmd.CommandType = CommandType.StoredProcedure;
// Input param.
SqlParameter param = new SqlParameter();
param.ParameterName = "@carID";
param.SqlDbType = SqlDbType.Int;
param.Value = carID;
// The default direction is in fact Input, but to be clear:
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
// Output param.
param = new SqlParameter();
param.ParameterName = "@petName";
param.SqlDbType = SqlDbType.Char;
param.Size = 10;
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
// Execute the stored proc.
cmd.ExecuteNonQuery();
// Return output param.
carPetName = (string)cmd.Parameters["@petName"].Value;
}
}
return carPetName;
One important aspect of invoking a stored procedure is to keep in mind that a command object can
represent a SQL statement (the default) or the name of a stored procedure. When you want to inform a
command object that it will be invoking a stored procedure, you pass in the name of the procedure (as a
constructor argument or by using the CommandText property) and must set the CommandType property to
the value CommandType.StoredProcedure (if you fail to do this, you will receive a runtime exception
because the command object is expecting a SQL statement by default).
SqlCommand cmd = new SqlCommand("GetPetName", this.sqlCn);
cmd.CommandType = CommandType.StoredProcedure;
Next, notice that the Direction property of a parameter object allows you to specify the direction of
travel for each parameter passed to the stored procedure (e.g., input parameter, output parameter,
in/out parameter, or return value). As before, you add each parameter object to the command object’s
parameters collection.
// Input param.
SqlParameter param = new SqlParameter();
param.ParameterName = "@carID";
param.SqlDbType = SqlDbType.Int;
param.Value = carID;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
846