Writing (object oriented) queries
Any OR/M tool which abstracts multiple RDBMS needs a "RDBMS neutral language" which allows
the programmers to express queries. When i started thinking about this in
DataBlock one of my first thoughts was : I'll implement OQL for DataBlock.
Except there is a slight problem with OQL......nobody seem to give a rat's
ass about it. The "regular" .NET programmer doesn't even know what OQL is.
So i had to skip OQL (for now....if there are enought request i might add support for OQL) and i had implemented an OO way to express queries in DataBlock.It looks like this :
Product prod = new Product(); //the mapped object.
QueryCriteria qc = new QueryCriteria(prod); //query criteria
The above will select all the fields from the Product table. To select partial
fields you can use:
QueryCriteria qc = new QueryCriteria(prod.TableName, prod.TableFields[0],prod.TableFields[1]);
Adding criterias to the query is simple :
qc.Add(CriteriaOperator.Smaller, prod.TableFields[0], 36);
qc.Add(CriteriaOperator.Higher, prod.GetField("SupplierId"), 50);
qc.Add(CriteriaOperator.Like, prod.TableFields[1], "Chai");
The query criteria above "translates" into the following SQL:
SELECT Products.ProductID, Products.ProductName, Products.SupplierId, Products.
CategoryId, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Products
WHERE
Products.ProductId < 36 AND
Products.SupplierId > 50 AND
Products.ProductName LIKE 'Chai%'
Another feature of the QueryCriteria is field aliases. You can express this with :
qc.AddAlias(prod.GetField("Name"), "ProductName");
The last feature i would like to write about is the join feature. Of course the
query criteria must allow joins. Let's suppose that the Product table has a 1:m
relation with the Suppliers. The query criteria can look like
Suppliers sp = new Suppliers();
QueryCriteria qcSupplier = new QueryCriteria(sp);
QueryCriteria qcProduct = new QueryCriteria(prod);
qcProduct.AddJoin(JoinType.Inner, prod.GetField("SupplierId"), sp.GetFiled("SupplierId"), qcSupplier);
It's quite simple. We add the child criteria (qcSupplier) to the criteria of the main
tale (qc). The result is a join between the two tables.
So there you have....the result is a nice, clean, object oriented way of writing queries.