CRUD operations using AngularJs in Asp.net MVC

Here I will explain Steps to performs CRUD operations using AngularJS and Stored Procedure in Asp.net MVC. I this example we perform INSERT, UPDATE and DELETE operations in Asp.net MVC using AngularJS.

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