Sql.Net
|
||||
About » What is Sql.Net
» Benefits » Features » Examples » Licensing » Support » Contact Us Documentation »
Users Guide » Reference Download »
DownloadCommercial License
»
Purchase
|
Users GuideTable of Contents
TopicsBegin working with Sql.Net
Basic SELECT querySql.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 queryAfter 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 conditionsSql.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. 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 JOINsWhile 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 expressionsSql.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")); UNIONsIn 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); PagingIn 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 queriesYou 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. 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
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. 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-DownIn 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"); |