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

Search, sort, paging, insert, update and delete with ASP.Net MVC and bootstrap modal popup and WebGrid to show records with search, sort and paging feature and in next article we will complete our article by adding insert, update and delete feature with bootstrap modal popup. We are going to use Entity Framework 6 code first model with a very simple table phone. Adding client side sorting and paging feature is tricky for which we don’t need a single line of code but for it we need to set the WebGrid in correct order, which we will do in this article.

Points which we are going to cover in this article:

1. Entity Framework 6 Code First to a new database
2. Search and bind record to WebGrid with paginated data
3. Client side paging and sorting by grid column
4. Insert, update and delete with bootstrap Modal Popup
5. Common method to open and save Modal Popup form data

Here is the final output screen which we are going to achieve

Packages we will need for this project, open package manager console and run one by one

• Install-Package EntityFramework
• Install-Package System.Linq.Dynamic.Library (for dynamic sort order)
• Install-Package bootstrap
• Install-Package jQuery (to update jQuery to latest version)

I don’t think I need to explain any of the above packages except the second one which I added to use the string in our LINQ queries to sort records, which we will see in more detail latter in this article.

As we know, for code first entity framework model we need to first define the model so let’s create a very simple model say phone with only four properties. Create a class “Phone” in Models folder on the root and here is the complete class with data annotations

public class Phone
{
    [Display(Name = "ID")]
    [Key]
    public int PhoneId { get; set; }

    [Required]
    [Display(Name = "Model Name")]
    public string Model { get; set; }

    [Required]
    [Display(Name = "Company Name")]
    public string Company { get; set; }

    [Required]
    [Display(Name = "Price")]
    [DataType(DataType.Currency)]
    public decimal Price { get; set; }
}

How can we create and access the database by just creating the model, so create a new folder on the root, name it DAL. Add a class to this DAL folder and name it to MobileDBContext and another to initialize the database with name MobileDBInitializer, here is the code for these two classes

public class MobileDBContext : DbContext
{        
    public MobileDBContext() : base("DefaultConnection")
    {
        Database.SetInitializer<MobileDBContext>
            (new DropCreateDatabaseIfModelChanges<MobileDBContext>());
    }

    public DbSet<Phone> Phones { get; set; }
}


public class MobileDBInitializer : System.Data.Entity.DropCreateDatabaseIfModelChanges<MobileDBContext>
{
    protected override void Seed(MobileDBContext context)
    {
        var phones = new List<Phone>
        {
        new Phone{Model="Samsung Galaxy Note 1", Company="Samsung",Price= 339},
        new Phone{Model="Samsung Galaxy Note 2", Company="Samsung",Price= 399},
        new Phone{Model="Samsung Galaxy S III", Company="Samsung",Price= 217},
        new Phone{Model="Samsung Galaxy S IV", Company="Samsung",Price= 234},
        new Phone{Model="iPhone 5", Company="Apple",Price= 456}
        };

        phones.ForEach(p => context.Phones.Add(p));
        context.SaveChanges();
    }
}

First class MobileDBContext will be used to access the database which is derived from DbContext and its constructor is derived from base with a string DefaultConnection which is the connection string so we need to define a connection string in our config file, add the following lines in configuration section to create a local database with the name Mobiles, if you want to create the database in your local SQL Server then you can change the Data Source to you database otherwise it will create a database to our application as in our example.

<connectionStrings>
    <add name="DefaultConnection" 
          connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=Mobiles;
              Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\Mobiles.mdf"             
              providerName="System.Data.SqlClient" />
</connectionStrings>

We need more configuration settings to call the database creation on first access of the database and call to our second class to add our dummy records to the table. After running “Install-Package EntityFramework”, it add a new node to the configuration file like … , we need to add following lines to this section

 <contexts>
   <context type="MvcBootstrapCrud.DAL.MobileDBContext, MvcBootstrapCrud">
     <databaseInitializer type="MvcBootstrapCrud.DAL.MobileDBInitializer, MvcBootstrapCrud" />
   </context>
 </contexts>

The complete node will looks like this

<configuration> 
    …………………………..
    …………………………..
  <entityFramework>
    <contexts>
      <context type="MvcBootstrapCrud.DAL.MobileDBContext, MvcBootstrapCrud">
        <databaseInitializer type="MvcBootstrapCrud.DAL.MobileDBInitializer, MvcBootstrapCrud" />
      </context>
    </contexts>
    <defaultConnectionFactory 
      type="System.Data.Entity.Infrastructure.SqlConnectionFactory,  EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" 
           type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration> 

Now we completed our code first entity framework database creation. Before writing the code for our controller, let’s create a new model to pass data from controller to view with table rows as well as paging information. Create new class into Models folder with name “PagedList”, here is the complete class

public class PagedList<T>
{
    public List<T> Content { get; set; }
    public Int32 CurrentPage { get; set; }
    public Int32 PageSize { get; set; }
    public int TotalRecords { get; set; }

    public int TotalPages
    {
        get { return (int)Math.Ceiling((decimal)TotalRecords / PageSize); }
    }
}

Time to create our controller, right click on the controllers folder and add a new controller “PhoneController”. I am not going to define the detail that how to create controller and what option you need to check or uncheck, I consider you already know it. Add a new action method “Index” with parameters filert, page, page size, sort column and sortdirection, here is the complete action method

public class PhoneController : Controller
{
    private MobileDBContext db = new MobileDBContext();

