In my last articles we have learned how to make CRUD operations using ASP.NET MVC which are all with on premises database but the many reader asking me how to make CRUD operations from Microsoft azure database so by considering their demand I have decided to write this article , Let's learn it step by step
Step 1: Create Azure (cloud) SQL database
First we need to create the Azure SQL database , To create Azure SQL database you need a Azure subscription account, I hope you have an azure account , Now go to the your azure portal
using following link
Microsoft Azure Account
Now after logging into the Azure watch following my video which explains the how to create the Azure SQL database
I hope you have created Azure SQL Database by watching steps shown in preceding video
, after creating the database it listed under the database section of azure portal as shown in the following image ,
The preceding is the sample Azure SQL Database named
EDS and the Database server location is Central India
Step 2 : Find the database credentials
Now we have created database and use from our premises we need to obtain the connecting string , So to obtain connection string
double click on listed database as shown in the preceding image , after clicking on database it will display the following screen
I hope by you have followed the preceding steps as shown in the image and obtained the database connection string
Step 3 : Connect to the Azure SQL Database
Now our database is created in azure cloud and we have also the connection string details to connect that created database, Now open your SQL Server Management studio from you local system and watch the my following video which shows you how to connect SQL Server Management studio step by step
I hope after watching the preceding video you have connected with your created cloud azure database
.
Step 4 : Create an MVC Application.
Now let us start with a step by step approach from the creation of simple MVC application as in the following:
- "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
- "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:
As shown in the preceding screenshot, click on
Empty template and check
MVC option, then click
OK. This will create an empty MVC web application whose Solution Explorer will look like the following:
Step 5 : Add The Reference of Dapper ORM into Project
Now next step is to add the reference of Dapper ORM into our created MVC Project ,follow the following steps
- Right click on Solution ,find Manage NuGet Package manager and click on it
- After as shown into the image and type in search box "dapper"
- Select Dapper as shown into the image .
- Choose version of dapper library and click on install button
After installing the Dapper library ,It will be added into the References of our solution explorer of MVC application as
hope you have followed the same steps and installed dapper library.
Step 6: Create Model Class
Now let us create the model class named EmpModel.cs by right clicking on model folder as in the following screenshot:
EmployeeModel.cs
public class EmployeeModel
{
[Display(Name = "Id")]
public int Empid { get; set; }
[Required(ErrorMessage = "First name is required.")]
public string Name { get; set; }
[Required(ErrorMessage = "City is required.")]
public string City { get; set; }
[Required(ErrorMessage = "Address is required.")]
public string Address { get; set; }
}
In the above model class we have added some validation on properties with the help of DataAnnotations.
Step 7: Create Controller.
Now let us add the MVC 5 controller as in the following screenshot:
After clicking on
Add button it will show the following window. Now specify the
Controller name as
Employee with suffix
Controller as in the following screenshot:
After clicking on
Add button controller is created with by default code that support CRUD operations and later on we can configure it as per our requirements.
Step 8 : Create Table and Stored procedures.
Now We are connected with azure SQL database from SQL Server management studio and before creating the views let us create the table name
Employee in database according to our model fields to store the details:
I hope you have created the same table structure as shown above. Now create the stored procedures to insert, update, view and delete the details as in the following code snippet:
To Insert Records
Create procedure [dbo].[AddNewEmpDetails]
(
@Name varchar (50),
@City varchar (50),
@Address varchar (50)
)
as
begin
Insert into Employee values(@Name,@City,@Address)
End
To View Added Records
CREATE Procedure [dbo].[GetEmployees]
as
begin
select Id as Empid,Name,City,Address from Employee
End
To Update Records
Create procedure [dbo].[UpdateEmpDetails]
(
@EmpId int,
@Name varchar (50),
@City varchar (50),
@Address varchar (50)
)
as
begin
Update Employee
set Name=@Name,
City=@City,
Address=@Address
where Id=@EmpId
End
To Delete Records
Create procedure [dbo].[DeleteEmpById]
(
@EmpId int
)
as
begin
Delete from Employee where Id=@EmpId
End
Step 9: Create Repository class.
Now create Repository folder and Add EmpRepository.cs class for database related operations, after adding the solution explorer will look like the following screenshot:
Now create methods in EmpRepository.cs to handle the CRUD operation as in the following
EmpRepository.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CRUDUsingMVC.Models;
using Dapper;
using System.Linq;
namespace CRUDUsingMVC.Repository
{
public class EmpRepository
{
public SqlConnection con;
//To Handle connection related activities
private void connection()
{
string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();
con = new SqlConnection(constr);
}
//To Add Employee details
public void AddEmployee(EmployeeModel objEmp)
{
//Additing the employess
try
{
connection();
con.Open();
con.Execute("AddNewEmpDetails", objEmp, commandType: CommandType.StoredProcedure);
con.Close();
}
catch (Exception ex)
{
throw ex;
}
}
//To view employee details with generic list
public List<EmployeeModel> GetAllEmployees()
{
try
{
connection();
con.Open();
IList<EmployeeModel> EmpList = SqlMapper.Query<EmpModel>(
con, "GetEmployees").ToList();
con.Close();
return EmpList.ToList();
}
catch (Exception)
{
throw;
}
}
//To Update Employee details
public void UpdateEmployee(EmployeeModel objUpdate)
{
try
{
connection();
con.Open();
con.Execute("UpdateEmpDetails", objUpdate, commandType: CommandType.StoredProcedure);
con.Close();
}
catch (Exception)
{
throw;
}
}
//To delete Employee details
public bool DeleteEmployee(int Id)
{
try
{
DynamicParameters param = new DynamicParameters();
param.Add("@EmpId", Id);
connection();
con.Open();
con.Execute("DeleteEmpById", param, commandType: CommandType.StoredProcedure);
con.Close();
return true;
}
catch (Exception ex)
{
//Log error as per your need
throw ex;
}
}
}
}
Note
- In the above code we manually opening and closing connection ,however you can directly pass the connection string to the dapper without opening it ,dapper will automatically handled .
- Log the exception in database or text file as per you convenience , since it article so i have not implemented it .
Step 10 : Create Methods into the EmployeeController.cs file.
Now open the
EmployeeController.cs and create the following action methods:
using System.Web.Mvc;
using CRUDUsingMVC.Models;
using CRUDUsingMVC.Repository;
namespace CRUDUsingMVC.Controllers
{
public class EmployeeController : Controller
{
// GET: Employee/GetAllEmpDetails
public ActionResult GetAllEmpDetails()
{
EmpRepository EmpRepo = new EmpRepository();
return View(EmpRepo.GetAllEmployees());
}
// GET: Employee/AddEmployee
public ActionResult AddEmployee()
{
return View();
}
// POST: Employee/AddEmployee
[HttpPost]
public ActionResult AddEmployee(EmployeeModel Emp)
{
try
{
if (ModelState.IsValid)
{
EmpRepository EmpRepo = new EmpRepository();
EmpRepo.AddEmployee(Emp);
ViewBag.Message = "Records added successfully.";
}
return View();
}
catch
{
return View();
}
}
// GET: Bind controls to Update details
public ActionResult EditEmpDetails(int id)
{
EmpRepository EmpRepo = new EmpRepository();
return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id));
}
// POST:Update the details into database
[HttpPost]
public ActionResult EditEmpDetails(int id, EmployeeModel obj)
{
try
{
EmpRepository EmpRepo = new EmpRepository();
EmpRepo.UpdateEmployee(obj);
return RedirectToAction("GetAllEmpDetails");
}
catch
{
return View();
}
}
// GET: Delete Employee details by id
public ActionResult DeleteEmp(int id)
{
try
{
EmpRepository EmpRepo = new EmpRepository();
if (EmpRepo.DeleteEmployee(id))
{
ViewBag.AlertMsg = "Employee details deleted successfully";
}
return RedirectToAction("GetAllEmpDetails");
}
catch
{
return RedirectToAction("GetAllEmpDetails");
}
}
}
}
Step 11: Create the Partial view to Add the employees
To create the Partial View to add Employees, right click on ActionResult method and then click
Add view. Now specify the view name, template name and model class in EmpModel.cs and After clicking on Add button it generates the strongly typed view whose code is given below:
AddEmployee.cshtml
@model CRUDUsingMVC.Models.EmployeeModel
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Add Employee</h4>
<div>
@Html.ActionLink("Back to Employee List", "GetAllEmpDetails")
</div>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10" style="color:green">
@ViewBag.Message
</div>
</div>
</div>
}
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
To View Added Employees
To view the employee details let us create the partial view named GetAllEmpDetails:
Now click on add button, it will create GetAllEmpDetails.cshtml strongly typed view whose code is given below:
GetAllEmpDetails.CsHtml
@model IEnumerable<CRUDUsingMVC.Models.EmployeeModel>
<p>
@Html.ActionLink("Add New Employee", "AddEmployee")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
@Html.HiddenFor(model => item.Empid)
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.ActionLink("Edit", "EditEmpDetails", new { id = item.Empid }) |
@Html.ActionLink("Delete", "DeleteEmp", new { id = item.Empid }, new { })
</td>
</tr>
}
</table>
To Update Added Employees
Follow the same procedure and create EditEmpDetails view to edit the employees. After creating the view the code will be like the following:
EditEmpDetails.cshtml
@model CRUDUsingMVC.Models.EmployeeModel
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Update Employee Details</h4>
<hr />
<div>
@Html.ActionLink("Back to Details", "GetAllEmployees")
</div>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.Empid)
<div class="form-group">
@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Update" class="btn btn-default" />
</div>
</div>
</div>
}
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
Step 12 : Configure Action Link to
Edit and delete the records as in the following figure:
The above ActionLink I have added in
GetAllEmpDetails.CsHtml view because from there we will delete and update the records.
Step 13: Configure RouteConfig.cs to set default action as in the following code snippet:
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional }
);
}
}
From the above RouteConfig.cs the default action method we have set is AddEmployee. It means that after running the application the AddEmployee view will be executed first.
Now after adding the all model, views and controller our solution explorer will be look like as in the following screenshot:
I hope from the preceding examples we have learned how to implement CRUD Operations in Azure SQL database using ASP.NET MVC
Note:
- Configure the database connection in the web.config file depending on your Azure SQL database server location.
- To Use Azure SQL Database you need Microsoft Azure Subscription
- Since this is a demo, it might not be using proper standards, so improve it depending on your skills
- This application is created completely focusing on beginners.
Summary
I hope, this article is useful for all the readers. If you have any suggestions, please contact me.