Hand rolling your datalayer

There are higher and higher demands on you as a developer to learn more and more frameworks that I sometimes get lost in the djungle out there. If you ask me to store some data into a database my mind will automatically go into an NHibernate, Entity Framework, Linq 2 SQL, Castle Active Record mantra and start discussing with itself what is the perfect framework for this specific problem. When you have four entities you don't really need an ORM to handle persistance. In fact, using an ORM in that case is same as "stealing from your clients". (I'm aware that the link goes to a post that states the opposite of my argument) I suggest we take a moment here to reflect on how you hand roll a data layer that will suit for up to four entities. First we need some sort of provider that may deliver us data connections and commands. Some sort of object that will get the plumbing out of the way.

/// <summary>
/// Provider class for the database connection and command
/// </summary>
public class DataProvider : IDataProvider
{
    private readonly ConnectionStringSettings settings;

/// &lt;summary&gt;
/// Create new instance of DataProvider
/// &lt;/summary&gt;
/// &lt;param name=&quot;connectionStringName&quot;&gt;Name of the active connectionString in the App.Config/Web.Config&lt;/param&gt;
public DataProvider(string connectionStringName)
{
    settings = ConfigurationManager.ConnectionStrings[connectionStringName];

    if (settings == null)
    {
        throw new ConfigurationErrorsException(&quot;Expected a connectionString with the name &quot; + connectionStringName);
    }

    DbProviderFactory = DbProviderFactories.GetFactory(settings.ProviderName);
}

/// &lt;summary&gt;
/// Gets the DbProviderFactory
/// &lt;/summary&gt;
public DbProviderFactory DbProviderFactory { get; private set; }

/// &lt;summary&gt;
/// Opens a new connection to the database. Make sure you close it before you&#39;re done.
/// &lt;/summary&gt;
/// &lt;returns&gt;An open DbConnection&lt;/returns&gt;
public DbConnection OpenConnection()
{
    var connection = DbProviderFactory.CreateConnection();
    connection.ConnectionString = settings.ConnectionString;
    connection.Open();

    return connection;
}

/// &lt;summary&gt;
/// Creates a new DbCommand
/// &lt;/summary&gt;
/// &lt;param name=&quot;connection&quot;&gt;The connection we utilize for this command&lt;/param&gt;
/// &lt;param name=&quot;query&quot;&gt;The SQL query to run&lt;/param&gt;
/// &lt;returns&gt;The DbCommand&lt;/returns&gt;
public DbCommand CreateCommand(DbConnection connection, string query)
{
    var command = DbProviderFactory.CreateCommand();
    command.Connection = connection;
    command.CommandText = query;

    return command;
}

/// &lt;summary&gt;
/// Creates a DbParameter for parameterized database calls
/// &lt;/summary&gt;
/// &lt;param name=&quot;name&quot;&gt;Name of the parameter&lt;/param&gt;
/// &lt;param name=&quot;type&quot;&gt;Type of the parameter&lt;/param&gt;
/// &lt;param name=&quot;value&quot;&gt;Value of the parameter&lt;/param&gt;
/// &lt;returns&gt;A DbParameter&lt;/returns&gt;
public DbParameter CreateParameter(string name, DbType type, object value)
{
    var parameter = DbProviderFactory.CreateParameter();
    parameter.ParameterName = name;
    parameter.DbType = type;
    parameter.Value = value;

    return parameter;
}

}

And this would be an example of how you can use that provider to talk to the database.

public class BookRepository
{
    private const string GetByIdQuery = "SELECT title, author FROM books WHERE id=@id";
    private readonly IDataProvider provider;

public BookRepository(IDataProvider provider)
{
    this.provider = provider;
}

public Book GetById(int id)
{
    using (var connection = provider.OpenConnection())
    using (var command = provider.CreateCommand(connection, GetByIdQuery))
    {
        var idParameter = provider.CreateParameter("id", DbType.Int32, id);
        command.Parameters.Add(idParameter);

        var reader = command.ExecuteReader();

        if (!reader.Read())
        {
            throw new DataException("Expected Book entity with id " + id + " in the database");
        }

        var result = new Book
        {
            Id = id,
            Title = reader["title"] as string,
            Author = reader["author"] as string
        };

        return result;
    }
}

}

From your presenter or controller (or whatever) you may now use your data layer like this.

// Could use a DI framework here, but what the heck!
var repository = new BookRepository(new DataProvider("MyDatabaseConnection"));
var book = repository.GetById(1);

There's nothing wrong with going back to the basics, sometimes.