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)      Comments [0]  Miado

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)      Comments [0]  Miado

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: }

In my next post, I will describe how to use the IDbStatement to return our old friend DataSet.

Sunday, March 01, 2009 5:29:02 PM (Eastern Standard Time, UTC-05:00)      Comments [2]  Miado

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)      Comments [0]  Miado

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.

Monday, February 16, 2009 1:00:13 PM (Eastern Standard Time, UTC-05:00)      Comments [0]  Miado

I released a new version of Miado on CodePlex last night, so I thought I’d spend a couple of blog posts explaining how to use the API.  Consider this post as the first in a multi-part series on how to use the newest version.

Basics – Creating an IDatabase

The first thing you need to do is get a handle on an IDatabase.  The interface (and its corresponding implementation) serves as the entry point of the API.  Obviously, the easiest way to do so would be to directly new one up.  The Database constructor takes in a DbProviderFactory object and a connection string.

  1: string connString = 
  2:     "Data Source=localhost;Initial Catalog=MyDatabase; User ID=MyUser; Password=Password1";
  3: IDatabase db = new Database(SqlClientFactory.Instance, connString);

I’ve created a few sub-classes of the Database class that take care of the provider initialization (using the corresponding DbProviderFactory implementations provided in the .Net Framework) and just take in a connection string:

• OdbcDatabase
• OleDatabase
• OracleDatabase
• SqlServerDatabase

e.g.

  1: string connString = 
  2:     "Data Source=localhost;Initial Catalog=MyDatabase; User ID=MyUser; Password=Password1";
  3: IDatabase db = new SqlServerDatabase(connString);

Using Miado with a Depedency Injection Container

I’m a big fan of Dependency Injection, so I normally create a reference to the IDatabase in my application start event and store it in the DI container. 

  1: public class Global : System.Web.HttpApplication, IContainerAccessor
  2: {
  3:     public IUnityContainer Container 
  4:     {
  5:         get;
  6:         protected set;
  7:     }
  8: 
  9:     protected void Application_Start()
 10:     {
 11:         this.InitializeContainer();
 12:     }
 13: 
 14:     protected void InitializeContainer() 
 15:     {
 16:         var container = new UnityContainer();
 17:         
 18:         string connString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
 19:         // create IDatabase instance and store it in the container
 20:         var db = new SqlServerDatabase(connString);
 21: 
 22:         container.RegisterInstance<IDatabase>(db);
 23: 
 24:         // create a Repository object - IDatabase will injected in it
 25:         container.RegisterType<IUserRepository, UserRepository>();
 26: 
 27:         // rest of the container initialization omitted ...
 28: 
 29:         Container = container;
 30:     }
 31: }

When I create my repository objects, I design them to take a reference to the IDatabase in the constructor.

  1: public class UserRepository : IUserRepository
  2: {
  3:     public UserRepository(IDatabase db)
  4:     {
  5:         this.Database = db;
  6:     }
  7: 
  8:     public IDatabase Database 
  9:     {
 10:         get;
 11:         private set;
 12:     }
 13: 
 14:     // CRUD methods omitted
 15: 
 16: }
 17: 

The nice thing about the DI container is that it will automatically inject a reference to the IDatabase instance I registered when it resolves the Repository reference.

  1: IContainerAccessor accessor = HttpContext.Current.ApplicationInstance as IContainerAccessor;
  2: 
  3: // IDatabase is resolved and injected in UserRepository constructor
  4: IUserRepository userRepository = accessor.Container.Resolve<IUserRepository>();

If you don’t want to use a DI container, you can accomplish a similar de-coupling by hiding the IDatabase creation behind a Factory object.

  1: public static class DatabaseFactory 
  2: {
  3:     private static readonly IDatabase _db = 
  4:         new SqlServerDatabase(ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString);
  5: 
  6:     public static IDatabase CreateDatabase() 
  7:     {
  8:         return _db;
  9:     }
 10: }

In my next post, I will discuss how to use the IDatabase interface to register queries (a new feature in Miado).

Sunday, February 15, 2009 11:23:59 AM (Eastern Standard Time, UTC-05:00)      Comments [0]  Miado

