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
Using Miado – Part 5
Better late than never, eh? Due to a crazy series of events in my life (both personally and professionally), I’ve had to put blogging on a hold for a short time. Well, I’m back baby! Today I’m going to continue my series on the latest Miado release.
In this post, I will discuss the use of DataSet (both generic and typed) with Miado.
Plain ol’ DataSet
If you really want to use a DataSet object, it’s dead-simple easy with Miado. It’s just like using the Miado API for the IDbStatement, except that you don’t need to map the Result Set to a custom business object. You just need to call the QueryForDataSet() method on the IDbStatement.
1: public DataSet FindBlogEntriesByBlog(int blogID)
2: {
3: return
4: this.Database
5: .ExecutingSql(SELECT_BLOG_ENTRIES_BY_BLOG_ID_SQL)
6: .AddParameter("BlogID", blogID)
7: .QueryForDataSet();
8: }
Typed DataSets
If you weren’t aware, Typed DataSet classes were introduced in 2.0 version of the .Net framework to compensate for some of the shortcomings of the original DataSet object. Typed DataSets inherit from the DataSet class, but allow for strong typing on the rows and columns contained in the DataSet. So instead of having to access the column values in the rows through a Dictionary key lookup, the column values now are exposed as strongly-typed properties on the Typed DataSet object itself.
On the QueryForDataSet() method detailed above, Miado exposes an optional generic typed version of the method that let’s you define the type of DataSet that will be returned. QueryForDataSet<T>() can be used to return a strongly-typed DataSet object since T is constrained to be a subclass of DataSet.
1: public BlogEntryDataSet FindBlogEntriesByBlog(int blogID)
2: {
3: return
4: this.Database
5: .ExecutingSql(SELECT_BLOG_ENTRIES_BY_BLOG_ID)
6: .AddParameter("BlogID", blogID)
7: .QueryForDataSet<BlogEntryDataSet>();
8: }
DataTable
Not surprisingly, you can also retrieve a DataTable from the IDbStatement instead of a DataSet if you are only interested in a single table’s result set. Simply call QueryForDataTable() instead of QueryForDataSet().
1: public DataTable FindBlogEntriesByBlog(int blogID)
2: {
3: return
4: this.Database
5: .ExecutingSql(SELECT_BLOG_ENTRIES_BY_BLOG_ID_SQL)
6: .AddParameter("BlogID", blogID)
7: .QueryForDataTable();
8: }
Thoughts on DataSet
I consider myself to be Alt.Net, so by law I’m required to tell you not to use DataSet. It’s funny though – I like to play devil’s advocate a lot and ask the question as to why we don’t use it more often. I’m just as guilty as the next person in creating my share of dumb Data Transfer Objects (DTOs) that directly represent database tables. In doing so, I am forced to manually map the columns in the result set to properties on the DTO. Why not skip that effort and just let the DataSet (typed or regular) handle that? It’s already built into the framework after all. And if you’ve ever played with Rails – isn’t an ActiveRecord object effectively the same thing as a Typed DataSet???
I guess it boils down to control. I don’t like letting go of the classes I create and can easily refactor and manipulate without having to employ or depending on tooling to do so. It’s a personal choice. I don’t think I’d turn my nose down at a developer who chooses to use DataSets. That being said, I can’t remember the last time I’ve done so.
Coming up next!
In my next post (whenever that may be), I will discuss how you can use Miado to build dynamic queries.
Friday, May 01, 2009 10:22:07 PM (Eastern Standard Time, UTC-05:00) Miado