How To Use ASP.NET Web Service with FlexiGrid

• Download source - 45.6 KB

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: 

1. page: index of page
2. rp: row per page
3. sortname: column name on which sorting will be applied
4. sortorder: order of sorting like asc or desc
5. qtype: column name on which filtering will be applied
6. 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
                }
                );

            //This function adds parameters to the post of flexigrid. 
	   //You can add a verification as well can 
            //return false if you don't want flexigrid to submit            
            function addFormData()
            {
            //passing a form object to serializeArray will get the 
	   //valid data from all the objects, but, if you pass a non-form object,
            //you have to specify the input elements that the data will come from
            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

• 16^th August, 2008: Initial post