11 June 2008

A Strongly Typed SQL Query API... almost!

by mo

An idea the team an I had today, was to build a more fluent interface for creating dynamic SQL queries. Here’s what I mean:

public class when_creating_an_insert_query_for_two_or_more_columns {
  public void should_return_the_correct_sql() {
    var query = Insert.Into<CustomersTable>()
      .ValueOf("mo").ForColumn(c => c.FirstName())
      .ValueOf("khan").ForColumn(c => c.LastName())

    var expected = "INSERT INTO Customers ( FirstName, LastName ) VALUES ( @FirstName, @LastName );";

It’s the responsibility of the query object to prepare the command with the command parameter names and values, so in this test I’m just focused on the raw sql. One of the benefits of this API, is that it’s strongly typed, so you can’t stick a string in a column represented by a long.

For example, Imagine a customers table that looks like this:

public class CustomersTable : IDatabaseTable {
  public string Name() {
    return "Customers";

  public IDatabaseColumn<long> Id() {
    return new DatabaseColumn<long>("Id");

  public IDatabaseColumn<string> FirstName() {
    return new DatabaseColumn<string>("FirstName");

  public IDatabaseColumn<string> LastName() {
    return new DatabaseColumn<string>("LastName");

Here’s what we’ve got so far for contracts…

public class Insert {
  public static ITableSelector<Table> Into<Table>() where Table : IDatabaseTable {
    return new TableSelector<Table>();

public interface ITableSelector<Table> {
  IColumnSelector<Table, ColumnType> ValueOf<ColumnType>(ColumnType value);

public interface IColumnSelector<Table, ColumnType> {
  IChainedSelector<Table> ForColumn<TColumn>(Func<Table, TColumn> columnSelection) where TColumn : IDatabaseColumn<ColumnType>;

public interface IChainedSelector<Table> {
  ITableSelector<Table> And();
  IQuery End();

And here’s as far as we got with the implementation…

  public class TableSelector<Table> : ITableSelector<Table> where Table : IDatabaseTable {
    public IColumnSelector<Table, T> ValueOf<T>(T value) {
      return new ColumnSelector<Table, T>(value);

  public class ColumnSelector<Table, T> : IColumnSelector<Table, T> where Table : IDatabaseTable {
    private readonly T value;

    public ColumnSelector(T value) {
      this.value = value;

    public IChainedSelector<Table> ForColumn<TColumn>(Func<Table, TColumn> columnSelection)
      where TColumn : IDatabaseColumn<T> {
      var table = Activator.CreateInstance<Table>();
      return new ChainedSelector<Table, T, TColumn>(table, value, columnSelection(table));

  public class ChainedSelector<Table, Value, Column> : IChainedSelector<Table> where Table : IDatabaseTable where Column : IDatabaseColumn<Value> {
    private readonly Table table;
    private readonly Value value;
    private readonly Column column;

    public ChainedSelector(Table table, Value value, Column column) {
      this.table = table;
      this.value = value;
      this.column = column;

    public ITableSelector<Table> And() {
      throw new NotImplementedException();

    public IQuery End() {
      var builder = new InsertStatementBuilder(table.Name());
      builder.Add(column, value);
      return builder.EndQuery();

The most important piece is still missing, and that’s implementing the And() method on ChainedSelector… and finishing off the End method. I’m drawing a blank.. Thoughts are appreciated!

books csharp