In this series of posts we will present a searchable data grid. The grid is provided by the jqGrid plugin for jQuery. On the server side we use an ASPMVC Controller to provide the data. This instalment will cover the setting up of the data and the basic grid.
The data that we display in the grid will be log4net entries written by an ADODB appender. The schema for the table follows
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE [logs].[DetailedLog]( [Id] [int] IDENTITY(1,1) NOT NULL, [Date] [datetime] NOT NULL, [Thread] [int] NOT NULL, [RequestId] [nchar](32) NOT NULL CONSTRAINT [DF_Log4Net_RequestId] DEFAULT (''), [ServerIP] [nchar](15) NOT NULL CONSTRAINT [DF_Log4Net_ServerIP] DEFAULT (''), [ClientIP] [nchar](15) NOT NULL CONSTRAINT [DF_Log4Net_ClientIP] DEFAULT (''), [UserId] [nchar](32) NOT NULL CONSTRAINT [DF_Log4Net_UserId] DEFAULT (''), [Level] [nchar](10) NOT NULL, [Logger] [nvarchar](255) NOT NULL, [Message] [nvarchar](4000) NOT NULL, [Exception] [nvarchar](2000) NULL ) ON [PRIMARY] |
You should create a SQL Server database or use an existing one and execute the above statement to create the table. I will attach some sample data to this post to load into the table.
Our sample web application will be an ASP MVC2 application so start by creating an empty “ASP MVC 2 Web Application” in Visual Studio. I’m currently using VS2008 so you need to install ASP MVC2. (TIP: Use the Platform SDK from Microsoft). For VS2010 users its already included.
We will use LINQ to SQL as our data access object so we need to create a Data Context. This is done by adding a “LINQ to SQL Classes” item to the Models folder of your solution. Create a connection to your database in Server Explorer and drag the DetailedLog table onto the design surface
No that we have our data setup we can dive into some code. We will work from the database up to the front end so with our Data Context defined we can write a simple data access routine. We will used the deferred execution style.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
public class MaintenanceDao { static private GatewayDbContext _ctx = new GatewayDbContext(); public static IQueryable<DetailedLog> GetDetailedLog(int page, int pageSize, ref int total, string sidx, string sord) { total = _ctx.DetailedLogs.Count(); return _ctx.DetailedLogs.OrderBy(sidx, sord).Skip((page - 1) * pageSize).Take(pageSize); } } |
We should note that we are using the dynamic LINQ capabilities provided by the ASPMVC2 Futures library. Download the Futures library and add the dynamic.cs file to your solution. The MainentanceDao class we defined above has a static method which will result in two queries. The first will return the total number of records and the second will return a subset of records the governed by the page and pageSize parameters. The order of the records is governed by the sidx and sord parameters, the jqGrid is the ultimate source of the values for these parameters. So with our data access method defined we can write a basic controller.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
public class Log4NetController : Controller { public ActionResult Index() { return View(); } public JsonResult DetailedJson(int page, int rows, string sidx, string sord) { JsonResult jr; try { int records = 0; var rowData = MaintenanceDao.GetDetailedLog(page, rows, ref records, sidx, sord); var json = new { page = page, total = Math.Ceiling((double)records / rows), records = records, rows = ( from iter in rowData.ToList() select new { id = iter.Id, cell = new string[] { iter.Id.ToString(), iter.Date.ToString("t"), iter.Thread.ToString(), iter.RequestId.TrimEnd().Equals("(null)") ? "SYSTEM" : iter.RequestId.TrimEnd(), iter.ServerIP.TrimEnd(), iter.ClientIP.TrimEnd(), iter.UserId.TrimEnd().Equals("(null)") ? "SYSTEM" : iter.UserId.TrimEnd(), iter.Level.TrimEnd(), iter.Logger.TrimEnd(), iter.Message.TrimEnd(), iter.Exception.TrimEnd() } } ).ToArray() }; jr = Json(json, JsonRequestBehavior.AllowGet); } catch (Exception e) { Response.StatusCode = 500; jr = Json(new HttpException(500, e.Message), JsonRequestBehavior.AllowGet); } return jr; } } |
The Log4NetController calls our GetDetailedLog to get the raw records into the variable rowData we create an anonymous object containing the data to be passed to the jqGrid. This object is then passed to the Json method to construct a JsonResult object. The data to be displayed in the grid is represented as a dictionary with a key identified as ID and a value identified as CELL which is an array of string. No that we have our controller we need the Index view. This is a straight forward view with the code needed to render the jqGrid.
The following is the complete source for our view. When the page loads an asynchronous request is made to the DetailedJson action of our Log4NetController class resulting in a rendered grid.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %> <asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server"> Index </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> <h2>Index</h2> <table id="list"></table> <div id="pager"></div> </asp:Content> <asp:Content ID="Content3" ContentPlaceHolderID="ScriptContent" runat="server"> <link href="/Content/ui.jqgrid.css" rel="stylesheet" type="text/css"> <script src="/Scripts/jquery.form.js" type="text/javascript"></script> <script src="/Scripts/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="/Scripts/jquery.jqGrid.min.js" type="text/javascript"></script> <script type="text/javascript"> $("document").ready(function() { setupDetailedGrid(); }); function setupDetailedGrid() { $("#list").jqGrid({ url: '/Log4Net/DetailedJson', datatype: 'json', mtype: 'GET', colNames: ['Id', 'Date', 'Thread', 'RequestId', 'ServerIP', 'ClientIP', 'UserId', 'Level', 'Logger', 'Message', 'Exception'], colModel: [ { name: 'Id', index: 'Id', width: 40, align: 'left', hidden: true }, { name: 'Date', index: 'Date', width: 60, align: 'center', hidden: true }, { name: 'Thread', index: 'Thread', width: 60, align: 'center', hidden: false }, { name: 'RequestId', index: 'RequestId', width: 60, align: 'center', hidden: true }, { name: 'ServerIP', index: 'ServerIP', width: 60, align: 'center', hidden: true }, { name: 'ClientIP', index: 'ClientIP', width: 60, align: 'center', hidden: true }, { name: 'UserId', index: 'UserId1', width: 160, align: 'center', hidden: false }, { name: 'Level', index: 'Level', width: 60,align: 'center' }, { name: 'Logger', index: 'Logger', width: 140, align: 'left' }, { name: 'Message', index: 'Message', width: 250, align: 'left' }, { name: 'Exception', index: 'Exception', width: 250, align: 'left', hidden: true}], rowNum: 50, pager: '#pager', rowList: [50, 100, 150], sortname: 'Date', sortorder: "asc", viewrecords: true, imgpath: '/Content/redmond/images', caption: 'Detailed Log(s)', loadtext: 'Please Wait...', multiselect: false, height: 150, width: 700 }).navGrid('#pager', { view: false, del: false, add: false, edit: false, search: false }, {}, // use default settings for edit {}, // use default settings for add {}, // delete instead that del:false we need this {multipleSearch: false }, // enable the advanced searching {closeOnEscape: true} /* allow the view dialog to be closed when user press ESC key*/); } </script> </asp:Content> |
The following image is how our rendered grid looks.
In Part 2 we will show how to perform advanced searching on the grid data.
Leave a Reply