You use Microsoft Visual Studio 2010 and Microsoft .NET Framework 4 to create an application. You
create a stored procedure to insert a new record in the Categories table according to following code
segment.
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. (Line numbers are included for reference only).
1 Private Shared Sub ReturnIdentity(connectionString As String)
2 Using connection As New SqlConnection(connectionString)
3 Dim adapter As New SqlDataAdapter(“SELECT CategoryID, CategoryName FROM
dbo.Categories”, connection)
4 adapter.InsertCommand = New SqlCommand(“InsertCategory”, connection)
5 adapter.InsertCommand.CommandType = CommandType.StoredProcedure
6 Dim rowcountParameter As SqlParameter =
adapter.InsertCommand.Parameters.Add(“@RowCount”, SqlDbType.Int)
7
8 adapter.InsertCommand.Parameters.Add(“@CategoryName”, SqlDbType.NChar, 15,
“CategoryName”)
9 Dim identityParameter As SqlParameter =
10 adapter.InsertCommand.Parameters.Add(“@Identity”, SqlDbType.Int, 0, “CategoryID”)
11
12 Dim categories As New DataTable()
13 adapter.Fill(categories)
14 Dim categoryRow As DataRow = categories.NewRow()
15 categoryRow(“CategoryName”) = “New Beverages”
16 categories.Rows.Add(categoryRow)
17 adapter.Update(categories)
18 Dim rowCount As Int32 =
19 DirectCast(adapter.InsertCommand.Parameters(“@RowCount”).Value, Int32)
20 End Using
21 End Sub
You need to retrieve the identity of the new record. You also need to retrieve the row count. What
should you do?
A.
Insert the following code segment at line 07.
rowcountParameter.Direction = ParameterDirection.ReturnValue
Insert the following code segment at line 11.
identityParameter.Direction = ParameterDirection.ReturnValue
B.
Insert the following code segment at line 07.
rowcountParameter.Direction = ParameterDirection.Output
Insert the following code segment at line 11.
identityParameter.Direction = ParameterDirection.Output
C.
Insert the following code segment at line 07.
rowcountParameter.Direction = ParameterDirection.ReturnValue
Insert the following code segment at line 11.
identityParameter.Direction = ParameterDirection.Output
D.
Insert the following code segment at line 07.
rowcountParameter.Direction = ParameterDirection.Output
Insert the following code segment at line 11.
identityParameter.Direction = ParameterDirection.ReturnValue
Explanation:
Input – The parameter is an input parameter.
InputOutput – The parameter is capable of both input and output.
Output – The parameter is an output parameter.
ReturnValue – The parameter represents a return value from an operation such as a stored
procedure, builtin function, or user-defined function.
ParameterDirection Enumeration
(http://msdn.microsoft.com/en-us/library/system.data.parameterdirection(v=vs.71).aspx)