Sql.Net
 
About
» What is Sql.Net
» Benefits
» Features
» Examples
» Licensing
» Support
» Contact Us
 
Documentation
» Users Guide
» Reference
 
Download
» Download
 
Commercial License
» Purchase

 

 

 

 

 

SourceForge.net Logo

Users Guide

Table of Contents

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

Topics

Begin working with Sql.Net

  • Download and Install Sql.Net
  • Open a VS.NET project which you intend to use Sql.Net for
  • In the VS.NET project, add a reference to Reeb.SqlOM.dll located in Sql.Net installation folder
  • Consult the Object Model Reference and this Users Guide in Sql.Net start menu or on this site

Basic SELECT query

Sql.Net supports most of SQL constructs. The following example will demonstrate some of the framework's features to give you an idea how Sql.Net works. Full list of features can be found in Object Model Reference and some more examples can be found in the Tests project.
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

Sql.Net 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

Sql.Net 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 Sql.Net 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.
See "Using sp_executesql" on MSDN for more information about parameterized queries on SQL Server.

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)

Sql.Net Reporting Framework includes support for dynamic Cross-Tabs (aka Pivot Tables). Sql.Net 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
orderId productId customerId date quataty
1 1 1 2004-12-30 12
2 1 2 2004-12-31 2
3 2 2 2005-12-15 80
4 2 1 2003-01-01 27
5 1 1 2003-01-02 8

into the following Cross-Tab:

customerId IsTotal before2004 y2004 after2004 product1 product2
1 0 35 12 NULL 20 27
2 0 NULL 2 80 2 80
NULL 1 35 14 80 22 107

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("before2004", 
	new Range(null, currentYear.Add(-1).PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("y2004", 
	new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("after2004", 
	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), "y2004");