When I first jumped into C# programming, one of the biggest points of confusion for me was in understanding delegates.  You see, Java had no real similar concept.  So the idea of function pointers didn’t really register with me.  I saw some really weird syntax for event handlers that looked like this:

   1: myButton.Clicked += MyButton_Clicked;

   2:  

   3: // ...

   4:  

   5: private MyButton_Clicked(object sender, EventArgs e) 

   6: {

   7:     // do something

   8: }

Eventually, I caught on that because the function called MyButton_Clicked satisfied the signature required by the OnClick event, it could be added as an event handler to the event.

Aha!  Delegates are nothing more than “interfaces” at the method (as opposed to class) level.  So even though I understood what delegates were, I still didn’t see a widespread use for them outside event handlers.  Sure, I saw examples where they were used, but they all seemed very contrived to me. 

Enter in lambda expressions

Lambda expressions were introduced in C# 3.0.  I’ve said it before, while lambda expressions are new to .Net, they are not even close to being a new concept in programming languages.  They have been around forever.  Basically, it’s a terse syntax for defining anonymous delegates.  In .Net, they were really a simpler way to interact with LINQ.  Now you no longer had to go through the ceremony of creating an explicit method just to satisfy the compiler.  This “inline” function call was especially useful in simple delegate calls, as in a Predicate:

   1: var myList = new List<Person>();

   2: // ...

   3: Person chris = myList.Find(person => person.FirstName == "Chris");

I don’t know why, but I really latched on to lambda expressions in a  way I never did with delegates.  Which is actually a little weird, since I’ve heard numerous complaints that the lambda expression syntax is too confusing for the average developer. 

Changing the way I code

Now, lambda expressions have really changed the way I approach programming.  I’ll give an example from Miado.  I wanted to add support to Miado for creating typed DataSet objects (in addition to regular DataSet objects).  I added support for a querying for a DataSet using generics, where the generic type had to be a class derived from DataSet:

   1: public virtual T QueryForDataSet<T>() where T : DataSet

I wanted to have one common method that handles creating both types of DataSets (the ol’ DRY – i.e. “Don’t Repeat Yourself”).  Here’s the rub - when using a DataAdapter to fill the DataSet, the method actually is just a little different from using a regular DataSet versus a typed one.  It’s not that different, but enough to make you have to handle it two different ways.  Previously, I probably would’ve created a template method or maybe handled it through an interface.  But to use interfaces would require an interface to define the method and two separate classes to implement the varying implementations.  That’s a lot of work to accomplish this seemingly simple task.

Hmm … if I could just pass in the method  that I want to call, I can solve this problem much easier.  Enter in my good friend Action<T> to the rescue! 

Action<T>

Action<T> is a delegate method.  It takes in an instance of the generic T type defined in its signature and has no return value.  So it’s a callback method that performs some action (and thus cleverly named).  I love this delegate.  It’s really great for implementing simple one line functions via a lambda expression.  The syntax becomes very terse without having to build up all the ceremony just to satisfy the compiler.

Implementation

Actually, Action is overloaded so you can pass in multiple arguments.  In my case, I am using two arguments to the method (i.e. Action<T,U>).  Here is the way I implemented it:

   1: private T QueryForDataSetUsingCustomDataAdapterFillFunction<T>(

   2:             Action<DbDataAdapter, T> adapterFillFunction) where T : DataSet

   3: {

   4:     T ds = CreateEmptyDataSet<T>();

   5:  

   6:     using ( DbConnection conn = this.Database.CreateConnection() )

   7:     {

   8:         conn.Open();

   9:  

  10:         using ( DbCommand cmd = conn.CreateCommand() )

  11:         {

  12:             cmd.CommandText = this.CommandText;

  13:             cmd.CommandType = this.CommandType;

  14:  

  15:             if ( this.Parameters != null )

  16:             {

  17:                 cmd.Parameters.AddEnumeration(this.Parameters);

  18:             }

  19:  

  20:             using ( DbDataAdapter adapter = this.Database.CreateDataAdapter() )

  21:             {

  22:                 adapter.SelectCommand = cmd;

  23:                 adapterFillFunction(adapter, ds);

  24:             }

  25:         }

  26:     }

  27:  

  28:     return ds;

  29: }

  30:  

  31: public virtual DataSet QueryForDataSet()

  32: {

  33:     return QueryForDataSetUsingCustomDataAdapterFillFunction<DataSet>(

  34:             (adapter, ds) => adapter.Fill(ds));

  35: }

  36:  

  37: public virtual T QueryForDataSet<T>() where T : DataSet

  38: {

  39:     return QueryForDataSetUsingCustomDataAdapterFillFunction<T>(

  40:             (adapter, ds) => adapter.Fill(ds, ds.Tables[0].TableName));

  41: }

