How to: Connect to Azure SQL Database Using the ADO.NET Entity Framework
Updated: December 6, 2013
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 from the 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 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. For information on how to migrate the School database to Azure SQL Database, see How To: Migrate a Database by Using Scripts.
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 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
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 Azure SQL Database.
Migrate the application to SQL Database
Migrate the School database to Azure SQL Database by following the instructions in How To: Migrate a Database by Using Scripts.
In the SchoolEFApplication project, open the App.Config file. Change the connection string so that it connects to your Azure 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 Azure SQL Database.