Click here to Skip to main content
Click here to Skip to main content
Go to top

Stored Procedure Driven Data Grid

, 22 Mar 2010
Rate this:
Please Sign up or sign in to vote.
Dynamically updates datagrid columns and formats without changing the code files

Introduction

DataGrid control is the main method to display information for web users. Heavy loaded applications like accounting, stock management, etc. require many more reports and update while application gets bigger. Altering the data source, reflecting the column changes to the datagrid mostly takes the longest time. Moreover displayed data of a report can vary for each branch or user, end-user requests for summary columns, column positions, cell colors, etc. is sometimes frustrating. End-user doesn't want complicated grid controls, so we have degraded our complicated grid controls to standard datagrids..

Background

Basically this method dynamically controls the aspx datagrid content from a stored procedure. If you alter the datasource columns, you don't need to update the project files, and you may control display format of the grid from the same data source.

Using the Code

First of all, we must provide a datasource for datagrid. I'm using SQL Server 2005 for my projects so the samples work on that platform. SQL command below fetches the "deliveries" table's data with given criteria.

SELECT 
id, company_title, owner_name, delivery_time, delivery_total
FROM deliveries 
WHERE delivery_time BETWEEN @starts_on AND @ends_on
  AND (@search = '' 
      OR company_title LIKE '%' + @search + '%'
      OR owner_name LIKE '%' + @search + '%')

Each table/view column must have an alias between "[ ]" characters.

SELECT 
[id|invisible] = id,
[title|caption:Company Title|alignment:left|width:200px] = company_title,
[owner|caption:Owner Name|bold|alignment:left|width:200px] = owner_name,
[delivery_on|caption:Delivery Time|format:dd/MM/yyyy|alignment:center] = delivery_time,
[total|caption:Total|format:n2|bold|color:#ff0000|alignment:right|sum] = delivery_total
FROM deliveries 
WHERE delivery_time BETWEEN @starts_on AND @ends_on
  AND (@search = '' 
      OR company_title LIKE '%' + @search + '%'
      OR owner_name LIKE '%' + @search + '%')

First argument of the command between "[]" characters must be the name of the column. Column name can be different from field name of the table. And then we're concatenating optional remaining format arguments separating by "|" character.

[title|caption:Company Title|alignment:left|width:200px] = company_title

"company_title" table field is being named for datagrid as "title". "caption" parameter provides column header text and caption of this column will be "Company Title". "alignment" argument can take one of the "left","right" or "center" parameters. You may get other arguments and their proper usage from the below list:

command sample input / description
format any format expressions like "MM, dd YYYY" / "HH:mm" / "n2", etc.
alignment left/right/center
caption plain text
bold no parameter
color #ff0000
width 200px
sum no parameter
invisible no parameter
column_type img/image, ib/ImageButton/image_button, link/link_button, double/dbl/d, integer/int/i, text/txt/t, checkbox/cbox/cb
image_url URL of image file
tooltip plain text

DataGrid control will take place in the aspx page like below:

<asp:GridView ID="gvMain" runat="server" AutoGenerateColumns="true"></asp:GridView>

GridFormatter object will be initialized in code behind with the datatable that contains the stored procedure generated data. Formatted rows and columns will be applied to the datagrid in the RowDataBound event.

protected void Button_Query_Click(object sender, EventArgs e)
{
    .
    .
    gf = new GridFormatter(datatable);
    
    this.gvMain.DataSource = dt.DefaultView;
    this.gvMain.DataBind();
    .
    .
}

protected void gvMain_RowDataBound(object sender, 
	System.Web.UI.WebControls.GridViewRowEventArgs e)
{
    gf.HandleRowDataBoundEvent(this.Page, ref this.gvMain, e.Row, 
      GridFormatter.SelectionTypes.RowSelect, GridFormatter.EventTypes.ServerSide, -1); 
} 

GridFormatter Object

GridFormatter object provides formatted data and click event behaviours. All the data source commands will be handled in this object. Please refer to the command abbreviations and usages in GridFormatter's ColumnData class.

"HandleRowDataBoundEvent" method is being called from the RowDataBound event of aspx page. Format commands will be applied to the grid rows in this event.

If a postback is requested when a row is clicked on datagrid EventType parameter of the HandleRowDataBoundEvent method must be set to GridFormatter.EventTypes.ServerSide. So, gvMain_SelectedIndexChanged event will be fired when user clicks on a row.

If you want to catch the click event on an image button without postback EventType parameter must be GridFormatter.EventTypes.ClientSide. Event must be controlled by a JavaScript function which is called GridCellClicked() in aspx page.

function GridCellClicked(v){
    var b=getVal(v,'cname');
    var i=getVal(v,'id');
    if (b=='delete') {
        alert('delete index : ' + i);
        return false;
    }
    else if (b=='edit') {
        alert('update index : ' + i);
        return false;
    }
}

Summary Row

If you specified a "sum" argument in a table field as an alias, you must add the following command after the DataBind() method. This method adds footer row at the end of the datagrid, and will show the total of the numeric columns that has "sum" argument. Display format of the summary cell will be the same as the column format.

gf.SetSummaryColumns(ref this.gvMain);

Points of Interest

The control must have a paging function. I'm using an algorithm for that, but it's not effective for some reason. Another todo list item is packing it in a user control of course.

Conclusion

I hope this code snippet will help you if you are lost in reports like me. Any new ideas and fixes are welcome...

History

  • 2010-03-22 Initial release

License

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

Share

About the Author

Dogan Erdogant
Engineer
Turkey Turkey
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberg0got21-Apr-10 12:34 
GeneralAnother alternative PinmemberPaul B.29-Mar-10 15:11 
GeneralRe: Another alternative PinmemberDogan Erdogant29-Mar-10 21:54 
Thanks for your participation but i think that your approach would not be enough for sake of easy maintenance in a hot-swap web site design with distributed data.
Dogan Erdogant

GeneralAlternative Approach Pinmemberjcain0022-Mar-10 8:16 
GeneralRe: Alternative Approach PinmemberDogan Erdogant29-Mar-10 22:10 

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 | Mobile
Web03 | 2.8.140916.1 | Last Updated 22 Mar 2010
Article Copyright 2010 by Dogan Erdogant
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid