In this article I will explain with an example, how to perform CRUD operation (Create, Read, Update and Delete) using Web API and Stored Procedure in ASP.Net MVC Razor using Entity Framework and jQuery AJAX.
The Stored Procedure will be called using Entity Framework to perform CRUD operations such as Select, Insert, Edit, Update and Delete operations.
Note: For beginners in ASP.Net MVC and Web API, please refer my article: What is Web API, why to use it and how to use it in ASP.Net MVC for details about using and configuring Web API.
 
 
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
I have already inserted few records in the table.
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
Stored Procedure for Select, Insert, Update and Delete
The following stored procedure will be used to perform CRUD operation i.e. Select, Insert, Update and Delete operations on the SQL Server database table.
This Stored Procedure will be called using Entity Framework.
CREATE PROCEDURE [dbo].[Customers_CRUD]
      @Action VARCHAR(10)
      ,@CustomerId INT = NULL
      ,@Name VARCHAR(100) = NULL
      ,@Country VARCHAR(100) = NULL
AS
BEGIN
      SET NOCOUNT ON;
 
      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT [CustomerId], [Name], [Country]
            FROM Customers
      END
 
      --INSERT
    IF @Action = 'INSERT'
      BEGIN
            INSERT INTO Customers(Name, Country)
            VALUES (@Name, @Country)
 
            SET @CustomerId = SCOPE_IDENTITY()
            SELECT @CustomerId [CustomerId], @Name [Name], @Country [Country]
      END
 
      --UPDATE
    IF @Action = 'UPDATE'
      BEGIN
            UPDATE Customers
            SET Name = @Name, Country = @Country
            WHERE CustomerId = @CustomerId
 
            SELECT [CustomerId], [Name], [Country]
            FROM Customers
            WHERE CustomerId = @CustomerId
      END
 
      --DELETE
    IF @Action = 'DELETE'
      BEGIN
            DELETE FROM Customers
            WHERE CustomerId = @CustomerId
 
            SELECT @CustomerId [CustomerId], '' [Name], '' [Country]
      END
END
 
 
Entity Framework Model
Once the Entity Framework is configured and connected to the database table, the Model will look as shown below.
Note: For beginners in ASP.Net MVC and Entity Framework, please refer my article ASP.Net MVC: Simple Entity Framework Tutorial with example. It covers all the information needed for connecting and configuring Entity Framework.
 
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
 
Importing the Stored Procedure
Next step is to import the Stored Procedure by Right Clicking and selecting Update Model from Database option as shown below.
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
Then the Stored Procedure to be added needs to be selected and the Finish Button must be clicked.
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
Note: The above two steps are not required if the Stored Procedure has been added while creating the Entity Data Model.
 
Finally, in order to call the Stored Procedure using Entity Framework the Stored Procedure needs to be imported as a Function. Thus, again Right Click and select Add and then click on Function Import option.
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
This will open the Add Function Import dialog window where the Function Name and the Stored Procedure to be imported as Function is selected.
Finally the Entity RadioButton is selected and the Customer Entity is chosen from the DropDown.
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
 
Controller
The Controller consists of the following Action method.
Action method for handling GET operation
Inside this Action method, the Stored Procedure is being called with the Action parameter value set as SELECT and all the other parameters set to NULL.
The returned records from the Customers table are sent to the View as a Generic List collection.
Before returning to the View, if no records are returned from the Database then a dummy (empty) record is added to the Generic List collection.
Note: The empty record is inserted so that it can be used to add new row by the Client Side jQuery functions.
 
public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        CustomersEntities entities = new CustomersEntities();
        List<Customer> customers = entities.Customers_PerformCRUD("SELECT", null, null, null).ToList();
        if (customers.Count == 0)
        {
            customers.Add(new Customer());
        }
 
        return View(customers);
    }
}
 
 
Web API Controller
The Web API Controller consists of the following three Action methods.
Action method for Inserting
Inside this Action method, the Stored Procedure is being called with the Action parameter value set as INSERT, the CustomerId parameter set to NULL while Name and Country values are set to their respective values received from the jQuery AJAX function.
The Stored Procedure returns the inserted Customer object along with the generated CustomerId
The Customer object is returned back to the calling jQuery AJAX function.
 
