Thursday, February 23, 2017

How NDO Works » Queries

Register  Login


 Queries in NDO  


Objects only! No RowSets, DataSets or other relational constructs

  •  SQL like Syntax
  •  Field name checking with the C# or VB Compiler
  • Optional SQL pass-through queries

In NDO you don't need to work with RowSets or similar data oriented constructs. You work with ordinary .NET containers like ArrayList or List<T> (and even with your own containers). Here is an example of a simple query in NDO:

Query q = persistenceManager.NewQuery(typeof (Employee));
IList l = q.Execute();  // l contains Employee objects  

Here's the same query with generics:

NDOQuery<Employee> q = new NDOQuery<Employee>(pm);
List<Employee> l = q.Execute();  // l contains Employee objects

With typeof (Employee) or the generic parameter type <Employee> you tell NDO that you want to fetch objects of type Employee. We didn't use a condition string in this query, so the query will give us back all existing Employee objects.

Condition strings follow the syntax of the SQL WHERE clauses. But instead of column names you provide the names of the persistent fields mapped to database columns.

Query q = persistenceManager.NewQuery(typeof (Employee), "income > 50000");
IList l = q.Execute();  // l contains Employee objects

With generics the same query looks like that:

NDOQuery<Employee> q = new NDOQuery<Employee>(pm, "income > 50000");
List<Employee> l = q.Execute();

Income is the name of a private field of the class Employee (please note that future versions of NDO will also allow the mapping of properties).

To be sure that your query doesn't contain a typo, you can use the QueryHelper classes, which are generated by the NDO enhancer. This example shows the same query using QueryHelpers:

Employee.QueryHelper qh = new Employee.QueryHelper();
Query q = persistenceManager.NewQuery(typeof (Employee), qh.income + " > 50000");
IList l = q.Execute();

The QueryHelpers of course work with generic queries, too.

If the class Employee doesn't have a field income, the compiler will show an error. The QueryHelper classes also help to express complex queries:

Customer.QueryHelper qh = new Customer.QueryHelper();
Query q = persistenceManager.NewQuery(typeof (Customer), + " = 'Shirt'");
IList l = q.Execute();  // l contains Customer objects

This creates the following query:

SELECT Customer.* FROM Customer, Orders, OrderItem, Product WHERE
(Orders.IDCustomer = Customer.ID AND OrderItem.IDBestellung = Orders.ID
AND Product.ID = OrderItem.IDProduct) AND Product.Name = 'Shirt'

If the NDO query language is not sufficient for the queries you need, you can optionally use SQL pass-through queries. The SQL queries should give back result sets which are compatible with the mapping information of the class you want to query:

 NDOQuery<Employee> q = new NDOQuery<Employee>(pm, "SELECT * FROM Employees WHERE income > 50000", false, Query.Language.Sql);
List<Employee> l = q.Execute(); // l contains Employee objects