70-516 Question #238: Real Exam Question with Answer & Explanation

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). 01 private static void ReturnIdentity(string connectionString) 02 { 03 using (SqlConnection connection = new SqlConnection(connectionString)) 04 { 05 SqlDataAdapter adapter = new SqlDataAdapter( 06 "SELECT CategoryID, CategoryName FROM dbo.Categories",connection); 07 adapter.InsertCommand = new SqlCommand("InsertCategory", connection); 08 adapter.InsertCommand.CommandType = CommandType.StoredProcedure; 09 SqlParameter rowcountParameter = adapter.InsertCommand.Parameters.Add( 10 "@RowCount", SqlDbType.Int); 12 adapter.InsertCommand.Parameters.Add( 13 "@CategoryName", SqlDbType.NChar, 15, "CategoryName"); 14 SqlParameter identityParameter = adapter.InsertCommand.Parameters.Add( 15 "@Identity", SqlDbType.Int, 0, "CategoryID"); 17 DataTable categories = new DataTable(); 18 adapter.Fill(categories); 19 DataRow categoryRow = categories.NewRow(); 20 categoryRow["CategoryName"] = "New Beverages"; 21 categories.Rows.Add(categoryRow); 22 adapter.Update(categories); 23 Int32 rowCount = (Int32)adapter.InsertCommand.Parameters["@RowCount"].Value; 24 } 25 } You need to retrieve the identity of the new record. You also need to retrieve the row count. What should you do?