Click here to Skip to main content
13,449,155 members (53,886 online)
Click here to Skip to main content
Add your own
alternative version


27 bookmarked
Posted 22 Mar 2010

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


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..


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.

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.

[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.
captionplain text
boldno parameter
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;

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.


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


  • 2010-03-22 Initial release


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


About the Author

Dogan Erdogant
Turkey Turkey
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 1 Pin
g0got21-Apr-10 12:34
memberg0got21-Apr-10 12:34 
GeneralAnother alternative Pin
Paul B.29-Mar-10 15:11
memberPaul B.29-Mar-10 15:11 
GeneralRe: Another alternative Pin
Dogan Erdogant29-Mar-10 21:54
memberDogan Erdogant29-Mar-10 21:54 
GeneralAlternative Approach Pin
jcain0022-Mar-10 8:16
memberjcain0022-Mar-10 8:16 
GeneralRe: Alternative Approach Pin
Dogan Erdogant29-Mar-10 22:10
memberDogan Erdogant29-Mar-10 22:10 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02-2016 | 2.8.180318.3 | Last Updated 22 Mar 2010
Article Copyright 2010 by Dogan Erdogant
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid