Free mag vol1 | Page 900

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