Accessing Cloud Data using an OData Web Service

By Eric D. Boyd & Nathaniel Clark

In the previous article, Storing and Accessing Data in the Cloud, you learned how to create a database using Windows Azure SQL Database to store a holiday shopping list. In that article, I walked you through the steps to setting up the database, creating the tables, building the relationships between the tables, and adding data. The next step is to build a web service that makes your data accessible and an application to that consumes your data.

Create an OData Web Service

You now have a SQL Database in Windows Azure with tables and data, but now you need a simple and unified way to query and manage that data from all of the applications that you will build. Web services provide an abstraction over your data storage technology and with the broad adoption of web technologies and HTTP, RESTful web services are accessible from pretty much every platform and device. As a result, in this section you will create an OData web service using ASP.NET, an ADO.NET Entity Framework Data Model and a WCF Data Service.

To begin, open Visual Studio. I will be using Visual Studio 2013, but the same basic steps will work from Visual Studio 2012 and 2010. If you don’t already have Visual Studio, you can download Visual Studio Express which will work great for this following along with this article. We will also be working with Windows Azure Web Sites later in the article. Since we are not using Windows Azure Cloud Services in this article, the Windows Azure SDK is not required.

From the File menu, hover over New and select Project.

In the New Project dialog, browse to Installed > Templates > Visual C# and select the ASP.NET Web Application project template.

select the ASP.NET Web Application project template

Enter CodeGuru.eSeminar.OData for the Project Name.
Enter CodeGuru.eSeminar for the Solution name.

Click the OK button to continue creating your new Visual Studio project and solution. You will now be presented with the New ASP.NET Project dialog. For this OData project select the Empty ASP.NET template and click OK to continue.

Click the OK button

Your new Visual Studio solution and project should now be displayed in the Visual Studio Solution Explorer.

In the Solution Explorer, right click on the CodeGuru.eSeminar.OData project and select Add > New Item…

Browse to Installed > Visual C# > Data and select ADO.NET Entity Data Model.

Enter ShoppingListModel in the name for ADO.NET Entity Data Model and click Add.

You will now be presented with the Entity Data Model Wizard. In the Entity Data Model Wizard, select Generate from database and click Next.

select Generate from database

On the Choose Your Data Connection screen of the Entity Data Model Wizard, click on the New Connection button to display the Connection Properties dialog and configure the connection to your Windows Azure SQL Database.

click on the New Connection button

In your web browser, navigate to the Windows Azure Management Portal at http://manage.windowsazure.com and browse to your MyShoppingList SQL Database home screen.

On this screen you will see your SQL Database server name at the bottom. Select your server name and copy it to your clipboard. Your SQL Database server name will look like yourserver.database.windows.net,1433.

Your SQL Database server name will look like yourserver.database.windows.net,1433

Switch back to the Connection Properties dialog in the Visual Studio Entity Data Model Wizard and paste your SQL Database server name into the Server name text box.

Select Use SQL Server Authentication and enter your SQL Database User name and Password.

Expand the Select or enter a database name drop down and select your MyShoppingList database.

Click the OK button to finish configuring your SQL Database connection and return to the Entity Data Model Wizard.

Connection Properties

Select the Yes, include the sensitive data in the connection string option and name your entity connection ShoppingListEntities in the text box at the bottom.

Entity Data Model Wizard

Click the Next button to advance to the Choose Your Database Objects and Settings screen.

In the Choose Your Database Objects and Settings screen, check the Tables check box to include all of the tables in the data model.

Enter ShoppingListModel in the Model Namespace text box and click the Finish button.

Enter ShoppingListModel in the Model Namespace text box

Now that you have added an ADO.NET Entity Data Model to your Visual Studio project, you need to add a WCF Data Service.

Right click again on the CodeGuru.eSeminar.OData project in the Solution Explorer and select Add > New Item…

