SqlOM 1.0.1

What is SqlOM

Dynamic SQL generator

SqlOM is a software component which allows you to programmatically create SQL queries in runtime using a convenient .Net Core object model, thus creating an abstraction layer over SQL. Dynamic SQL generation is useful in several scenarios:

• Generate SQL dynamically when query structure is not known at development time (i.e. user defined reports or filters)
• Generate SQL dynamically when database structure is not known at development time (i.e. user defined tables or fields)
• Create a database independent data layer

SqlOM automates the process of SQL generation in a dynamic, convenient, time saving, database independent way.

Supported Databases

Currently the following databases are supported. We continuously add support for additional databases. If you your database is not on the list, contact us or tweak the source code on your own to add the desired functionality.

• SQL Server
• Oracle
• MySql
• MariaDB
• Sqlite

Users Guide

1. Basic SELECT query
2. Rendering a query
3. Complex WHERE conditions
4. Complex JOINs
5. CASE expressions
6. UNIONs
7. Paging
8. Parameterized queries
9. Cross-Tabs (Pivot Tables)
10. Cross-Tab Drill-Down

Basic SELECT query

SqlOM supports most of SQL constructs. The following example will demonstrate some of the framework's features to give you an idea how SqlOM works.

FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");

SelectQuery query = new SelectQuery();

query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("name", tProducts));
query.Columns.Add(new SelectColumn("price", tProducts));

query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");

query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
	SqlExpression.Field("name", tCustomers), 
	SqlExpression.String("John"), 
	CompareOperator.Equal));


query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending));

Rendering a query

After you create and configure a SelectQuery object you would probably want to render it into a SQL statement that can be executed on your database. In order to generate a statement, create a "renderer" object which suits your database, configure it and call one of the RenderXXX methods. Currently the following "renderers" exist: SqlServerRenderer, OrcaleRenderer and MySqlRenderer. The configuration stage is optional since all renderers are configured to be used with default database configuration by default.

SelectQuery query = new SelectQuery();
...
string sql = new SqlServerRenderer().RenderSelect(query);

Complex WHERE conditions

SqlOM supports comparison operators ( <, ⇐, >, >=, ==, !=), BETWEEN clause, bitwise AND operator, IN/NOT IN clause, EXISTS/NOT EXISTS clause and IS NULL/IS NOT NULL clause. All conditions operate on SqlExpression objects which can represent database fields, constants, sub queries and other SQL expressions.
Conditions are encapsulated in WhereTerm objects which compose a WhereClause object which applies a logical operator (AND or OR) on the contained conditions. WhereClause objects can contain other WhereClause objects with different logical operators.  Hopefully, the following example will insert some clarity into issue:

query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
	SqlExpression.Field("name", tCustomers), 
	SqlExpression.String("John"), 
	CompareOperator.Equal));

WhereClause group = new WhereClause(WhereClauseRelationship.Or);

group.Terms.Add(WhereTerm.CreateBetween(
	SqlExpression.Field("price", tProducts), 
	SqlExpression.Number(1), 
	SqlExpression.Number(10)));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), 
	SqlConstantCollection.FromList(new string\[\] {"Nail", "Hamer", "Skrewdriver"})));
