Auto-SQL DataGrid component. Part I: Non-editable version

• Download source files - 4 Kb
• Download demo project - 18 Kb

What is Auto SQL DataGrid?

The Auto SQL DataGrid is capable to automatically build the SQL statements using to populate, insert, update and delete the data from/into the database behind the datagrid by using only information of the properties of the datagrid and its columns. In optimal cases, you have only to name the datagrid’s ID after the DB table name and the DataField of each column with the correct name of the DB column behind it and the ASQLDataGrid will do all the magic for you. It also provides a simple and intuitive programming interface to alter the default behavior to deal with more complicated cases like the one when you have to join two or more DB tables in the Select statement. In this 1^st article of the series, I will introduce the non-editable version of the ASQLDataGrid, while the 2^nd article will complete the class with insert, update and delete features. You should be familiar with the basics of the Relational Database Management Systems (RDBMS) and the syntaxes of SQL statements in order to work with the ASQLDataGrid component.

The CmdButtonForm and the CmdButton classes

You can check the details of how to use the CmdButtonForm class in my previous article here. Shortly, it provides the class-library level mechanism of handling (multiple) submit button on a form by using hidden tags. You should inherit your own WebForm from this class like this:

public class WebForm1 :NTM.Controls.CmdButtonForm

The CmdButton class is the accompanying class to the CmdButtonForm. It has two public properties: Cmd which specifies the command to execute (Populate, Save, etc) and the

Param 

for the parameter string (e.g. the ID of the target datagrid). In the sample project, I added the Populate button to the web form like this:

<ntm:CmdButton id="btPopEmployees" runat="server" Cmd="DG_POP" Param="DG_Employees" Text="Populate" Width="73"></ntm:CmdButton>

The ASQLDataGridCmdExecutor class

The

ASQLCmdExecutor 

property of the ASQLDataGrid specifies the object to handle the commands and it should be instantiated from the ASQLDataGridCmdExecutor class or from a class inherited from it. If you don’t assign any value to this property, the ASQLDataGrid class will create a default ASQLDataGridCmdExecutor object and let it handle the (standard) commands:

protected override void OnLoad(EventArgs e)
{
...
            if (this._ASQLCmdExecutor == null)
            {
                //Create and use a default command executor
                this._ASQLCmdExecutor = new ASQLDataGridCmdExecutor();
            }
            _ASQLCmdExecutor.ExecuteCmd(this, Command, CommandParam);
...
}

Besides the standard commands like DG_POP for populating the data from the DB, you can define any command and write your on command executor to handle it. For instance you may want to have a Delete All button. First, you should add the command button to your web form:

<ntm:cmdbutton id="btDelAllEmployees" runat="server" Cmd="DG_DELALL" Param="DG_Employees" Text="Delete All" Width="73"></ntm:cmdbutton>

You can inherit your own command executor class from the ASQLDataGridCmdExecutor as shown in the following code:

public class MyCmdExecutor : ASQLDataGridCmdExecutor
{
    public override void ExecuteCmd(ASQLDataGrid dg, string Cmd, string CmdParams)
    {
        if (Cmd.Equals("DG_DELALL"))
        {                           
            //Write you own code to here
            ...
        }
        else
            base. ExecuteCmd (dg, Cmd, CmdParams);
    }
}

Now on the WebForm, you can assign your newly developed command executor to the DataGrid like this:

private void Page_Load(object sender, System.EventArgs e)
{
    ...
    DG_Employees.ASQLCmdExecutor = new MyCmdExecutor ();                                              
    ...
}

The ASQLDataGridStatBuilder class

Here comes the meat of the component – the SQL statement (auto) builder class. Just like the command executor, if you don’t specify any value to the ASQLBuilder property of the ASQLDataGrid, it will create a default ASQLDataGridStatBuilder instance and use it to construct the SQL statements. Let’s see what does this default

ASQLDataGridStatBuilder 

object do. Here is how the BuildSelect method of it (which returns the whole Select statement) looks like:

public virtual string BuildSelect(ASQLDataGrid dg)
{
    return  "select " + SEL_BuildListOfColumns(dg) + " from " + SEL_BuildFromClause(dg) + SEL_BuildWhereClause(dg)
                + SEL_BuildGroupByClause(dg) + SEL_BuildHavingClause(dg) + SEL_BuildOrderByClause(dg);
}

