Select Insert Edit Update Delete (CRUD) with Stored Procedure using AngularJS in ASP.Net MVC

Last Reply one month ago By dharmendr

Posted one month ago

Sir,

I am working on MVC4. I have to perform CRUD operation using AngularJS and JSON and have to use StoredProcedures. I have got your one article as below:

MVC AngularJS CRUD: Select Insert Edit Update and Delete using AngularJS in ASP.Net MVC

In above article there is a nice demo for CRUD operation using AngularJS and JSON, but I would also need to use StoredProcedures.

Kindly provide a simple demo regarding this.

Posted one month ago

Hi Sumeet,

Check this example. Now please take its reference and correct your code.

Model

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

Namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public JsonResult GetCustomers()
    {
        List<Customer> customers = new List<Customer>();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Customers_Select"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    customers.Add(new Customer
                    {
                        CustomerId = Convert.ToInt32(sdr["CustomerId"]),
                        Name = sdr["CustomerId"].ToString(),
                        Country = sdr["CustomerId"].ToString()
                    });
                }
                con.Close();
            }
        }

        return Json(customers);
    }

    [HttpPost]
    public JsonResult InsertCustomer(Customer customer)
    {
        string name = customer.Name;
        string country = customer.Country;
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Customer_Insert"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Country", country);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        return Json(customer);
    }

    [HttpPost]
    public ActionResult UpdateCustomer(Customer customer)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Customer_Update"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Id", customer.CustomerId);
                cmd.Parameters.AddWithValue("@Name", customer.Name);
                cmd.Parameters.AddWithValue("@Country", customer.Country);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        return new EmptyResult();
    }

    [HttpPost]
    public ActionResult DeleteCustomer(int customerId)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Customer_Delete"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Id", customerId);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        return new EmptyResult();
    }
}

View

<div ng-app="MyApp" ng-controller="MyController">
    <table id="tblCustomers" class="table" cellpadding="0" cellspacing="0">
        <tr>
            <th style="width: 100px">
                Customer Id
            </th>
            <th style="width: 150px">
                Name
            </th>
            <th style="width: 150px">
                Country
            </th>
            <th style="width: 100px">
            </th>
        </tr>
        <tbody ng-repeat="m in Customers">
            <tr>
                <td>
                    <span>{{m.CustomerId}}</span>
                </td>
                <td>
                    <span ng-hide="m.EditMode">{{m.Name}}</span>
                    <input type="text" ng-model="m.Name" ng-show="m.EditMode" />
                </td>
                <td>
                    <span ng-hide="m.EditMode">{{m.Country}}</span>
                    <input type="text" ng-model="m.Country" ng-show="m.EditMode" />
                </td>
                <td>
                    <a class="Edit" href="javascript:;" ng-hide="m.EditMode" ng-click="Edit($index)">Edit</a>
                    <a class="Update" href="javascript:;" ng-show="m.EditMode" ng-click="Update($index)">Update</a> 
                    <a class="Cancel" href="javascript:;" ng-show="m.EditMode" ng-click="Cancel($index)">Cancel</a> 
                    <a href="javascript:;" ng-hide="m.EditMode" ng-click="Delete(m.CustomerId)">Delete</a>
                </td>
            </tr>
        </tbody>
    </table>
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td style="width: 150px">
                Name<br /><input type="text" ng-model="Name" style="width: 140px" />
            </td>
            <td style="width: 150px">
                Country:<br /><input type="text" ng-model="Country" style="width: 140px" />
            </td>
            <td style="width: 200px">
                <br /><input type="button" value="Add" ng-click="Add()" />
            </td>
        </tr>
    </table>
</div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.9/angular.min.js"></script>
<script type="text/javascript" src="http://ajax.cdnjs.com/ajax/libs/json2/20110223/json2.js"></script>
<script type="text/javascript">
    var app = angular.module('MyApp', [])
    app.controller('MyController', function ($scope, $http, $window) {
        //Getting records from database.
        var post = $http({
            method: "POST",
            url: "/Home/GetCustomers",
            dataType: 'json',
            headers: { "Content-Type": "application/json" }
        });
        post.success(function (data, status) {
            //The received response is saved in Customers array.
            $scope.Customers = data;
        });

        //Adding new record to database.
        $scope.Add = function () {
            if (typeof ($scope.Name) == "undefined" || typeof ($scope.Country) == "undefined") {
                return;
            }
            var post = $http({
                method: "POST",
                url: "/Home/InsertCustomer",
                data: "{name: '" + $scope.Name + "', country: '" + $scope.Country + "'}",
                dataType: 'json',
                headers: { "Content-Type": "application/json" }
            });
            post.success(function (data, status) {
                //The newly inserted record is inserted into the Customers array.
                $scope.Customers.push(data)
            });
            $scope.Name = "";
            $scope.Country = "";
        };

        //This variable is used to store the original values.
        $scope.EditItem = {};

        //Editing an existing record.
        $scope.Edit = function (index) {
            //Setting EditMode to TRUE makes the TextBoxes visible for the row.
            $scope.Customers[index].EditMode = true;

            //The original values are saved in the variable to handle Cancel case.
            $scope.EditItem.Name = $scope.Customers[index].Name;
            $scope.EditItem.Country = $scope.Customers[index].Country;
        };

        //Cancelling an Edit.
        $scope.Cancel = function (index) {
            // The original values are restored back into the Customers Array.
            $scope.Customers[index].Name = $scope.EditItem.Name;
            $scope.Customers[index].Country = $scope.EditItem.Country;

            //Setting EditMode to FALSE hides the TextBoxes for the row.
            $scope.Customers[index].EditMode = false;
            $scope.EditItem = {};
        };

        //Updating an existing record to database.
        $scope.Update = function (index) {
            var customer = $scope.Customers[index];
            var post = $http({
                method: "POST",
                url: "/Home/UpdateCustomer",
                data: '{customer:' + JSON.stringify(customer) + '}',
                dataType: 'json',
                headers: { "Content-Type": "application/json" }
            });
            post.success(function (data, status) {
                //Setting EditMode to FALSE hides the TextBoxes for the row.
                customer.EditMode = false;
            });
        };

        //Deleting an existing record from database.
        $scope.Delete = function (customerId) {
            if ($window.confirm("Do you want to delete this row?")) {
                var post = $http({
                    method: "POST",
                    url: "/Home/DeleteCustomer",
                    data: "{customerId: " + customerId + "}",
                    dataType: 'json',
                    headers: { "Content-Type": "application/json" }
                });
                post.success(function (data, status) {
                    //Remove the Deleted record from the Customers Array.
                    $scope.Customers = $scope.Customers.filter(function (customer) {
                        return customer.CustomerId !== customerId;
                    });
                });
            }
        };
    });
</script>

Screenshot


Posted one month ago

In the sample Stored procedure with ADO.Net is used. EntityFramework is not used.

If you want to use stored procedure with entity framework, then refer below article.

ASP.Net MVC: Call Stored Procedure using Entity Framework


Posted one month ago

ADO.Net is much faster than EntityFramework.

If perfromance is your criteria use ADO.NET.

EntityFramework's advantage is to save time and not to write lot of code.