In the Add New Item dialog, browse to Installed > Visual C# > Web and select WCF Data Service 5.6. Name the new WCF Data Service ShoppingListService and click Add.

Add New Item

WCF Data Services make it easy to create an OData service using an ADO.NET Entity Data Model by providing a generic DataService class that is constructed using an ADO.NET Entity Data Model.

Open the ShoppingListService.svc and locate the first TODO comment in the ShoppingListService class definition.

locate the first TODO comment in the ShoppingListService class definition

Change /* TODO: put your data source class name here */ to ShoppingListEntities. This change will setup your OData service. The only remaining thing to do is grant access to the entities exposed in the OData service.

Locate and uncomment the following line of code in the ShoppingListService.InitializeService method and change MyEntityset to *. This will grant read permissions to all entities in your OData service.

// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);

grant read permissions to all entities in your OData service

Now you can test your OData service. Right click on ShoppingListService.svc in the Visual Studio Solution Explorer and select View in Browser.

You should now see the following AtomPub feed in your browser. This is the metadata listing the entities that are available in your OData service using the AtomPub web standard.

AtomPub feed
Click for larger image

Your OData service is now ready to be published to Windows Azure!

Publishing Your OData Service to Windows Azure

In your browser, navigate to the Windows Azure Management Portal and click on Web Sites in the list of services on the left-hand side.

Click the New button in the toolbar at the bottom. In the New menu that is displayed, select Compute > Web Site > Quick Create.

Enter a URL for your OData service in the URL text box. You should choose a URL that is relevant and descriptive. Since you are creating a web site for your OData service, you might want to append Api or Service to the URL.

Click the Create Web Site checkmark to create your website.

Click the Create Web Site checkmark

Note: To avoid bandwidth charges and added latency for the transfer between data centers, it would be a good practice to create your web site in the same region as your SQL Database.

After your website has been successfully created, select your web site and download the publishing profile by clicking on the Download the publish profile link.

Download the publish profile link

After your publish profile is downloaded, switch back to Visual Studio and right click on your CodeGuru.eSeminar.OData project and select Publish…

The Publish Web wizard will be displayed and you can begin publishing your OData service to Windows Azure.

On the Profile screen of the Publish Web wizard, click the Import… button to display the Import Publish Profile dialog.

On the Profile screen of the Publish Web wizard, click the Import… button

On the Import Publish Profile dialog, select the Import from a publish profile file option and click the Browse button. Browse to the Publish Profile you downloaded from your Web Site in the Windows Azure Management Portal, select it and click OK to return to the Publish Web wizard.

Import from a publish profile file

You will notice that the Publish Web wizard has populated all of the Web Deploy configuration using the Publish Profile file that you downloaded from the Windows Azure Management Portal. Click the Publish button and your OData service will be published to your Windows Azure Web Site.

Click the Publish button

When your Publish has successfully completed, browse to your OData service at http://YourODataSite.azurewebsites.net/ShoppingListService.svc to verify that your service is accessible and returning the AtomPub feed that with your entities.

Display Your Data in an ASP.NET MVC Web Site

At this point, you have a database in Windows Azure, an OData service that will make it simple to query and work with your data in Windows Azure Web Sites, now we want to put a face on your data and create an ASP.NET MVC Web Site to display your shopping list.

Return back to Visual Studio and right click on the CodeGuru.eSeminar solution in the Solution Explorer and select Add > New Project…

In Add New Project dialog, browse to Installed > Visual C# > Web and select the ASP.NET Web Application project type.

Name your new ASP.NET Web Application project CodeGuru.eSeminar.Web and click the OK button.

new ASP.NET Web Application project

You will now be presented with the New ASP.NET Project dialog where you can choose a specific template for your ASP.NET Project. Select MVC from the list of templates and click the OK button.

New ASP.NET Project

You should see your new CodeGuru.eSeminar.Web project in the Visual Studio Solution Explorer. Right click on the CodeGuru.eSeminar.Web project and select Add > Service Reference…