The statement builder atomized the select statement into smaller clauses: the List of columns, the From and the Where condition, the GroupBy, the Having and the OrderBy clauses. By default the Where,GroupBy,Having and the OrderBy clauses returned empty strings. The From clause is extracts from the ID of the DataGrid by taking all the characters after the 1st underline character in it (if exists). It means when working with DB table name Employees, we should assign to our ASQLDataGrid with ID something like DG_Employees for instance. The SEL_BuildListOfColumns method concatenates all the strings returned by the GetDBColumnName method for each column with the comma separators between the tags while the

GetDBColumnName 

in its turn returns the DataField or

DataTextField 

of the BoundColumn/HyperLinkColumn. If you are using TemplateColumns you should write your own Statement Builder and specify the correct DB column as I will show you later. Note that I implemented the statement builder class following the syntax of the select statement (and other statements in the 2nd article) of MS Trans-SQL. You may have to alter it and implement your own version of statement builder to suit your needs (e.g. working with other RDBMS).

The sample project works with the Employees table from the MS NorthWind database. To have a datagrid which will show you the list of the current employees, all you have to do is to have an ASQLDataGrid with ID = DG_Employees and assign the DataField property of each BoundColumn to the corresponding DB column’s name (EmployeeID, TitleOfCourtesy, FirstName, LastName, BirthDate, Address and ReportsTo for instance). The Statement Builder will help us to construct the Select statement which will be:

select EmployeeID, TitleOfCourtesy, FirstName, LastName, BirthDate, Address, ReportsTo from Employee

Add the Populate command button as I showed earlier and that’s it – we have a working datagrid application. Very simple to use, isn’t it? Now we will see how we can alter this default statement builder to improve our sample application. By now, the ReportsTo column contains EmployeeIDs (of whom the employee has to report to) and we want to display the names of the persons instead of the IDs. To do it, we have to join the Employee DB table to itself in the select statement. So we inherit our own Statement Builder from the ASQLDataGridStatBuilder class like this:

public class ASQLBuilder1 : ASQLDataGridStatBuilder
{
    public override string  SEL_BuildFromClause(ASQLDataGrid dg)
    {
        return "Employees E1 left join Employees E2 on E1.ReportsTo = E2.EmployeeID";
    }
    public override string GetDBColumnName(TypeOfSQLStatement stat, ASQLDataGrid dg, int ColumnIndex)
    {
        if (ColumnIndex == 6)
        {
            //ReportsTo
            if (stat == TypeOfSQLStatement.Select)
            {
                return "(E2.FirstName + ' ' + E2.LastName) as ReportsTo";
            }
            else
                return base.GetDBColumnName(stat, dg, ColumnIndex);
        }
        else
        {
            if (stat == TypeOfSQLStatement.Select)
            {
                //Qualifying all the column with the (E1) table name
                string DBColName  = base.GetDBColumnName(stat, dg, ColumnIndex);
                if (DBColName.Length>0)
                    return "E1." + DBColName;
                else
                    return "";
            }
            else
                return base.GetDBColumnName(stat, dg, ColumnIndex);
        }
    } 

As you see, we override the SEL_BuildFromClause method of the Statement Builder and now it returns the join of two Employee tables. Also, we have to qualify all the column names with the table name and displaying the ReportsTo person name instead of the ID by overriding the

GetDBColumnName 

method as shown. And now, the newly developed Statement Builder will help us to construct the final Select statement:

select E1.EmployeeID,E1.TitleOfCourtesy,E1.FirstName,E1.LastName,E1.BirthDate,E1.Address,(E2.FirstName + ' ' + E2.LastName) as ReportsTo 
        from Employees E1 left join Employees E2 on E1.ReportsTo = E2.EmployeeID

Practically, by overriding suitable method(s) of the

ASQLDataGridStatBuilder 

class, you can have a Statement Builder which is capable to build very complicated SQL statements necessary to your real applications. After writing your own Statement Builder, don’t forget to assign it to the DataGrid in a similar way as I showed with the Command Executor earlierly:

private void Page_Load(object sender, System.EventArgs e)
{
    ...
    DG_Employees.ASQLBuilder = new ASQLBuilder1();
    ...
}

The final note is that you can even use one Statement Builder to serve multiple ASQLDataGrid. Sometimes it can reduce the developing time.

Conclusion

The ASQLDataGrid presented here is a useful component when working with RDBMSs. It can reduce the time of development; help you to focus on the real business logic instead of dealing with SQL syntaxes. I will complete this class with the auto-construction of Insert, Update and Delete statements in the 2nd article.

Happy programming!

History

• 08, August 2005 - Initial version.