ASP.NET MVC 5: Connect with Azure SQLServer Database

Article
1/17/2024

Introduction

Using MVC, Entity Framework, ASP.NET Scaffolding, and Azure SQL Server you can create a web application that stores your information on an SQL Azure database. This demo shows you how to create a web application with MVC and Entity Framework 6, that communicate with a SQL Azure database.

Steps

1. Create Azure Account

You need to get a Microsoft Azure account. Everyone can open a Microsoft Azure account for free.

Check the link below for more information.

https://azure.microsoft.com/en-us/free/

trong>

2. Create SQL Database on Azure

After get access to an Azure Account, we need to create a SQL Database to store your data.

So for that we need to select the option New on the left bottom of our web page and then select the option Data Services -> SQL Database-> Quick Create and give a name to your SQL Database and provide credentials.

On this case our SQL Database will have the name "MVCDemo".

http://code.msdn.microsoft.com/site/view/file/113355/1/1.png

http://code.msdn.microsoft.com/site/view/file/113356/1/2.png

After creating the SQL Database, we need to get the connection string that will be used on Web Application to access the Azure SQL Database.

For that, select the database created and on the main window, on the right side, we have an option called "Show Connection String".

When we select that option, a new window will appear, like the following image, with the connection string formatted to different providers.

http://code.msdn.microsoft.com/site/view/file/113357/1/3.png

3. Create Web Application

I will be using Visual Studio 2013 as my development environment. Our first will be to create an ASP.NET Web Application.

Open Visual Studio 2013 and create a new project of type ASP.NET Web Application.
On this project I create a solution called MVCDemoAzureSQLServer.

http://code.msdn.microsoft.com/site/view/file/113358/1/4.png

Press OK, and a new screen will appear, with several options of template to use on our project.
Select the option MVC.

http://code.msdn.microsoft.com/site/view/file/113359/1/5.png

4. Create Data Model

After we have our web application created, we need to create our data model.

For that, select the option Add New Item on solution and choose the option ADO.NET Entity Data Model.

On this sample, we call it DataModel.edmx.

http://code.msdn.microsoft.com/site/view/file/113360/1/6.png

After we select this component, a configuration wizard will appear.

On the first screen, we need to choose model contents. Two options will be available:

Generate from database
Empty Model

http://code.msdn.microsoft.com/site/view/file/113361/1/7.png

By selecting Generate from database, you can generate an .edmx file from an existing database. In the next steps, the Entity Data Model Wizard will guide you through selecting a data source, database, and database objects to include in the conceptual model.

By selecting Empty model, you can add an .edmx file that contains empty conceptual model, storage model, and mapping sections to your project. Select this option if you plan to use the Entity Designer to build your conceptual model and later generate a database that supports the model.

On this sample, we select the first one.

http://code.msdn.microsoft.com/site/view/file/113362/1/8.png

On the next screen, we need to select the Data Connection.

Select the option New Connection and paste the information existent on STEP2 to ADO.NET provider.

The database will be filled with the name of our Azure SQL Database.

On this moment we have our empty .edmx file created with the correct connection string to our Azure SQL Database.

So, lets add some entities to our model.

On the DataModel.edmx diagram select the option Add New -> Entity

http://code.msdn.microsoft.com/site/view/file/113363/1/9.png

Create two entities like the existent on the next image.

http://code.msdn.microsoft.com/site/view/file/113364/1/10.png

Select the option Generate Database from Model.