the Last Component

Querying

Persistent Datasets framework provides a special way for querying data from the relational database, called Typed Queries. The main focus of developing Persistent Datasets framework was to assist you in writing the most reliable code ever. Probably you have already mentioned how complicate it is to find a bug in an application, querying data from a database using SQL statements. Just modify some tables in the database with 100-200 tables and try to find out which SQL in your application does not function any more. You will need at list run the application and then find out all the places in your code where modified tables are used. Or do you prefer text search? If you use Typed Queries, you will never have this problem. Most of errors you will find at the compile-time!

If you have generated a persistent layer with the Domain Model, at least three classes will be generated for each table in the database. Suppose we have an Orders table, than we will get the following classes generated:

· OrderTable

· Order

· OrderQuery

Like OrderTable contains DataColumns and DataRelations of the database table ‘Order’, OrderQuery also contains this information. Like OrderTable contains one property of the DataColumn type for each field in the table ‘Orders’, OrderQuery also contains one typed property for each Orders’ field. But unlike OrderTable, these properties are not of the DataColumn type, but of a special OPathExpression type. These properties together with the properties of relations allow you to define query criteria and specify selection columns.

Each time you start writing a query, you have to choose a root table against which the query should be run. For example, we want to query Orders from the ‘Orders’ table:

OrderQuery query = new OrderQuery();

query.Criteria = query.CustomerID == "AFSJD";

Now create an instance of NorthwindModel and fill the Order table:

NorthwindModel northwind = new NorthwindModel();

northind.Orders.Fill(query);

The last line will execute the following SQL statement and fill Orders of the NorthwindModel model:

SELECT A0.[OrderID], A0.[CustomerID], A0.[EmployeeID], A0.[OrderDate], A0.[RequiredDate], A0.[ShippedDate], A0.[ShipVia], A0.[Freight], A0.[ShipName], A0.[ShipAddress], A0.[ShipCity], A0.[ShipRegion], A0.[ShipPostalCode], A0.[ShipCountry]

FROM [Northwind].[dbo].[Orders] A0

WHERE (A0.[CustomerID] = 'AFSJD')

Since the OPathExpression type and his inheritors overload operators, you can use the native C# syntax for building criteria.

OrderQuery query = new OrderQuery();

query.Criteria = query.CustomerID == "AFSJD" && query.OrderDate.Between(DateTime.Now.AddDays(-7), DateTime.Now);

NorthwindModel northwind = new NorthwindModel();

northind.Orders.Fill(query);

This is the SQL output:

FROM [Northwind].[dbo].[Orders] A0

WHERE ((A0.[CustomerID] = 'AFSJD') AND (A0.[OrderDate] BETWEEN '8/16/2005' AND '8/23/2005'))

Along with table fields Typed Queries contain references. If two tables in a database have a defined foreign key, both Typed Queries will have the corresponding reference property to a child or the parent table. Each reference property is also Typed Query. For example, the Orders table has a foreign key to the Employees table so the generated OrderQuery class will have the Employee property of the EmployeeQuery type. This feature allows you to use joins in queries. For example, you want to find all Orders that belong to the Employee with the FirstName field matching concrete criteria, than your query will look like this:

OrderQuery query = new OrderQuery();

query.Criteria = query.Employee.FirstName == "Jon";

NorthwindModel northwind = new NorthwindModel();

northind.Orders.Fill(query);

When executing this query, Persistent Datasets framework will automatically add a join between the Orders and Employees tables:

FROM [Northwind].[dbo].[Orders] A0

INNER JOIN [Northwind].[dbo].[Employees] A1 ON (A0.[EmployeeID] = A1.[EmployeeID])

WHERE (A1.[FirstName] = 'Jon')

Actually Typed Query allows you to build SQL statements of any complexity. Each Typed Query is inherited from the ObjectQuery type that has the following properties:

· ObjectQuery.Selection – collection that allows specifying the desired columns. When Typed Query is used for filling Domain Model, this collection can be left empty. In this case all columns of the PersistentDataTable type will be filled.

· ObjectQuery.Criteria - OPathExpression property allows specifying the WHERE clause of an SQL statement.

· ObjectQuery.OrderByList – order by collection

· ObjectQuery.GroupByList – group by collection

· ObhectQuery.HavingCriteria - OPathExpression property allows specifying the HAVING clause.

When filling Domain Models, you need not specify the columns to be selected. Since Domain Model and Typed Query are generated from the same mapping schema, the framework already knows the fields, which should be filled. But if you fill typed DataSets, you may need to specify explicitly binding between the query selection and DataColumn.

OrderQuery query = new OrderQuery();

query.Criteria = query.Employee.FirstName == "Jon";

OrdersDataSet orders = new OrdersDataSet();

query.Bind(orders.OrdersID, query.OrderID);

query.Bind(orders.EmployeeName, query.Emloyee.LastName);