Action method for Updating
Inside this Action method, the Stored Procedure is being called with the Action parameter value set as UPDATE and the CustomerId, Name and Country values are set to their respective values received from the jQuery AJAX function.
 
Action method for Deleting
Inside this Action method, the Stored Procedure is being called with the Action parameter value set as DELETE and the CustomerId value set to the respective value received from jQuery AJAX function while the Name and Country values set to NULL.
public class AjaxAPIController : ApiController
{
    [Route("api/AjaxAPI/InsertCustomer")]
    [HttpPost]
    public Customer InsertCustomer(Customer _customer)
    {
        using (CustomersEntities entities = new CustomersEntities())
        {
            _customer = entities.Customers_PerformCRUD("INSERT", null, _customer.Name, _customer.Country).FirstOrDefault();
        }
 
        return _customer;
    }
 
    [Route("api/AjaxAPI/UpdateCustomer")]
    [HttpPost]
    public bool UpdateCustomer(Customer _customer)
    {
        using (CustomersEntities entities = new CustomersEntities())
        {
            entities.Customers_PerformCRUD("UPDATE", _customer.CustomerId, _customer.Name, _customer.Country);
        }
 
        return true;
    }
 
    [Route("api/AjaxAPI/DeleteCustomer")]
    [HttpPost]
    public void DeleteCustomer(Customer _customer)
    {
        using (CustomersEntities entities = new CustomersEntities())
        {
            entities.Customers_PerformCRUD("DELETE", _customer.CustomerId, null, null);
        }
    }
}
 
 
View
Inside the View, in the very first line the Entity Framework Customer Model class is declared as Model for the View.
Display
For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
 
Insert
When the Add Button is clicked the name and the country values are fetched from their respective TextBoxes and then passed to the InsertCustomer Action method using jQuery AJAX call.
Once the response is received, a new row is appended to the HTML table using the AppendRow function.
 
Edit
When the Edit Button is clicked, the reference of the HTML Table row is determined and the HTML SPAN elements are made hidden while the TextBoxes are made visible in the Name and Country columns of the HTML Table.
 
Update
When the Update Button is clicked, the reference of the HTML Table row is determined and the updated values are fetched from the respective TextBoxes of Name and Country columns while the CustomerId is determined from the HTML SPAN element of the CustomerId column.
The values of CustomerId, Name and Country are passed to the UpdateCustomer Action method using jQuery AJAX call.
Once the response is received, the HTML SPAN elements are made visible and the TextBoxes are made hidden for the Name and Country columns of the HTML Table row.
 
Cancel
When the Cancel Button is clicked, the reference of the HTML Table row is determined and the HTML SPAN elements are made visible while the TextBoxes are made hidden in the Name and Country columns of the HTML Table row.
 
