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.
   
   
   
   
2. In Manage NuGet Packages dialog, click on browse end enter “entity” search textbox, then select EntityFramework Package and install it.
   
   

3. Create a new folder in your project and rename it “DataAccess”.
   
   

4. Inside DataAccess Folder, create a new class and rename EmployeeDataContext then write the following code.
   

   public class EmployeeDataContext : DbContext
           {
           public EmployeeDataContext():base("name=EmployeeDbCs")
           {
   
           }
           public DbSet<Employee> Employees { get; set; }
      }

5. Create Employee Class inside DataAccess folder then write the following code.
   

   public class Employee
     {
         public int Id { get; set; }
         public string Name { get; set; }
   
         [DataType(DataType.Date)]
         public DateTime JoinDate { get; set; }
   
         public decimal VacationBalance { get; set; }
   
         [DataType(DataType.Date)]
         public DateTime UpdateDate { get; set; }
   
     }

6. 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.
   
   
   
   
7. In Web.config Add the connection string tag,
   

   <connectionStrings>
     <add name="EmployeeDbCs" connectionString="Data Source=.;Initial Catalog=EmployeeDb;Integrated Security=True;MultipleActiveResultSets=True"
          providerName="System.Data.SqlClient" />
   </connectionStrings>

8. Add new controller.
   
   
   
   
9. In controller dialog select the model class and data context class and fill the controller name,
   
   
   
   
10. We need to test the application, and make sure the application is working.
    
    

Step 2 - Deploy the web and using SQL Azure Database

11. 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.
    
    
    
    
12. Wait until the creation is complete.
    
    
    
    
13. 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
    
    
    
    
14. Copy the database connection from database dashboard and past into notepad to modify it.
    
    
    
    
15. Modify the User ID and Password values.
    
    
    
    
16. Add the connection string to the Azure web through web dashboard, application settings, under connection strings. Then, click Save.
    
    
    
    
17. Now test your application.
    
    

Congratulations! Your application is online now.