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.<\/p>\n
\nHere 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.\n<\/p>\n
\npseudo code in C#:\n<\/p>\n
\r\n public static void BuildSimpleSelect()\r\n {\r\n TSelectSqlStatement statement = new TSelectSqlStatement(TDbVendor.DbVMssql);\r\n statement.IsParsed = true;\r\n\r\n TLzField column = null;\r\n TLzJoin join = null;\r\n\r\n column = new TLzField(statement);\r\n column.FieldName = \"ename\";\r\n column.FieldPrefix = \"e\";\r\n column.aliasclause = new TLz_AliasClause(statement);\r\n column.aliasclause._aliastoken = new TSourceToken(statement);\r\n column.aliasclause._aliastoken.AsText = \"empname\";\r\n statement.Fields.Add(column);\r\n\r\n column = new TLzField(statement);\r\n column.FieldName = \"id\";\r\n column.FieldPrefix = \"d\";\r\n column.aliasclause = new TLz_AliasClause(statement);\r\n column.aliasclause._aliastoken = new TSourceToken(statement);\r\n column.aliasclause._aliastoken.AsText = \"deptid\";\r\n column.aliasclause._withas = true;\r\n statement.Fields.Add(column);\r\n \r\n join = new TLzJoin(statement);\r\n join.JoinTable = new TLzTable(statement);\r\n join.JoinTable.TableName = \"emp\";\r\n join.JoinTable.AliasClause = new TLz_AliasClause(statement);\r\n join.JoinTable.AliasClause._aliastoken = new TSourceToken(statement);\r\n join.JoinTable.AliasClause._aliastoken.AsText = \"e\";\r\n statement.JoinTables.Add(join);\r\n \r\n join = new TLzJoin(statement);\r\n join.JoinTable = new TLzTable(statement);\r\n join.JoinTable.TableName = \"dept\";\r\n join.JoinTable.AliasClause = new TLz_AliasClause(statement);\r\n join.JoinTable.AliasClause._aliastoken = new TSourceToken(statement);\r\n join.JoinTable.AliasClause._aliastoken.AsText = \"d\";\r\n statement.JoinTables.Add(join);\r\n \r\n statement.WhereClauseText = \"e.deptid = d.id\";\r\n Console.WriteLine(\"\\n{0}\", statement.AsText);\r\n }\r\n<\/pre>\nSQL generated by this code snippet:<\/p>\n
\r\nSELECT e.ename empname,\r\n d.id AS deptid\r\nFROM emp e,\r\n dept d\r\nWHERE e.deptid = d.id \r\n<\/pre>\n