Saturday, September 08, 2007
Query Simplification
As some of you may know (although not all... which is what we are trying to address), the Milos DataService classes provide a simple way to query multiple records by multiple field names. For instance, if you want to query all customer with a last name of "Pronger" and a first name of "Chris" and active status, then you have been able to simply do this (in a business object):
string[] fieldNames = new string[] { "FirstName", "LastName", "IsActive" };
object[] parameters = new object[] { "Chris", "Pronger", true };
IDbCommand command = this.DataService.BuildQueryCommand("Customers", "*", fieldNames, parameters, DataRowProcessMethod.IndividualCommands);
DataSet customers = this.ExecuteQuery(command);
This is nice, but a lot of people do not know that this functionality exists. Besides, there really is no reason why people would have to know about this somewhat hidden method on the DataService. For this reason, we added a few convenience methods right on the business object that are easier to discover:
string[] fieldNames = new string[] { "FirstName", "LastName", "IsActive" };
object[] parameters = new object[] { "Chris", "Pronger", true };
IDbCommand command = this.GetQueryCommand("Customers", "*", fieldNames, parameters);
DataSet customers = this.ExecuteQuery(command);
Or, to make things simpler yet:
string[] fieldNames = new string[] { "FirstName", "LastName", "IsActive" };
object[] parameters = new object[] { "Chris", "Pronger", true };
DataSet customers = this.Query("Customers", "*", fieldNames, parameters);
There really is no particular reason for using GetQueryCommand() over Query(), unless you want to first retrieve the command object and then make further modifications on the object (which is hardly ever done). So just use Query().
The values in the field and filter list are all handled as "and" parameters. Also, all fields are used "as is" with no added wild cards. For instance, passing in "Pron" as the last name will not include "Pronger" in the result set. However, it is possible to pass in "Pron%" which will include "Pronger"s, assuming the database understands that wildcard character.
Posted @ 1:02 PM by Egger, Markus (markus@code-magazine.com)
Post a Comment:
Comment Title (required):
Your Name (optional):
Your Email (optional):
Your Web Site (optional):
Your Comment (required):