Chris Rauber - Using Miado

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

Monday, February 16, 2009

Using Miado - Part 2

This is part 2 in a series on the new updates to the Miado API. You can read the first part in the series here. In this post, I am going to discuss some of the new features available when configuring Miado.

Query Registration

I’ve had a couple of clients who’ve had a requirement where they wanted to store their SQL in a single location rather than spread across the Repository objects as embedded SQL. In fact, one client put all their SQL in .sql files and stored them on the file system. I had never seen this before, but after working with it for a while I definitely warmed up on the idea. I definitely saw some advantages for doing this:

Versioning – it’s very easy to isolate changes to individual SQL statements, and you can take advantage of all the benefits of using a version control system, like labeling, diffs, branching, etc.
Testing - you can open the SQL files directly in your DB management software to test the queries (as opposed to cutting and pasting out your source code, where you have to remove code formatting like string concatenations)
Collaboration – you can easily share the queries with a DBA, who can help tweak them or run profiling tools against them
Isolation – all your queries are stored in a common well-known place

So I added the capability in Miado to “register” queries by name. You can either do this manually or by pointing to files or a directory on the file system. The IQueryRegistry interface controls the registration and retrieval of stored queries. To get an access to this interface, you need to declare an Action<IQueryRegistry> delegate to configure itself. The delegate is passed in via a method on the IDatabase interface.

Registering Queries Manually

The easiest way to register queries is by setting name/value pairs:

  1: var db = new SqlServerDatabase(connString);
  2: db.ConfigureQueryRegistry(queryRegistry => 
  3:     {
  4:         string name = "SelectStates";
  5:         string sql = "select StateCode, Name from States";
  6:         queryRegistry.RegisterQuery(name, sql);
  7: 
  8:         string name1 = "SelectUserByID";
  9:         string sql1 = "select UserID, Email " +
 10:                       "from Users " + 
 11:                       "where UserID = @ID";
 12:         // NamedQuery is a simple name/value pair object
 13:         var namedQuery = new NamedQuery(name1, sql1);
 14:         queryRegistry.RegisterQuery(namedQuery);
 15:     });

Registering Queries From Files

Alternately, you can register queries from files on the file system. There are multiple ways to do this. First of all, you can start at a selected directory and optionally pass a Predicate to select only certain files. From the resulting files, you need to build a NamedQuery list from each individual file.

  1: var db = new SqlServerDatabase(connString);
  2: db.ConfigureQueryRegistry(queryRegistry =>
  3:     {
  4:         // start looking in the "sql" directory for files named *.sql
  5:         queryRegistry.RegisterQueriesFromPath(
  6:             "sql", 
  7:             fileInfo => String.Compare(fileInfo.Extension, ".sql") == 0,
  8:             fileInfo => 
  9:             {
 10:                 string queryName = fileInfo.Name.Substring(0, fileInfo.Name.IndexOf('.'));
 11:                 string sql = File.ReadAllText(fileInfo.FullName);
 12:                 return new NamedQuery[] { new NamedQuery(queryName, sql) };
 13:             });
 14:     });

You can also pass in the specific files you want processed:

  1: var db = new SqlServerDatabase(connString);
  2: db.ConfigureQueryRegistry(queryRegistry => 
  3:     {
  4:         // find the files yourself
  5:         string[] files = Directory.GetFiles("sql", "*.sql", SearchOption.AllDirectories);
  6:         queryRegistry.RegisterQueriesFromFiles(
  7:             files, 
  8:             fileInfo =>
  9:             {
 10:                 string queryName = fileInfo.Name.Substring(0, fileInfo.Name.IndexOf('.'));
 11:                 string sql = File.ReadAllText(fileInfo.FullName);
 12:                 return new NamedQuery[] { new NamedQuery(queryName, sql) };
 13:             });
 14:     });

Be Smart

Obviously, you need to be careful about exposing the raw SQL in any directory that can be compromised. If you are using a web app, make sure it is not in a publicly visible folder. If you are using a rich client app, you might want to think about using Isolated Storage as your underlying query repository.

In my next post, I will show how to use the IDbStatement to run queries, including those registered in the IQueryRegistry.