Creating and Hosting OData Service in Azure

In this article I would describe in detail on how to create and host OData Service in Azure. The OData service connects to Northwind database in SQL Azure.

In this demo we would go through the following steps:

1. Creating and Populating data with Northwind database in SQL Azure
2. Creating a OData Service that connects to Northwind Db in SQL Azure
3. Deploying the OData Service as a Web role in Azure.

    

Creating and populating data with Northwind database in SQL Azure:

In the Azure portal go to the SQL Databases options in the left sidebar and select Create new Database. Enter the name of the database as Northwind and leave other options as default.

Proceed to the second step in the creation of the Azure SQL database.

It is important to choose the option “Allow Windows Azure Services to Access the Server” as this is the option that enables the Azure Web role to access the database. If you deselect this option the SQL Azure firewall would not let you in.

As a next step we need to populate data in the Northwind DB in SQL Azure. For this we would need the installnwnd-sqlazure.sql script. Download the installnwnd-sqlazure.sql to your local machine.

Run the SQL Server Management Studio(SSMS 2012). When prompted to connect enter the SQL Azure database server name. (The SQL Azure database server name is available in the Azure portal under SQL Databases).

You need to authenticate with SQL Server authentication with user name and password that was entered while creating SQL Azure DB.

Once you have connected successfully, open the locally stored installnwnd-sqlazure.sql in SSMS 2012. Make sure you have selected the Northwind as your database and execute the SQL script.

Once the SQL script for populating the data is completed, you would see the status at the bottom corner which shows the number of rows affected.

To verify that your Northwind SQL Azure database is correctly populated you can expand on the tables and check the presence of Customers/Orders table in the DB and should be able to view all the customer / order data in the DB (issue a select query)

Creating a OData Service that connects to Northwind Db in SQL Azure

 

In order to create OData service that connects to SQL Azure you should have Visual Studio (2010 or 2012) installed in your machine.

Select New Project and select ASP.NET Empty Web Application:

 

Once the empty ASP.NET web application is created, add a new item and select ADO.NET Entity Data Model.

This would show the Entity Data Model Wizard. Select the option Generate from database

 

Create a new SQL Server connection (under connection properties the data source should be Microsoft SQL Server (SQL Client). You would need to enter the SQL Azure server name, select SQL authentication and enter the username / password. Once you have entered all the relevant information to connect to the SQL Azure you would see the following entity data model wizard from where you can select the Database tables that you want to be exposed as OData entities.

In this case I have selected “Customers” and “Orders” entity to be included in the model.
Once you have completed the above steps, the ADO.NET entity framework is initialized which is the adapter for getting data from the SQL Azure.

Now you would need to add WCF Data Service and bind the data source to the ADO.NET Entity data context.
Select Add New Item and select WCF Data Service:

 

Once you add the WCF Data Service you would need to bind the ADO.NET DataContext to the WCF Data Service. See below for more details:

public
class
NorthwindODataService : DataService< NorthwindEntities >

{

// This method is called only once to initialize service-wide policies.

public
static
void InitializeService(DataServiceConfiguration config)

{

// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.

// Examples:

// config.SetEntitySetAccessRule(“MyEntityset”, EntitySetRights.AllRead);

// config.SetServiceOperationAccessRule(“MyServiceOperation”, ServiceOperationRights.All);

config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;

config.SetEntitySetAccessRule(“*”, EntitySetRights.All);

config.SetServiceOperationAccessRule(“*”, ServiceOperationRights.All);

}

}

In the above example I have set the NorthwindODataService to fetch data from NorthwindEntities datacontext.

Build and run your application and you would be able to see the OData service running exposing the Customers and Orders entity from SQL Azure in its service manifest.

Deploying the OData Service as a Web role in Azure

As a next step let us deploy the above OData Service in Azure as a web role. For that purpose we would have to add a new project of type “Windows Azure Cloud Service”.
Note: This requires that you have installed the Azure SDK for Visual Studio.

Let us call the Azure Cloud Service as NorthwindAzure. Then we would add a ASP.NET Web Role for hosting the OData service in Azure.

Sadly there is no way to add an existing ASP.NET project to the Azure cloud service as the web role. So we would have to redo the steps we did in “Creating a OData Service that connects to Northwind Db in SQL Azure”.
In the new ASP.NET Web Role project you would have to explicitly add the WCF Data Service and bind it to the ADO.NET entity data context as explained in the previous section.

Once you complete the above steps you would be to run the OData Service connecting to SQL Azure against the local Azure simulator.

I am not covering how to deploy the Azure Web role to Azure cloud in this sample. You would have to follow the instructions described here.

Summary:

In summary developing and hosting a OData service that connects to SQL Azure is very direct and simple with Visual Studio. If you have any questions / issues please leave your comments below.