Basically, I made it so that the common method takes in a function that, given a DataAdapter and a DataSet, does some work on those objects.  So the two public methods pass in a different implementation of the Fill() method on the DataAdapter.  Like I said before, it’s kind of like using interfaces for methods.  The common method (in line 23) calls the Action method to perform its specific function by passing back the current DataAdapter and DataSet.

Conclusion

Again, lambda expressions have changed the way I approach programming.  Once you get a handle on the syntax, it’s a great tool to have in your toolbox!

Sunday, February 01, 2009 3:46:49 PM (Eastern Standard Time, UTC-05:00)      Comments [1]  .Net | C# | Miado

There is a great book on the Java programming language called Effective Java by Joshua Bloch.  Even if you don’t use Java as your primary coding language, this book contains innumerable software engineering tips and design patterns that will help you improve in no matter what language you code.  It’s a great reference guide that I continuously go back to, even though I haven’t done much Java coding in the last few years.  The fact that I bought the second edition when it came out despite the fact I no longer really do Java should tell you something.  Josh, if you’re reading this post, you can contact me to see how you can send me the referral fees.

The first item in the “Classes And Interfaces” section of the book is: “Minimize the accessibility of classes and interfaces”. The idea is to not just think of encapsulation in terms of class internals, but to also take it to the next level - meaning up to your API in general.  As Bloch says,

"The single most important factor that distinguishes a well-designed module from a poorly designed one is the degree to which the module hides its internal data and other implementation details from other modules.  A well-designed module hides all of its implementation details, cleanly separating its API from its implementation.  Modules then communicate only through their APIs and are oblivious to each others’ inner workings."

It’s a principle I strive for in all my software design.  By making your API strong and weakening your implementations, you are effectively decoupling your module from the rest of the system.  This decoupling allows you to easily develop, test, and refactor your module in isolation without affecting the rest of the system.  It’s a concept that falls right in line with “The Open/Closed Principle”.  This principal states you should make your software modules “open” for extension, but “closed” for modification.

One of the easiest ways to accomplish encapsulation in an object-oriented language is through the use of access modifiers (e.g. public, protected, internal, private).  Anything you declare as public is open to the whole world.  Therefore, Bloch says that when design your software, “make each class or member as inaccessible as possible.”

I am the author of an open-source project called Miado.  It’s an API that sits on top of ADO.Net and attempts to simplify data access and remove the repetitive, error-prone, boiler plate code we write over and over again in our data access modules.  In building my API, I’ve tried to adhere to good OO design principles.  I’m a big fan of coding to interfaces, using dependency injection, unit testing, and mocking.  Therefore, I’ve exposed most of the API as interfaces rather than concrete classes.  Since I want to abide by the tenants I’ve talked about already, I’ve restricted the corresponding class implementations of these interfaces.  They are declared as “internal”, which in C# terms means they are only exposed to classes in the same assembly in which they are defined.  Since no external assembly can reference my implementations, the “black box” of my interface is upheld, yet allows the consumer to override their own implementations (even if it is only in a mock).

Now this is all well and good, but there’s one more thing of which you need to be aware.  Remember that when you mark a class as “internal” in C#, only classes in that same assembly can reference that class.  Like I said, I’m a big fan of unit testing.  How do you test a class in a test assembly when you can’t even see it???  You could move your test classes to the declaring class assembly, but that’s really just a bad idea.  It adds extra overhead to an assembly and provides no real value to its consumer.  It’s just extra noise taking up space.  And jumping through hoops to remove those test classes as part of a build step reeks of wasted effort.

