Microsoft Exam Questions

Which code segment should you add?

The database contains a table named Categories. The Categories table has a primary key identity column named CategoryID.
The application inserts new records by using the following stored procedure.

CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT

You write the following code segment.

SqlDataAdapter adapter = new SqlDataAdapter(“SELECT categoryID, CategoryName FROM dbo.Categories”,connection);
adapter.InsertCommand = new SqlCommand(“dbo.InsertCategory”, connection);
adapter.InsertCommand.CommandType = commandType.StoredProcedure;
adapter.InsertCommand.Parameters.Add(new SqlParameter(“@CategoryName”, SqlDbType.NVarChar, 15,”CategoryName”));

You need to retrieve the identity value for the newly created record. Which code segment should you add?

A.
SqlParameter parameter = adapter.InsertCommand.Parameters.Add(“@CategoryName”, SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
parameter = adapter.InsertCommand.Parameters.Add(“@Identity”, SqlDbType.Int, 0, “CategoryID”);
parameter.Direction = ParameterDirection.Output;

B.
SqlParameter parameter = adapter.InsertCommand.Parameters.Add(“@CategoryName”, SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
parameter = adapter.InsertCommand.Parameters.Add(“@Identity”, SqlDbType.Int, 0, “CategoryID”);
parameter.Direction = ParameterDirection.ReturnValue;

C.
SqlParameter parameter = adapter.InsertCommand.Parameters.Add(“@RowCount”, SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = adapter.InsertCommand.Parameters.Add(“@Identity”, SqlDbType.Int, 0, “CategoryID”);
parameter.Direction = ParameterDirection.Output;

D.
SqlParameter parameter = adapter.InsertCommand.Parameters.Add(“@RowCount”, SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
parameter = adapter.InsertCommand.Parameters.Add(“@Identity”, SqlDbType.Int, 0, “CategoryID”);
parameter.Direction = ParameterDirection.ReturnValue;