Data Service in the Cloud

  • Article

Julie Lerman

https://thedatafarm.com

Windows Azure allows you to host your .NET web applications and services in the cloud. And because it is a .NET platform, you can build and deploy Azure apps directly in Visual Studio using all of the familiar tools and code that you build any .NET application with. Add to this the ability to connect to SQL Azure, the cloud relational database, you can build and deploy WCF Data Services to let Windows Azure host your data services and serve up OData from the cloud.

In this walkthrough, you’ll learn how to use Visual Studio to build a cloud data service using an Entity Framework data model which exposes data from a SQL Azure database. You’ll see an Azure hosted ASP.NET MVC application consuming the service. When deployed, the entire solution will be managed by Windows Azure services.

You will also need to install the Windows Azure SDK and Windows Azure Tools for Visual Studio.

Creating the Windows Azure Project

Creating a Windows Azure project is not the same as creating other Visual Studio project types. With Windows Azure, there is only one project type to choose – a Windows Azure Project – whether you want to build a web application, a service or something else. When you create a Windows Azure Project you then select what type of Role (or Roles) you want the project to host. For example, you can have a role that provides a user interface (ASP.NET Web Role or ASP.NET MVC Web Role) or one that provides a service interface (WCF Service Web Role). A Data Service needs to be hosted in one of the web roles. I’ll use the WCF Service.

  1. Start Visual Studio 2010 with Administrator privileges. This is a requirement for working with Windows Azure Tools.
  2. Create a new Visual Studio project.
  3. From the Cloud project templates, select Windows Azure Project.
  4. For this walkthrough, be sure to keep the default solution name, WindowsAzureProject1.
  5. In the New Windows Azure Project window, double click WCF Service Web Role so that it appears in the Windows Azure solutions panel on the right and then click OK.

The wizard will create not one, but two projects. One is the Azure Service, which is the host for your role. The role is in its own project. As a result of choosing the WCF Service role, you will have a WindowsAzureProject project and a WCFServiceWebRole project in your solution.

Change the name of the WCFServiceWebRole project from WCFServiceWebRole1 to AWDataService.

Adding a Model to the Service Project

Visual Studio 2010 database and Entity Data Model designers are able to work directly with a SQL Azure database as long as your provide the correct connection string. However, since the design time service will run on your local machine, you can build the model against a local copy of the database and test your service with that. Then when you get closer to deploying the service, you can switch the connection string to use the cloud database. You can learn more about the Visual Studio Entity Data Model designer integration with SQL Azure in the whitepaper: _________________________________________.

I’ll use an existing sample database, AdventureWorksLT, which you can get from CodePlex Microsoft Community  Samples https://msftdbprodsamples.codeplex.com/releases/view/37304. Because of the differences between SQL Azure and SQL Server, you will need a specially written script to create the database rather than using the same one that you would use to create the database in SQL Server. On the Microsoft Community Samples page you will find the download as well instructions for installing the sample database onto your SQL Azure account server.  You will also need a SQL Azure account, which you can learn more about at https://www.microsoft.com/en-us/sqlazure/purchase.aspx.

We’ll use the ADO.NET Entity Data Model template wizard to reverse-engineer the local instance of the AdventureWorksLT database into a model.

  1. Add New Item to the AWDataServiceWebRole project.
  2. From the Item Templates, choose ADO.NET Entity Data Model and name it AWEntities.edmx.
  3. In the Choose Model Context page of the Entity Data Model wizard, select Generate from database and click Next.
  4. In the Choose Data Connection wizard page, select the AdventureWorksLT2008R2 connection if you have already set it up in Visual Studio. Otherwise, click the New Connection button and add a connection to the AdventureWorksLT2008R2 database.
  5. Change the default name of the connection string, which also becomes the name of your model’s ObjectContext from the long AdventureWorksLT2008R2Entities to a shorter name — AWEntities.
  6. Click Next.
  7. On the Choose Your Database Objects page of the wizard, check the Tables and Views items.
  8. Expand the Tables item and uncheck BuildVersion and ErrorLog.
  9. Click Finish.

Visual Studio will respond by displaying the newly created model that is mapped to the AdventureWorksLT database. In a real world application, the domain model most likely not have a one:one correspondence with tables in the database, in shich case you would also need to customize the model. However for the sake of this walkthrough, the initial model is sufficient.

Adding a WCF Data Service to the Project

Now that the model is created, you can build a WCF Data Service on top of it. The service will allow users and other applications to consume your data as it is described by your data model through URIs rather than through custom service operations. The format of the resulting data is based on the Open Data Protocol (OData)  which you can read more about at https://www.odata.org.

 By default, the Data Service will not expose anything from your model. It is your responsibility to configure the service in order to define how various data can be accessed. Let’s create the service and expose some of the data.

  1. Add a New Item to the AWDataServiceWebRole project.
  2. Select a WCF Data Service from the Installed Templates. You can find this Item Template in the Web subset or use the Template search box to locate it.
  3. Change the name of the service to AWDataService.svc before clicking the Add button.