Java has a nice way to get around this gotcha.  In Java, “protected” scope is somewhat similar to “internal” scope in C#, with one important distinction.  Protected accessibility in Java allows any subclass to access that member, as well as any other class in the same “package” (a close cousin to a “namespace” in C#).  So when you write your unit tests in Java, you merely have to declare your unit test class as belonging to the same package as the class being tested, which then allows you to access any of that class’s internal state and behavior, even if it gets deployed as a separate jar file.  Unfortunately, C# has no similar “namespace” accessibility modifier.

But .Net does have some aces up its sleeve.  There is a way to declare another assembly as a sort of “friend” assembly so that it can see the original’s internal members.  In the original assembly (the one being tested), add the following line to your AssemblyInfo.cs class:

   1:  // let the unit tests see internals of this assembly

   2:  [assembly: InternalsVisibleTo("Miado.Test")]

Monday, January 12, 2009 4:46:33 PM (Eastern Standard Time, UTC-05:00)      Comments [0]  .Net | C# | Java | Miado

This new release contains significant changes.  First and foremost, all the implementation classes have had their public scope removed, so you will now have to program exclusively against the interfaces.  I have also moved the IMiadoDao interface to IMiadoRepository in order to encourage composition and not force an inheritance hierarchy.  Combined with that change, I also moved the functionality from SimpleSql into IMiadoRepository, as well as making that interface the point where you access an IDbStatement, making the IMiadoRepository the main worker module of Miado.   Another change is that you need to use the MiadoRepositoryFactory to create an instance of the IMiadoRepository.

Enough with the talk, let's see some code...

public AdddressDao 
{
    private static readonly string INSERT_ADDRESS_SQL = 
        "insert into Address(Address1, Address2, City, State, ZipCode) " +
        "values(@Addr1, @Addr2, @City, @State, @ZipCode)";
        
    private static readonly string SELECT_ADDRESS_SQL = 
        "select AddressId, Address1, Address2, City, " +
        "State, ZipCode " +
        "from Address ";
    
    private static readonly string SELECT_ADDRESS_BY_ID = 
        SELECT_ADDRESS_SQL + "where AddressId = @Id ";
        
    private static readonly string SELECT_ADDRESS_BY_ZIP_CODE =
        SELECT_ADDRESS_SQL + "where ZipCode = @ZipCode ";
        
    private static readonly string SELECT_COUNT_BY_STATE = 
        "select count(*) from Address where State = @State ";

    // uses MiadoRepositoryFactory.CreateMiadoRepository()    
    public AddressDao() 
        : this(MiadoRepositoryFactory.CreateMiadoRepository(
                   SqlClientFactory.Instance, 
                   ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString)) {}

    public AdddressDao(IMiadoRepository repository) 
    {
        this.MiadoRepository = repository;
    }
    
    protected IMiadoRepository MiadoRepository 
    {
        get;
        set;
    }
    
    public void SaveAddress(Address addr)
    {
        // uses SQL right in IMiadoRepository
        this.MiadoRepository
            .ExecuteNonQuery(INSERT_ADDRESS_SQL, 
                             addr.Address1,
                             addr.Address2,
                             addr.City,
                             addr.State,
                             addr.ZipCode)
    }
     
    public ICollection<Address> FindAddressesByZipCode(string zipCode) 
    {
        // build IDbStatement and do the mapping in a lambda expression 
        ICollection<Address> addresses = 
            this.MiadoRepository
                .CreateDbStatement(SELECT_ADDRESS_BY_ZIP_CODE)
                .AddParameter("ZipCode", zipCode)
                .QueryForResults<Address>(
                    row => new Address() 
                    {
                        AddressId = row.GetValue<int>("AddressId"), 
                        Address1 = row.GetValue<string>("Address1"),
                        Address2 = row.GetValue<string>("Address2"),
                        City = row.GetValue<string>("City"),
                        State = row.GetValue<string>("State"),
                        ZipCode = row.GetValue<string>("ZipCode")
                    });
        return addresses;
    }
     
    public Address LoadAddress(int addrId) 
    {
        // uses delegate method to do the mapping
        return 
            this.MiadoRepository
                .CreateDbStatement(SELECT_ADDRESS_BY_ID)
                .AddParameter("Id", addrId)
                .QueryForOne<Address>(CreateAddress);
    }
     
    public int FindCountByState(string state) 
    { 
        return 
            this.MiadoRepository
                .CreateDbStatement(SELECT_COUNT_BY_STATE)
                .AddParameter("State", state)
                .QueryForOne<int>(row => row.GetValue<int>(0));
    }
     
    private static Address CreateAddress(IResultSetRow row) 
    {
        return 
            new Address() 
            {
                AddressId = row.GetValue<int>("AddressId"), 
                Address1 = row.GetValue<string>("Address1"),
                Address2 = row.GetValue<string>("Address2"),
                City = row.GetValue<string>("City"),
                State = row.GetValue<string>("State"),
                ZipCode = row.GetValue<string>("ZipCode")
            };
    }
    
}

