Simplify ASP.NET DataGrid by Eliminating Code-Behind

• Download source files - 88.7 Kb

Introduction

The DataGrid control is one of the most powerful controls in the .NET framework. It makes presenting data in columns and rows, one of the most common tasks, a lot easier. But it also has its down side. It's simply too complicated. It requires thorough understanding of the .NET event-driven programming style, such as life-cycle, view-state management etc. Here, I have written an extension class of the ASP.NET DataGrid control, hopefully to reduce the code-behind programming required. The approach is simple; instead of writing lengthy code-behind, move most of the programming to the designer by simply setting the server control attributes. It's not a new concept. Many commercial ASP.NET controls take the same approach to simplify common programming practices. It hides the actual programming so anyone with little or no programming skills can use these controls. I call this kind of a programming approach, "attribute-based programming".

The first step to simplify the DataGrid is to figure out what kinds of attributes we need to set in our DataGrid. The common attributes we need are columns description, columns shown, and row hover color. If it is an editable DataGrid, we need attributes to set it to true or false. In addition, a few essential database related attributes are needed, such as the SQL string, table name, etc.

Since we are writing an extension to the current .NET DataGrid control, we inherit the class from System.Web.UI.WebControls.DataGrid.

/// <summary>
/// SimpleGrid is a customized ASP.NET datagrid,
/// derived from DataGrid oject.
/// It is NOT a collective controls
/// but a single Web DataGrid Control.
/// </summary>
public class SimpleGrid : System.Web.UI.WebControls.DataGrid

We declare the attributes as member variables. Then the variables are initiated in the class constructor. It's considered a good programming practice to initiate all the member variables, specially in C#, which is a strongly-typed language.

Note: ClsDatabase is a data access layer class created separately to facilitate the creation of the DataGrid. Its source code is included at the end of this article. Readers can look into the details of its implementation on their own.

Database db;
DataSet ds;
private string sqlStr;
private string sqlFilters;
private string tableName;
private string columnsShown;
private string columnsDesc;
private string rowHoverColor;
private bool isEditable;

// constructor. member variables MUST be initialized.
public SimpleGrid():base()
{
    db = new ClsDatabase("server=server_name;" + 
         "uid=your_id;pwd=your_pass;Database=demo;");

    sqlStr = null;
    sqlFilters = null;
    columnsShown = null;
    columnsDesc = null;
    rowHoverColor = null;
    isEditable = false;
}

To have those member variables/attributes available in the designer, each one needs to have its own property.

[Bindable(true),
Category("Data"),
DefaultValue("")]
public string SqlStr
{
    get {return sqlStr;}
    set {sqlStr = value;}
}

[Bindable(true),
Category("Data"),
DefaultValue("")]
public string SqlFilters
{
    get {return sqlFilters;}
    set {sqlFilters = value;}
}


[Bindable(true),
Category("Data"),
DefaultValue("")]
public string TableName
{
    get {return tableName;}
    set {tableName = value;}
}


[Bindable(true),
Category("Appearance"),
DefaultValue("")]
public string ColumnsShown
{
    get {return columnsShown;}
    set {columnsShown = value;}
}

[Bindable(true),
Category("Appearance"),
DefaultValue("")]
public string ColumnsDesc
{
    get {return columnsDesc;}
    set {columnsDesc = value;}
}

[Bindable(true),
Category("Appearance"),
DefaultValue("")]
public string RowHoverColor
{
    get {return rowHoverColor;}
    set {rowHoverColor = value;}
}


[Bindable(true),
Category("Appearance"),
DefaultValue("False")]
public bool IsEditable
{
    get {return isEditable;}
    set {isEditable = value;}
}

[Bindable(true),
Category("Appearance"),
DefaultValue("")]
public string ColumnsReadOnly
{
    get {return columnsReadOnly;}
    set {columnsReadOnly = value;}
}

The next step is to create our DataGrid columns. The key is to override the OnInit function to add each column to the grid, by manually iterating each column of the DataTable. The AutoGenerateColumns property needs to be set to false. Otherwise, it will generate a bunch of duplicated columns. The reason that we need to manually add columns to the grid is because the auto generate columns do not have a reference to its members, such as HeaderText, DataField etc. See the post from last week regarding the DataGrid Gotcha.

this.AutoGenerateColumns = false;

The OnInit function is overridden as follows:

protected override void OnInit(EventArgs e)
{
    base.OnInit (e);

    string[] arrColShown = this.columnsShown.Split(';');
    string[] arrColDesc  = this.columnsDesc.Split(';');
    string[] arrDescPair = null;

    // override autogeneratcolumns property to false
    // since we will manually add them below
    this.AutoGenerateColumns = false;

    // set filter before using it.
    if(this.sqlFilters != null)
    {
        this.sqlStr += " WHERE " + this.sqlFilters + "='" + 
         HttpContext.Current.Request.QueryString[this.sqlFilters] + "'";
    }
    ds = db.getDataSetFromTable(this.sqlStr, "SimpleGridTableName");
    DataTable dt = ds.Tables[0];

    for(int i  = 0; i < dt.Columns.Count; i++)
    {
        BoundColumn bndColumn = new BoundColumn();
        bndColumn.HeaderText = dt.Columns[i].ColumnName;
        bndColumn.DataField = dt.Columns[i].ColumnName;
        // set header text. It must be format "index:Desc"
        // pair delimited by semicolom;
        for(int j = 0; j < arrColDesc.Length; j++)
        {
            arrDescPair = arrColDesc[j].Split(':');
            if(Convert.ToInt32(arrDescPair[0]) == i)
            {
                bndColumn.HeaderText = 
                     Convert.ToString(arrDescPair[1]);
            }
        }                                                     

        // set sort expression
        bndColumn.SortExpression = dt.Columns[i].ColumnName;

        // set columns visiblity by hides everything at first
        bndColumn.Visible = false;
        for(int m = 0; m < arrColShown.Length; m++)
        {
            if(Convert.ToInt32(arrColShown[m]) == i)
            {
                bndColumn.Visible = true;
            }
        }
       
        this.Columns.Add(bndColumn);
    }

    // add edit/delete cmd to the right end of the grid columns
    if(this.isEditable)
    {
        EditCommandColumn  cmdColumn = new EditCommandColumn ();
        cmdColumn.EditText = "Edit";
        cmdColumn.UpdateText = "Update";
        cmdColumn.CancelText = "Cancel";
        this.Columns.Add(cmdColumn);

        ButtonColumn btnColumn = new ButtonColumn();
        btnColumn.ButtonType = ButtonColumnType.LinkButton;
        btnColumn.Text = "Delete";
        btnColumn.CommandName = "Delete";
        this.Columns.Add(btnColumn);
    }
}

The next step is to write our DataGrid binding function. With the help of the database class getDataSetFromTable() function, it takes only three lines.

private void BindGrid()
{
    ds = db.getDataSetFromTable(this.sqlStr, "SimpleGridTableName");
    this.DataSource = ds;
    this.DataBind();
}

Now, we override the OnLoad event handler so we can have a simple working DataGrid:

protected override void OnLoad(EventArgs e)
{
    base.OnLoad (e);
    if(!Page.IsPostBack)
    {
        this.BindGrid();
    }
}

Since we are extending the DataGrid class, we just need to override the existing event handlers to provide added functionalities. The functions to be overridden are:

• OnItemCreated()
• OnItemDataBound()
• OnEditCommand()
• OnCancelCommand()
• OnUpdateCommand()
• OnDeleteCommand()
• OnSortCommand()

The following is the implementation of the above event handler functions. Essentially, all they are doing is to construct the necessary SQL statement. Everything else is rather straightforward, though the UpdateCommand method requires a little bit attention. UpdateCommand iterates through each cell in the current Item/Row, and creates an SQL UPDATE statement only if the current cell has control. In other words, we don't want to update a field which is set to read-only. In addtion, we cannot update a DataKeyField, which holds the primary key value of the current record. Lastly, remember to call the BindGrid function at the end of each event handler, in order to view the changes made to the grid.

// The hover over color change visual effect
// are handled in the OnItemCreated since the effect can only be
// created after the table rows are generated by the datagrid.
protected override void OnItemCreated(DataGridItemEventArgs e)
{
    base.OnItemCreated (e);

    // set hover color here
    if(this.rowHoverColor != null)
    {
        this.SetRowHighlight(this, e);
    }
}

/// <summary>
/// This method changes the color
/// of the row when the mouse is over it.
/// </summary>
public void SetRowHighlight(DataGrid dg, 
       System.Web.UI.WebControls.DataGridItemEventArgs e)
{
    try
    {    // Is the item an item or alternating item?
        if((e.Item.ItemType == ListItemType.Item) || 
           (e.Item.ItemType == ListItemType.AlternatingItem))
        {
            e.Item.Attributes.Add("onmouseover", 
               "preRowColor=this.style.backgroundColor;" + 
               "this.style.backgroundColor='" +
               this.rowHoverColor + "'");
            e.Item.Attributes.Add("onmouseout", 
               "this.style.backgroundColor=preRowColor");
        }
    }
    catch{}
}
               
// Here is a good place to set field to read-only during editing.
protected override void OnItemDataBound(DataGridItemEventArgs e)
{
    base.OnItemDataBound (e);


    for(int i=0; i < e.Item.Cells.Count; i++)
    {   
        TableCell currCell = e.Item.Cells[i];
       
        // format date-time to display date only
        try
        {
            string currValue = 
              (DateTime.Parse(currCell.Text)).ToShortDateString() ;
            currCell.Text = currValue;
        }
        catch{}

        // set read-only fields
        if(this.isFieldSetToReadOnly(i))
        {
            // The currCell.Text will have value
            // of "<input type='text' ...>" in update command.
            // So we must partse the text out as following.
            // e.Item.Cells[i].Text = currCell.Text
            if(currCell.HasControls())
            {
                if(currCell.Controls[0] is TextBox && currCell.Visible)
                {
                    TextBox txtValue = (TextBox)currCell.Controls[0];
                    e.Item.Cells[i].Text = txtValue.Text;
                }
            }
        }
    }
}