This will open the Add Service Reference dialog. Click on the Discover button which will automatically discover your OData service in your solution and populate the Address text box and display the service in the Services list.

Name the service reference by entering ShoppingListService in the Namespace text box and click the OK button.

Add Service Reference

In your Visual Studio Solution Explorer, expand the CodeGuru.eSeminar.Web project and open the Web.config file.

In the Web.config, locate the <appSettings> section and add the following key:

 <add 
  key="ODataUri" 
  value="http://YourODataSite.azurewebsites.net/shoppinglistservice.svc" />

Be sure to replace the above URL with the URL of your Windows Azure Web Site that is hosting your OData service, and be sure to add the full path to your service including the *.svc file.

In your Visual Studio Solution Explorer expand CodeGuru.eSeminar.Web > Views > Home and open the Index.cshtml file.

Select all of the code in Index.cshtml and delete it.

Select all of the code in Index.cshtml

At the top of your blank Index.cshtml file, add the following using statement to reference your ShoppingListService.

@using CodeGuru.eSeminar.Web.ShoppingListService

Now you want to make this view accept a Model of a list of people from your OData service. To do that, add the following life after your using statement.

@model IList<Person>

Next add the following code to set the page title.

@{
    ViewBag.Title = "Home Page";
}

Add the following HTML and Razor code to display your shopping list. This HTML is adding a bootstrap row with a table to your page and the Razor code is iterating through all of the Person objects in your Model and then iterating through each of the Items associated with each Person. For each of those items, it is displaying the data in a table row.

<div class="row">
    <div class="col-md-12">
        <h3>Things to buy:</h3>
        <table class="table">
            @foreach (Person person in Model)
            {
                foreach (Item item in person.Items)
                {
                    <tr>
                        <td>@person.FirstName @person.LastName</td>
                        <td>@item.ProductName</td>
                        <td>@string.Format("{0:C}", item.Price)</td>
                        <td>@item.Quantity</td>
                    </tr>
                }
            }
        </table>
    </div>
</div>

At this point, your Index.cshtml file should have the following code.

@using CodeGuru.eSeminar.Web.ShoppingListService
@model IList<Person>
@{
    ViewBag.Title = "Home Page";
}
<div class="row">
    <div class="col-md-12">
        <h3>Things to buy:</h3>
        <table class="table">
            @foreach (Person person in Model)
            {
                foreach (Item item in person.Items)
                {
                    <tr>
                        <td>@person.FirstName @person.LastName</td>
                        <td>@item.ProductName</td>
                        <td>@string.Format("{0:C}", item.Price)</td>
                        <td>@item.Quantity</td>
                    </tr>
                }
            }
        </table>
    </div>
</div>

Now you need to get your Model of data from your OData service, and pass it into your view from your ASP.NET MVC Controller.

In the Visual Studio Solution Explorer expand CodeGuru.eSeminar.Web > Controllers and open the HomeController.cs file.

At the very top of the HomeController.cs file, locate the existing using statements and add the following using for System.Configuration.

using System.Configuration;

Next, locate the Index method and make the following modifications inside the method.

At the beginning of the method, add a serviceUri variable using the following code. This will be used to store the URL of your OData service.

var serviceUri = new Uri(ConfigurationManager.AppSettings["ODataUri"]);

Below the serviceUri variable, add a context variable using the following code. This will instantiate a DataServiceContext for the OData Service Reference that you created, using the serviceUri that you initialized above.

var context = new ShoppingListService.ShoppingListEntities(serviceUri);

Below the context variable, add a query variable using the following code. This will query the People entity from the DataServiceContext and return all People and their associated Items using the Expand method. Think of this like a SQL query joining the Person and Item tables, but instead of using SQL, you are using Linq.

var query = from p in context.People.Expand("Items")
            select p;
 

