MVC insert update delete and select records

CRUD (Create, Retrieve, Update and Delete) in MVC. When we start to learn new language, first we try to run an application with “Hello World” and then CRUD functionality. So in this article we will see how to select records from database (with WebGrid, pagination and sort functionality), update a record, add new record and finally delete a record in ASP.Net MVC.

Let’s create a simple table Products with very basic columns for now, here is the table structure:

CREATE TABLE [Products](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
    [Price] [decimal](18, 2) NOT NULL,
    [Qunatity] [int] NOT NULL,
    [ReorderLevel] [int] NOT NULL,
 CONSTRAINT [PK_Products_1] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Open Visual Studio and create a new project, from project type list select ‘ASP.NET MVC 4 Web Application’ and give name SampleCRUD

Project Template > Select a template > Internet Application

View engine: Razor

Don’t create a unit test project; it is out of scope of this article.

If it is your first MVC project then check the structure of application, Content (contains theme and css files), Controllers (contains our controller classes), Models (Models to use in view to pass values from page to controllers) and Views (page UI).

To keep this article simple, we will use LINQ To SQL Classes to fetch data from database, so create LINQ To SQL Class by right clicking on the solution > Add > New Item.., it will open a dialog to add new item so select LINQ To SQL Classes and give name NW.dbml

Open Sever Explorer from view menu and add your database, where you created your products table, say Northwind

Drag Products table to the NW.dbml surface

Our application structure is completed, now we will create our own controller.

If you will expand the controller folder it will have two default controllers account and home, leave them as it is and create a new controller ProductsController

Right click on controller folder and select Add > Controller, it will open a box to type the controller name with name Default1Controller where Default1 is selected, so change Default1 to Products (don’t select action checkbox, we will create our own).

It will create a class

namespace SampleCRUD.Controllers
{
  public class Default1Controller : Controller
  {
    public ActionResult Index()
    {
        return View();
    }
  }
}

In Index action add following code to fetch data from database, so our index action will be

public ActionResult Index()
{
    NWDataContext db = new NWDataContext();
    List<Product> products = db.Products
       .OrderByDescending(x=>x.ProductId)
       .ToList<Product>();
    return View(products);
}

Our action is ready to fetch data from database and send to view, so time to create the view.

1. Right click inside Index action and select Add View…
2. View name will automatically b Index (don’t change it)
3. Check “Create a strongly typed view”
4. In model class drop down type List
5. Check Use a layout or master page
6. Click on the brose button and select _layout.cshtml from shared view folder
7. Click Add

Index.cshtml will be created with following code

@model List<SampleCRUD.Product>
@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
 }
 <h2>Index</h2>

Let’s change page title and heading of the page to “Product List” and add a WebGrid control to show the product list similar to GridView, so finally our view will be

@model List<SampleCRUD.Product>
@{
      ViewBag.Title = "Product List";
      Layout = "~/Views/Shared/_Layout.cshtml";

    <style type="text/css">
       .grid{    width:100%; }
    </style>
}

<h2>Product List</h2>
<div style="padding:7px 0;">
    <input type="button" value="Add New Product" onclick="OpenCreatePopup()" />
