by Stephen Trembath
Just a quick summary of the DOCollection methods and how they work at the query level.
I will add to documentation eventually once I refine this a bit more, and maybe reduce the number of methods:
Take for example a CustomerCollection, comprised of Customer DataObjects. For the below examples the query will find customers with FirstName=Stephen
SetCurrentQuery
Simply sets a SqlBuilder as the query to run on the DOCollection – does not actually run any SQL.
To run the query you have to use one of the below methods. If you do not run any of the below methods, and try to access DataObjects within the collection, it will default to running the Find() method.
Find
For the current query, selects all IDs:
e.g. SELECT CustomerID FROM Customer WHERE FirstName=’Stephen’
Only when the actual Customer objects are accessed is the DataObject loaded via a new SQL query:
e.g. if you do custColl(0) it will run: SELECT Customer.* FROM Customer WHERE CustomerID = ‘……’
Iterating through a large collection, can result in a lot of queries – i.e. one to select all IDs, and one to select details of each DataObject you access.
Fetch
Retrieves all data for all matching rows in the query:
e.g. SELECT Customer.* FROM Customer WHERE FirstName=’Stephen’
This returns more data, but there is no need for follow-up queries to retrieve customer information.
FindAll
Works like a Find, but disregards the current query, and instead finds all matching IDs within the table
e.g. SELECT Customer.CustomerID FROM Customer
Should be used sparingly.
FetchAll
Works like a Fetch, but disregards the current query, and instead finds all matching IDs within the table
e.g. SELECT Customer.* FROM Customer
Should be used sparingly.
Count
If the DOCollection has been populated by a Find/Fetch etc, this will just return the number of rows from the collection itself.
If the DOCOllection has not been populated, this will do a SELECT Count(*) FROM Customer
FindRange
Returns a subset of the rows retrieved by the query. e.g. custColl.FindRange(50, 99) will skip the first 50 rows and return the following 50.
It does this by finding all IDs that match the query, while using a SELECT TOP to minimize the upper range
(e.g. SELECT TOP 100 CustomerID FROM Customer WHERE FirstName=’Stephen’)
It then manually removes in code the IDs that are not needed, e.g. the first 50 IDs in this case.
After that, accessing each DataObject will result in a SELECT Customer.* FROM Customer WHERE CustomerID = ‘……’ type query.
FetchRange
Similar to FindRange() except that it does a SELECT(*) to retrieve all data, e.g. custColl.FetchRange(50, 99) will skip the first 50 rows and return the following 50.
(e.g. SELECT TOP 100 Customer.* FROM Customer WHERE FirstName=’Stephen’)
It then manually removes in code the DataObjects that are not needed, e.g. the first 50 DataObjects in this case.
Generally not recommended.
FetchSelected
This is a new method that is still under development for general use, and needs to be defined better. It is best used where performance is crucial, and where the query is not overly complex.
It is similar to FindRange but uses SQL Server 2005′s built-in paging for efficiency, using the ROW_NUMBER function:
http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
It also only selects the columns explicitly entered in the SqlBuilder (you can still add a SELECT *)
However, with that comes a greater chance for limitations in queries, and should only be used for now where fullt tested.
The ROW_NUMBER function will only return from the database the actual rows that are required, and there is no need for manual removal in code.
e.g. Case 1:
Dim mySql as New SqlBuilder(“Customer”)
mySql.addSelect(Customer.
Columns.CustomerID)
mySql.addSelect(Customer.Columns.FirstName)
mySql.addSelect(Customer.Columns.LastName)
mysql.addWhere(Customer.Columns.FirstName, ‘Stephen’)
custColl.FetchSelected(50, 99)
The query will be similar to the following, but using the ROW_NUMBER syntax to retrieve the desired rows:
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName FROM Customer WHERE Customer.FirstName=’Stephen’
e.g. Case 2:
Dim mySql as New SqlBuilder(“Customer”)
mySql.addSelectAll()
mysql.addWhere(Customer.Columns.FirstName, ‘Stephen’)
custColl.FetchSelected(50, 99)
The query will be similar to the following, but using the ROW_NUMBER syntax:
SELECT * FROM Customer WHERE Customer.FirstName=’Stephen’
