Exporteren (0) Afdrukken
Alles uitvouwen

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

Updated: November 26, 2014


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


  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.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    namespace SchoolApplicationEF
    class Program
        private static SchoolEntities schoolContext;
    static void Main(string[] args)
            //Initialize the ObjectContext
            using (schoolContext = new SchoolEntities())
                // Define a query that returns all People
                // objects ordered by last name.
                var personQuery = from p in schoolContext.People.Include("Courses")
                                  orderby p.LastName
                                  select p;
                Console.WriteLine("List of people: ");
                        foreach (Person per in personQuery)
                            Console.WriteLine("{0} {1}", per.FirstName, per.LastName);
                catch (Exception ex)
                        Console.WriteLine("An error occurred: " + ex.Message);
            Console.WriteLine("Hit enter...");

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


© 2014 Microsoft