Friday, May 30, 2008 10:57:03 PM (Eastern Standard Time, UTC-05:00)      Comments [0]  .Net | C# | Miado

One of the nice things about Miado is that it wraps a lot of tedious, repetitive ADO.Net code and therefore greatly reduces the amount of code you actually do need to write. 

Here's a quick example.  One of the tasks I had to do at my last last gig was upload a set of prices from a CSV file every month.  I wrote a PowerShell script to use Miado to do the DB inserts:

As you can see, the script iterates over each row in the CSV file and inserts a record for each row.  There's no need to write all the code necessary to create the DbConnection and DbCommand objects, nor do you have to dynamically create DbParameter objects for each row.  Just pass the SQL and the variables (in order of replacement) into the IMiadoRepository.ExecuteNonQuery() call and Miado takes care of all the ugly details.  One line for each insert - that's it!

Edit: This post was updated to reflect the changes in the 0.7.10530.1

Wednesday, April 30, 2008 11:38:38 AM (Eastern Standard Time, UTC-05:00)      Comments [0]  .Net | Miado | Powershell

I have uploaded a new version of Miado out on CodePlex.  I am still calling this a beta.  There are a few features I would like to add before I officially proclaim a 1.0 version (ability to use SqlServer-type syntax with an ODBC provider and integrated caching with the IDbStatement are two at the top of my list).

For this latest version, I have moved the DbProvider and vendor specific stuff to the Miado.Configuration namespace.  I have added an IParameterParser interface which is used in the SimpleSql class to determine the parameter name declarations in a SQL statement based on whether you are using the ODBC-type syntax or newer syntax for variable declaration. For example,

MyParam = ?

MyParam = @MyParam

This week I am going to try and come up with a good set of DAOs that use the AdventureWorks DB so people can see samples of how the API should be used.

Monday, April 14, 2008 6:52:06 AM (Eastern Standard Time, UTC-05:00)      Comments [0]  .Net | Miado

I have published the API documentation for Miado.  The IDbStatement and SimpleSql objects are the two main components of the API and both contain examples of how it should be used.

I have to give a shout out to the Sandcastle Help File Builder project on CodePlex.  It made my life 1000x better in using Sandcastle to generate the API documentation.

Monday, April 07, 2008 7:07:02 PM (Eastern Standard Time, UTC-05:00)      Comments [0]  .Net | API | Miado | Sandcastle

I have been refining some data access code that I have used on a bunch of my projects to the point where I've made it fairly generic.  I decided to publish it as a project on CodePlex.  I just put the initial release out today.  If you are interested, please take a moment to check it out and give me any feedback.  Keep in mind, I wrote this specifically for those people who have (or want) to use straight SQL and ADO.Net in their data access layer.  So if you want to use LINQ, Nhibernate, Subsonic, etc... they are absolutely great tools, but they try to hide/remove the SQL from your code, whereas this project just makes it much easier for those who use SQL.