When you add the new data service, you’ll see its class in the code editor window. Note that the class inherits from the DataService generic class and that there is a placeholder for the type.

public class AWDataService :
  DataService< /* TODO: put your data source class name here */ >
  1. Replace that placeholder with the ObjectContext class created by your model.
public class AWDataService : DataService< AWEntities >

This allows the Data Service to be aware of what entities are available from the model, but as stated before, by default, the service will not expose any of them, until you explicitly specify which entities the service should allow users to access.  We’ll start by exposing only the Customers entity set.

Below the four commented lines, add the following service configuration instruction.

config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead);

There are a nine other EntitySetRights enums (e.g., ReadSingle and WriteMerge) that you can use. You can even combine rights to build the rule that applies to your needs. This tells the service to allow consumers of the service to query the Customer entities.

It is also very helpful to add in one additional line of code that will display error messages while you are developing the service.

config.UseVerboseErrors = true;

At this point, you can run the service and explore how it presents its data.

  1. Right click the svc file in Solution Explorer and click View in Browser.

You’ll notice that the service doesn’t run immediately. If you watch the status bar at the bottom of the Visual Studio window, you’ll see messages such as Packaging Deployment and Running Deployment. You’ll also see a notification about the Windows Azure Emulator starting. The Windows Azure tool is creating a local service that will emulate the Windows Azure environment in the cloud. This will allow you to run your Windows Azure applications and services in your development environment. When all of this background work is complete, the tool will open your browser which is pointed to a randomly assigned port using the file-based Web Development server that Visual Studio uses for other web application development purposes.


Figure 1

Here you can see that the service exposes only Customers even though as the developer, you know that the model contains many more entities than that.

You can perform all types of queries using the OData Uri querying syntax. Figure 2 shows a simple example to look at a single Customer.


Figure 2: Query Data Service for single customer