group.Terms.Add(WhereTerm.CreateNotIn(
	SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateExists("select productId from products"));

query.WherePhrase.SubClauses.Add(group);

Complex JOINs

While most joins are based one a single equality term (t1.key = t2.fkey), some situations require more complex conditions. In order to specify more then one equality condition you can create the required number of JoinCondition objects and use an appropriate FromClause.Join method overload. 

query.FromClause.Join(JoinType.Left, tCustomers, tOrders, 
	new JoinCondition("customerId"), 
	new JoinCondition("customerId"));

If you wish to specify other (then equality) kinds of conditions, create and configure a WhereClause object and use a FromClause.Join method which accepts a WhereClause object.

WhereClause condition = new WhereClause(WhereClauseRelationship.Or);
condition.Terms.Add(WhereTerm.CreateCompare(
	SqlExpression.Field("productId", tOrders), 
	SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
condition.Terms.Add(WhereTerm.CreateCompare(
	SqlExpression.Field("orderId", tOrders), 
	SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition);

CASE expressions

SqlOM supports CASE expressions in SELECT column list, WHERE clause and other clauses which use the SqlExpression object. To define a CASE expression, create a CaseClause object, populate its Terms collection with Condition-Value pairs, set the default value using the ElseValue property and use SqlExpression.Case method to create the expression.

CaseClause caseClause = new CaseClause();
caseClause.ElseValue = SqlExpression.Null();

caseClause.Terms.Add(new CaseTerm(condition1, SqlExpression.Field("field1")));
caseClause.Terms.Add(new CaseTerm(condition2, SqlExpression.Field("field2")));
caseClause.Terms.Add(new CaseTerm(condition3, SqlExpression.String("constant string")));

query.Columns.Add(new SelectColumn(SqlExpression.Case(caseClause), "caseCol"));

UNIONs

In order to create a UNION statement, create a SqlUnion object, populate it with SelectQuery objects and use ISqlOMRenderer.RenderUnion method to generate the SQL.

SqlUnion union = new SqlUnion();

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");

union.Add(query);

query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");

union.Add(query, DistinctModifier.All);

string sql = new SqlServerRenderer().RenderUnion(union);

Paging

In order to fetch paged results, create and configure a SelectQuery object and use the ISqlOMRenderer.RenderPage method instead of ISqlOMRenderer.RenderSelect to generate SQL. Current version of SqlOM uses a paging technique which requires the total number of rows the query would produce if not paged. You can apply ISqlOMRenderer.RenderRowCount method on the same query to generate SQL which counts the total number of rows. Additionally, the SelectQuery object must be ordered for paging to work.

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers");
query.OrderByTerms.Add(new OrderByTerm("name", null, OrderByDirection.Descending));

SqlServerRenderer renderer = new SqlServerRenderer();
string rowCountSql = renderer.RenderRowCount(query);
int totalRows = (int)ExecuteScalar(rowCountSql);
string sql = renderer.RenderPage(pageIndex, pageSize, totalRows, query);

Parameterized queries

You might want to parameterize your queries to achieve better performance. There is a high chance that SQL Server will reuse an execution plan if the same query is executed repeatedly and when parameters are used, SQL Server can reuse the same execution plan for all parameter values, achieving performance similar to stored procedures. Keep in mind that SQL Server can only cache a plan when all object names (tables, views)  are fully qualified. In order to create fully qualified object names use FromTerm.Table(tableName, alias, ns1, ns2) method to create FromTerm objects or omit the namespaces and use SelectQuery.TableSpace property to set a mutual namespace for all FromTerm objects used in the query.
Use SqlExpression.Parameter(name) method to insert parameter references into your query. Then, populate IDbCommand.Parameters collection with parameter values.

FromTerm tCustomers = FromTerm.Table("customers");

SelectQuery query = new SelectQuery();
query.TableSpace = "SqlOM.dbo";
query.Columns.Add(new SelectColumn("name", tCustomers));
query.FromClause.BaseTable = tCustomers;
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
	SqlExpression.Parameter("@pName"), 
	SqlExpression.Field("name", tCustomers), CompareOperator.Equal ) );

string sql = new SqlServerRenderer().RenderSelect(query);
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@pName", "John");
command.ExecuteNonQuery();

Cross-Tabs (Pivot Tables)

SqlOM Reporting Framework includes support for dynamic Cross-Tabs (aka Pivot Tables). SqlOM goes beyond simple Cross-Tab transformation and supports specifying pivot range (specifying a range rather then scalar pivot values), pivoting a query on more then one column and Drill-Down (getting the rows which combine a specific Cross-Tab cell value).

In order to explain how to create a Cross-Tab we will walk through an example of transforming the following data:

select * from orders

into the following Cross-Tab:

The "orders" table records which customer purchased how many items of a specific product and when. The Cross-Tab displays how many items were purchased by customers on a year scale, and product scale. The last row displays the total for each column.

The following code generates SQL which produces the Cross-Tab above:

PivotTable pivot = new PivotTable();
pivot.BaseSql = "select * from orders";
pivot.Function = SqlAggregationFunction.Sum;
pivot.ValueField = "quantaty";
pivot.RowField = "customerId";

PivotColumn pivotCol = new PivotColumn("date", SqlDataType.Date);
TimePeriod currentYear = TimePeriod.FromToday(TimePeriodType.Year);
pivotCol.Values.Add(PivotColumnValue.CreateRange("before2023", 
	new Range(null, currentYear.Add(-1).PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("y2023", 
	new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("after2023", 
	new Range(currentYear.PeriodStartDate, null)));
pivot.Columns.Add(pivotCol);

pivotCol = new PivotColumn("productId", SqlDataType.Number);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1));
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product2", 2));
pivot.Columns.Add(pivotCol);

SelectQuery pivotQuery = pivot.BuildPivotSql();

BaseSql property determines the data to be transformed. Function and ValueField properties determine how Cross-Tab cell values are to be calculated. RowField property determines how to group the data. At least one PivotColumn is required to specify which data column needs to be pivoted. Each PivotColumnValue results in a column in the Cross-Tab. PivotColumnValue's value can be scalar or range.

Cross-Tab Drill-Down

In order to generate Drill-Down SQL, issue BuildDrillDownSql(crossTabRowKey, crossTabColumnName) method on the same (or identical) PivotTable instance used to generate the Cross-Tab. You specify the drilled cell by providing the value of the RowField data column (specifying the row) and Cross-Tab column name (specifying the column). In order to drill down a total value, pass null into the first parameter.

SelectQuery drillDownQuery = pivot.BuildDrillDownSql(SqlConstant.Number(1), "y2023");