Auto-SQL DataGrid component Part II: The full-featured version

• Download source files - 5.53 Kb
• Download demo project - 21.4 Kb

Introduction

In this second article of our series, we will make our ASQLDataGrid class editable – that is, the class will be able to auto-construct other SQL statements: INSERT, UPDATE and DELETE. Please check the first article for the premise. Before we go into the details let me tell you about some particular features of our ASQLDataGrid class. First, although our ASQLDataGrid is editable, only one row is edited at a time even if this row is a new row just added to the DataGrid or an existing row. The second special feature of this class is that it will have buttons (Edit and Delete) on each data row instead of having the hyperlinks as you see in other DataGrid examples.

The CmdButtonColumn class

The CmdButtonColumn is the DataGrid column version of the CmdButton that I've shown in the first article. It has four public properties:

• Cmd : the executing command (e.g.: DG_NEW, DG_SAVE).
• ButtonText : the text of the button shown when the row is in the view mode.
• LabelText : the text of the label shown when the row is in edit mode.
• PreStatement : the JavaScript code, executed before the command is executed. It is used for adding confirmation before deleting a row. The necessary JavaScript is attached to each button inside the column’s cells at the ItemDataBound event of the DataGrid as shown in the following code:
  C#

  protected override void OnItemDataBound(DataGridItemEventArgs e)
  {
      base.OnItemDataBound(e);
  
      if ((e.Item.ItemType == ListItemType.Item) || 
          (e.Item.ItemType == ListItemType.AlternatingItem) || 
          (e.Item.ItemType == ListItemType.SelectedItem))
      {
          for (int i=0; i<Columns.Count;i++)
          {
            if (Columns[i] is CmdButtonColumn)
            {
              CmdButtonColumn column = (CmdButtonColumn)Columns[i];
              ((Button)e.Item.Cells[i].Controls[0]).Attributes.Add(
                       "OnClick", column.PreStatement + 
                       "__SetCmd('" + column.Cmd + "','" + this.ID + 
                       "$" + e.Item.ItemIndex.ToString() + "');"); 
            }
          }
      }
  }

The ASQLDataGridCmdExecutor class

Comparing to the version in the first article, our standard command executor will handle some new commands like:

• DG_NEW: adding a new row to the top of the DataGrid.
• DG_CANCEL: Cancel the actual editing (both for adding a new row or modifying an existing row).
• DG_SAVE: save the changes.
• DG_EDIT: put a row to edit mode.
• DG_DELETE: delete a row from the DataGrid.

The implementation is quite straightforward as you can check the details in the ExecuteCmd method of the class.

The ASQLDataGridStatBuilder class

Besides the BuildSelect method that we already have from the first article, we will have three new methods to build the INSERT, UPDATE and DELETE statements. Lets' take a closer look at the BuildInsert method first:

public virtual string BuildInsert(ASQLDataGrid dg)
{
    return "insert into " + INS_BuildIntoClause(dg) + 
                         INS_BuildListOfColumns(dg) + 
                         INS_BuildListOfValues(dg);
}

The INS_BuildIntoClause method extracts the DB table name from the DataGrid’s ID, exactly like the SEL_BuildFromClause in the BuildSelect method. The INS_BuildListOfColumns returns a list of columns in the same way as SEL_BuildListOfColumns method does. The INS_BuildListOfValues builds a comma-separated list of values of each cell in the new row (row number 0) by calling the GetColumnValue method for each column in the DataGrid. If the column is a BoundColumn, then the GetColumnValue method will check if the current cell contains a TextBox or only a text. The returned value will be the current text of the TextBox or the text of the cell itself:

if (dg.Columns[ColumnIndex] is BoundColumn)
{
    if (dg.Items[RowIndex].Cells[ColumnIndex].HasControls())
    {
        control = 
          dg.Items[RowIndex].Cells[ColumnIndex].Controls[0];
        if (control is TextBox)
        {
            Value = ((TextBox)control).Text;
        }
    }
    else
    {
        Value = dg.Items[RowIndex].Cells[ColumnIndex].Text;
    }
}