</div>
<div style="width:100%;">
@{
    WebGrid grid = new WebGrid(Model);
     @grid.GetHtml(
         tableStyle: "grid",
         fillEmptyRows: false,
         headerStyle: "gvHeading",
         alternatingRowStyle: "gvAlternateRow",
         rowStyle: "gvRow",
         footerStyle: "gvFooter",

         mode: WebGridPagerModes.All,
         firstText: "<< First",
         previousText: "< Prev",
         nextText: "Next >",
         lastText: "Last >>",
         columns: new[] {
         grid.Column("ProductId",header: "ID"),
         grid.Column("ProductName",header: "Product"),
         grid.Column("Price"),
         grid.Column("Qunatity"),     
         grid.Column("ReorderLevel", header: "R.O.L."),
         grid.Column("ContactusId", header: "Action", canSort:false,

         format: @<text>
          @Html.Raw("<img src='/img/edit.png' title='Edit' 
                     onclick='EditProduct("+ item.ProductId  ")'  />")
          @Html.Raw("<img src='/img/delete.png' title='Delete' 
                     onclick='DeleteProduct("+ item.ProductId +")'  />")
           </text>
        )       
     })    
}
</div>
 <div id="DivToAppendPartialVoew"></div>


<script type="text/javascript">
    function EditProduct(pid) {
        var ph = $("#DivToAppendPartialVoew");
        ph.load("/Products/edit?productid=" + pid , function () {
            ph.dialog({
                modal: true,
                width: 500,
                height: 438,
                title: "Edit Product",
                resizable: false
            });
        });
    }

    function DeleteProduct(pid) {
        if (confirm("Do you want to delete product: " + pid)) {
            var data = { 'ProductId': pid }
            $.post('/Products/Delete', data,
            function (data) {
                if (data == true)
                    location = location.href;
                else
                    alert("Could not delete");
            });
        }
    }

    function OpenCreatePopup() {
        var div = $("#DivToAppendPartialVoew");
        div.load("/Products/Create", function () {
            div.dialog({
                modal: true,
                width: 500,
                height: 438,
                title: "Add New Product",
                resizable: false
            });
        });
    }

</script>

We will discuss these JavaScript functions latter, these are to open the partial view as a model popup for Create and Update the record as well as to delete a record with confirmation box. Note: we used a div with Id DivToAppendPartialVoew to append the partial view and open as a modal popup.

Now run your application and in ULR type http://localhost:xxxx/products

Try paging and sorting functionality and everything will work as it should be while we have not written anything for it, isn’t a good feature of WebGrid.

Now we will see how we can open a partial view as a model popup to create new product, notice a button on top with text Add New Product and onclick function OpenCreatePopup

Create a model to use by right clicking on Models folder and define the model like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace SampleCRUD.Models
{
    public class ProductModels
    {        
        public int ProductId;

        [Required(ErrorMessage = "Please provide product name!")]
        [StringLength(100, ErrorMessage = "Product name is too long!")]
        [Display(Name = "Product Name")]
        public string ProductName;

        [Required(ErrorMessage = "Please provide product price!")]
        [Range(1, 10000, ErrorMessage = "Product price must be greater than 0!")]
        public Decimal Price;

        [Required(ErrorMessage = "Please provide product quantity!")]
        [Range(1, 1000, ErrorMessage = "Product quantity must be greater than 0!")]
        public Int32 Qunatity;

        [Required(ErrorMessage = "Please provide reorder level!")]
        [Range(1, 100, ErrorMessage = "Product reorder level must be between 1 to 100!")]
        [Display(Name = "Reorder Level")]
        public Int32 ReorderLevel;
    }
}

Let’s create the controller for Create for httpGet as well as for HttpPost in our PrductsController

[HttpGet]
public PartialViewResult Create()
{
   return PartialView(new SampleCRUD.Models.ProductModels());
}

[HttpPost]
public JsonResult Create(SampleCRUD.Product product)
{
   NWDataContext db = new NWDataContext();
   db.Products.InsertOnSubmit(product);
   db.SubmitChanges();
   return Json(product, JsonRequestBehavior.AllowGet);           
}

Right click on Create action method to add a view Select a strongly typed view Type the model name SampleCRUD.Models.ProductModels which we created earlier Select Create from Scaffold tamplate Check Create as a partial view and click Add button, It will create following code

@model SampleCRUD.Models.ProductModels

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>Product</legend>

        <div class="editor-label">
            @Html.LabelFor(model => model.ProductName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.ProductName)
            @Html.ValidationMessageFor(model => model.ProductName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Price)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Price)
            @Html.ValidationMessageFor(model => model.Price)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Qunatity)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Qunatity)
            @Html.ValidationMessageFor(model => model.Qunatity)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.ReorderLevel)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.ReorderLevel)
            @Html.ValidationMessageFor(model => model.ReorderLevel)
        </div>

        <p>      
            <input type="button" value="Create" onclick="SaveProduct()" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Close","Index")
</div>

<script type="text/javascript">
    function SaveProduct() {
        var pname = $("#ProductName").val();
        var pprice = $("#Price").val();
        var pqty = $("#Qunatity").val();
        var prol = $("#ReorderLevel").val();

        var product = { "ProductName": pname, "Price": pprice, 
                            "Qunatity": pqty, "ReorderLevel": prol };

        $.post('/products/create', product,
        function (data) { if (data == 0) { location = location.href; } }, 'json');

    }
</script>

If you will notice we changed the create button type to button from submit to button and added onclick event and javascript function at the end to save the record into database, here is the output:

Input your data and save it.

Similarly we can create the edit partial view and open as a modal popup so let’s create action methods for Edit

[HttpGet]
public PartialViewResult Edit(Int32 productId)
{
    NWDataContext db = new NWDataContext();
    Product product = db.Products.Where(x => x.ProductId == productId).FirstOrDefault();
    ProductModels prod = new ProductModels();
    prod.ProductId = product.ProductId;
    prod.ProductName = product.ProductName;
    prod.Price = product.Price;
    prod.Qunatity = product.Qunatity;
    prod.ReorderLevel = product.ReorderLevel;

    return PartialView(prod);
}

