How to: Connect to Windows Azure SQL Database Using the ADO.NET Entity Framework
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 Windows 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 from the .NET Framework 3.5 Service Pack 1 or later versions.
Create the data model
In Visual Studio, on the File menu, point to New, and then click Project.
In the Project types pane, select Windows. In the Templates pane, select Console Application. Enter SchoolApplicationEF as the Name, and then click OK.
In Solution Explorer, right-click SchoolApplicationEF, point to Add, and select New Item.
Select ADO.NET Entity Data Model, enter SchoolDataModel.edmx as the Name, and then click Add.
In the Entity Data Model Wizard, select Generate from database and then click Next.
Click New Connection, and enter the information for the School database on your 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. For information on how to migrate the School database to SQL Database, see How to: Migrate a Database by Using the Generate Scripts Wizard (Windows Azure SQL Database).
Select Tables and then click Finish. This creates an .edmx file that defines an entity type for each table in the database.
Save the SchoolDataModel.edmx file.
Note When running this example in Visual Studio 2008, the Entity Framework cannot create the .edmx file and related client classes from a 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 Windows 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
Coursein the following code example.
Create the Console Application
Replace the code in your program file with the following code, and then press F5 to run the application against your SQL Database.
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 Department objects ' and related Course objects, ordered by name. Dim departmentQuery = _ From d In schoolContext.Departments.Include("Courses") _ Order By d.Name _ Select d Try For Each dep As Department In departmentQuery Console.WriteLine(dep.Name) Next Catch ex As Exception Console.WriteLine("An error occurred: " + ex.Message) End Try End Using Console.WriteLine("Hit enter...") Console.Read() End Sub End Module
Migrate the application to SQL Database
Migrate the School database to SQL Database by following the instructions in How to: Migrate a Database by Using the Generate Scripts Wizard (Windows Azure SQL Database).
In the SchoolEFApplication project, open the App.Config file. Change the connection string so that it connects to your SQL Database.
<connectionStrings> <add name="SchoolEntities" connectionString="metadata=res://*/SchoolDataModel.csdl|res://*/SchoolDataModel.ssdl|res://*/SchoolDataModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=<provideServerName>.database.windows.net;Initial Catalog=School;Integrated Security=False;User ID=<provideUserID>;Password=<providePassword>;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False"" providerName="System.Data.EntityClient"/> </connectionStrings>
Press F5 to run the application against your SQL Database.