Delete
When the Delete Button is clicked, the reference of the HTML Table row is determined and the value of the CustomerId is fetched and passed to the DeleteCustomer Action method using jQuery AJAX call.
Once the response is received, the respective row is removed from the HTML Table row.
@model IEnumerable<jQuery_WebAPI_StoredProc_CRUD_MVC.Customer>
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <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:150px"></th>
        </tr>
        @foreach (Customer customer in Model)
        {
            <tr>
                <td class="CustomerId">
                    <span>@customer.CustomerId</span>
                </td>
                <td class="Name">
                    <span>@customer.Name</span>
                    <input type="text" value="@customer.Name" style="display:none" />
                </td>
                <td class="Country">
                    <span>@customer.Country</span>
                    <input type="text" value="@customer.Country" style="display:none" />
                </td>
                <td>
                    <a class="Edit" href="javascript:;">Edit</a>
                    <a class="Update" href="javascript:;" style="display:none">Update</a>
                    <a class="Cancel" href="javascript:;" style="display:none">Cancel</a>
                    <a class="Delete" href="javascript:;">Delete</a>
                </td>
            </tr>
        }
    </table>
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td style="width: 150px">
                Name<br/>
                <input type="text" id="txtName" style="width:140px" />
            </td>
            <td style="width: 150px">
                Country:<br/>
                <input type="text" id="txtCountry" style="width:140px" />
            </td>
            <td style="width: 200px">
                <br/>
                <input type="button" id="btnAdd" value="Add" />
            </td>
        </tr>
    </table>
 
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript" src="https://ajax.cdnjs.com/ajax/libs/json2/20110223/json2.js"></script>
    <script type="text/javascript">
        $(function () {
            //Remove the dummy row if data present.
            if ($("#tblCustomers tr").length > 2) {
                $("#tblCustomers tr:eq(1)").remove();
            } else {
                var row = $("#tblCustomers tr:last-child");
                row.find(".Edit").hide();
                row.find(".Delete").hide();
                row.find("span").html('&nbsp;');
            }
        });
        function AppendRow(row, customerId, name, country) {
            //Bind CustomerId.
            $(".CustomerId", row).find("span").html(customerId);
 
            //Bind Name.
            $(".Name", row).find("span").html(name);
            $(".Name", row).find("input").val(name);
 
            //Bind Country.
            $(".Country", row).find("span").html(country);
            $(".Country", row).find("input").val(country);
 
            row.find(".Edit").show();
            row.find(".Delete").show();
 
            $("#tblCustomers").append(row);
        };
 
        //Add event handler.
        $("body").on("click", "#btnAdd", function () {
            var txtName = $("#txtName");
            var txtCountry = $("#txtCountry");
            var _customer = {};
            _customer.Name = txtName.val();
            _customer.Country = txtCountry.val();
            $.ajax({
                type: "POST",
                url: "/api/AjaxAPI/InsertCustomer",
                data: JSON.stringify(_customer),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {
                    var row = $("#tblCustomers tr:last-child");
                    if ($("#tblCustomers tr:last-child span").eq(0).html() != "&nbsp;") {
                        row = row.clone();
                    }
                    AppendRow(row, r.CustomerId, r.Name, r.Country);
                    txtName.val("");
                    txtCountry.val("");
                }
            });
        });
 
        //Edit event handler.
        $("body").on("click", "#tblCustomers .Edit", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find("input").length > 0) {
                    $(this).find("input").show();
                    $(this).find("span").hide();
                }
            });
            row.find(".Update").show();
            row.find(".Cancel").show();
            row.find(".Delete").hide();
            $(this).hide();
        });
 
        //Update event handler.
        $("body").on("click", "#tblCustomers .Update", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find("input").length > 0) {
                    var span = $(this).find("span");
                    var input = $(this).find("input");
                    span.html(input.val());
                    span.show();
                    input.hide();
                }
            });
            row.find(".Edit").show();
            row.find(".Delete").show();
            row.find(".Cancel").hide();
            $(this).hide();
            var _customer = {};
            _customer.CustomerId = row.find(".CustomerId").find("span").html();
            _customer.Name = row.find(".Name").find("span").html();
            _customer.Country = row.find(".Country").find("span").html();
            $.ajax({
                type: "POST",
                url: "/api/AjaxAPI/UpdateCustomer",
                data: JSON.stringify(_customer),
                contentType: "application/json; charset=utf-8",
                dataType: "json"
            });
        });
 
        //Cancel event handler.
        $("body").on("click", "#tblCustomers .Cancel", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find("input").length > 0) {
                    var span = $(this).find("span");
                    var input = $(this).find("input");
                    input.val(span.html());
                    span.show();
                    input.hide();
                }
            });
            row.find(".Edit").show();
            row.find(".Delete").show();
            row.find(".Update").hide();
            $(this).hide();
        });
 
        //Delete event handler.
        $("body").on("click", "#tblCustomers .Delete", function () {
            if (confirm("Do you want to delete this row?")) {
                var row = $(this).closest("tr");
                var _customer = {};
                _customer.CustomerId = row.find("span").html();
                $.ajax({
                    type: "POST",
                    url: "/api/AjaxAPI/DeleteCustomer",
                    data: JSON.stringify(_customer),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        if ($("#tblCustomers tr").length > 2) {
                            row.remove();
                        } else {
                            row.find(".Edit").hide();
                            row.find(".Delete").hide();
                            row.find("span").html('&nbsp;');
                        }
                    }
                });
            }
        });
    </script>
</body>
</html>
 
 
Screenshot
Web API CRUD operations with Stored Procedure in ASP.Net MVC
 
 
Downloads
Download Code