Tuesday, March 8, 2011

Using Flexigrid for ASP.NET MVC


In this post is a working code how to show tabular data on grid via Flexigrid and ASP.NET MVC's controller


The code for Model:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace FlexigridUsingMvc2.Models
{
    public class Customer
    {
        public string CustomerID { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
    }




    public class EntContext : DbContext
    {
        public DbSet<Customer> Customers { get; set; }
    }
}

This is the code for Controller:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using FlexigridUsingMvc2.Models;

using FlexigridUsingMvc2.Extensions;

namespace FlexigridUsingMvc2.Controllers
{
  public class HomeController : Controller
  {

    [HttpPost]
    public JsonResult List(int page, int rp, string qtype, 
                           string letter_pressed, string query)
    {

      using (var db = new EntContext())
      {
        if (letter_pressed == "ALL" ) letter_pressed = "";

        var CustomerFiltered =
          db.Customers
          .Where(x =>                 
              (
                (
                  !string.IsNullOrEmpty(letter_pressed)
                  && x.CompanyName.StartsWith(letter_pressed)
                )
                ||
                string.IsNullOrEmpty(letter_pressed)
              )
              &&
              (
                (
                  !string.IsNullOrEmpty(query)
                  && 
                  (
                    (qtype == "CompanyName" && x.CompanyName.Contains(query))
                    ||
                    (qtype == "ContactName" && x.ContactName.Contains(query))
                    ||
                    (qtype == "ContactTitle" && x.ContactTitle.Contains(query))
                  )
                )
                ||
                string.IsNullOrEmpty(query)
              )
            );
        

        return Json(
          new
          {
            page = page,
            total = CustomerFiltered.Count(),
            rows =
              CustomerFiltered              
              .OrderBy(x => x.CompanyName)
              .LimitAndOffset(pageSize: rp, pageOffset: page)
              .ToList()
              .Select(x =>
                new
                {
                  id = x.CustomerID,
                  cell = new string[] { x.CompanyName, x.ContactName, x.ContactTitle }
                })              
          });
        
        
      }//using
    }//List

    public ActionResult Index()
    {
      return View();
    }


  }
}

Point of interest in Controller's code is line number 68. Those have to match the colModel of Flexigrid, names doesn't need to match, only their respective positions in flexigrid's colModels

The code for View:
<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">


    <link href="<%: Url.Content("~/Content/Site.css")%>" rel="stylesheet" type="text/css" />
    <script src="<%: Url.Content("~/Scripts/jquery-1.4.1.min.js")%>" type="text/javascript"></script>
    <script src="<%: Url.Content("/Scripts/flexigrid/flexigrid.pack.js")%>" type="text/javascript"></script>
    <link href="<%: Url.Content("~/Scripts/flexigrid/css/flexigrid.css")%>" rel="stylesheet"
        type="text/css" />
    

    <script type="text/javascript">
        $(document).ready(function () {
        
        
                


            
            $("#fff").flexigrid
            (
            {
                url: '/Home/List/',
                dataType: 'json',
                colModel: [                
                { display: 'Company', name: 'CompanyName', width: 200, sortable: false, align: 'left' },
                { display: 'Contact', name: 'ContactName', width: 280, sortable: false, align: 'left' },
                { display: 'Contact Title', name: 'ContactTitle', width: 220, sortable: false, align: 'left' }
                ],
                buttons: [
                { name: 'Add', bclass: 'add', onpress: add },
                { name: 'Edit', bclass: 'edit', onpress: edit },
                { name: 'Delete', bclass: 'delete', onpress: del },                                                
                { separator: true },
                { name: 'A', onpress: sortAlpha },
                { name: 'B', onpress: sortAlpha },
                { name: 'C', onpress: sortAlpha },
                { name: 'D', onpress: sortAlpha },
                { name: 'E', onpress: sortAlpha },
                { name: 'F', onpress: sortAlpha },
                { name: 'G', onpress: sortAlpha },
                { name: 'H', onpress: sortAlpha },
                { name: 'I', onpress: sortAlpha },
                { name: 'J', onpress: sortAlpha },
                { name: 'K', onpress: sortAlpha },
                { name: 'L', onpress: sortAlpha },
                { name: 'M', onpress: sortAlpha },
                { name: 'N', onpress: sortAlpha },
                { name: 'O', onpress: sortAlpha },
                { name: 'P', onpress: sortAlpha },
                { name: 'Q', onpress: sortAlpha },
                { name: 'R', onpress: sortAlpha },
                { name: 'S', onpress: sortAlpha },
                { name: 'T', onpress: sortAlpha },
                { name: 'U', onpress: sortAlpha },
                { name: 'V', onpress: sortAlpha },
                { name: 'W', onpress: sortAlpha },
                { name: 'X', onpress: sortAlpha },
                { name: 'Y', onpress: sortAlpha },
                { name: 'Z', onpress: sortAlpha },
                { name: 'ALL', onpress: sortAlpha }
                ],
                searchitems: [
                { display: 'Company', name: 'CompanyName', isdefault: true },
                { display: 'Contact', name: 'ContactName' },
                { display: 'Contact Title', name: 'ContactTitle' }
                ],
                singleSelect: true,
                sortname: "CompanyName",
                sortorder: "asc",
                usepager: true,
                title: 'Companies',
                useRp: true,
                rp: 10,
                showTableToggleBtn: false,
                width: 800,
                height: 255
            }
            );
            

            


        });
        function sortAlpha(com) {
            jQuery('#fff').flexOptions({ newp: 1, params: [{ name: 'letter_pressed', value: com }, { name: 'qtype', value: $('select[name=qtype]').val()}] });
            jQuery("#fff").flexReload();
        }



        function edit(com, grid) {

            // alert(com); // Edit
            
            if ( $('.trSelected',grid).length == 1 ) 
            {
                var items = $('.trSelected',grid);
                alert(com + ' ' + items[0].id.substr(3));
            }
        }


        function add(com, grid) {

            alert(com); 

            /*if ($('.trSelected', grid).length == 1) {
                var items = $('.trSelected', grid);
                alert(items[0].id.substr(3));
            }*/
        }


        function del(com, grid) {

            // alert(com); // Delete

            if ($('.trSelected', grid).length == 1) {
                var items = $('.trSelected', grid);
                alert(com + ' ' + items[0].id.substr(3));
            }
        }
        
    </script>


    <title>Index</title>



</head>
<body>
    <div>
        <table id="fff" style="display:none"></table>
    </div>
</body>




</html>


Sample code: http://code.google.com/p/using-flexigrid-on-aspnet-mvc/downloads/list

Just change the Web.config's connectionString's Data Source

7 comments:

  1. Thank you so much, been searching for this type of example for hours!

    ReplyDelete
  2. sorry, but what is LimitAndOffset ?

    ReplyDelete
  3. It's just an IQueryable helper for paging. After figuring out paging using Skip and Take, it easily gets old, just make a wrapper around those. It's just this: http://www.ienablemuch.com/2011/01/paging-for-linq.html

    ReplyDelete
  4. When I run it I get

    "CreateDatabase is not supported by the provider."

    At this line

    db.Customers
    .Where(x =>
    (
    (
    !string.IsNullOrEmpty(letter_pressed)
    && x.CompanyName.StartsWith(letter_pressed)
    )
    ||
    string.IsNullOrEmpty(letter_pressed)
    )
    &&
    (
    (
    !string.IsNullOrEmpty(query)
    &&
    (
    (qtype == "CompanyName" && x.CompanyName.Contains(query))
    ||
    (qtype == "ContactName" && x.ContactName.Contains(query))
    ||
    (qtype == "ContactTitle" && x.ContactTitle.Contains(query))
    )
    )
    ||
    string.IsNullOrEmpty(query)
    )
    );

    ReplyDelete
  5. How to add check box to flexigrid (e.g: http://blog.csdn.net/tangmm168/article/details/5772631 pleas see this link)

    ReplyDelete
  6. When I click on the column header of the of a column it doesn't sort ascending or descending.
    How do you add Column sorting?

    ReplyDelete