Click here to Skip to main content
Click here to Skip to main content

How To Use ASP.NET Web Service with FlexiGrid

, 16 Aug 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Use ASP.NET web service in flexigrid
flexigriddata.jpg - Click to enlarge

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

  • 16th August, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Fazlur Rahman
Software Developer (Senior) MediaNet Group
United Arab Emirates United Arab Emirates
I am Bachelor in CSE from KUET,Bangladesh. I have more than 6 years experience in ASP.NET and C# and currently working in a software company in Dubai,UAE as a Senior 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.


Comments and Discussions

 
QuestionRegarding the flexigrid Pinmembervivek_29845-Apr-14 7:28 
Questioncan't populate FlexiGrid with web method!!! Pinmemberprahalad.gaggar12-Dec-12 1:08 
I am using flexi-grid in my web application.
if its possible can you please write an article which is not calling web-service or using MVC model!!!
QuestionNOt working PinmemberUmesh Bai24-Apr-12 22:45 
Questionpassing another value to service method Pinmemberbakasur13-Feb-12 20:27 
QuestionScript in page header PinmemberZ@clarco12-Oct-11 2:27 
GeneralEdit and delete row PinmemberMember 82732713-May-11 18:51 
GeneralRe: Edit and delete row PinmemberSunasara Imdadhusen16-May-11 21:40 
GeneralRe: Edit and delete row Pinmemberdishandayarathna26-Oct-11 22:19 
GeneralWithout webservie can we pass aspx page as url in jquery Pinmembervenkatesh198126-Oct-10 22:12 
AnswerRe: Without webservie can we pass aspx page as url in jquery Pinmembergomerpylevw3-Nov-10 19:46 
Generalwhen i use JsonSerializer class.... Pinmemberranzige20-Apr-10 18:42 
Questionhow about json? Pinmemberranzige20-Apr-10 16:42 
GeneralNice! Pinmembertreesprite22-Feb-10 2:38 
GeneralThanks Pinmemberserkan ylmaz7-Jul-09 23:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150331.1 | Last Updated 16 Aug 2008
Article Copyright 2008 by Fazlur Rahman
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid