Storing SQL Data In Azure By Using Azure Database And Entity Framework

Introduction

Azure SQL Database is a relational Database-as-a-Service using the Microsoft SQL Server Engine. SQL Database is a high-performance, reliable, and secure database you can use to build data-driven applications and websites in the programming language of your choice, without needing to manage the infrastructure.

In this post, we will learn how to use SQL Database in Azure platform as a service (PaaS).

Step 1 - Create Data Context and Entity Classes in existing MVC Project

  1. From Visual Studio project in Solution Explorer, right click and select Manage NuGet Packages.

    Azure SQL Database

  2. In Manage NuGet Packages dialog, click on browse end enter “entity” search textbox, then select EntityFramework Package and install it.

    Azure SQL Database
  1. Create a new folder in your project and rename it “DataAccess”.

    Azure SQL Database
  1. Inside DataAccess Folder, create a new class and rename EmployeeDataContext then write the following code.
    1. public class EmployeeDataContext : DbContext  
    2.         {  
    3.         public EmployeeDataContext():base("name=EmployeeDbCs")  
    4.         {  
    5.   
    6.         }  
    7.         public DbSet<Employee> Employees { get; set; }  
    8.    }  
  1. Create Employee Class inside DataAccess folder then write the following code.
    1. public class Employee  
    2.   {  
    3.       public int Id { get; set; }  
    4.       public string Name { get; set; }  
    5.   
    6.       [DataType(DataType.Date)]  
    7.       public DateTime JoinDate { get; set; }  
    8.   
    9.       public decimal VacationBalance { get; set; }  
    10.   
    11.       [DataType(DataType.Date)]  
    12.       public DateTime UpdateDate { get; set; }  
    13.   
    14.   }  
  1. To test, we need to create a local database in the local SQL server. To do that in SQL Server, instantly create a new database.

    Azure SQL Database

  2. In Web.config Add the connection string tag,
    1. <connectionStrings>  
    2.   <add name="EmployeeDbCs" connectionString="Data Source=.;Initial Catalog=EmployeeDb;Integrated Security=True;MultipleActiveResultSets=True"   
    3.        providerName="System.Data.SqlClient" />  
    4. </connectionStrings>  
  1. Add new controller.

    Azure SQL Database

  2. In controller dialog select the model class and data context class and fill the controller name,

    Azure SQL Database

  3. We need to test the application, and make sure the application is working.

    Azure SQL Database

Step 2 - Deploy the web and using SQL Azure Database

  1. In Azure portal from SQL Database in the left menu create a new SQL Database and fill in all required fields. Then create the database and SQL server.

    Azure SQL Database

  2. Wait until the creation is complete.

    Azure SQL Database

  3. Open database, from database dashboard, click on set server firewall, click on set client IP and then click save the test database by opening from local SQL management studio

    Azure SQL Database

  4. Copy the database connection from database dashboard and past into notepad to modify it.

    Azure SQL Database

  5. Modify the User ID and Password values.

    Azure SQL Database

  6. Add the connection string to the Azure web through web dashboard, application settings, under connection strings. Then, click Save.

    Azure SQL Database

  7. Now test your application.

    Azure SQL Database

Congratulations! Your application is online now.