Introduction
Flexigrid uses the strength of JQuery and has many interesting features like column resize, show/hide column, search data, paging, sorting, etc. It introduces new history in the edge of DataGrid
. It uses web service for data transaction.
This article shows how it works with ASP.NET.
Background
ASP.NET Ajax provides the way to do grid operations like paging, sorting without whole page post back. But it makes the page heavy because of large viewstate data. Otherwise the grid does not provide searching facility based on column and many others that flexigrid provides. This site describes the features of flexigrid.
Steps of Using the Code
- Create Database
- Create DataAccess
- Create Web Service
- Display Data
Create Database
At first it needs to create a simple database with one table named Product
and one stored procedure named GetProducts
. The stored procedure takes the main responsibility of paging, filtering and sorting. It takes four parameters like WhereClause
for filtering, SortExpression
for sorting, index of starting row and number of rows for paging. Here I have used SQL Server 2005 for database. The scripts will be available with the downloads.
Create Data Access
Product.cs is the entity class of product
table. DataAccess.cs is responsible for accessing the database and creating a list of products.
Here is what the function looks like:
public List<Product> GetAllProducts
(string whereClause,string sortExp,int startRowIndex,int numberOfRows)
{
const string SP = "dbo.GetProducts";
List<Product> productList = new List<Product>();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(SP,con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@WhereClause",whereClause));
cmd.Parameters.Add(new SqlParameter("@SortExpression", sortExp));
cmd.Parameters.Add(new SqlParameter("@RowIndex", startRowIndex));
cmd.Parameters.Add(new SqlParameter("@NoOfRows", numberOfRows));
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product product = new Product();
product.Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
product.Name = reader.IsDBNull(1) ? string.Empty :
reader.GetString(1);
product.Description = reader.IsDBNull(2) ? string.Empty :
reader.GetString(2);
product.Unit = reader.IsDBNull(3) ? string.Empty :
reader.GetString(3);
product.UnitPrice = reader.IsDBNull(4) ? 0 : reader.GetDecimal(4);
product.CreateDate = reader.IsDBNull(5) ?
DateTime.MinValue : reader.GetDateTime(5);
productList.Add(product);
}
if ((reader.NextResult()) && (reader.Read()))
{
this.TotalRows = reader.GetInt32(0);
}
}
}
}
return productList;
}
Create Web Service
Add a web service to the site. The web method must have ScriptMethod
attribute with ResponseFormat.Xml and must return XmlDocument
. Here I used LINQ for creating the XML. XML may be generated in other ways. FlexiGrid request object contains six form parameters which are listed below:
page
: index of page rp
: row per page sortname
: column name on which sorting will be applied sortorder
: order of sorting like asc or desc qtype
: column name on which filtering will be applied query
: filtering value
Here is what the web method looks like:
[WebMethod]
[ScriptMethod(UseHttpGet = false,
XmlSerializeString = true,ResponseFormat = ResponseFormat.Xml)]
public XmlDocument GetProductList()
{
int page = 1;
if (HttpContext.Current.Request.Form["page"] != null)
{
page = int.Parse(HttpContext.Current.Request.Form["page"].ToString());
}
int rp = 1;
if (HttpContext.Current.Request.Form["rp"] != null)
{
rp = int.Parse(HttpContext.Current.Request.Form["rp"].ToString());
}
string sortname = "Name";
if (HttpContext.Current.Request.Form["sortname"] != null)
{
sortname = HttpContext.Current.Request.Form["sortname"].ToString();
}
string whereCondition = "";
if (HttpContext.Current.Request.Form["qtype"] != null &&
HttpContext.Current.Request.Form["query"] != null &&
HttpContext.Current.Request.Form["query"].ToString() != string.Empty)
{
whereCondition = BuildWhereCondition
(HttpContext.Current.Request.Form["qtype"].ToString(),
HttpContext.Current.Request.Form["query"].ToString());
}
string sortorder = "asc";
if (HttpContext.Current.Request.Form["sortorder"] != null)
{
sortorder = HttpContext.Current.Request.Form["sortorder"].ToString();
}
string sortExp = sortname + " " + sortorder;
int start = ((page - 1) * rp);
DataAccess newDataAccess = new DataAccess();
List<Product> data = newDataAccess.GetAllProducts
(whereCondition, sortExp, start, rp);
XDocument xmlDoc = new XDocument(
new XDeclaration("1.0", "utf-8", "yes"),
new XElement("rows",
new XElement("page", page.ToString()),
new XElement("total", newDataAccess.TotalRows.ToString()),
data.Select(row => new XElement("row", new XAttribute("id", row.Id),
new XElement("cell", row.Id),
new XElement("cell", row.Name),
new XElement
("cell", row.Description),
new XElement("cell", row.Unit),
new XElement
("cell", row.UnitPrice),
new XElement("cell",
row.CreateDate.
ToShortDateString())
)
)
)
);
XmlDocument newDoc = new XmlDocument();
newDoc.LoadXml(xmlDoc.ToString());
return newDoc;
}
Displaying Data
Add an ASPX page to the site. Include flexigrid.cs, flexigrid.js and jquery.js to the header section of the page. Now add the following code section under form
tag of the ASPX page. Here url
contains webservice location/webmethod, dataType
must be xml
:
<div style="text-align: left; width: 100%;">
<table id="fgrdProduct" style="display:none;"></table>
<script type="text/javascript">
$(document).ready(new function(){
$("#fgrdProduct").flexigrid
(
{
url: 'FlexiGridService.asmx/GetProductList',
dataType: 'xml',
colModel : [
{display: 'Id', name : 'Id', width : 20,
sortable : true, align: 'left'},
{display: 'Name', name : 'Name', width : 180,
sortable : true, align: 'left'},
{display: 'Description', name : 'Description', width : 180,
sortable : true, align: 'left'},
{display: 'Unit', name : 'Unit', width : 120,
sortable : true, align: 'left'},
{display: 'Unit Price', name : 'UnitPrice', width : 130,
sortable : true, align: 'left', hide: false},
{display: 'Create Date', name : 'CreateDate', width : 80,
sortable : true, align: 'left'}
],
searchitems : [
{display: 'Name', name : 'Name'},
{display: 'Description', name : 'Description'},
{display: 'Unit', name : 'Unit'},
{display: 'Unit Price', name : 'UnitPrice'},
{display: 'Create Date', name : 'CreateDate'},
{display: 'Id', name : 'Id', isdefault: true}
],
sortname: "Name",
sortorder: "asc",
usepager: true,
title: 'List of Products',
useRp: true,
rp: 10,
showTableToggleBtn: true,
width: 805,
onSubmit: addFormData,
height: 200
}
);
function addFormData()
{
var dt = $('#sform').serializeArray();
$("#fgrdProduct").flexOptions({params: dt});
return true;
}
$('#sform').submit
(
function ()
{
$('#fgrdProduct').flexOptions({newp: 1}).flexReload();
return false;
}
);
});
</script>
</div>
Points of Interest
The functionality demonstrated is:
- The exchange of data between FlexiGrid and WebServer is done by web service
- Provides maximum features of FlexiGrid
- And, at last, a demo project
Hope this helps.
History
- 16th August, 2008: Initial post
I am Bachelor in CSE from Khulna University of Engineering & Technology,Bangladesh. I have more than 11 years experience in software design & development, data analysis & modeling, project management and currently working in a software company in Dubai,UAE as a Lead Software Engineer. I am MCAD(Microsoft Certified Application Developer) certified since 2005. Please feel free to contact with me at nill_akash_7@yahoo.com.