[HttpPost]
public JsonResult Edit(SampleCRUD.Product product)
{
    NWDataContext db = new NWDataContext();
    Product tblProd = db.Products.Where(x => x.ProductId == product.ProductId)
                               .FirstOrDefault();

    tblProd.ProductName = product.ProductName;
    tblProd.Price = product.Price;
    tblProd.Qunatity = product.Qunatity;
    tblProd.ReorderLevel = product.ReorderLevel;
    db.SubmitChanges();

    return Json(tblProd, JsonRequestBehavior.AllowGet);
}

Now create the view by right clicking on action method here is complete code

@model SampleCRUD.Models.ProductModels
@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>Product</legend>

        @Html.HiddenFor(model => model.ProductId)

        <div class="editor-label">
            @Html.LabelFor(model => model.ProductName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.ProductName)
            @Html.ValidationMessageFor(model => model.ProductName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Price)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Price)
            @Html.ValidationMessageFor(model => model.Price)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Qunatity)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Qunatity)
            @Html.ValidationMessageFor(model => model.Qunatity)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.ReorderLevel)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.ReorderLevel)
            @Html.ValidationMessageFor(model => model.ReorderLevel)
        </div>

        <p>
            <input type="button" value="Update" onclick="UpdateProduct()" />
        </p>
    </fieldset>
}
<div>
    @Html.ActionLink("Close", "Index")
</div>


<script type="text/javascript">
    function UpdateProduct() {
        var pId = $("#ProductId").val();
        var pname = $("#ProductName").val();
        var pprice = $("#Price").val();
        var pqty = $("#Qunatity").val();
        var prol = $("#ReorderLevel").val();

        var product = {"ProductId": pId, "ProductName": pname,
                     "Price": pprice, "Qunatity": pqty, "ReorderLevel": prol };

        $.post('/products/Edit', product,
        function (data) { if (data == 0) { location = location.href; } }, 'json');

    }
</script>

Once you will click on any edit button in any row it will open a popup like this

So far we complete for select, add and edit so let’s complete by adding delete button functionality, if you can notice we already added JavaScript in Index view

function DeleteProduct(pid) {
    if (confirm("Do you want to delete product: " + pid)) {
        var data = { 'ProductId': pid }
        $.post('/Products/Delete', data,
        function (data) {
            if (data == true)
                location = location.href;
            else
                alert("Could not delete");
        });
    }
}

We added confirmation script so if user will confirm to delete and item it will delete record from database and refresh page. And here is the action method

public JsonResult Delete(Int32 productId)
{
    NWDataContext db = new NWDataContext();
    Product product = db.Products.Where(x => x.ProductId == productId).FirstOrDefault();
    db.Products.DeleteOnSubmit(product);
    db.SubmitChanges();
    return Json(true, JsonRequestBehavior.AllowGet);
}

Now we completed our article.

DOWNLOAD Source Code

If you want to use bootstrap then you can also check this:

Search, sort, paging, insert, update and delete with ASP.Net MVC and bootstrap modal popup

Other blogs you may like

mvc search page example with code

MVC Searh page with pagination: It’s very easy to create a search page in asp.net but when I try to create the same in MVC I faced many problems, how to create model, how to keep searched values in search controls, pagination, I found myself nowhere, so start searching for some good examples but... By Ali Adravi   On 25 Aug 2013  Viewed: 40,241

How to create a single thanks page for entire controller in MVC

Sometimes we need a thanks page say we have user registration, change password, activate account functionality in our application then we need a thanks page after registering with our site, to say thanks for registering with us or showing confirmation that your password is successfully changed or... By Hamden   On 30 Jun 2013  Viewed: 3,771

MVC jquery autocomplete with value and text field

In MVC, autocomplete with jquery is the best way to pull data from database and render according to our requirements, so in this article we will use jquery to show the auto complete text box without any ajax controls. We need a method to get the data from database, a controller method to handle the... By Ali Adravi   On 29 Jun 2013  Viewed: 7,022

Upload files with model data in MVC

Upload multiple files with model data in MVC is really very easy, when I started to test by uploading some files, I though it would be more complicated but it is really not. In my previous post [ASP.Net MVC file upload][1], I promised to post soon about how to upload multiple files. When I was... By Ali Adravi   On 04 Jun 2013  Viewed: 25,571

ASP.Net MVC file upload

How to upload file or image in asp.net mvc is not difficult but is totally different compare to asp.net FileUpload control. If we are asp.net developer and we are new to mvc then we immediately noticed we don’t have server side controls so the FileUpload control as well and our problem start here.... By Ali Adravi   On 25 May 2013  Viewed: 8,088