With TemplateColumns, currently the method can get the value of the cell if the column contains a ListControl or a CheckBox. In any other case, the value will be returned as an empty string and it means the BuildInsert method will leave this column out of the statement. In fact, if a DB column name or its value is returned as an empty string then it will be left out of the statement. Of course you can always have your own Statement Builder and override any part of the statement if the default behavior doesn’t match your needs.

An important note here is that the value is returned "as it is" without any validating or formatting. In your real applications you may have to add your own validating and formatting of the cell’s values before letting the ASQLDataGrid class automatically execute the statements.

Here is how the BuildUpdate method was implemented:

public virtual string BuildUpdate(ASQLDataGrid dg)
{
    string sWhere = UPD_BuildWhereClause(dg);
    if (sWhere.Length == 0)
    {
        //Prevent unintentional update of all the data
        throw new Exception("The update statement " + 
          "requires the correct where clause to work with!");
    }
    return "update " + UPD_BuildTableName(dg) + 
             " set " + UPD_BuildSetClause(dg) + 
             UPD_BuildFromClause(dg) + " where " 
             + sWhere;
}

To construct the WHERE clause of the UPDATE statement, we need a new property of the ASQLDataGrid: the ListOfPKColumns string which contains the comma-separated list of indexes of the columns participating in the primary key of the DB table. Usually, just like in our example project, it will be only one column – in our case it is the EmployeeID so we set the ListOfPKColumns = "0". The UPD_BuildWhereClause takes all the columns in the ListOfPKColumns, gets the columns’ DB names and the current values in pair and returns the WHERE clause similar to this:

PKColName1 = 'CurrentValue1' and PKColName2 = 'CurrentValue2'

To prevent accidentally updating all the data in the DB table, the BuildUpdate will throw an exception if the UPD_BuildWhereClause returns an empty string. The UPD_BuildSetClause method works very similar to the UPD_BuildWhereClause, except it works with all the columns not in the ListOfPKColumns.

The syntax of the DELETE statement is the simplest. Here is how the BuildDelete method looks like:

public virtual string BuildDelete(ASQLDataGrid dg, int RowIndex)
{
    string sWhere = DEL_BuildWhereClause(dg, RowIndex);
    if (sWhere.Length == 0)
    {
        //Prevent unintentional delete of all the data
        throw new Exception("The delete statement " + 
               "requires the correct where clause " + 
               "to work with!");
    }
    return "delete from " + DEL_BuildFromClause(dg) + 
                                   " where " + sWhere;
}

The DEL_BuildWhereClause works with the ListOfPKColumns property, just like the UPD_BuildWhereClause.

The sample project

We will modify the sample project of the first article a little bit. First, we change the ReportsTo BoundColumn to a TemplateColumn containing a DropDownList. The content of this DropDownList is populated at the (instance-level) ItemDataBound event handler of the DataGrid:

private void DG_Employees_ItemDataBound(object sender, 
         System.Web.UI.WebControls.DataGridItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.EditItem)
    {
        //Populating the DropDownList with the employees' names
        ...
    }
}

Because the EmployeeID is an identity column in the DB – means the value will be generated by the database engine, we should instruct the Statement Builder to leave the column out of the INSERT statement. As I mentioned above, if a DB column name is returned as an empty string then it will be left out of the statement - here is how we do it:

public override string GetDBColumnName(TypeOfSQLStatement stat, 
                               ASQLDataGrid dg, int ColumnIndex)
{
    ...
        else if (stat == TypeOfSQLStatement.Insert)
        {
            if (ColumnIndex == 0)
            {
                //Skip the EmployeeID because 
                //it is an identity column
                return "";
            }
            else
                return base.GetDBColumnName(stat, 
                                     dg, ColumnIndex);
        }

    ...
}

Finally, we should specify the correct value to our ListOfPKColumns property. As the Employee DB table's primary key is the EmployeeID column, we set ListOfPKColumns="0".

Conclusion

My first auto-SQL components were written in a programming language called Gupta SQL Windows 4.0 back in 1993 ( with all my credits to Németh Miklós) and since then I just couldn’t leave it out of my class libraries including my recent .NET class library. It may take a little time before you master the idea but I hope you will find it useful as I and my team did.

Happy and creative programming!

History

• 8^th August, 2005 - Initial version.