Programming, Software and Code

I had originaly wanted to write a short post about my new library, CastIron, but then I started having to explain why I was doing things the way I was doing them, and why I didn’t pick any alternatives, and why I just couldn’t be happy enough with goddamn Entity Framework. My little post turned into 2, and then it grew to 4. See the first few here:

1. The Data Layer
2. ORM Problems
3. Data Access Patters

After all that, after complaining about ORMs in general (though I didn’t really touch on Micro-ORMs) and Active Record and Repositories and all that, what do I use instead?

I’ve really become enamored with the Query Object (and the Command Object, which I’m not sure if anybody else has talked about before) pattern. I like the idea of wrapping up a single database interaction into a well-named, parameterizable object. You build an object that represents that database interaction, and then you simply pass it off to an executor to run it. This is CastIron.

Overview

The CastIron project is all about providing implementations of the Query Object and Command object patterns to standardize and simplify database access. CastIron.Sql, the first library I’m releasing for this project, is a bare-metal implementation of these patterns specifically for Microsoft SQL Server.

With CastIron you’re working directly with SQL code and, if desired, directly with IDbConnection and IDbCommand objects. This is great if you:

1. Are competent and comfortable writing your own SQL code
2. Would like to be able to leverage SQL features that a normal ORM hides from you
3. Would like to be able to use multiple result sets
4. Would like to leverage batching multiple statements onto a single connection
5. Would like to be able to use the Query Object and Command Object patterns with your application.

Example

First we can create a query object to load out all Products by supplier Id, and a command object to delete them all:

public class GetAllProductsFromSupplier : ISqlQuery<IEnumerable<Product>>
{
    private readonly int _supplierId

    public GetAllProductsFromSupplier(int supplierId)
    {
        _supplierId = supplierId;
    }

    public string GetSql()
    {
        return $"SELECT * FROM Product WHERE SupplierId = {_supplierId}";
    }

    public IEnumerable<Product> Read(SqlResultSet resultSet)
    {
        return resultSet.AsEnumerable<Product>().ToList();
    }
}

public class DeleteAllProductsBySupplier : ISqlCommand
{
    private readonly int _supplierId

    public DeleteAllProductsBySupplier(int supplierId)
    {
        _supplierId = supplierId;
    }

    public string GetSql()
    {
        return $"DELETE FROM Product WHERE SupplierId = {_supplierId}";
    }
}

Next we can execute it. Here’s an example showing a whole Data Gateway class with some methods which use our Query and Command from above:

public class ProductDataGateway
{
    private readonly SqlRunner _runner;

    public ProductDataGateway(string connectionString)
    {
        _runner = new SqlRunner(connectionString);
    }

    public IReadOnlyList<Product> GetProducts(int supplierId)
    {
        return _runner.Query(new GetAllProductsFromSupplier(supplierId)).ToList();
    }

    public void DeleteProducts(int supplierId)
    {
        _runner.Execute(new DeleteAllProductsFromSupplier(supplierId));
    }

    public void GetProductsThenDeleteThem(int supplierId)
    {
        var batch = new SqlStatementBatch();
        var promise = batch.Add(new GetAllProductsFromSupplier(supplierId));
        batch.Add(new DeleteallProductsFromSupplier(supplierId));
        _runner.Execute(batch);
        return promise.Results.ToList();
    }
}

Features

The example above is helpful but limited. It misses a lot of the features that CastIron has been designed to support. Here are some of them:

1. Database parameters. You can easily parameterize your queries, especially if you’re talking about strings and SQL injection vulnerabilities. This also includes output parameters, which are easily accessible.
2. Batching. As shown above, you can easily combine multiple queries and commands into a single batch and execute them all at once on a single connection. Promises allow you to access the resultsets of multiple queries in a batch.
3. Automatic mapping. CastIron provides utilities to automatically map result sets to objects. It includes the ability to map columns to properties and constructor parameters by name. You can also easily specify your own mappings, or turn mapping off entirely if you don’t want CastIron to do it for you.
4. Multiple result sets. If your query returns multiple result sets, CastIron provides helpful tools for accessing those. You can also get access to the raw IDataReader and do it yourself if that’s what you want.
5. Helpful exceptions. CastIron provides more helpful exceptions than System.Data.Sql typically provides, including the full text of problematic SQL queries for easy debugging, the column numbers and type information if IDataRecord throws an InvalidCastException and other details.

Design Goals

I’m adhering to a few basic design goals as I write CastIron:

1. Hide Nothing. CastIron exposes all the underlying features (IDbConnection, IDbCommand, IDbDataReader etc) if you want to use them, but also provides (optional) niceties and layers on top of that. There are no hidden assumptions or mandatory abstractions. Use what you need and ignore what you don’t.
2. Improve Usability. Every chance I get, I try to add something to improve usability for developers. From better exception messages to helpful extension methods, I really want to make it easy for a developer to use this library and get shit done.
3. Good Design Principles. I’m really doing my best to try and leverage good patterns and follow best practices. Spending just a little bit more time on niceties will, I hope, make the library more accessible and easier for other developers to jump in to.

Upcoming

We’re still in early days, so I’ve got plenty of items in the TODO list. I want to add tested support for other databases such as MySql, MariaDb and PostgreSQL. Oracle maybe, though I don’t currently have any way to test that.

I have a lot of work to do in terms of concurrency and thread-safety.

CastIron does not currently support streaming large result sets without loading the whole set into memory. I’m going to be doing a lot of work in this area to make sure that result streaming works easily and efficiently.

The same code has been lightly tested to work, with only small additions, with MySQL and MariaDB.

Anyway, I’m tired of writing lists about this thing. If CastIron sounds like it might be interesting to you, please go check out the code and send me as much feedback as you can.