Chris Rauber - Using Miado

this.Blog.Find(entry => entry.IsHelpful);

Sunday, March 01, 2009

Using Miado – Part 4

This is part 4 in a series about the latest release of Miado. In my last post, I discussed about creating an IDbStatement from the IDatabase interface. In this post, I will start to discuss the main methods exposed by the IDbStatement interface.

Adding Parameters

Adding parameters to the IDbStatement is really easy. It is accomplished simply by calling the AddParameter() method with a name/value pair. The name should match the parameter name declared in the SQL statement and the value is what will be substituted during the query execution. Optionally, you can also set the DBType and/or parameter direction. If the DBType is not set, it will be inferred by the DbProvider implementation based the type of the parameter value. The parameter direction is assumed to be an input parameter unless explicitly declared. This routine should be very familiar to anyone who has ever done traditional ADO.Net development.

Like most other methods on the IDbStatement interface, the AddParameter() method returns a reference to itself so it can be chained with other method calls on the IDbStatement interface. Example:

  1: private static readonly string INSERT_ADDRESS_SQL = 
  2:     "insert into Address(Address1, Address2, City, State, ZipCode) " +
  3:     "values(@Addr1, @Addr2, @City, @State, @ZipCode)";
  4: 
  5: public void SaveAddress(Address addr) 
  6: {
  7:     this.Database
  8:         .ExecutingSql(INSERT_ADDRESS_SQL)
  9:         .AddParameter("Addr1", addr.Address1)
 10:         .AddParameter("Addr2", addr.Address2)
 11:         .AddParameter("City", addr.City, DbType.String) // explicitly set DbType
 12:         .AddParameter("State", addr.State)
 13:         .AddParameter("ZipCode", addr.ZipCode)
 14:         .ExecuteNonQuery();
 15: }

If you really want total control over the parameter, you can get a reference to the actual DbParameter object using the Action<DbParameter> method signature of AddParameter().

  1: public void UpdateUser(User user) 
  2: {
  3:     this.Database
  4:         .ExecutingSql(UPDATE_USER_SQL)
  5:         .AddParameter(dbParm => 
  6:         {
  7:             dbParm.ParameterName = "Email";
  8:             dbParm.Value = user.Email;
  9:         }) // AddParameter(Action<DbParameter> dbParmAction)
 10:         .AddParameter(dbParm => 
 11:         { 
 12:             dbParm.ParameterName = "UserID";
 13:             dbParm.Value = user.UserID;
 14:             dbParm.DbType = DbType.Int32;
 15:             dbParm.ParameterDirection.Input;
 16:         }) // dbParm is a DbParameter
 17:         .ExecuteNonQuery();
 18: }

Insert/Update/Delete

Anytime you want to execute a SQL statement that does not return a result set, you can call the ExecuteNonQuery() method (you probably already realized this fact from previous examples). I’ve thought about adding explicit Save(), Update(), and Delete() methods to the API for clarity sake, but since I’m not providing an ORM, these methods would only be information and could not be guaranteed to perform their exact intended action. In other words, they would merely wrap the ExecuteNonQuery() method, so you could call the Save() method to in fact execute a “delete from User where UserID = @ID” SQL statement with no problems. I would be interested to see what other people thought about this change in the comments. Does the readability of Save(), Update(), and Delete() outweigh the need to validate whether the right one is called? Should there be validation against the raw SQL when one of these methods is called (e.g. explicitly checking the SQL string for “INSERT” when Save() is called)? The validation method option is the route I’m leaning.

Creating Custom Business Objects

When you call QueryForResults() or QueryForOne(), you must use a generic type to declare the type of object that will be created from each row in the result set. Your job will be to create an object for each row as the cursor moves forward in each iteration by mapping the values from the columns in the result set row. Most of the time, this will be a custom business object. However, it could be something as simple as an int if you are just returning a count(*). Both these method types take a delegate that passes in an IResultSetRow interface (which merely wraps a DbDataReader) and return an instance of the generic type you declared. The IResultSetRow interface has both typed - using generics GetValue<T>() - and non-typed - GetValue() - methods that can be used to retrieve values using ordinals or by column name.

If your return type is simple or will not likely be re-used, you can use a lambda expression to create the returned object.

  1: private static readonly SELECT_USER_COUNT_SQL = "select count(*) from Users";
  2: 
  3: public int FindUserCount() 
  4: {
  5:     return 
  6:         this.Database
  7:             .ExecutingSql(SELECT_USER_COUNT_SQL)
  8:             .QueryForOne<int>(row => row.GetValue<int>(0));
  9: }

Alternatively, you can create a true delegate method that satisfies the signature required by the QueryForOne() and QueryForResults() methods and pass a reference to the delegate.

  1: public IEnumerable<BlogEntry> FindBlogEntriesByBlog(int blogID)
  2: {
  3:     return 
  4:         this.Database
  5:             .ExecutingSql(SELECT_BLOG_ENTRIES_BY_BLOG_ID_SQL)
  6:             .AddParameter("BlogID", blogID)
  7:             .QueryForResults<BlogEntry>(CreateBlogEntry);
  8: }
  9: 
 10: private BlogEntry CreateBlogEntry(IResultSetRow row) 
 11: {
 12:     return new BlogEntry 
 13:     {
 14:         BlogEntryID = row.GetValue<int>("BlogEntryID"),
 15:         Text = row.GetValue<string>("BlogText")
 16:         // rest of mapping ommitted
 17:     };
 18: }