query.Execute();

Even in the selection you can work with the joins. In the example above, for each selected Order, the EmployeeName field will be filled joining the Orders and Employee tables.

SELECT A0.[OrderID], A1.[LastName]

FROM [Northwind].[dbo].[Orders] A0

INNER JOIN [Northwind].[dbo].[Employees] A1 ON (A0.[EmployeeID] = A1.[EmployeeID])

WHERE (A1.[FirstName] = 'Jon')

When you write a query using the Typed Queries technology, you build an expression tree. At the root of your query is a Typed Query of the object, which you wish to select. When you navigate to the related tables using references you produce joins. The queries, joined by using references, automatically become Criteria, which specify the join condition (see the figure below).

Actually you can join two queries providing join criteria manually or extending an automatically added Criteria using the ObjectQuery.Criteria property of a joined query.

Conclusion.

Typed Query is a powerful class that can be used as table alias, select statement and data adapter at the same time.

Typed Queries technology offers users two possibilities to prepare your query.

· Using the Typed Query properties Selection, Criteria, GroupByList, OrderByList and HavingCriteria.

· Or using a continuous form that looks more like SQL.

There is an example of a complex query, which uses Group By. This example fills a typed DataTable.

OrderStatisticDataSet.OrderTotalPriceTable result = new OrderStatisticDataSet.OrderTotalPriceTable();

OrderDetailQuery query = new OrderDetailQuery();

query.Selection.Bind(result.OrderID, query.OrderID);

query.Selection.Bind(result.OrderDate, query.Order.OrderDate)

query.Selection.Bind(result.Customer, query.Order.Customer.ContactName);

query.Selection.Bind(result.Employee, query.Order.Employee.LastName);

query.Selection.Bind(result.TotalPrice, Sum(query.UnitPrice * query.Quantity.ToDecimal()));

query.Criteria =

query.Order.OrderDate.Between(dateFrom, dateTo) &&

query.Product.Supplier.Country == "USA" &&

query.Quantity > 1;

query.GroupByList.Add(query.OrderID,

query.Order.OrderDate,

query.Order.Customer.ContactName,

query.Order.Employee.LastName);

query.OrderByList.Add(query.OrderID);

query.Execute();

query.Selection.Bind call binds the corresponding DataColumn to a query expression.

query.Criteria specifies a WHERE condition for a query.

query.GroupByList specifies a GROUP BY clause.

query.OrderByList specifies an ORDER BY clause.

query.Execute() runs a query and fills DataTable.

The same query can be written using a continuous form that looks like this:

OrderStatisticDataSet.OrderTotalPriceTable result =

new OrderStatisticDataSet.OrderTotalPriceTable();

OrderDetailQuery query = new OrderDetailQuery();

query.Bind(result.OrderID, query.OrderID)

.Bind(result.OrderDate, query.Order.OrderDate)

.Bind(result.Customer, query.Order.Customer.ContactName)

.Bind(result.Employee, query.Order.Employee.LastName)

.Bind(result.TotalPrice, Sum(query.UnitPrice * query.Quantity.ToDecimal()))

.Where(

query.Order.OrderDate.Between(dateFrom, dateTo) &&

query.Product.Supplier.Country == "USA" &&

query.Quantity > 1

)

.GroupBy(query.OrderID,query.Order.OrderDate,

query.Order.Customer.ContactName,

query.Order.Employee.LastName).

.OrderBy(query.OrderID);

query.Execute();

Both queries above produce the following SQL query when you call the Execute method.

SELECT A0.[OrderID], A1.[OrderDate], A2.[ContactName], A3.[LastName], SUM((A0.[UnitPrice] * CONVERT(decimal, A0.[Quantity])))

FROM [Northwind].[dbo].[Order Details] A0

INNER JOIN [Northwind].[dbo].[Orders] A1 ON (A0.[OrderID] = A1.[OrderID])

INNER JOIN [Northwind].[dbo].[Customers] A2 ON (A1.[CustomerID] = A2.[CustomerID])

INNER JOIN [Northwind].[dbo].[Employees] A3 ON (A1.[EmployeeID] = A3.[EmployeeID])

INNER JOIN [Northwind].[dbo].[Products] A5 ON (A0.[ProductID] = A5.[ProductID])

INNER JOIN [Northwind].[dbo].[Suppliers] A4 ON (A5.[SupplierID] = A4.[SupplierID])

WHERE (((A1.[OrderDate] BETWEEN '01.08.1996' AND '30.08.1996') AND (A4.[Country] = 'USA')) AND (A0.[Quantity] > 1))

GROUP BY A0.[OrderID], A1.[OrderDate], A2.[ContactName], A3.[LastName]

ORDER BY A0.[OrderID]

It is the same query but we just used another form of building it up. The way the typed Queries function will be explained in the next topics.