Of Home-brew ORMs, screaming cases and Dapper.NET

I was working on an application with an Oracle database whose tables look something like this:

ID FULL_NAME EMAIL
1 John Doe john.doe@nodeprotocol.com
2 Jane Doe jane.doe@nodeprotocol.com

This application is from way back so it was using a home brew ORM with classes that look like this.

namespace com.nodeprotocol.entities
{
    [Table("TBL_USER")]
    public class User
    {
        [Column("ID")]
        public long Id 
        {
            get;
            set;
        }

        [Column("FULL_NAME")]
        public long FullName 
        {
            get;
            set;
        }

        [Column("EMAIL")]
        public long Email 
        {
            get;
            set;
        }
    }
}

The current implementation of the home brew ORM utilizes attributes to alias the screaming case column names to the pascal case objects.

Looks great, except that now when using the latest ODP.NET Oracle package from NuGet, things went tits up. I trawled through the code base and determined that I am not going to waste my time reinventing the wheel. The back-end of the ORM was powered by very fancy ADO.NET extensions. There is something like that already in place, created by the boffins in StackExchange, namely Dapper.NET. Think of this as the JSON.NET of the ADO.NET extensions. Fast, simple and effective.

I converted the wonky ADO.NET extension code to use Dapper.NET. The code was simplified from the crazy old school ADO.NET data access code to something like this:

namespace com.nodeprotocol.dao
{
    public class DaoBase : IDaoBase
    {
        private readonly string _connectionString;

        public DaoBase()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["Db"].ConnectionString;
        }

        public DaoBase(string connectionString)
        {
            _connectionString = connectionString;
        }

        public IList<T> GetAll(string sqlStatement)
        {
            using(IDatabaseConnection dbConnection = new OracleConnection(_connectionString))
            {
                var returnPayload = dbConnection.Query<T>(sqlStatement).ToList();

                return returnPayload;
            }
        }
    }
}

Note: Code has been abbreviated for the save of brevity

It was working great, until I realized the default mapper does not like underscores in the field names. I could alter the SQL statements but it will take too long. I could write a custom dapper mapper as written here to work with the currently present C# attributes but again, too much work. I needed something quick and fast, and Dapper.NET has that as well, for DBA guys who like their screaming case field names. The one liner looks like this:

DefaultTypeMap.MatchNamesWithUnderscores = true;

This would allow the default mapper to match field names that are screaming cased, with the same spelling of course but thats a simple fix if such a problem occurs. Here is the updated class:

namespace com.nodeprotocol.dao
{
    public class DaoBase : IDaoBase
    {
        private readonly string _connectionString;

        public DaoBase()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["Db"].ConnectionString;
        }

        public DaoBase(string connectionString)
        {
            _connectionString = connectionString;
        }

        public IList<T> GetAll(string sqlStatement)
        {
            using(IDatabaseConnection dbConnection = new OracleConnection(_connectionString))
            {
                DefaultTypeMap.MatchNamesWithUnderscores = true;

                var returnPayload = dbConnection.Query<T>(sqlStatement).ToList();

                return returnPayload;
            }
        }
    }
}

Note: Code has been abbreviated for the save of brevity

Now we have Dapper.NET calls that map screaming cased field names to the more conventional pascal cased .NET class. No fancy extensions and no need to create SQL query field aliases. A drop in solution should you come into contact with code written by developers who want to write their own ORM code.