Export (0) Print
Expand All

How to: Connect to Azure SQL Database Using the ADO.NET Entity Framework

Updated: November 26, 2014

The ADO.NET Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema.

This topic shows how to create a simple conceptual model, that is mapped to a Microsoft Azure SQL Database, and query over the conceptual model. The following Visual C#/Visual Basic example creates a strongly-typed query against the CLR objects that represent entities and associations in the School model, which returns the department names in the School model.

To follow this example, you must install the Entity Framework and have the sample School database created on your server. To create the sample School database, see Getting Started with Microsoft Azure SQL Database and complete steps one through eight.

Create the data model

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

  2. In the New Project dialog box, in the Installed templates list, select either Visual Basic or Visual C#, and then select Console Application. Enter SchoolApplicationEF as the Name, and then click OK.

  3. In Solution Explorer, right-click SchoolApplicationEF, 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 EF Designer from database, and then click Next.

  6. Click New Connection, then in the Choose Data Source dialog box select Microsoft SQL Server, and then click Continue.

  7. In the Connection Properties dialog box, enter the fully qualified name of the sever in the Server name box. For example, z8ue9o20al.database.windows.net.

  8. In the Log on to the server box, select Use SQL Server Authentication and enter the credentials for the database administrator. For example, tester or tester@ z8ue9o20al.database.windows.net.

  9. Click the Test Connection button to validate the logon information.

  10. In the Connect to a database box, select Select or enter a database name and then select the School database from the list. Click OK.

  11. In the Entity Data Model Wizard, choose No, exclude sensitive data from the connection string. I will set it in my application code option. Then, click Next.

  12. Select the most recent version of the Entity Framework, and then click Next.

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

  14. Save the SchoolDataModel.edmx file.

    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. In addition, the Pluralize or singularize generated object names check box does not exist in Visual Studio 2008. So, you must update Departments as Department and Courses as Course in the following code example.

Create the Console Application

  1. Replace the code in your Program file with the following code.

    Imports System
    Imports System.Collections.Generic
    Imports System.Linq
    Imports System.Text
    Module Module1
        Private schoolContext As SchoolEntities
        Sub Main()
            ' Initialize the ObjectContext.
            Using schoolContext = New SchoolEntities()
                // Define a query that returns all People
                // objects ordered by last name.
                Dim personQuery = _
                    From d In schoolContext.People.Include("Courses") _
                    Order By p.LastName _
                    Select p
                    For Each per As Person In personQuery
                        Console.WriteLine("{0} {1}", per.FirstName, per.LastName)
                Catch ex As Exception
                    Console.WriteLine("An error occurred: " + ex.Message)
                End Try
            End Using
            Console.WriteLine("Hit enter...")
        End Sub
    End Module

Migrate the application to SQL Database

  1. In the SchoolEFApplication project, open the App.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>.database.windows.net;Initial Catalog=School;Integrated Security=False;User ID=<provideUserID>;Password=<providePassword>;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False&quot;" providerName="System.Data.EntityClient"/>
  2. Press F5 to run the application against your Azure SQL Database. The console window will show the names of all the people in the School database.

See Also

Community Additions

© 2014 Microsoft