You use Microsoft Visual Studio 2010 and Microsoft .NET Framework 4.0 to create an application.
The application connects to a Microsoft SQL Server 2008 database. You add the following store
procedure to the database.
CREATE PROCEDURE GetSalesPeople
AS
BEGIN
SELECT FirstName, LastName, Suffix, Email, Phone
FROM SalesPeople
END
You write the following code segment. (Line numbers are included for reference only.)
01 SqlConnection connection = new SqlConnection(“…”);
02 SqlCommand command = new SqlCommand(“GetSalesPeople”, connection);
03 command.CommandType = CommandType.StoredProcedure;
04 …
You need to retreive all of the results from the stored procedure. Which code segment should you
insert at line 04?

You use Microsoft .NET Framework 4.0 and the Entity Framework to develop an application. You
create an Entity Data Model that has an entity named Customer. You set the optimistic concurrency
option for Customer. You load and modify an instance of Customer named loadedCustomer, which is
attached to an ObjectContext named context. You need to ensure that if a concurrency conflict
occurs during a save, the application will load up-to-date values from the database while preserving
local changes. Which code segment should you use?

###BeginCaseStudy###
Case Study: 1
Background
You are updating an existing Microsoft .NET Framework 4 application that includes a data
layer built with ADO.NET Entity Framework 4. The application communicates with a
Microsoft SQL Server 2008 instance named INST01 on a server named SQL01. The
application stores data in a database named Contoso in the INST01 instance.
You need to update the existing technology and develop new functionality by using
Microsoft Visual Studio 2010.
Application and Data Structure

The application tracks bicycle parts as they pass through a factory. Parts are represented by
the abstract Part entity and its associated partial classes. Each part has a name stored in the
Name field and a unique identifier stored in the Id field.
Parts are either components (represented by the Component class) such as chains, wheels,
and frames, or finished products (represented by the Product class) such as completed
bicycles. The Component class and the Product class derive from the Part class and may
contain additional class-specific properties.
Parts may have a color (represented by the Color class), but not all parts have a color. Parts
may be composed of other parts, and those parts may in turn be composed of other parts; any
part represents a tree of the parts that are used to build it. The lowest level of the tree consists
of components that do not contain other components.
A product is a part that has been completed and is ready to leave the factory. A product
typically consists of many components (forming a tree of child parts) but can also be
constructed by combining other products and/or components to form a bundled product, such
as a bicycle and a helmet that are sold together.
Components and products are stored in a database table named Parts by using a table-perhierarchy (TPH) mapping. Components have a null ProductType field and a non-null
PartType field. Products have a non-null ProductType field and a null PartType field.
The following diagram illustrates the complete Entity data model diagram (EDMX diagram).

The following graphic illustrates details of the Part-Color Association.

The following code segments show relevant portions of the files referenced by the case study
items. (Line numbers in the samples below are included for reference only and include a twocharacter prefix that denotes the specific file to which they belong.)
Extension Method.cs

Model.edmx

Model/Color.cs

Model/component.cs

Model ContosoEntities.cs

Model IName.cs

Model Part.cs

Model/Product.cs

SP_FindObsolete

###EndCaseStudy###

You are developing a new feature that displays an auto-complete list to users as they type color
names. You have an existing ContosoEntities context object named context. To support the new
feature you must develop code that will accept a string object named text containing a users partial
input and will query the Colors database table to retrieve all color names that begin with that input.
You need to create an Entity SQL (ESQL) query to meet the requirement. The query must not be
vulnerable to a SQL injection attack. Which code segment should you use?

###BeginCaseStudy###
Case Study: 1
Background
You are updating an existing Microsoft .NET Framework 4 application that includes a data
layer built with ADO.NET Entity Framework 4. The application communicates with a
Microsoft SQL Server 2008 instance named INST01 on a server named SQL01. The
application stores data in a database named Contoso in the INST01 instance.
You need to update the existing technology and develop new functionality by using
Microsoft Visual Studio 2010.
Application and Data Structure

The application tracks bicycle parts as they pass through a factory. Parts are represented by
the abstract Part entity and its associated partial classes. Each part has a name stored in the
Name field and a unique identifier stored in the Id field.
Parts are either components (represented by the Component class) such as chains, wheels,
and frames, or finished products (represented by the Product class) such as completed
bicycles. The Component class and the Product class derive from the Part class and may
contain additional class-specific properties.
Parts may have a color (represented by the Color class), but not all parts have a color. Parts
may be composed of other parts, and those parts may in turn be composed of other parts; any
part represents a tree of the parts that are used to build it. The lowest level of the tree consists
of components that do not contain other components.
A product is a part that has been completed and is ready to leave the factory. A product
typically consists of many components (forming a tree of child parts) but can also be
constructed by combining other products and/or components to form a bundled product, such
as a bicycle and a helmet that are sold together.
Components and products are stored in a database table named Parts by using a table-perhierarchy (TPH) mapping. Components have a null ProductType field and a non-null
PartType field. Products have a non-null ProductType field and a null PartType field.
The following diagram illustrates the complete Entity data model diagram (EDMX diagram).

The following graphic illustrates details of the Part-Color Association.

The following code segments show relevant portions of the files referenced by the case study
items. (Line numbers in the samples below are included for reference only and include a twocharacter prefix that denotes the specific file to which they belong.)
Extension Method.cs

Model.edmx

Model/Color.cs

Model/component.cs

Model ContosoEntities.cs

Model IName.cs

Model Part.cs

Model/Product.cs

SP_FindObsolete

###EndCaseStudy###

You have a ContosoEntities context object named context and a Color object stored in a variable
named color. You write the following code:
context.Colors.DeleteObject(color); context.SaveChanges();
When the code runs, it generates the following exception:
System.Data.UpdateException:
An error occurred while updating the entries. See the inner exception for details. —>
System.Data.SqlClient.SqlException:
The DELETE statement conflicted with the REFERENCE constraint “FK_PartColor”. The conflict
occurred in database “Contoso”, table “dbo.Parts”, column ‘ColorId’. You need to resolve the
exception without negatively impacting the rest of the application. What should you do?