Here is an example of how to use it to create a collection of custom business objects:

// ideally this declaration would be buried in your base DAO class
// or injected via an IoC container
IDbConfigurable dbConfig = new DbConfiguration(SqlClientFactory.Instance, connString);
IDbStatement dbStmt = new DbStatement(dbConfig);

ICollection<Address> addresses = 
    dbStmt.LoadSql("SELECT AddressId, Address1, Address2, City, State, ZipCode " + 
                   "FROM Address WHERE City = @City")
          .AddParameter("City", "Atlanta")
          .QueryForResults<Address>(
              row => new Address() 
                  {
                      AddressId = row.GetValue<int>("AddressId"), 
                      Address1 = row.GetValue<string>("Address1"),
                      Address2 = row.GetValue<string>("Address2"),
                      City = row.GetValue<string>("City"),
                      State = row.GetValue<string>("State"),
                      ZipCode = row.GetValue<string>("ZipCode")
                  });

Notice how it removes all the usual boiler plate code from a normal ADO.Net implementation.  The only things you really need to do are provide the SQL, set the parameters, and then map the result set to properties on your business object.  I'm using lambda expressions in these examples, but if you want to re-use the result set-to business object mappings, I normally create a method in my DAOs to use as a delegate:

ICollection<Address> addresses = 
    dbStmt.LoadSql("SELECT AddressId, Address1, Address2, City, State, ZipCode " + 
                   "FROM Address WHERE City = @City")
          .AddParameter("City", "Atlanta") 
          .QueryForResults<Address>(CreateAddress); 
...
private Address CreateAddress(IResultSetRow row)
{
    //  full details not provided
    return new Address() { AddressId = row.AddressId };
}

If you embed the creation of the IDbStatement interface in a base class (or use the one provided in Miado.Integration), you can see how the code reads even more like a Domain-Specific-Language:

// CreateSqlStatment() method is provided in Miado.Integration.MiadoDao
ICollection<Product> products = 
    this.CreateSqlStatement("SELECT ProductId, Name, Description, Color, Quantity " +
                            "FROM Product WHERE Color = @Color and Quantity > @Qty")
        .AddParameter("Color", "Blue")
        .AddParameter("Qty", 20)
        .QueryForResults<Product>(
              row => new Product() 
                  {
                      ProductId = row.GetValue<int>("ProductId"), 
                      Name = row.GetValue<string>("Name"),
                      Description = row.GetValue<string>("Description"),
                      Color = row.GetValue<string>("Color"),
                      Quantity = row.GetValue<int>("Quantity")
                  });

There is also a SimpleSql object that can be used similar to IDbStatement but the syntax is a little more terse since you don't have to map the parameters - they are merely replaced in the order you provide them:

IDbConfigurable dbConfig = new DbConfiguration(SqlClientFactory.Instance, connString);
SimpleSql sql = new SimpleSql(dbConfig);
sql.ExecuteNonQuery("UPDATE Person SET FirstName = @FirstName, " + 
                    "LastName = @LastName " + 
                    "WHERE PersonId = @PersonId", 
                    "John", "Smith", 1001);
// again, I like to embed this in a base class so it reads:
// this.CreateSimpleSql().ExecuteNonQuery(sql, param1, param2);

For this first release, SimpleSql is not supported for ODBC SQL statements (since it uses pattern matching to substitute the SQL parameters).

Here's another example where you can see how easy it is to get a count:

int count = 
    this.CreateSimpleSql()
        .QueryForOne<int>("SELECT count(*) FROM Employee WHERE YearsOfService > @YearsOfSvc",
                           new object[] { 15 },
                           row => row.GetValue<int>(0));

And yes, it supports the creation of DataSets and DataTables:

DataSet ds = 
    this.CreateSimpleSql()
        .QueryForDataSet("SELECT * FROM ProductType WHERE Price > @Price", 10.0);

This is the first code drop, so treat it like a beta release.

Sunday, April 06, 2008 9:35:57 AM (Eastern Standard Time, UTC-05:00)      Comments [1]  .Net | Miado