SALES: 1-800-867-1380

How to: Connect to Azure SQL Database Through WCF Data Services

Updated: April 12, 2014

WCF Data Services (formerly known as ADO.NET Data Services) enables you to create data services that implement the Open Data Protocol (OData), which is based on representational state transfer (REST). Because OData is based on standard internet protocols, data services can be accessed by any client application that can send and receive HTTP messages and parse XML. In addition, there are many client libraries that make it easier to consume OData feeds on various application platforms. For more information on client libraries that support OData, see the OData SDK.

WCF Data Services requires a data services provider implementation to define the data model and provide access the underlying data source. By using the Entity Framework provider, you can create data services for a Azure SQL Database. For more information on how to use WCF Data Services, see WCF Data Services documentation.

When opening a connection to a Azure SQL Database, set the connection timeout to 30 seconds.

This topic shows how to create a simple data service that is based on a Azure SQL Database and an Azure application that accesses the data service. This topic uses the School sample database that is deployed to Azure SQL Database in How To: Migrate a Database by Using Scripts.

To follow this example, you must install the Azure SDK and the Azure Tools for Microsoft Visual Studio. For more information about Azure, see Azure SDK. You must also initialize the Development Storage service installed with the Azure Tools using the DSInit utility. Development Storage must be initialized only one time. For more information, see About Development Storage.

Create the data model.

  1. In Visual Studio, on the File menu, point to New, and then click Project.

    You must run Visual Studio as an administrator to use the Azure Development Fabric.

  2. In the Project types pane, select Web. In the Templates pane, select ASP.NET Web Application. Enter SchoolApplication as the Name, and then click OK.

  3. In Solution Explorer, right-click SchoolApplication, point to Add, and select New Item.

  4. Select ADO.NET Entity Data Model, enter SchoolDataModel.edmx as the Name, and then click Add.

  5. In the Entity Data Model Wizard, select Generatefrom database and then click Next.

  6. Click New Connection, and enter the information for the School database on your Azure SQL Database server, click OK. Choose No, exclude sensitive data from the connection string. I will set it in my application code option. Then, click Next .

  7. Select Tables and then click Finish. This creates an .edmx file that defines an entity type for each table in the database.

    To follow this example in Visual Studio 2010 or later, clear the Pluralize or singularize generated object names check box. When running this example in Visual Studio 2008, the Entity Framework cannot create the .edmx file and related client classes from a Azure SQL Database. It must connect to a local instance of SQL Server to generate the model and mapping files. However, the Entity Framework will connect to Microsoft Azure SQL Database when the model and mapping files have been created.

Create the data service.

  1. In SolutionExplorer, right-click SchoolApplication, point to Add, and select NewItem.

  2. Select WCF Data Service, enter SchoolDataService.svc as the Name, and then click Add.

  3. In SchoolDataService.svc.cs or SchoolDataService.svc.vb, replace the comment /* TODO: put your data source class name here */ in the definition of the class that defines the data service with the type that is the entity container of the data model (in this case SchoolEntities). The class definition should resemble the following:

    Inherits DataService(Of SchoolEntities)
  4. Replace the placeholder code in the InitializeService function with the following code, which grants access to the Course, Department, and Person entities:

    Public Shared Sub InitializeService(ByVal config As IDataServiceConfiguration)
        config.SetEntitySetAccessRule("Course", EntitySetRights.All)
        config.SetEntitySetAccessRule("Department", EntitySetRights.All)
        config.SetEntitySetAccessRule("Person", EntitySetRights.All)
    End Sub

Create the client application.

  1. In Solution Explorer, right-click the solution, point to Add, and then click New Project.

  2. In the Add New Project dialog box, from the Project types pane, select Cloud Service. Type SchoolSite for the Name and then click OK.

  3. In the New Cloud Service Project dialog box, add an ASP.NET Web Role to the Cloud Service Solution pane and then click OK.

  4. Replace the source in Default.aspx with the following code:

    <html xmlns="" >
    <head runat="server">
        <form id="form1">
            <asp:DropDownList ID="peopleList" runat="server" 
            <br />
            <br />
            <asp:Label ID="infoLabel" runat="server"></asp:Label>
            <br />

    This creates a drop down list that is named peopleList and a Label control that is named infoLabel.

  5. Press F6 to build the application.

  6. In Solution Explorer, right-click the Web Role project and then click Add Service Reference….

  7. In the Add Service Reference dialog box, click Discover. SchoolDataService.svc appears in the Services pane. Type SchoolServiceReference for the Name and then click OK. This adds the file that contains the data classes that access data service resources.

Access the School data service from the Azure Web Role.

  1. In Default.aspx.cs or Default.aspx.vb, add the following using statements (Imports in Visual Basic):

    Imports System.Data.Services.Client
    Imports WebRole1.SchoolServiceReference
  2. In the partial class, add the following declarations for the DataServiceContext and the URI to the data service (the URI that was displayed earlier in the Address field of the Add Service Reference dialog box):

    Private schoolContext As SchoolEntities
    ' Replace the host server and port number with the values
    '   for the test server that is hosting your data service instance.
    Private svcUri As Uri = New Uri("http://localhost:61925/SchoolDataService.svc")
  3. In the Page_Load method, instantiate the data service context and load the drop-down list with information from the data service by adding the following code:

    ' Instantiate the DataServiceContext
    schoolContext = New SchoolEntities(svcUri)
    ' Get all person entities from the data service
    Dim people As DataServiceQuery(Of Person) = schoolContext.Person
    ' Add each person to the drop down list
    For Each p As Person In people
        peopleList.Items.Add(New ListItem(String.Concat(p.FirstName.ToString(), " ", _
                                                        p.LastName.ToString()), p.PersonID.ToString()))
  4. In the design view of Default.aspx, double-click the drop-down list to add the peopleList_SelectedIndexChanged event.

  5. In the peopleList_SelectedIndexChanged event, populate the Label control based on the selected person by adding the following code:

    Protected Sub peopleList_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles peopleList.SelectedIndexChanged
        ' Define a LINQ query that returns information
        '   about the selected person.
        Dim personInfo = (From p In schoolContext.Person _
                           Where p.PersonID = Convert.ToInt32(peopleList.SelectedItem.Value) _
                           Select p).FirstOrDefault()
        ' Display information about the person
        infoLabel.Text = String.Concat("ID: ", personInfo.PersonID.ToString(), " ", _
                                       "Enroll Date:", personInfo.EnrollmentDate.ToString(), " ", _
                                       "Hire Date: ", personInfo.HireDate.ToString())
        infoLabel.Visible = True
    End Sub

Migrate the application to SQL Database.

  1. Migrate the School database to Azure SQL Database by following the instructions in How To: Migrate a Database by Using Scripts.

  2. In the SchoolApplication project, open the Web.config file. Change the connection string so that it connects to your Azure SQL Database.

        <add name="SchoolEntities" connectionString="metadata=res://*/SchoolDataModel.csdl|res://*/SchoolDataModel.ssdl|res://*/SchoolDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=<provideServerName>;Initial Catalog=School;Integrated Security=False;User ID=<provideUserID>;Password=<providePassword>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False&quot;" providerName="System.Data.EntityClient"/>
  3. In Solution Explorer, right-click the WebRole1 project and select Set as startup project.

  4. Press F5 to run the application.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2014 Microsoft