Using Miado – Part 6
In my last post, I discussed how to use DataSet objects with Miado. In this post, I will tackle a common problem – building dynamic queries.
ISqlQuery Interface
I have created an ISqlQuery interface that can be used to run dynamic queries. The interface is quite simple. It exposes three properties – IsBuilt, Parameters, and Sql – and one method – Build().
1: public interface ISqlQuery
2: {
3: bool IsBuilt { get; }
4: string Sql { get; }
5: IDictionary<string, object> Parameters { get; }
6: void Build();
7: }
The idea behind this design is for the Sql property represents the SQL that will eventually be run against the database. It can be built up dynamically based on the filtering that is applied to the object. The same goes for the Parameters property. It represents the DB parameters that will be bound to the Sql statement. Parameters can be added to this property in the same way they are added to a IDbStatement. By doing so, you can still safely use parameterized queries (as opposed to dynamically substituting the variables straight in the SQL string – i.e. String.Format(“and Name=’{0}’”, name), which exposes a risk for SQL Injection attacks).
The Build() method should be the place where you put your logic to dynamically create the SQL and populate the parameters.
1: public class CustomerQueryByCityAndState : ISqlQuery
2: {
3: private const string SELECT_CUSTOMERS_BASE_SQL =
4: "select CustomerID, Name, Address1, Address2, City, County, " +
5: "StateCode, ZipCode " +
6: "from Customers " +
7: "where 1=1 "; // etc...
8:
9: public CustomerQueryByCityAndState(string city, string state)
10: {
11: City = city;
12: State = state;
13: Parameters = new Dictionary<string, object>();
14: }
15:
16: public string City { get; private set; }
17: public string State { get; private set; }
18:
19: public bool IsBuilt { get; private set; }
20: public string Sql { get; private set; }
21: public IDictionary<string, object> Parameters { get; private set; }
22:
23: public void Build()
24: {
25: var sb = new StringBuilder();
26: sb.Append(SELECT_CUSTOMERS_BASE_SQL);
27:
28: // add City
29: sb.Append(" and City = @City ");
30: Parameters.Add("City", City);
31:
32: // add State
33: sb.Append(" and StateCode = @State ");
34: Parameters.Add("State", State);
35:
36: // Populate the SQL
37: Sql = sb.ToString();
38:
39: // info
40: IsBuilt = true;
41: }
42: }
Though it’s not actually being for used anything now, the last step of the Build() method should flip the IsBuilt property to True. This is purely for information purposes at this point, but it could be informative to the calling class.
SqlQuery Abstract Class
To simplify things, I have created a base class you can use that implements the ISqlQuery interface. It manages the Sql and the Parameters, and will automatically flip the IsBuilt property when the Build() method is called. The only thing a derived class must do is implement the DoBuild() method, which is a template method to actual perform the logic that was previously implemented in Build(). It should contain the logic of dynamically creating the SQL and Parameters.
1: protected abstract void DoBuild();
2:
3: public virtual void Build()
4: {
5: DoBuild();
6: IsBuilt = true;
7: }
So the previous example above becomes much simpler…
1: public class CustomerQueryByCityAndState : SqlQuery
2: {
3: private const string SELECT_CUSTOMERS_BASE_SQL = ...
4:
5: public CustomerQueryByCityAndState(string city, string state)
6: {
7: City = city;
8: State = state;
9: }
10:
11: public string City { get; private set; }
12: public string State { get; private set; }
13:
14: public override void DoBuild()
15: {
16: var sb = new StringBuilder();
17: sb.Append(SELECT_CUSTOMERS_BASE_SQL);
18:
19: sb.Append(" and City = @City ");
20: Parameters.Add("City", City);
21:
22: sb.Append(" and State = @State ");
23: Parameters.Add("State", State);
24:
25: Sql = sb.ToString();
26: }
27: }
The disadvantage is that inheriting from this class binds you into an inheritance hierarchy, which may not be a good thing (see inheritance vs. composition). If you need to maintain a pure inheritance hierarchy, use the interface instead, or hide the implementation of this class in an internal Property.
Filter Objects
In many cases, you probably expose a common lightweight object to the UI layer for representing a “filter” to the database. In reality, this is nothing more than a simple Data Transfer Object (DTO) that has properties representing all the ways the data can be filtered.
A common pattern I use is to take a Filter DTO object into the Query object’s constructor and build the SQL from it dynamically:
1: public class SiteLocatorQuery : SqlQuery
2: {
3: private const string SELECT_SITES_SQL = ..
4:
5: public SiteLocatorQuery(SiteLocatorFilter filter)
6: {
7: Filter = filter;
8: }
9:
10: public SiteLocatorFilter Filter { get; private set; }
11:
12: public override void DoBuild()
13: {
14: var sb = new StringBuilder();
15: sb.Append(SELECT_SITES_SQL);
16:
17: if ( Filter.IsRecent )
18: {
19: sb.Append(" and IsRecent = @IsRecent ");
20: Parameters.Add("IsRecent", 1);
21: }
22: if ( Filter.BrandID > 0 )
23: {
24: sb.Append(" and BrandID = @BrandID ");
25: Parameters.Add("BrandID", filter.BrandID);
26: }
27:
28: // etc...
29:
30: Sql = sb.ToString();
31: }
32: }
This has the advantage of hiding the complexity of how the SQL is built up (or the fact that we are using raw SQL at all) from the UI layer. I normally isolate my “Query” objects to a namespace in my MyCompany.Data dll, requiring a reference to the Filter objects (and not the other way around). Plus, the light-weight DTOs can be used to pass data in and out of the UI layer very easily, as in the case of using messaging or web services.
So I’ve got my ISqlQuery interface coded … how do I use it?
Once you have a reference to the IDatabase interface, you can simply call the RunningQuery(ISqlQuery query) method to get a reference to an IDbStatement interface. The method will call the Build() method on your interface and use the Sql and Parameters properties to populate the IDbStatement appropriately. Once you get the IDbStatement back, you can then proceed just as you would have otherwise.
1: public IEnumerable<Customer> FindCustomersByFilter(CustomerFilter filter)
2: {
3: var query = new CustomerQuery(filter);
4: this.Database
5: .RunningQuery(query)
6: .QueryForResults(row => new Customer()); // row mapping omitted
7: }
Saturday, May 16, 2009 10:13:47 PM (Eastern Standard Time, UTC-05:00) 
Miado