jQGrid and MVC 3 with Custom Paging and Custom Search Filters

During past some days I was studying MVC 3. In start I found MVC little bit challenging. But its features are really exiting, I believe MVC has the power of ASP .Net along with the flexibility of classic ASP.

While implementing data driven application in MVC you have three major choices for grids i.e. MVC DataGrid, jQGrid and MvcContrib grid. jQGrid is really something which has worked best for me. So, I am sharing a sample with you to highlight key features of jQGrid along with MVC.

First of all you have to download jQGrid from jQGrid Home Page. Also you can download the source code for this article.

Client Side Code

First you have to place some container divs where your grid will be rendered like this:-

<div id="content-grid">
    <table id="jq-grid" class="scroll" cellpadding="0" cellspacing="0">
    table>
    <div id="jq-grid-pager" class="scroll" style="text-align: center;">
    <div>
<div>

In jq-grid table, your grid will be rendered, while jq-grid-pager will be used for the pager in the grid.

Now you need to initate jQGrid with call to$("#jq-grid").jqGrid({ in your document.ready event. Here I am giving you a description of individual parameters which I used in my sample.

Url is used to point to the location from where grid will fetch the data.

url: 'http://localhost:60760/Home/GetList',//Service URL to fetch data

I generally uses JSON because it is really light weight

datatype: 'json',//Data Type which service will return

Now because I am using custom filters to search the records for my grid. So we have to explicitly pass them to server. They will be passed as JSON string in filters parameter or our server side function which I will discuss later.

postData://Data that will be posted with every request
{
filters: function () {
      return $.toJSON([
                          //Here add the parameters which you like to pass to server
                          { Key: "EmployeeName", Value: $("#txtEmployeeName").val() },
                          { Key: "Id", Value: $("#txtId").val() }
                    ]);
                     }
}

Now we define column and their properties through above code. I always like to fix the widths of columns, however you can ignore width parameter as well. Also note that in last column I disabled sorting using sortable:false.

colNames: ['Employee Id', 'Employee Name', 'Action'],//Column Names
colModel: [//Column details
           { name: "Employee Id", index: "Employee Id", width: "220px" },
           { name: "Employee Name", index: "Employee Name", width: "220px" },
           //Do not allow sorting on Action Column
           { name: "Action", index: "Action", sortable: false, width: "220px" }
          ],

I am persisting sort order, sort column, total pages and current page in session, so whenever page is reloaded, I am populating them  from my ViewBag.

sortname: '@ViewBag.EmployeeGridRequest.sidx',//This will persist sorting column 

sortorder: '@ViewBag.EmployeeGridRequest.sord', // This will persist sorting order 

rowNum: @ViewBag.EmployeeGridRequest.rows, //Total Pages
page: @ViewBag.EmployeeGridRequest.page, // Current Page
rowList: [@ViewBag.EmployeeGridRequest.rowList], // Page Selection Options

This one is simple, it will set the grid's caption, if you leave this blank then grid's header will be hidden.

caption: 'Employee Grid List',//Grid's Caption, you can set it to blank if you do not want this

Also there are some parameters which you should know, see above inline comments for their details.

altRows: true,//Use alternate row colors
altclass: 'jqgaltrow',//Alternate row class
hoverrows: true, //Do not hover rows on mouse over
pager: $('#jq-grid-pager'),//Div in which pager will be displayed
toppager: true,
pgbuttons: true,//Show next/previous buttons
loadtext:'Loading Data please wait ...',

loadui :'block',//enable by default, block will disable inputs, remove this line if you do not want to block UI

I have created a function to load the data for grid whenever search or refresh button is pressed. Here is the code:-

    function reloadGrid(refresh) {
        if (refresh == true) {
            //Set initial values of your filters here
            $("#txtId").val("");
            $("#txtEmployeeName").val("");
    
            //reload grid's page 1 instead of current page
            $("#jq-grid").trigger("reloadGrid", { page: 1 });
        }
        else {
            $("#jq-grid").trigger("reloadGrid");
        }
    }

When I want to refresh grid, I set page:1, which will load the first page when ever you press refresh button.

Server Side Code
First Method which be called when Page is loaded is:-

[HttpGet]
public ActionResult Index()
{
    ViewBag.Filters = string.Empty;
    if (EmployeeGridRequest.DataFilters().Count > 0)
    {
        ViewBag.Filters = "$('#txtEmployeeName').val('" + EmployeeGridRequest.DataFilters()["EmployeeName"] + "');";
        ViewBag.Filters += "$('#txtId').val('" + EmployeeGridRequest.DataFilters()["Id"] + "');";
    }
 
    ViewBag.EmployeeGridRequest = EmployeeGridRequest;
    return View();
}

In above code I have checked from my session wrapper EmployeeGridRequest that if there is any filters available then use them to populate the initial values for the my filters and save in my ViewBag Object. As you can see below, in document.ready I have checked that if ViewBag contains filters then render them as Raw Html:-

$(document).ready(function () {
@if (ViewBag.Filters != string.Empty)
{
    @Html.Raw(ViewBag.Filters);
}

Now comes my main method which is responsible for providing data to grid. I have created some custom classes which encapsulate all the parameters which are used by jQGrid. So every time my implementation remains the same. See:-

[HttpGet]
        public JsonResult GetList(GridDataRequest request)
        {
            EmployeeGridRequest = request;
            var response = model.GetRecords(request);//Tricky Part Here :)
            var jsonData = new
            {
                total = response.TotalPages,
                page = request.page,
                records = response.TotalRecords,
                rows = response.Rows
            };
            return Json(jsonData, JsonRequestBehavior.AllowGet);
        }

Now in our model we have to create the list which we want to display in grid based on the parameters provided.

public GridDataResponse GetRecords(GridDataRequest request)
{
    .....
 GridDataResponse response = new GridDataResponse();
 response.TotalRecords = selectedList.Count;
 response.TotalPages = (int)Math.Ceiling((float)selectedList.Count() / (float)request.rows);
 response.Rows =
  (
  from item in selectedListAfterPagging
  select new GridDataDetail()
  {
   id = item.Id,
   cell = new string[] { 
   item.Id.ToString(),
   item.Name,
   "+item.Id.ToString()+"' title='Edit'>Edit " +
   "+item.Id.ToString()+"' title='Delete'>Delete"
   }
  }
  ).ToArray();
 return response;
}

I have used a List to store my Employee objects, this is only because I do not want to add a database to my sample, in real world off course you will be using database to access data. In above method I am creating a GridResponse object which will be bind with grid.
Since I am using custom paging instead of sending all records to client, so I have to manually calculate the no of records as i did:-

response.TotalPages = (int)Math.Ceiling((float)selectedList.Count() / (float)request.rows);

Also see that Grid detail object contain two properties i.e. id and cell. The id will be used as identified in grid and col will be bind to the columns in our grid.

Final Output

Take a look at our final output. First see how page is loaded first time:-

Now I have changed some sorting and applied filters to the grid.

Now edit some record by pressing edit link.

And press save button to get back to list page and see that our grid settings are persisted across page.

Finally its all done. And now you can see your grid in action. Hopefully this will be useful for you. Feel free to comment. Happy Coding !!!

Read/Edit Excel 2007 File Contents using EPPlus

In my previous articles related to EPPlus I have shown you that how you can generate Basic and Advance reports using EPPlus. Many people has asked me that how we can read the contents of an excel file using EPPlus. This is actually quite simple task. Here is the code to do this:-

using (ExcelPackage p = new ExcelPackage())
{
    using (FileStream stream = new FileStream("92b69c48-dda7-4544-bdcc-c6925a5f1bec.xlsx", FileMode.Open))
    {
        p.Load(stream);
 
        ExcelWorksheet ws = p.Workbook.Worksheets["Sample Sheet"];
        int rowIndex = 2;
 
        string text = ws.Cells[rowIndex, 1].Value.ToString(); //Read Text
        MessageBox.Show("Text in [" + rowIndex + ",1]=" + text);
 
        string comment = ws.Comments[0].Text; // Access Comments
        MessageBox.Show("Comments = " + comment);
 
        string pictureName = ws.Drawings[0].Name;// Access Picture
        MessageBox.Show("Picture = " + pictureName);
    }
}

Things are very simple so I am not sharing more details. You can use above code to edit your existing excel files as well.

To download code, use this link.

Feel free to comment. Happy Coding !!!

Automatically redirect user when session timout

You can create a BasePae class which inherits System.Web.UI.Page. Override the OnInit method and write appropriate code in that which will redirect to default page.

public class BasePage : System.Web.UI.Page
{
    protected override void OnInit(EventArgs e)
    {
        if (Session["UserName"] == null)
        {
            Response.Redirect("~/Login.aspx");
        }
    }
}

And inherit all your ASPX pages from BasePage class, instead of System.Web.UI.Page like this:-

public partial class HomePage : BasePage
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
}

And finally in your login page, simply set Session["UserName"] with user's name. Feel free to share your comments. Happy Coding !!!

$(document).ready(), pageLoad() and Sys.Application.add_init()

When I have basic understanding of AJAX and jQuery, I thought that $(document).ready(), pageLoad() and Sys.Application.add_init() are equivalent and they does the same job. In most of the samples they seems to behave in same way and I wondered that why there are too many ways to do the same thing. So I did bit research on this and I am sharing that with the developer community.

jQuery's $(document).ready()
This function is called when DOM is ready. Generally if browser supports DOMContentLoaded event, $(document).ready() will fire after this event. If browser do not support DOMContentLoaded, then when document achieve readyState, then $(document).ready() will be fired. If above two events are not available in Brower's event model then window.onload event is used to fire $(document).ready() event.

If you are using jQuery, then my No. 1 recommendation is this function as this will work on
all browsers. Here is a sample how you can use this function:-

$(document).ready(function () {
//Your code here
});

Application.Init
This event is fired only once when page is loaded first time. Remember, that if you are using update panel then this method will not be called whenever update panel refresh your page. So, this is best event when you want initialization which should be done only once and not after every update panel refresh.

<script type="text/javascript">
    Sys.Application.add_init(function () {
        // Your code here
    }); 
script>

ASP.NET AJAX's pageLoad() Method
As we all know java scripts runs as a single thread, pageLoad method uses a trick, it calls a setTimeout() function of JavaScript with 0. So, whenever DOM is ready and JavaScript execution is started, you  pageLoad() will be triggered.

Now, use this method when you want to call your code every time when your update panel gets refresh.

<script type="text/javascript">
    function pageLoad() {
        // Your code here 
    } 
script>

Note that to use AJAX methods you need to place ScriptManager object on  your page other wise they won't work.

Feel free to comment, Happy Coding !!!

How to dynamically change connection string in web.config

Most of people wonder how they can dynamically change the contents of web.config file. To simplify things I have created couple of functions through which you can change the contents of web.config file. Here is the code to change the contents of web.config file:-

/// <summary>

/// Updates the setting.

/// </summary>

/// <param name="key">The key.</param>

/// <param name="value">The value.</param>

public void UpdateSetting(string key, string value)

{

    Configuration config = WebConfigurationManager.OpenWebConfiguration("~");

    if (config.AppSettings.Settings[key] == null)

    {

        config.AppSettings.Settings.Add(key, value);

    }

    else

    {

        config.AppSettings.Settings[key].Value = value;

    }

    config.Save();

    ConfigurationManager.RefreshSection("appSettings");

}

/// <summary>

/// Updates the connection string.

/// </summary>

/// <param name="key">The key.</param>

/// <param name="value">The value.</param>

public void UpdateConnectionString(string key, string value)

{

    Configuration config = WebConfigurationManager.OpenWebConfiguration("~");

    if (config.ConnectionStrings.ConnectionStrings[key] == null)

    {

        config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings(key, value));

    }

    else

    {

        config.ConnectionStrings.ConnectionStrings[key].ConnectionString = value;

    }

    config.Save();

    ConfigurationManager.RefreshSection("connectionStrings");

}

You can update the contents of web.cofig file by simply calling above functions like this:-

protected void Page_Load(object sender, EventArgs e)

{

    UpdateSetting("test", "123");

    UpdateConnectionString("testcon", "12345");

}