Using Miado – Part 3
This is part 3 in a series about the new release of Miado. If you want, you can go back and see Part 1 and Part 2 where I talk about creating an IDatabase reference. In this part, I will discuss how to use the IDatabase to create an IDbStatement.
IDbStatement
The IDbStatement interface is the main working component of the Miado API. You need to use this interface to run raw SQL or to execute a stored procedure. Once you get a handle on the interface (from the IDatabase interface), you can begin “building” the statement that will be run against the DB. Most of the methods on the interface return a reference to itself, so the method calls can be chained to form a fluent-type interface.
Executing SQL
Most of the time, I use raw SQL to execute my DB statements. The ExecutingSql() method on the IDatabase interface returns an IDbStatement reference.
1: public class ProductRepository : IProductRepository
2: {
3: private static readonly string SELECT_PRODUCT_BY_ID =
4: "select ProductID, Name, ProductNumber, Color, ListPrice, ProductModel " +
5: "from Products " +
6: "where ProductID = @ID";
7:
8: // more SQL
9:
10: public ProductRepository(IDatabase db)
11: {
12: this.Database = db;
13: }
14:
15: public IDatabase Database
16: {
17: get;
18: private set;
19: }
20:
21: public Product Load(int productID)
22: {
23: IDbStatement dbStmt = this.Database.ExecutingSql(SELECT_PRODUCT_BY_ID);
24: dbStmt.AddParameter("ID", productID);
25: return dbStmt.QueryForOne<Product>(CreateProduct);
26: }
27:
28: // more CRUD methods
29:
30: private Product CreateProduct(IResultSetRow row)
31: {
32: // implementation omitted...
33: }
34: }
As I said before, since the methods on the IDbStatement return themselves, you can chain the calls. In line 21-26, you could change the Load() method to read like this:
1: public Product Load(int productID)
2: {
3: return
4: this.Database
5: .ExecutingSql(SELECT_PRODUCT_BY_ID)
6: .AddParameter("ID", productID)
7: .QueryForOne<Product>(CreateProduct);
8: }
Stored Procedures
Stored Procedures are built up in a similar fashion, except you need to call the CallingStoredProcedureNamed() method to load the SP.
1: public class EmployeeRepository : IEmployeeRepository
2: {
3: public EmployeeRepository(IDatabase db)
4: {
5: this.Database = db;
6: }
7:
8: public IDatabase Database
9: {
10: get;
11: private set;
12: }
13:
14: public IEnumerable<Employee> FindEmployeesByManager(int mgrID)
15: {
16: return
17: this.Database
18: .CallingStoredProcedureNamed("uspGetManagerEmployees")
19: .AddParameter("ManagerID", mgrID)
20: .QueryForResults<Employee>(CreateEmployee);
21: }
22:
23: private Employee CreateEmployee(IResultSetRow row)
24: {
25: // implementation omitted
26: }
27: }
Registered Queries
I showed in my last post how you can use the IDatabase interface to “register” queries. You can easily retrieve them in the IDbStatement interface by using LoadQueryRegisteredAs() method, which will load the corresponding SQL.
1: public IEnumerable<State> FindAllStates()
2: {
3: return
4: this.Database
5: .LoadQueryRegisteredAs("SelectStates")
6: .QueryForResults<State>(
7: row => new State
8: {
9: Abbreviation = row.GetValue<string>("StateCode"),
10: Name = row.GetValue<string>("Name")
11: });
12: }
Running Custom Queries
There is one more way to “load” a query in the IDbStatement interface, and that is by calling the RunningQuery() method, and passing in an object that implements the ISqlQuery interface. That is new functionality to this release and probably deserves its own blog post, so I will cover that later.
In my next post, I will discuss how the IDbStatement executes and discuss the different kinds of return types available, and how you can create custom business objects from the query result set.
Tuesday, February 17, 2009 4:40:52 PM (Eastern Standard Time, UTC-05:00) 
Miado