In this article I will explain with an example, how to perform Select Insert Edit Update and Delete (CRUD) using Single Stored Procedure in ASP.Net MVC Razor.
Single Stored Procedure will be called using Entity Framework and a parameter named Action will be used to send instructions about the operation to be performed in SQL Server database. Single Stored Procedure CRUD operation will be performed by calling the Controller Action methods using jQuery AJAX and JSON in ASP.Net MVC Razor.
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.
 
 
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
Select Insert Edit Update and Delete (CRUD) using Single Stored Procedure in ASP.Net MVC
 
I have already inserted few records in the table.
Select Insert Edit Update and Delete (CRUD) using Single 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.
Select Insert Edit Update and Delete (CRUD) using Single 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.
Select Insert Edit Update and Delete (CRUD) using Single Stored Procedure in ASP.Net MVC
 
Then the Stored Procedure to be added needs to be selected and the Finish Button must be clicked.
Select Insert Edit Update and Delete (CRUD) using Single 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.
Select Insert Edit Update and Delete (CRUD) using Single 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.
Select Insert Edit Update and Delete (CRUD) using Single Stored Procedure in ASP.Net MVC
 
 
Controller
The Controller consists of four Action methods.
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, a dummy (empty) record is added to the Generic List collection at first position (Index).
Note: The empty record is inserted so that it can be used to add new row to the WebGrid by the Client Side jQuery functions.
 
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 to the View.
 
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 HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        using (CustomersEntities entities = new CustomersEntities())
        {
            List<Customer> customers = entities.Customers_PerformCRUD("SELECT", null, null, null).ToList();
 
            //Add a Dummy Row.
            customers.Insert(0, new Customer());
            return View(customers.ToList());
        }
    }
 
    [HttpPost]
    public JsonResult InsertCustomer(Customer customer)
    {
        using (CustomersEntities entities = new CustomersEntities())
        {
            Customer _customer = entities.Customers_PerformCRUD("INSERT", null, customer.Name, customer.Country).FirstOrDefault();
            return Json(_customer);
        }
    }
 
    [HttpPost]
    public ActionResult UpdateCustomer(Customer customer)
    {
        using (CustomersEntities entities = new CustomersEntities())
        {
            entities.Customers_PerformCRUD("UPDATE", customer.CustomerId, customer.Name, customer.Country);
        }
 
        return new EmptyResult();
    }
 
    [HttpPost]
    public ActionResult DeleteCustomer(int customerId)
    {
        using (CustomersEntities entities = new CustomersEntities())
        {
            entities.Customers_PerformCRUD("DELETE", customerId, null, null);
        }
 
        return new EmptyResult();
    }
}
 
 
View
Inside the View, in the very first line the Customer Entity is declared as IEnumerable which specifies that the Model will be available as a Collection.
Display
The WebGrid is initialized with the Model i.e. IEnumerable collection of Customer Entity class objects as source.
Note: For more information on usage of WebGrid, please refer WebGrid Step By Step Tutorial with example in ASP.Net MVC.
 
Below the WebGrid, two TextBoxes (Name and Country) and an Add Button has been placed. The Add Button has been assigned a jQuery click event handler.
 
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<Customer>
 
@{
    Layout = null;
    WebGrid webGrid = new WebGrid(source: Model, canPage: true, canSort: false);
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width"/>
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }
 
        .Grid {
            border: 1px solid #ccc;
            border-collapse: collapse;
        }
 
        .Grid th {
            background-color: #F7F7F7;
            font-weight: bold;
        }
 
        .Grid th, .Grid td {
            padding: 5px;
            width: 150px;
            border: 1px solid #ccc;
        }
 
        .Grid, .Grid table td {
            border: 0px solid #ccc;
        }
 
        .Grid th a, .Grid th a:visited {
            color: #333;
        }
    </style>
