Exercise 2: Creating an OData Feed over SQL Server

In this exercise, you will learn how to create a data service that exposes a SQL Server database, and consume it using a web browser.

Task 1 – Creating the Data Service Web Application Project

In this task, you will create an ASP.NET Web application project that will host and expose the data service.

1. Open Microsoft Visual Studio 2010 from Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010.
2. Open the OpenDataServices.sln solution file, located under the Source\Ex2\Begin folder of this lab (choosing the folder that matches the language of your preference). This is an empty solution for this exercise.
3. In Solution Explorer, right-click the OpenDataServices solution, point to Add and select New Project.
4. In the Add New Project dialog, select the language of your preference (Visual C# or Visual Basic), and then click the Web project type. Make sure that .NET Framework 4.0 is selected, and then select the ASP.NET Empty Web Application template.
5. You may set the location to the Source\Ex2\Begin folder of this lab (and choosing the folder that matches the language of your preference).

6. Set the Name to WebSite, and click OK.

   Figure 4

   Creating a new Website (C#)

   Figure 5

   Creating a new Website (Visual Basic)

7. Configure the web site to use port 50000.
   1. To do this, in Solution Explorer, right-click the WebSite project and in the context menu select Properties.
   2. In the Property page, open the Web tab.
   3. In the Servers section, select Specific Port.
   4. Set the port number to 50000.

   5. Press Ctrl + S to save changes.

      Figure 6

      Specifying a port number

Task 2 – Creating the Entity Data Model

In this task, you will create the mapping specification that connects programmable classes to storage structures.

The Entity Data Model (EDM) is a specification for defining the data used by applications built on the Entity Framework. Applications using the EDM define a design schema, which includes entities and relationships in the domain of the application.

1. Create the AdventureWorks Entity Data Model. To do this, in Solution Explorer, right-click the WebSite project, point to Add, and click New Item.

2. In the Add New Item dialog box, select ADO.NET Entity Data Model. Specify a Name value of AdventureWorksShopping.edmx, and then click Add.

   Figure 7

   Adding the ADO.NET Entity Data Model (C#)

   Figure 8

   Adding the ADO.NET Entity Data Model (Visual Basic)

3. In the Entity Data Model Wizard, select Generate from Database and click Next.

4. Specify the Database connection. To do this, follow the following steps:

   1. Click New Connection.
   2. If the Choose Data Source dialog is prompted, select Microsoft SQL Server Database File (SqlClient) as Data Source and click Continue. If the Connection Properties dialog is displayed directly, make sure the Data source is set to Microsoft SQL Server Database File (SqlClient). If not, change it to this value.
   3. In the Connection Properties dialog, browse to Source\Assets\AdventureWorksShopping.mdf in the Database file name field, and then click OK.
   Figure 9

   Specify the database connection

5. Back on the Entity Data Model Wizard click Next, and then Yes when the Wizard asks to copy the database file to the application data folder.

6. Include only the Tables objects from all the proposed database objects and leave the Model Namespace. Make sure that Pluralize or singularize generated object names option is unchecked.

   Figure 10

   Choose database objects to include in the model

   Note:

   For more information, see ADO.NET Entity Framework.

7. Click Finish.

Task 3 – Creating the WCF Data Service

In this task, you will create the WCF Data Service that exposes data by using the Entity Data Model as specified by the ADO.NET Entity Framework.

1. Add the Data Service. To do this, in Solution Explorer, right-click the WebSite project, point to Add, and click New Item.

2. In the Add New Item dialog box, select the WCF Data Service template. Specify a Name value of AdventureWorks.svc, and then click Add.

   Figure 11

   Add the WCF Data Service item (C#)

   Figure 12

   Add the WCF Data Service item (Visual Basic)

3. Locate the class definition and replace it with the following code. This indicates that the service uses the Entity Framework Data Model as data source.

   Note:

   Visual Studio will open the code file for the new service by default. You can also find the file in the Solution Explorer by right-clicking the AdventureWorks.svc file.

   (Code Snippet – OData Lab - Ex 2.3.3 - AdventureWorks Data Service Definition CSharp)

   C#

   public class AdventureWorks : DataService<AdventureWorksShoppingEntities>

   (Code Snippet – ODataLab - Ex 2.3.3 - AdventureWorks Data Service Definition VB)

   Visual Basic

   Public Class AdventureWorks Inherits DataService(Of AdventureWorksShoppingEntities)

4. Grant read and write access to all resources of the Entity Data Model associated with the service, by replacing the InitializeService method with the following code:

   (Code Snippet – OData Lab - Ex 2.3.4 - AdventureWorks.svc InitializeService CSharp)

   C#

   public static void InitializeService(DataServiceConfiguration config) { config.SetEntitySetAccessRule("*", EntitySetRights.AllRead); config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2; }

   (Code Snippet – OData Lab - Ex 2.3.4 - AdventureWorks.svc InitializeService VB)

   Visual Basic

   Public Shared Sub InitializeService(ByVal config As DataServiceConfiguration) config.SetEntitySetAccessRule("*", EntitySetRights.AllRead) config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2 End Sub
   Note:

   By default, a data service, does not expose any resources. Access to resources need to be explicitly enabled before any resources or associations are accessible. For more information, see IDataServiceConfiguration.SetEntitySetAccessRule Method (System.Data.Services).

Exercise 2 Verification

In this verification, you will use a web browser to consume the Data Service and to check its XML response.

1. Navigate to the AdventureWorks data service. To do this, right-click the AdventureWorks.svc file and select View in Browser. You will notice that the XML response of the data service is a list of entity-sets. The entity-sets in the Entity Data Model (EDM) style represent the database tables that are exposed by the data service. The output will be similar to the following figure.

   Note:

   The XML document returned by default is an Atom service document because the default serialization used by the data service is Atom.
   Note:

   If the browser displays a message saying it cannot display the feed, turn off the feed reading view. In Internet Explorer, clear the Turn on feed reading view check box, located under Tools | Internet Options | Content Tab | Feeds Section | Settings,.
   Figure 13

   Data Service XML response

2. To browse a specific entity, add its name to the address in the web browser. For instance, if the web server port is 50000 you should browse https://localhost:50000/AdventureWorks.svc/Product/. The following output should appear.

   Figure 14

   Data Service XML response for the product entity set

   Note:

   The WCF Data Services framework implements a data addressing scheme that uses URIs. It is based on the Entity Data Model (EDM) specification for data types and data sets. For building more complex requests using URIs check Simple Addressing Scheme for Data with Uniform URLs.