// test if the field is set read-only in designer
protected bool isFieldSetToReadOnly(int itemIndex)
{
    // set read-only fields to display text only, NOT controls!
    string[] arrColReadOnly = this.columnsReadOnly.Split(';');
    for(int j = 0; j < arrColReadOnly.Length; j++)
    {
        if(Convert.ToInt32(arrColReadOnly[j]) == itemIndex)
        {
            return true;
        }
    }
    return false;
}

#region "datagrid edit/delete/cancel command event handling"

protected override void OnEditCommand(DataGridCommandEventArgs e)
{
    base.OnEditCommand (e);
   
    this.EditItemIndex = e.Item.ItemIndex;
    BindGrid();
}


protected override void OnCancelCommand(DataGridCommandEventArgs e)
{
    base.OnCancelCommand (e);

    this.EditItemIndex = -1;
    BindGrid();
}

// generate update SQL statement, execute and then bind grid.
protected override void OnUpdateCommand(DataGridCommandEventArgs e)
{
    base.OnUpdateCommand (e);
   
    string dataField = null;
    string dataValue = null;
    string updateSql = "UPDATE " + this.tableName + " SET ";
    for(int i = 0; i < e.Item.Cells.Count; i++)
    {
        TableCell currCell = e.Item.Cells[i];
        if(currCell.HasControls())
        {
            if(currCell.Controls[0] is TextBox && currCell.Visible)
            {
                TextBox txtValue = (TextBox)currCell.Controls[0];
                dataValue = txtValue.Text;
                BoundColumn bndColumn = (BoundColumn)this.Columns[i];
                dataField = bndColumn.DataField;
               
                // don't update fields that are primary
                // key & those are set to read-only
                if(dataField != this.DataKeyField && 
                   this.isFieldSetToReadOnly(i) != true)
                {
                    updateSql += dataField + " = '" + dataValue + "',";
                }
            }
        }
    }

    updateSql = updateSql.Substring(0, updateSql.Length-1);
    updateSql += " WHERE " + this.DataKeyField + 
                 " = '" + this.DataKeys[e.Item.ItemIndex] + "'";
    db.ExecuteNonQuery(updateSql);
    this.EditItemIndex = -1;
  
    BindGrid();
}

protected override void OnDeleteCommand(DataGridCommandEventArgs e)
{
    base.OnDeleteCommand (e);

    string deleteSql = "DELETE FROM " + this.tableName + 
                       " WHERE " + this.DataKeyField + " = '"
                       + this.DataKeys[e.Item.ItemIndex] + "'";
    db.ExecuteNonQuery(deleteSql);
    this.EditItemIndex = -1;
    BindGrid();
}

protected override void OnSortCommand(DataGridSortCommandEventArgs e)
{
    base.OnSortCommand (e);

    this.SqlStr += " ORDER BY " + e.SortExpression.ToString();
    BindGrid();
}

#endregion

OK! Our simplified DataGrid class is complete! Now, we just need to have the run-time designer code to test our SimpleGrid class. Remember, this is an extension of the current .NET DataGrid class, so other existing attributes still can be used, such as AllowPaging, PageSize etc. That's it! There is no code-behind, no programming required, but simple markup attribute settings.

<%@ Register TagPrefix="sg" 
        Namespace="MyProject.Components" Assembly="MyProject" %>
<%@ Page language="c#" Codebehind="SimpleGridTest.aspx.cs" 
        AutoEventWireup="false" Inherits="MyProject.SimpleGridTest" %>


<form id="Form1" method="post" runat="server">
<sg:SimpleGrid id="simpleGrid1" runat="server" 
    EnableViewState="True" AllowSorting="True" 
    Font-Size="10pt" CellPadding="3" AllowPaging="True" PageSize="10"
    SqlStr="SELECT * FROM user_list"
    TableName="user_list"
    DataKeyField="user_list_id"
    ColumnsShown="1;2;3;4;6;10"
    ColumnsReadOnly="0;1;4"
    ColumnsDesc="1:Login Name;6:Email"
    RowHoverColor="#C3D1F7"
    IsEditable="True">
</sg:SimpleGrid>
</form>

The SimpleGrid is free and open source. You are most welcome to modify it to suit your needs, and to make it even better for others to use. The code has been tested, proven fully working in the ASP.NET 1.1 environment. It should work with 2.0, but I haven't tested it yet. You are welcome to leave any comments or questions that you may have regarding the SimpleGrid. The SimpleGrid doesn't have functions to be able to add a new record. Maybe it should be the next enhancement.