Before returning the Model to the View, you will want to force Linq to enumerate the items returned by the query. The following line of code will force that execution by calling the ToList() method.

var result = query.ToList();

Lastly, you need to pass the Model of data into your View, to do that, pass the result variable into the return View(); line like return View(result);.

Your HomeController.cs should now have an Index() method that looks like the following.

Your HomeController.cs should now have an Index() method

Go ahead and build your new ASP.NET MVC Web Site and view it in your browser to make sure everything works as expected and you see a Holiday Shopping list web site like the following.

Holiday Shopping list web site

Publishing Your MVC Web App to Windows Azure Web Sites

If everything worked as expected, you should have an ASP.NET MVC Web Site that displays your Holiday Shopping list. Which means it is now time to deploy your web app to Windows Azure Web Sites.

In your browser, navigate to the Windows Azure Management Portal and click on Web Sites in the list of services on the left-hand side.

Click the New button in the toolbar at the bottom. In the New menu that is displayed, select Compute > Web Site > Quick Create.

Enter a URL for your shopping list web app in the URL text box. You should choose a URL that is relevant and descriptive.

Click the Create Web Site checkmark to create your website.

Click the Create Web Site checkmark

Note: Again as a reminder, to avoid bandwidth charges and added latency for the transfer between data centers, it would be a good practice to create your web site in the same region as your SQL Database.

After your website has been successfully created, select your web site and download the publishing profile by clicking on the Download the publish profile link.

Download the publish profile link

After your publish profile is downloaded, switch back to Visual Studio and right click on your CodeGuru.eSeminar.Web project and select Publish…

The Publish Web wizard will be displayed and you can begin publishing your OData service to Windows Azure.

On the Profile screen of the Publish Web wizard, click the Import… button to display the Import Publish Profile dialog.

click the Import… button

On the Import Publish Profile dialog, select the Import from a publish profile file option and click the Browse button. Browse to the Publish Profile you downloaded from your Web Site in the Windows Azure Management Portal, select it and click OK to return to the Publish Web wizard.

You will notice that the Publish Web wizard has populated the entire Web Deploy configuration using the Publish Profile file that you downloaded from the Windows Azure Management Portal. Click the Publish button and your Shopping List web app will be published to your Windows Azure Web Site.

Click the Publish button

When your Publish has successfully completed, browse to your Shopping List web app at http://YourSite.azurewebsites.net/ShoppingListService.svc to verify that your service is accessible and displaying your Shopping List in a mobile-friendly, web app.

Summary

If you followed along with this article, you just:

1. Created an OData web service to query and work with your cloud data

2. Deployed your OData web service to the Cloud using Windows Azure Web Sites

3. Created an ASP.NET MVC web app to display the data from your OData service

4. Deployed your ASP.NET MVC web app to the Cloud using Windows Azure Web Sites

And all of that was pretty simple, painless and quick, and you didn’t have to talk to any server administrators to make it happen. Now you are equipped to apply what you’ve learned to your own projects, and utilize cloud data within your own web, mobile and desktop apps.

Authors

Eric D. Boyd

Eric D. Boyd is the Founder and CEO of responsiveX, a Windows Azure MVP, and a regular speaker at international conferences, regional code camps and local user groups. He is so passionate about apps and cloud services that he founded responsiveX (www.responsiveX.com), a management and technology consultancy that helps customers create great web, mobile and client experiences, and these apps are often powered by cloud services. Eric launched his technology career almost two decades ago with a web development startup and has served in multiple roles since including developer, consultant, technology executive and business owner. You can find Eric blogging at http://www.EricDBoyd.com and on Twitter at http://twitter.com/EricDBoyd

Nathaniel Clark

Nathaniel works for Chicago-based technology consultancy responsiveX, where he works on web development projects, marketing initiatives and authoring content for the company. Nathaniel loves all things tech and invests countless hours playing with technology, mobile devices and apps.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read