CRUD operations using AngularJs in Asp.net MVC
Introduction:
Today in this article, I will explain How to perform CRUD (Create, Read, Update and Delete) using AngularJS in Asp.net MVC application. In this example, I have used Ado.Net for database operation and Angular2.
Follow these steps in order to implement “CRUD operations using AngularJs in Asp.net MVC”
Step1: Create New Project.
Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to add button > It will show new dialog window for select template > here we will select empty project > then click to ok
Step2: Create a table and Stored procedure in the database.
In this example, I have used following table for CRUD operations.
CREATE TABLE [dbo].[tbl_registration] (
[Sr_no] INT IDENTITY (1, 1) NOT NULL,
[Email] NVARCHAR (100) NULL,
[Password] NVARCHAR (MAX) NULL,
[Name] VARCHAR (MAX) NULL,
[Address] NVARCHAR (MAX) NULL,
[City] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED ([Sr_no] ASC)
);
Now create stored procedure for CRUD operations
Add record
Create proc Sp_register
@Email NVARCHAR (100) ,
@Password NVARCHAR (MAX) ,
@Name VARCHAR (MAX) ,
@Address NVARCHAR (MAX) ,
@City NVARCHAR (MAX)
as
begin
insert into tbl_registration values(@Email,@Password,@Name,@Address,@City)
end
Get All record
Create proc Sp_register_get
as
begin
Select * from tbl_registration
End
Get record by id
Create proc Sp_register_byid
@Sr_no int
as
begin
Select * from tbl_registration where Sr_no=@Sr_no
End
Update record
CREATE proc Sp_register_Update
@Sr_no int ,
@Email nvarchar(200),
@Password nvarchar(200),
@Name nvarchar(200),
@Address nvarchar(max),
@City nvarchar(200)
as
begin
update tbl_registration set Email=@Email,Password=@Password,Name=@Name,Address=@Address,City=@City where Sr_no=@Sr_no
end
Delete record
Create proc Sp_register_delete
@Sr_no int
as
begin
delete from tbl_registration where Sr_no=@Sr_no
end
Run above script in MS SQL Server and click to execute button
Step3: Add Connection string in web.config file
Here I have added connection string in ‘web.config’ file under Configuration section as follows
<connectionStrings>
<add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Step4: Create a controller.
Go to Solutions Explorer > right click on controller folder > Add Controller > Enter Controller name > Select template “empty MVC Controller ” > Add.
Here I have created a controller “HomeController”
Step5: Create Database Access layer.
Here in this example, I have used Ado.net as database operation so we need to create a class for all database operations. Here I have created ‘db.cs’ class.
Go to Solutions Explorer > right click on project solution> Add New Folder > Enter Folder name (Here I rename it as ‘database_access_layer’) > right click on folder > Add new class.
Now add the following method into DB class that performs CRUD from the database table.
public class db
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
// Add Record
public void Add_record(register rs)
{
SqlCommand com = new SqlCommand("Sp_register", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Email", rs.Email);
com.Parameters.AddWithValue("@Password", rs.Password);
com.Parameters.AddWithValue("@Name", rs.Name);
com.Parameters.AddWithValue("@Address", rs.Address);
com.Parameters.AddWithValue("@City", rs.City);
con.Open();
com.ExecuteNonQuery();
con.Close();
}
//Display all record
public DataSet get_record()
{
SqlCommand com = new SqlCommand("Sp_register_get", con);
com.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
// Update all record
public void update_record(register rs)
{
SqlCommand com = new SqlCommand("Sp_register_Update", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Sr_no", rs.Sr_no);
com.Parameters.AddWithValue("@Email", rs.Email);
com.Parameters.AddWithValue("@Password", rs.Password);
com.Parameters.AddWithValue("@Name", rs.Name);
com.Parameters.AddWithValue("@Address", rs.Address);
com.Parameters.AddWithValue("@City", rs.City);
con.Open();
com.ExecuteNonQuery();
con.Close();
}
// Get Record by id
public DataSet get_recordbyid(int id)
{
SqlCommand com = new SqlCommand("Sp_register_byid", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Sr_no", id);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
// Delete record
public void deletedata(int id)
{
SqlCommand com = new SqlCommand("Sp_register_delete", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Sr_no", id);
con.Open();
com.ExecuteNonQuery();
con.Close();
}
}
Step6: Add Jsonresult return type methods in the controller for CRUD operations in the database.
Here I have created following methods in HomeController, that looks like as follows:
database_Acces_Layer.db dblayer = new database_Acces_Layer.db();
//Add record
public JsonResult Add_record(register rs)
{
string res = string.Empty;
try
{
dblayer.Add_record(rs);
res = "Inserted";
}
catch (Exception)
{
res = "failed";
}
return Json(res, JsonRequestBehavior.AllowGet);
}
// Display all records
public JsonResult Get_data()
{
DataSet ds = dblayer.get_record();
List<register> listrs = new List<register>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
listrs.Add(new register
{
Sr_no = Convert.ToInt32(dr["Sr_no"]),
Email = dr["Email"].ToString(),
Password = dr["Password"].ToString(),
Name = dr["Name"].ToString(),
Address = dr["Address"].ToString(),
City = dr["City"].ToString()
});
}
return Json(listrs, JsonRequestBehavior.AllowGet);
}
// Display records by id
public JsonResult Get_databyid(int id)
{
DataSet ds = dblayer.get_recordbyid(id);
List<register> listrs = new List<register>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
listrs.Add(new register
{
Sr_no = Convert.ToInt32(dr["Sr_no"]),
Email = dr["Email"].ToString(),
Password = dr["Password"].ToString(),
Name = dr["Name"].ToString(),
Address = dr["Address"].ToString(),
City = dr["City"].ToString()
});
}
return Json(listrs, JsonRequestBehavior.AllowGet);
}
// Update records
public JsonResult update_record(register rs)
{
string res = string.Empty;
try
{
dblayer.update_record(rs);
res = "Updated";
}
catch (Exception)
{
res = "failed";
}
return Json(res, JsonRequestBehavior.AllowGet);
}
// Delete record
public JsonResult delete_record(int id)
{
string res = string.Empty;
try
{
dblayer.deletedata(id);
res = "data deleted";
}
catch (Exception)
{
res = "failed";
}
return Json(res, JsonRequestBehavior.AllowGet);
}
Step7: Add model class for table
Go to Model folder >> Right click on model >> Add new class
Here I have added class as ‘register.cs’. It contains following code
public class register
{
public int Sr_no { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }
}
Step8: Add AngularJS library and AngularJS Controller js file
Go to Solution Explorer > Right Click on the folder (where you want to save your AngularJS controller JS files, here I have created a folder named "AngularController” under Script Folder) > Add > Select JavaScript file > Enter the name (I add a file as HomeAngularJS.js) > Add.
We write following code into HomeAngularJs.js file
var app = angular.module("Homeapp", []);
app.controller("HomeController", function ($scope, $http) {
$scope.btntext = "Save";
// Add record
$scope.savedata = function () {
$scope.btntext = "Please Wait..";
$http({
method: 'POST',
url: '/Home/Add_record',
data: $scope.register
}).success(function (d) {
$scope.btntext = "Save";
$scope.register = null;
alert(d);
}).error(function () {
alert('Failed');
});
};
// Display all record
$http.get("/Home/Get_data").then(function (d) {
$scope.record = d.data;
}, function (error) {
alert('Failed');
});
// Display record by id
$scope.loadrecord = function (id) {
$http.get("/Home/Get_databyid?id="+id).then(function (d) {
$scope.register = d.data[0];
}, function (error) {
alert('Failed');
});
};
// Delete record
$scope.deleterecord = function (id) {
$http.get("/Home/delete_record?id=" + id).then(function (d) {
alert(d.data);
$http.get("/Home/Get_data").then(function (d) {
$scope.record = d.data;
}, function (error) {
alert('Failed');
});
}, function (error) {
alert('Failed');
});
};
// Update record
$scope.updatedata = function () {
$scope.btntext = "Please Wait..";
$http({
method: 'POST',
url: '/Home/update_record',
data: $scope.register
}).success(function (d) {
$scope.btntext = "Update";
$scope.register = null;
alert(d);
}).error(function () {
alert('Failed');
});
};
});
Here I have created an angular controller named as ‘HomeController’ with parameter $http and $scope
$http: $http is an AngularJS service for reading data from remote servers. It makes a request to the server and returns a response.
$Scope: $Scope is the binding part between the HTML (view) and the JavaScript (controller). It is an object with the available properties and methods. $Scope is available for both the view and the controller.
Step9: Add action methods into controller for CRUD operations (Insert, Update and delete)
Here I have added following actions methods.
// View for Add record
public ActionResult Index()
{
return View();
}
// View for Display record
public ActionResult Show_data()
{
return View();
}
// View for Update record
public ActionResult update_data(int id)
{
return View();
}
Entire HomeController.cs look like as follows,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using AngualarCrudeOperation.Models;
using System.Data;
namespace AngualarCrudeOperation.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
database_Acces_Layer.db dblayer = new database_Acces_Layer.db();
// View for Add record
public ActionResult Index()
{
return View();
}
// View for Display record
public ActionResult Show_data()
{
return View();
}
// View for Update record
public ActionResult update_data(int id)
{
return View();
}
//Add record
public JsonResult Add_record(register rs)
{
string res = string.Empty;
try
{
dblayer.Add_record(rs);
res = "Inserted";
}
catch (Exception)
{
res = "failed";
}
return Json(res, JsonRequestBehavior.AllowGet);
}
// Display all records
public JsonResult Get_data()
{
DataSet ds = dblayer.get_record();
List<register> listrs = new List<register>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
listrs.Add(new register
{
Sr_no = Convert.ToInt32(dr["Sr_no"]),
Email = dr["Email"].ToString(),
Password = dr["Password"].ToString(),
Name = dr["Name"].ToString(),
Address = dr["Address"].ToString(),
City = dr["City"].ToString()
});
}
return Json(listrs, JsonRequestBehavior.AllowGet);
}
// Display records by id
public JsonResult Get_databyid(int id)
{
DataSet ds = dblayer.get_recordbyid(id);
List<register> listrs = new List<register>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
listrs.Add(new register
{
Sr_no = Convert.ToInt32(dr["Sr_no"]),
Email = dr["Email"].ToString(),
Password = dr["Password"].ToString(),
Name = dr["Name"].ToString(),
Address = dr["Address"].ToString(),
City = dr["City"].ToString()
});
}
return Json(listrs, JsonRequestBehavior.AllowGet);
}
// Update records
public JsonResult update_record(register rs)
{
string res = string.Empty;
try
{
dblayer.update_record(rs);
res = "Updated";
}
catch (Exception)
{
res = "failed";
}
return Json(res, JsonRequestBehavior.AllowGet);
}
// Delete record
public JsonResult delete_record(int id)
{
string res = string.Empty;
try
{
dblayer.deletedata(id);
res = "data deleted";
}
catch (Exception)
{
res = "failed";
}
return Json(res, JsonRequestBehavior.AllowGet);
}
}
}
Step10: Add view for action in controller & design.
Right Click on Action Method > Add View > Select View Engine (Razor) > Add.
It has following code and designs,
Index.cshtml :
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link href="~/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<div ng-app="Homeapp" ng-controller="HomeController">
<div class="container">
<div class="row">
<div class="col-sm-2"></div>
<div class="col-sm-8">
<div class="form-group">
<label>Email</label>
<input type="text" ng-model="register.Email" class="form-control" />
</div>
<div class="form-group">
<label>Password</label>
<input type="password" ng-model="register.Password" class="form-control" />
</div>
<div class="form-group">
<label>Name</label>
<input type="text" ng-model="register.Name" class="form-control" />
</div>
<div class="form-group">
<label>Address</label>
<input type="text" ng-model="register.Address" class="form-control" />
</div>
<div class="form-group">
<label>City</label>
<input type="text" ng-model="register.City" class="form-control" />
</div>
<div class="form-group">
<input type="button" class="btn btn-primary" ng-click="savedata()" value="{{btntext}}" />
</div>
</div>
<div class="col-sm-2"></div>
</div>
</div>
</div>
<script src="~/Scripts/angular.min.js"></script>
<script src="~/Scripts/AngularController/HomeAngularJS.js"></script>
</body>
</html>
Show_data.cshtml :
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style>
.tableData {
border-left: solid 1px #D8C3C3;
border-top: solid 1px #D8C3C3;
}
.tableData tr {
}
.tableData td, .tableData th {
border-right: solid 1px #D8C3C3;
border-bottom: solid 1px #D8C3C3;
text-align: left;
padding: 5px;
}
.tableData td {
}
.tableData th {
background-color: #FAFAFA;
padding: 7px 5px;
border-bottom-color: #9C9C9C;
}
.odd {
background-color: #f3f3f3;
}
.even {
background-color: #ffffff;
}
</style>
</head>
<body ng-app="Homeapp">
<div ng-controller="HomeController">
<table class="tableData">
<tr>
<th>Sr no</th>
<th>Email</th>
<th>Password</th>
<th>Name</th>
<th>Address</th>
<th>City</th>
<th>Actions</th>
</tr>
<tr ng-repeat="e in record" ng-class-even="'even'" ng-class-odd="'odd'">
<td>{{e.Sr_no}}</td>
<td>{{e.Email}}</td>
<td>{{e.Password}}</td>
<td>{{e.Name}}</td>
<td>{{e.Address}}</td>
<td>{{e.City}}</td>
<td><a href="/Home/update_data?id={{e.Sr_no}}">Update</a></td>
<td><a ng-click="deleterecord(e.Sr_no)">Delete</a></td>
</tr>
</table>
</div>
<script src="~/Scripts/angular.min.js"></script>
<script src="~/Scripts/AngularController/HomeAngularJS.js"></script>
</body>
</html>
Update_data.cshtml
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link href="~/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<div ng-app="Homeapp" ng-controller="HomeController" data-ng-init="loadrecord(@Convert.ToInt32(Request.QueryString["id"]))">
<div class="container">
<div class="row">
<div class="col-sm-2"></div>
<div class="col-sm-8">
<div class="form-group">
<label>Email</label>
<input type="text" ng-model="register.Email" class="form-control" />
</div>
<div class="form-group">
<label>Password</label>
<input type="password" ng-model="register.Password" class="form-control" />
</div>
<div class="form-group">
<label>Name</label>
<input type="text" ng-model="register.Name" class="form-control" />
</div>
<div class="form-group">
<label>Address</label>
<input type="text" ng-model="register.Address" class="form-control" />
</div>
<div class="form-group">
<label>City</label>
<input type="text" ng-model="register.City" class="form-control" />
</div>
<div class="form-group">
<input type="button" class="btn btn-primary" ng-click="updatedata()" value="{{btntext}}" />
</div>
</div>
<div class="col-sm-2"></div>
</div>
</div>
</div>
<script src="~/Scripts/angular.min.js"></script>
<script src="~/Scripts/AngularController/HomeAngularJS.js"></script>
</body>
</html>
Step 11: Run Application.
We have done all steps, now it’s time to run the application