Notice that by default all of the fields are exposed including potentially sensitive data such as Password fields. While you can’t remove properties in the feed, you can exert control in the service code over how the data is exposedFor example, you can use Interceptors (https://msdn.microsoft.com/en-us/library/dd744842.aspx).   In addition to simply querying the service using URIs, there are a number of client libraries that simplify interacting with OData from your applications. You should also consider modifying your model to be sure that only that data which you want exposed is presented in the model. You can learn about customizing a data service, the syntax for querying the service and information about the client libraries in the MSDN Library document, WCF Data Services at https://msdn.microsoft.com/en-us/library/cc668792.aspx.

Further on in this whitepaper, you will see one of these client libraries in action when the service is consumed by an MVC application.

Verify that the Service Works with the SQL Azure Database

While it will be more efficient to do your development and testing against the local database, it is helpful to perform a quick check to ensure that your service will properly interact with the SQL Azure database. You can do this by simply modifying the database connection string in the web.config file of the WCFServiceWebRole project.

Locate the connection in the connectionStrings element of the config file.

<connectionStrings>

  <add name="AWEntities"
       connectionString="metadata=res://*/AWEntities.csdl|res://*/AWEntities.ssdl|
         res://*/AWEntities.msl; provider=System.Data.SqlClient;
         provider connection string=
          &quot;Data Source=.;Initial Catalog=AdventureWorksLT2008R2;
                            Integrated Security=True;MultipleActiveResultSets=True&quot;"
       providerName="System.Data.EntityClient" />

</connectionStrings>

The database connection string is defined in the value of the provider connection string attribute.  Replace that with the connection string that points to your SQL Azure database.

provider connection string=
  &quot;Data Source=myserver.database.windows.net;
                    Initial Catalog=AdventureWorksLTAZ2008R2;
                    User ID=user;password=p*s*w*rd;
                    MultipleActiveResultSets=True&quot;

If you’ve never accessed your SQL Azure account from your development machine, you’ll need to allow your computer to get through SQL Azure’s firewall. In the SQL Azure portal, you can identify IP addresses that will be allowed to access your database. See the MSDN Library topic, SQL Azure Firewall, at https://msdn.microsoft.com/en-us/library/ee621782.aspx for details on how to accomplish this.

Run the service again, querying for a single customer as previously shown in Figure 2. The results will be the same although you are now pointing to the SQL Azure version of the database.

Deploying the Service to the Cloud

Windows Azure services are bundled up in packages to be deployed to the cloud. Visual Studio will do this for you. You can create the package or in one step, create and deploy the package. This requires that you already have a Windows Azure account. You can learn more about getting a Windows Azure account at https://msdn.microsoft.com/en-us/windowsazure/ff798116.aspx.

To package and deploy your service:

  1. Right click on the WindowsAzureProject1 project and choose Publish from the context menu. You’ll then see the Deploy Windows Azure project wizard as show in Figure 3.
  2. Open the Credentials drop down and click <Add>.


Figure 3: Deploy Windows Azure project wizard

If this is the first time you have published a Windows Azure service from Visual Studio, you will be prompted to provide a way for Visual Studio permissions to publish to your Azure account as shown in Figure 4. The “Help me set up my credentials” link will take you to an MSDN Library document that will walk you through selecting a certificate from your computer (or creating one if necessary) and associating that with your Windows Azure account. 


Figure 4: One-time Visual Studio authentication setup

Once you have credentials set up you can use them in the deployment screen. Selecting the credentials will automatically populate the Deployment environment and Storage account drop downs as show in Figure 5.


Figure 5: Deployment wizard with Credentials selected

Notice that this project is deploying to the Staging environment, not the Production environment. Staging is where you can deploy your projects to test them in the cloud before deploying them to production.

Click OK to complete the wizard and begin the deployment process.

As the Windows Azure tools are working at deploying the project, you will see the status displayed in the Windows Azure Activity Log in Figure 6. It will take a few minutes to deploy the project so be patient!


Figure 6: Deployment status

When the deployment is finished, you can see the service listed in your online Windows Azure portal at windows.azure.com as show in Figure 7.


Figure 7: Windows Azure Portal

After the service has been successfully deployed, the Website URL in the log will be replaced with the live URL which will be a combination of the Deployment ID and .cloudnet.app. You can click on that link to browse to the server. That URL will return an HTTP 403 error. You need to add to that the service which you deployed. 

Browsing to the service should give you the same results that you saw when testing the service in your development environment.

The service header does not access the database. Before you can do any queries, you’ll need to be sure that Windows Azure Service is able to access your SQL Azure database. In the SQL Azure portal, you can provide this access by selecting “Allow other Windows Azure services to access this server”. This will cause the MicrosoftServices rule to be added to your database’s Firewall Rules.


Figure 8

Given that the web.config contained the SQL Azure database connection string before you published this service, you will now be able to query the data service which will in turn, return data from the SQL Azure database.

Figure 9 shows the beginning of the results of an OData query that requests the first five customers from the service using the additional query “$top=5”.


Figure 9: Querying the service for the first five customers

Consuming the Service from an ASP.NET MVC UI Web Role

Any application where a developer is able to make HTTP calls is an application that can consume an OData Service; a good example is any application that is created using Javascript. In its current configuration, anyone can now use your cloud data service from their applications. It’s unlikely that they’ll be doing this from a web browser.  As mentioned earlier, Microsoft has also created client libraries for a number of platforms to make it easier to interact with OData. To download any of those client libraries, visit https://msdn.microsoft.com/en-us/data/ee720179.

Let’s take a look at building a cloud based application – you can follow along with the application that is included in the sample code attached to the article - to consume the current service which reads some of this Customer data. We’ll build an ASP.NET MVC application which is a client application. In the MVC application, you’ll use the WCF Data Services client library to access the OData feed.

In addition to a Service Web Role, there are also other Azure roles that provide user interfaces. One of those is an ASP.NET MVC application.

Add a new Windows Azure project named AzureMVCProject to the current solution.

In the New Windows Azure Project window, select ASP.NET  MVC 2 Web Role and move it to the solutions box on the right then click OK.

Similar to when you created the service, Visual Studio will add an MVCWebRole project to the solution along with the MVCAzureProject project that you specified. The MVCWebRole is the MVC UI which will be hosted in the cloud by the MVCAzureProject. If you specified that Tests should be created along with the MVC project, then you will have an additional new project for those tests.


Figure 10

The MvcWebRole project will need to work with the data service. Using the Visual Studio Add Service Reference feature, you can build proxy classes for the service and at the same time, add references to the .NET OData Client libraries.

  1. In Solution Explorer, right click the MvcWebRole project and from its context menu, choose Add Service Reference.
  2. In the Add Service Reference dialog, enter the URL for the data service into the Address box then click Go.

The tool will inspect the data service and display the accessible classes, in this case only Customers.

  1. Rename the service to AWEntitiesService and click OK.

If you were creating a reference to a service that has already been deployed in the cloud, you can do that from this dialog also. Just put the service URL in the address, for example: https://myservice.cloudapp.net/AWDataService.svc.

In addition to the service reference being added to the MvcWebRole project, references are added to other assemblies including System.Data.Services.Client – the WCF Services client which is used for working with an OData service in a .NET Framework application..

Using proxy classes generated by the Add Service Reference wizard based on the model and the client library functionality (including the libraries’ LINQ querying capabilities) you can write .NET code to interact with the data service rather than having to write OData URI syntax queries.

One of the most important elements of the proxy classes that Visual Studio creates is that the AWEntities class, which is the door to all of the entities, inherits the client library’s DataServiceContext class. This provides easy access to data service feeds for composing LINQ queries and is used to execute queries and track objects on the client.

Here, for example are two methods in a data access class that retrieve data from the service. The first method queries for Customers using paging techniques so that not too many customers are returned at once. The second returns a single Customer.

public static List<Customer> GetCustomers(int skip, int take)
    {
      var context = new AWEntities(_awEntitesUri);
      var query = from c in context.Customers
                  orderby c.LastName + c.FirstName
                  select c;
      return query.Skip(skip).Take(take).ToList();
    }
    public static Customer GetCustomerById(int id)
    {
      var context = new AWEntities(_awEntitesUri);
      var query = from c in context.Customers
                  where c.CustomerID==id
                  select c;
      return query.Single();
    }

Notice that in each of the methods, the context class is instantiated by supplying a URI; this Uri is the base URI of the data service. For the purposes of development and testing, the URI of the local service is used. After testing that the data access functions work, you can switch the URI to point to the service that you’ve deployed into the cloud.

static Uri _awEntitesUri=new Uri("https://localhost:81/AWDataService.svc");

Your best bet is to have the local data service work with the local database, so that all of the initial development of the MVC Web role can be performed locally. This way you can debug the service and profile the database if necessary.

In fact, a good plan to execute is to test your application in the following stages:

  1. Using the local service accessing the local database
  2. Using the local service with the SQL Azure database
  3. Using the Windows Azure hosted service with the SQL Azure database.

MVC stands for Model View Controller. The data service will provide the model, i.e. the domain classes. A controller is a class which interacts as an interface between the model and the Views, which are the user interface, for example and ASP.NET ASPX page. You can find excellent resources to get started with building ASP.NET MVC applications at https://www.asp.net/mvc where you will learn how to create the controllers and the views.  Below, you’ll focus on the controller code and it’s impact on what is displayed in the views.

In each method, a LINQ query is defined and executed so that the method can return the results of that query. The MVC application’s controller calls these methods and passes the results to the appropriate views.

In the controller, there’s the Index action returns a list of customers and a Detail action returns details of a single customer. The Index method accepts the current page number so that it is able to provide the paging information required by the GetCustomers method.

public ActionResult Index(int? page)
    {
      if (page == null)
        page = 0;
      return View(DataAccess.GetCustomers(page.Value*10, 10));
    }

An Index View displays the customers retrieved in the Index action.

The Detail ActionResult method retrieves a single Customer and then returns that in the Detail View.

Recall that in the service, you exposed the Customers with EntitySetRights.AllRead. If you wanted your application to enable editing, you would need to modify those rights to rights that would allow this for example EntitySetRights.All . You can learn more about various permissions in Data Services from the WCF Data Services’ landing page in the MSDN Library at https://msdn.microsoft.com/en-us/library/cc668792.aspx.

With this working against the local service and database, you can run the app again with the connection string pointing to the SQL Azure database and then finally just modify the URI that is used by the data access class to point to the Windows Azure hosted data service.

static Uri _awEntitesUri=new Uri("https://myserver.cloudapp.net/AWDataService.svc");

In a production app, you might find it more convenient to store URIs in a config file.

When running your app against the cloud service, latency should be minimized thanks to the use of paging in your data access class where you are querying for a limited set of results.

 In the sample download that accompanies this article, you can see how the Index.aspx view leverages the paging information to provide the appropriate navigation — links to go to the previous page or the next page of Customers. You’ll also find that the Uri is set in the web role’s configuration file and accessed in code, so that it can be easily changed if needed.

Deploying the Azure Project and MVC Web Role to the Cloud

Finally it’s time to publish the new Azure project along with its MVC Web Role to your Windows Azure portal. You can do this using the same steps that you used to deploy the data service.  Each service that you want to host on your Windows Azure portal needs its own cloud service, so you can’t publish this project to the same Deployment Environment that is already hosting the data service.

Start by right clicking on the AzureMVCProject project and selecting Publish from the context menu.

Once this project is deployed and running you will have an app that is running 100% from the cloud…the user interface, the data service and the SQL Azure database. Figure 11 shows the application running from a cloudapp.net Windows Azure server.


Figure 11

About the Author

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other Microsoft .NET topics at user groups and conferences around the world. Julie blogs at thedatafarm.com/blog and is the author of the highly acclaimed book, “Programming Entity Framework” (O’Reilly Media, 2009). Follow her on Twitter.com: julielerman.