Build various SQL elements from scratch

Generate SQL query dynamically is very useful if your application was driven by end users, and you can’t determine what’s kind of SQL query will be executed at design time, a typical use case is a user driven report application, in such an application, user choose what’s kind of data want to be retrieved, and what’s kind of condition need to be meet. Your application should generate SQL query dynamically based on end users decision.

Here is a code snippet in C# that build a simple select statement with where condition. You can download buildsql demo to check how to build more complicated SQL statement in your application.

pseudo code in C#:

        public static void BuildSimpleSelect()
        {
            TSelectSqlStatement statement = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement.IsParsed = true;

            TLzField column = null;
            TLzJoin join = null;

            column = new TLzField(statement);
            column.FieldName = "ename";
            column.FieldPrefix = "e";
            column.aliasclause = new TLz_AliasClause(statement);
            column.aliasclause._aliastoken = new TSourceToken(statement);
            column.aliasclause._aliastoken.AsText = "empname";
            statement.Fields.Add(column);

            column = new TLzField(statement);
            column.FieldName = "id";
            column.FieldPrefix = "d";
            column.aliasclause = new TLz_AliasClause(statement);
            column.aliasclause._aliastoken = new TSourceToken(statement);
            column.aliasclause._aliastoken.AsText = "deptid";
            column.aliasclause._withas = true;
            statement.Fields.Add(column);
            
            join = new TLzJoin(statement);
            join.JoinTable = new TLzTable(statement);
            join.JoinTable.TableName = "emp";
            join.JoinTable.AliasClause = new TLz_AliasClause(statement);
            join.JoinTable.AliasClause._aliastoken = new TSourceToken(statement);
            join.JoinTable.AliasClause._aliastoken.AsText = "e";
            statement.JoinTables.Add(join);
            
            join = new TLzJoin(statement);
            join.JoinTable = new TLzTable(statement);
            join.JoinTable.TableName = "dept";
            join.JoinTable.AliasClause = new TLz_AliasClause(statement);
            join.JoinTable.AliasClause._aliastoken = new TSourceToken(statement);
            join.JoinTable.AliasClause._aliastoken.AsText = "d";
            statement.JoinTables.Add(join);
            
            statement.WhereClauseText = "e.deptid = d.id";
            Console.WriteLine("\n{0}", statement.AsText);
        }

SQL generated by this code snippet:

SELECT e.ename empname,
       d.id    AS deptid
FROM   emp e,
       dept d
WHERE  e.deptid = d.id 

Download buildsql demo: C# version