Stored Procedure Driven Data Grid

• Download source - 15.94 KB

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:

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