Connect your MVC application to Azure SQL Database using Entiry Framework

In your MVC application, add reference to Entity Framework using NuGet. I will use the SalesDb in my SQL server local database to demonstrate this. The same has been deployed in my Windows Azure SQL Server as well (Refer my previous post here for details).

This demonstration assumes that you are familiar to MVC4(or 5) and  Entitiy Framework and hence I will skip detailed descriptions for those.

(Adding Entitiy Framework to your project using NuGet)EntityFramework

Create an ADO .NET Entity Model (right click Model folder under your MVC application and follow the Entiry Framework wizard to select the database and schema)

Untitled.png

Once the new data model is added, you can add a scafold item for the controller (Choose ‘MVC 5 controller with Views using Entity Framework’ ). For example, if you select the Products schema, it generates the required controller and view (.cshtml). Launch the application for a quick test for connectivity to your local SQL server database. And all looks good.

Next step is to publish the application to Azure and update the connection to the SQL server database.

Inspect your connection sting in Web.Config. It will be of the below format:-

<add name=”salesdbEntities” connectionString=”metadata=res://*/Models.SalesModel.csdl|res://*/Models.SalesModel.ssdl|res://*/Models.SalesModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source={My Server};initial catalog=salesdb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;” providerName=”System.Data.EntityClient” /> 

For the database in Azure, copy the connection string from your Azure account as shown below.

Untitled2

Server=tcp:{azure_db_server}.database.windows.net,1433;Database=SalesDB;User ID={username}@azure_db_server;Password={password};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

 

Use this Azure database connection specifics to update  the web.config connection string as below

<add name=”salesdbEntities” connectionString=”metadata=res://*/Models.SalesModel.csdl|res://*/Models.SalesModel.ssdl|res://*/Models.SalesModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Server=tcp:{azure_db_server}.database.windows.net,1433;Database=SalesDB;User ID={username}@azure_db_server;Password={password};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;;MultipleActiveResultSets=True;App=EntityFramework&quot;” providerName=”System.Data.EntityClient” />

The next step is to publish your application to Azure. In this case, I choose App Service under my Azure account to deploy the application.

Untitled3

Launch your application using the URL and check the data connectivity and all looks good here!

Untitled4

Advertisements