    // GET: /Phone/
    public ActionResult Index(string filter = null, int page = 1, 
         int pageSize = 5, string sort = "PhoneId", string sortdir = "DESC")
    {
        var records = new PagedList<Phone>();
        ViewBag.filter = filter;
        records.Content = db.Phones
                    .Where(x => filter == null ||
                            (x.Model.Contains(filter))
                               || x.Company.Contains(filter)
                          )
                    .OrderBy(sort + " " + sortdir)
                    .Skip((page - 1) * pageSize)
                    .Take(pageSize)
                    .ToList();

        // Count
        records.TotalRecords = db.Phones
                     .Where(x => filter == null ||
                           (x.Model.Contains(filter)) || x.Company.Contains(filter)).Count();

        records.CurrentPage = page;
        records.PageSize = pageSize;

        return View(records);
    }
}

We need to add using System.Linq.Dynamic; for dynamic order, as I mentioned earlier we will use dynamic sort order by using string, note .OrderBy(sort + " " + sortdir) which is two string parameter which possible with normal LINQ query that's why we installed the package System.Linq.Dynamic.Library.

First part of the code gets data from table for the current page only on the basis of page number, page size, sort column and sort direction, I used default sort column to PhoneId.

Now we need to add the HTML code with WebGrid to show the result, so let’s add it, right click on the action method and add a new view and change the html with following html

@model MvcBootstrapCrud.Models.PagedList<MvcBootstrapCrud.Models.Phone>
@{
    ViewBag.Title = "Phone List";
}

<h1>Phone List</h1>
<div class="well">
   @using (Html.BeginForm("index", null, FormMethod.Get))
    {
        <div class="row">
            <div class="col-sm-8">
                <div class="input-group">

                    <input type="text"
                        name="filter"
                        value="@ViewBag.filter"
                        class="form-control"
                        style="display: inline"
                        placeholder="Search by model and company" />
                    <span class="input-group-btn">
                        <button class="btn btn-default" type="submit">Go</button>
                    </span>

                </div>
            </div>
            <div class="pull-right col-lg-1">   
                <a class="btn btn-success" data-modal="" href="/Phone/Create" id="btnCreate">
                     <span class="glyphicon glyphicon-plus"></span>      
                </a>
            </div>
        </div>

        <div style="margin-top:17px;">
            @{
            var grid = new WebGrid(
                        canPage: true,
                        rowsPerPage: Model.PageSize,
                        canSort: true,
                        ajaxUpdateContainerId: "grid");

            grid.Bind(Model.Content, rowCount: Model.TotalRecords, autoSortAndPage: false);
            grid.Pager(WebGridPagerModes.All);

            @grid.GetHtml(htmlAttributes: new { id = "grid" },   // id for ajaxUpdateContainerId parameter
            fillEmptyRows: false,
            tableStyle: "table table-bordered table-hover",
            mode: WebGridPagerModes.All,
            columns: grid.Columns(
              grid.Column("PhoneId", "ID"),
              grid.Column("Model", "Model", style: "col-lg-4"),
              grid.Column("Company", "Company", style: "col-lg-3"),
              grid.Column("Price", header: "Price", format: @<text>@String.Format("{0:C}", item.Price) </text>),
              grid.Column(header: "Action", canSort: false, style: "action",
                format: @<text>
                   @Html.Raw("<a data-modal='' href='/phone/details/" + item.PhoneId + "' id='" + item.PhoneId + "' title='Detail'> <span class='glyphicon glyphicon-search'> </span> </a>")
                   @Html.Raw("<a data-modal='' href='/phone/edit/" + item.PhoneId + "' id='" + item.PhoneId + "' title='Edit'> <span class='glyphicon glyphicon-edit'> </span> </a>")
                   @Html.Raw("<a data-modal='' href='/phone/delete/" + item.PhoneId + "' id='" + item.PhoneId + "' title='Delete'> <span class='glyphicon glyphicon-trash'> </span> </a>")
                </text>)

            ));
            }


        </div>
    }
</div>

Notice we added all the buttons for add, detail, edit and delete which we will complete in part 2.
I left all the css, script and bundling code from main article because that was not important for the article, so let’s complete those missing items. Add bootstrap css file to the css bundle in bundleconfig.cs file in App_Start folder

 bundles.Add(new StyleBundle("~/Content/css").Include(
         "~/Content/bootstrap.css",
         "~/Content/site.css"));

_layout.cshtml file HTML

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <title>@ViewBag.Title - My ASP.NET MVC Application</title>
    <link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />
    <meta name="viewport" content="width=device-width" />
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
</head>
<body>
    <!-- Header section removed for clearity -->

    @Scripts.Render("~/bundles/jquery", "~/bundles/jqueryval", "~/Scripts/bootstrap.js")

    <div id="body" class="container">
        @RenderSection("featured", required: false)
        <section class="content-wrapper main-content clear-fix">
            @RenderBody()
        </section>
    </div>
    <footer>
        <div class="content-wrapper container">
            <div class="float-left">
                <p>&copy; @DateTime.Now.Year - Advancesharp.com :: MVC search, sort, 
                    paging, insert, update and delete with Bootstrap Modal Popup </p>
            </div>
        </div>
    </footer>

    @RenderSection("scripts", required: false)
</body>
</html>

I'll upload complete code in part-2. See Search, sort, paging, insert.... PART-2

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,117

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... By Ali Adravi   On 17 Aug 2013  Viewed: 105,948

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,738

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: 6,997

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,493