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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s