</head>
<body>
    @webGrid.GetHtml(
        htmlAttributes: new { @id = "WebGrid", @class = "Grid" },
        columns: webGrid.Columns(
                 webGrid.Column(header: "Customer Id", format: @<span class="label">@item.CustomerId</span>, style: "CustomerId" ),
                 webGrid.Column(header: "Name", format: @<span><span class="label">@item.Name</span>
                            <input class="text" type="text" value="@item.Name" style="display:none"/></span>, style: "Name"),
                 webGrid.Column(header: "Country", format: @<span><span class="label">@item.Country</span>
                                <input class="text" type="text" value="@item.Country" style="display:none"/></span>, style: "Country"),
                 webGrid.Column(format:@<span class="link">
                        <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>
                    </span> )))
    <br/>
    <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: 100px">
                <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 () {
            var row = $("#WebGrid TBODY tr:eq(0)");
            if ($("#WebGrid TBODY tr").length > 1) {
                row.remove();
            } else {
                row.find(".label").html("");
                row.find(".text").val("");
                row.find(".link").hide();
            }
        });
        function AppendRow(row, customerId, name, country) {
            //Bind CustomerId.
            $(".CustomerId", row).find(".label").html(customerId);
 
            //Bind Name.
            $(".Name", row).find(".label").html(name);
            $(".Name", row).find(".text").val(name);
 
            //Bind Country.
            $(".Country", row).find(".label").html(country);
            $(".Country", row).find(".text").val(country);
            row.find(".link").show();
            $("#WebGrid TBODY").append(row);
        };
 
        //Add event handler.
        $("body").on("click", "#btnAdd", function () {
            var txtName = $("#txtName");
            var txtCountry = $("#txtCountry");
            $.ajax({
                type: "POST",
                url: "/Home/InsertCustomer",
                data: '{name: "' + txtName.val() + '", country: "' + txtCountry.val() + '" }',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {
                    var row = $("#WebGrid TBODY tr:last-child").clone();
                    if (row.find(".label").is(":empty")) {
                        $("#WebGrid TBODY tr:last-child").remove();
                    }
                    AppendRow(row, r.CustomerId, r.Name, r.Country);
                    txtName.val("");
                    txtCountry.val("");
                }
            });
        });
 
        //Edit event handler.
        $("body").on("click", "#WebGrid TBODY .Edit", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find(".text").length > 0) {
                    $(this).find(".text").show();
                    $(this).find(".label").hide();
                }
            });
            row.find(".Update").show();
            row.find(".Cancel").show();
            row.find(".Delete").hide();
            $(this).hide();
        });
 
        //Update event handler.
        $("body").on("click", "#WebGrid TBODY .Update", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find(".text").length > 0) {
                    var span = $(this).find(".label");
                    var input = $(this).find(".text");
                    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(".label").html();
            customer.Name = row.find(".Name").find(".label").html();
            customer.Country = row.find(".Country").find(".label").html();
            $.ajax({
                type: "POST",
                url: "/Home/UpdateCustomer",
                data: '{customer:' + JSON.stringify(customer) + '}',
                contentType: "application/json; charset=utf-8",
                dataType: "json"
            });
        });
 
        //Cancel event handler.
        $("body").on("click", "#WebGrid TBODY .Cancel", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find(".text").length > 0) {
                    var span = $(this).find(".label");
                    var input = $(this).find(".text");
                    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", "#WebGrid TBODY .Delete", function () {
            if (confirm("Do you want to delete this row?")) {
                var row = $(this).closest("tr");
                var customerId = row.find(".label").html();
                $.ajax({
                    type: "POST",
                    url: "/Home/DeleteCustomer",
                    data: '{customerId: ' + customerId + '}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        if ($("#WebGrid TBODY tr").length == 1) {
                            row.find(".label").html("");
                            row.find(".text").val("");
                            row.find(".link").hide();
                        } else {
                            row.remove();
                        }
                    }
                });
            }
        });
    </script>
</body>
</html>
 
 
Screenshot
Select Insert Edit Update and Delete (CRUD) using Single Stored Procedure in ASP.Net MVC
 
 
Downloads
Download Code