You create an application by using the Microsoft .NET Framework 3.5 and Microsoft
ADO.NET. The application connects to a Microsoft SQL Server 2005 database. The
application uses the following stored procedure. CREATE PROCEDURE
[dbo].[UpdateShippers]

@CountryCode NVarchar(10)
,@NewRateCode int
AS BEGIN
Update dbo.Shippers
SET RateCode = @NewRateCode Where CountryCode = @CountryCode RETURN
@@ROWCOUNT
END
You write the following code segment. (Line numbers are included for reference only.)
01 using (SqlConnection connection = new
SqlConnection(connectionString))
02 {
03 connection.Open();
04 SqlCommand command = new
SqlCommand(“UpdateShippers”, connection);
05
06 command.ExecuteNonQuery();
07 }
You need to ensure that the application can update the Shippers table. Which code segment
should you insert at line 05?

A.
command.CommandType = CommandType.StoredProcedure; SqlParameter parameter =
command.Parameters.Add(
“@RowCount”, SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = command.Parameters.Add(“@CountryCode”, SqlDbType.NVarChar, 10);
parameter = command.Parameters.Add(“@NewRateCode”, SqlDbType.Int);
command.Parameters[“@CountryCode”].Value = “USA”;
command.Parameters[“@NewRateCode”].Value = “778”;

B.
command.CommandType = CommandType.StoredProcedure; SqlParameter parameter =
command.Parameters.Add(“@RowCount”, SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
parameter = command.Parameters.Add(“@CountryCode”, SqlDbType.NVarChar, 10,
“CountryCode”);
parameter = command.Parameters.Add(“@NewRateCode”, SqlDbType.Int, 0, “RateCode”);
command.Parameters[“@CountryCode”].Value = “USA”;
command.Parameters[“@NewRateCode”].Value = “778”;

C.
command.CommandType = CommandType.StoredProcedure; SqlParameter parameter =
command.Parameters.Add(“@RowCount”, SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = command.Parameters.Add(“CountryCode”, SqlDbType.NVarChar, 10,
“@CountryCode”);
parameter = command.Parameters.Add(“RateCode”, SqlDbType.Int, 0,”@NewRateCode”);
command.Parameters[“CountryCode”].Value = “USA”;
command.Parameters[“RateCode”].Value = “778”;

D.
command.CommandType = CommandType.StoredProcedure; SqlParameter parameter =
command.Parameters.Add(“@RowCount”, SqlDbType.Int);
parameter.Direction = ParameterDirection.InputOutput;
parameter = command.Parameters.Add(“@CountryCode”, SqlDbType.NVarChar,10);
parameter = command.Parameters.Add(“@NewRateCode”, SqlDbType.Int, 0);
command.Parameters[“@CountryCode”].Value = “USA”;
command.Parameters[“@NewRateCode”].Value = “778”;

Explanation:
ParameterDirection.ReturnValue is required since the stored procedure is returning the RowCount as a ReturnValue
The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

Syntax for adding a parameter requires @ParameterName to be the 1st parameter in the method signature for SQL Server platforms.
Oracle platforms would require : instead.
ODBC or OleDb platforms would require ? instead.