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:
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)
{
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)
{
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)
{
...
}
}
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)
{
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
- 8th August, 2005 - Initial version.