Repository Generator

Griffin DAL Generator is a tool which can generate pure ADO.NET based repositories for you. This new version is a Visual Studio Extension. Right-click on any project in the Solution Explorer to start the generator wizard.

As you might already know if you’ve read my blog, I’m not a big fan of ORM libraries. They speed up the development process and seemingly make the code cleaner. However, as soon as you begin to wander off the main path, the pain starts. Same headache when the ORM library fails. The tedious process of figuring out if it’s your mapping, the library itself or the generated SQL query begins.

A code generator is much better. It does the same job for you, but is 100% transparent. The code that you get does not hide anything and it’s yours to modify as you please. You can optimize it where it matters, ignore fields when needed or simply use it as is. If you get an exception, you will in most cases know where the error is (as the data exception is not wrapped in a ORM exception). No need to understand anything but SQL and standard ADO.NET code.

We’ve worked hard to make the generated code beautiful and easy to read. Some parts might not be as elegant, but that’s due to a tradeoff between simplicity and performance. Regarding performance, which type of library do you think performs best? An ORM that does a lot for you, a data mapper that does mappings for you, or generated code that contain no extra calls at all?

Functional Walkthrough

You start the tool by right-clicking on the project that you want to add the repositories to and pick the “Add -> Griffin DAL Repo Generator” option in the context menu:

The extension selects the first connection string that it finds in your app/web.config(s). You can of course select another connection string. All tables that exist in the database are listed in the UI. Simply select the tables that you want to generate repositories for.

As you can see, there is a preview for every file being generated.

The generator currently supports the following repository types:

• Repository (ADO.NET) – An ADO.NET repository without any external dependencies.
• Async Repository (ADO.NET) – An ADO.NET repository using async/wait.
• Repository (Griffin.Framework) – A repository that uses the super awesome data mapper in Griffin.Framework.
• Async Repository (Griffin.Framework) – A repository that uses the async API in the super awesome data mapper in Griffin.Framework.

Each repository generation creates the following classes/interfaces:

• Repository interface
• Repository
• Entity

As a bonus, you can also generate integration tests in your test project. These test each method in the repository class so that the SQL queries, mappings, etc. work as they should.

Once you are happy, click on the import button. When you do, the extension will download and include all required nuget packages, include some nice helper classes and add .NET assembly references if required.

Now we’ve done our job in delivering the classes. It’s your turn to nurture them and make them beautiful.

Nice Features

Here is a small subset of the functions that we’ve worked with:

• Primary key mapping
  • Interprets “Id”, “ID”, “id”, “[tableName]_id”, “[tableName]Id” as you wanted to have a property named “Id”.
• Naming conventions
  • Removes spaces in table and column names
  • Converts underscore convention to Pascal case
  • Pluralization of namespaces
  • Singularization of entity names
• Readable SQL queries
  • Spans them over multiple lines
  • Uses proper indentation
• Safe SQL queries
  • Uses named parameters
  • Handles transition from DBNull to primitives (i.e. NULL is replaced with -1 and vice versa)
• Tests
  • Centralizes the db connection creation to ease test maintenance
  • Factory method to generate a working entity (where foreign key properties are skipped by design)
  • Random values are generated for the created entity (upon code generation, i.e., static when the test runs)
  • Tests for all repository methods.
  • Uses MSTest/FluentAssertions for the tests

Generated Code

Below is all code produced for a single entity. It’s provided exactly as the generator produced it.

Interface

namespace DemoApp.Suppliers
{
    /// <summary>
    ///     Repository definition for <c>Supplier</c>.
    /// </summary>
    /// <remarks>
    ///     <para>
    ///         This definition do not include any query methods as you should typically only include them
    ///         once they are actually needed. I do recommend that you name them after where they are used to
    ///         make it easier to maintain and remove old queries when the application age.
    ///     </para>
    ///     <para>
    ///         You could for instance name a method <code>GetUsersForIndexPage</code>.
    ///     </para>
    /// </remarks>
    public interface ISupplierRepository
    {
        /// <summary>
        ///     Create a new entity.
        /// </summary>
        /// <param name="entity">Entity to insert into the table</param>
        /// <exception cref="DataException">Will include the 
        /// insert statement and all <c>IDbCommand</c> arguments</exception>
        /// <remarks>
        ///     <para>
        ///         Will throw an exception if the entity already exists.
        ///     </para>
        /// </remarks>
        /// <exception cref="DataException">DB operation failed 
        /// (exception will include command details)</exception>
        /// <exception cref="ArgumentNullException">entity</exception>
        Task CreateAsync(Supplier entity);

        /// <summary>
        ///     Delete an entity.
        /// </summary>
        /// <param name="id">primary key</param>
        /// <exception cref="ArgumentOutOfRangeException">
        /// Id is not within the expected range.</exception>
        /// <exception cref="DataException">Db operation failed</exception>
        Task DeleteAsync(int id);

        /// <summary>
        ///     Delete an entity.
        /// </summary>
        /// <param name="entity">entity</param>
        /// <remarks>
        ///     <para>
        ///         Will use the primary in the specified entity.
        ///     </para>
        /// </remarks>
        /// <exception cref="ArgumentNullException">entity</exception>
        /// <exception cref="DataException">Db operation failed</exception>
        Task DeleteAsync(Supplier entity);

        /// <summary>
        ///     Load an existing entity.
        /// </summary>
        /// <param name="id">primary key</param>
        /// <returns>Entity</returns>
        /// <exception cref="ArgumentOutOfRangeException">
        /// Id is not within the expected range.</exception>
        /// <exception cref="EntityNotFoundException">
        /// Entity did not exist in the database</exception>
        /// <exception cref="DataException">Db operation failed</exception>
        Task<Supplier> GetAsync(int id);

        /// <summary>
        ///     Update existing entity.
        /// </summary>
        /// <param name="entity">Entity to update</param>
        /// <remarks>
        ///     <para>
        ///         Will not try to create the entity if it do not exist.
        ///     </para>
        /// </remarks>
        /// <exception cref="DataException">DB operation failed 
        /// (exception will include command details)</exception>
        Task UpdateAsync(Supplier entity);
    }
}

Entity

namespace DemoApp.Suppliers
{
    public class Supplier
    {
        public int Id { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string Region { get; set; }
        public string PostalCode { get; set; }
        public string Country { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
        public string HomePage { get; set; }
    }
}

Repository Class

Uses extension methods that are included by the generator.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Threading.Tasks;
using DemoApp.Shared;

namespace DemoApp.Suppliers
{
    public class SupplierRepository : ISupplierRepository
    {
        private readonly DbTransaction _transaction;

        /// <summary>
        ///     Create a new instance of <see cref="SupplierRepository" />.
        /// </summary>
        /// <param name="transaction">Active transaction</param>
        /// <exception cref="ArgumentNullException">transaction</exception>
        public SupplierRepository(IDbTransaction transaction)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            _transaction = transaction as DbTransaction;
            if (_transaction == null) throw new NotSupportedException
            ("Async operations are not declared on the interfaces, 
            you need to use drivers that inherit the ADO.NET base classes.");
        }

        /// <summary>
        ///     Create a new entity.
        /// </summary>
        /// <param name="entity">Entity to create a database row from.</param>
        /// <exception cref="DataException">Will include the 
        /// insert statement and all <c>IDbCommand</c> arguments.</exception>
        /// <remarks>
        ///     <para>
        ///         Will throw an exception if the entity already exists.
        ///     </para>
        /// </remarks>
        /// <exception cref="ArgumentNullException">entity</exception>
        public async Task CreateAsync(Supplier entity)
        {
            if (entity == null) throw new ArgumentNullException("entity");

            var cmd = _transaction.Connection.CreateCommand();
            try
            {
                cmd.Transaction = _transaction;
                cmd.CommandText = @"INSERT INTO Suppliers (CompanyName, ContactName, 
                ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage)
                                    VALUES(@CompanyName, @ContactName, @ContactTitle, @Address, 
                                    @City, @Region, @PostalCode, @Country, @Phone, @Fax, @HomePage);
                                    SELECT CAST(SCOPE_IDENTITY() AS INT);";
                                    
                cmd.AddParameter("CompanyName", entity.CompanyName);
                cmd.AddParameter("ContactName", entity.ContactName);
                cmd.AddParameter("ContactTitle", entity.ContactTitle);
                cmd.AddParameter("Address", entity.Address);
                cmd.AddParameter("City", entity.City);
                cmd.AddParameter("Region", entity.Region);
                cmd.AddParameter("PostalCode", entity.PostalCode);
                cmd.AddParameter("Country", entity.Country);
                cmd.AddParameter("Phone", entity.Phone);
                cmd.AddParameter("Fax", entity.Fax);
                cmd.AddParameter("HomePage", entity.HomePage);

                var id = (System.Int32)await cmd.ExecuteScalarAsync();
                entity.Id = id;
            }
            catch (Exception exception)
            {
                throw cmd.CreateDataException(exception);
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        ///     Delete an entity
        /// </summary>
        /// <param name="id">primary key</param>
        /// <exception cref="ArgumentOutOfRangeException">
        /// Id is less than 1.</exception>
        /// <exception cref="DataException">Db operation failed</exception>
        public async Task DeleteAsync(int id)
        {
            if (id == 0) throw new ArgumentOutOfRangeException
            	("id", id, "Must be 1 or larger.");

            var cmd = _transaction.Connection.CreateCommand();
            try
            {
                cmd.Transaction = _transaction;
                cmd.CommandText = @"DELETE FROM Suppliers
                                    WHERE SupplierID = @SupplierID";
                cmd.AddParameter("SupplierID", id);

                await cmd.ExecuteNonQueryAsync();
            }
            catch (Exception exception)
            {
                throw cmd.CreateDataException(exception);
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        ///     Delete
        /// </summary>
        /// <param name="entity">entity</param>
        /// <remarks>
        ///     <para>
        ///         Will use the primary key in the specified entity.
        ///     </para>
        /// </remarks>
        /// <exception cref="ArgumentNullException">entity</exception>
        /// <exception cref="DataException">Db operation failed</exception>
        public async Task DeleteAsync(Supplier entity)
        {
            if (entity == null) throw new ArgumentNullException("entity");

            var cmd = _transaction.Connection.CreateCommand();
            try
            {
                cmd.Transaction = _transaction;
                cmd.CommandText = @"DELETE FROM Suppliers
                                    WHERE SupplierID = @SupplierID";
                cmd.AddParameter("SupplierID", entity.Id);

                await cmd.ExecuteNonQueryAsync();
            }
            catch (Exception exception)
            {
                throw cmd.CreateDataException(exception);
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        ///     Load an existing entity
        /// </summary>
        /// <param name="id">primary key</param>
        /// <returns>Entity</returns>
        /// <exception cref="ArgumentOutOfRangeException">
        /// Id is less than 1.</exception>
        /// <exception cref="EntityNotFoundException">
        /// Entity did not exist in the database</exception>
        /// <exception cref="DataException">Db operation failed</exception>
        public async Task<Supplier> GetAsync(int id)
        {
            if (id == 0) throw new ArgumentOutOfRangeException
            ("id", id, "Must be 1 or larger.");

            var cmd = _transaction.Connection.CreateCommand();
            try
            {
                cmd.Transaction = _transaction;
                cmd.CommandText = @"SELECT SupplierID, CompanyName, ContactName, 
                ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage
                                    FROM Suppliers
                                    WHERE SupplierID = @SupplierID";
                cmd.AddParameter("SupplierID", id);

                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    if (!await reader.ReadAsync())
                        throw new EntityNotFoundException(typeof(Supplier), 
                        "Failed to find 'Supplier' with id '" + id + "'.");

                    var entity = new Supplier();
                    Map(reader, entity);
                    return entity;
                }
            }
            catch (Exception exception)
            {
                throw cmd.CreateDataException(exception);
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        ///     Update existing entity
        /// </summary>
        /// <param name="entity">Entity to update</param>
        /// <remarks>
        ///     <para>
        ///         Will not try to create the entity if it do not exist.
        ///     </para>
        /// </remarks>
        /// <exception cref="DataException">Db operation failed</exception>
        public async Task UpdateAsync(Supplier entity)
        {
            var cmd = _transaction.Connection.CreateCommand();
            try
            {
                cmd.Transaction = _transaction;
                cmd.CommandText = @"UPDATE Suppliers SET
                                        CompanyName = @CompanyName, 
                                        ContactName = @ContactName, 
                                        ContactTitle = @ContactTitle, 
                                        Address = @Address, 
                                        City = @City, 
                                        Region = @Region, 
                                        PostalCode = @PostalCode, 
                                        Country = @Country, 
                                        Phone = @Phone, 
                                        Fax = @Fax, 
                                        HomePage = @HomePage
                                    WHERE SupplierID = @SupplierID";
                cmd.AddParameter("SupplierID", entity.Id);
                cmd.AddParameter("CompanyName", entity.CompanyName);
                cmd.AddParameter("ContactName", entity.ContactName);
                cmd.AddParameter("ContactTitle", entity.ContactTitle);
                cmd.AddParameter("Address", entity.Address);
                cmd.AddParameter("City", entity.City);
                cmd.AddParameter("Region", entity.Region);
                cmd.AddParameter("PostalCode", entity.PostalCode);
                cmd.AddParameter("Country", entity.Country);
                cmd.AddParameter("Phone", entity.Phone);
                cmd.AddParameter("Fax", entity.Fax);
                cmd.AddParameter("HomePage", entity.HomePage);

                await cmd.ExecuteNonQueryAsync();
            }
            catch (Exception exception)
            {
                throw cmd.CreateDataException(exception);
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        ///     Find all rows.
        /// </summary>
        /// <returns>Collection of entities (empty list if none is found).</returns>
        /// <exception cref="ArgumentOutOfRangeException">Id is less than 1.</exception>
        /// <exception cref="DataException">Db operation failed</exception>
        public async Task<IList<Supplier>> FindAllAsync(QueryOptions queryOptions = null)
        {
            var cmd = _transaction.Connection.CreateCommand();
            try
            {
                cmd.Transaction = _transaction;
                cmd.CommandText = @"SELECT SupplierID, CompanyName, ContactName, 
                ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage
                                    FROM Suppliers";
                if (queryOptions != null)
                {
                    if (!string.IsNullOrEmpty(queryOptions.SortColumn))
                    {
                        cmd.CommandText += " ORDER BY " + queryOptions.SortColumn;
                        if (queryOptions.SortOrder == QuerySortOrder.Descending)
                            cmd.CommandText += " DESC";
                    }
                    if (queryOptions.PageNumber > 0)
                    {
                        //SqlServer 2012 syntax.
                        var startRow = (queryOptions.PageNumber - 1)*queryOptions.PageSize;
                        cmd.CommandText += string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY",
                            startRow,
                            queryOptions.PageSize);
                    }
                }
                return await MapCollectionAsync(cmd);
            }
            catch (Exception exception)
            {
                throw cmd.CreateDataException(exception);
            }
            finally
            {
                cmd.Dispose();
            }
        }

        // requires that ALL columns are selected and that they
        // are in the correct order (more performance to use the int indexer)
        private static void Map(IDataRecord record, Supplier dto)
        {
            dto.Id = (int)record[0];
            dto.CompanyName = (string)record[1];
            dto.ContactName = record[2] is DBNull ? "" : (string)record[2];
            dto.ContactTitle = record[3] is DBNull ? "" : (string)record[3];
            dto.Address = record[4] is DBNull ? "" : (string)record[4];
            dto.City = record[5] is DBNull ? "" : (string)record[5];
            dto.Region = record[6] is DBNull ? "" : (string)record[6];
            dto.PostalCode = record[7] is DBNull ? "" : (string)record[7];
            dto.Country = record[8] is DBNull ? "" : (string)record[8];
            dto.Phone = record[9] is DBNull ? "" : (string)record[9];
            dto.Fax = record[10] is DBNull ? "" : (string)record[10];
            dto.HomePage = record[11] is DBNull ? "" : (string)record[11];
        }

        /// <summary>
        ///     Traverses over the entire recordset and converts the rows to items
        /// </summary>
        /// <param name="cmd">Command to invoke on</param>
        /// <returns>Collection</returns>
        private static async Task<List<Supplier>> MapCollectionAsync(DbCommand cmd)
        {
            using (var reader = await cmd.ExecuteReaderAsync())
            {
                var items = new List<Supplier>();
                while (await reader.ReadAsync())
                {
                    var item = new Supplier();
                    Map(reader, item);
                    items.Add(item);
                }
                return items;
            }
        }
    }
}

Test Class

using System;
using System.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using FluentAssertions;
using System.Threading.Tasks;
using DemoApp.Suppliers;

namespace DemoApp.Tests.Suppliers
{
    [TestClass]
    public class SupplierRepositoryTests
    {
        private IDbConnection _connection;
        private IDbTransaction _transaction;

        [TestMethod]
        public async Task can_insert_and_fetch_a_Row()
        {
            var entity = CreateCorrectEntity();

            var sut = new SupplierRepository(_transaction);
            await sut.CreateAsync(entity);
            var actual = await sut.GetAsync(entity.Id);

            actual.Should().NotBeNull();
        }

        [TestMethod]
        public async Task can_update_a_row()
        {
            var expected = CreateCorrectEntity();
            var sut = new SupplierRepository(_transaction);
            await sut.CreateAsync(expected);

            expected.CompanyName = "31506ddc59b946af8ed36e351e4e59f325486a06";
            await sut.UpdateAsync(expected);
            var actual = await sut.GetAsync(expected.Id);

            actual.CompanyName.Should().Be(expected.CompanyName);
        }

        [TestMethod]
        public async Task can_delete_row_using_id()
        {
            var entity = CreateCorrectEntity();
            var sut = new SupplierRepository(_transaction);
            await sut.CreateAsync(entity);

            await sut.DeleteAsync(entity);
            Func<Task> actual = async () => await sut.GetAsync(entity.Id);

            actual.ShouldThrow<DataException>("because deleted row cannot be found");
        }

        [TestMethod]
        public async Task can_delete_row_using_entity()
        {
            var entity = CreateCorrectEntity();
            var sut = new SupplierRepository(_transaction);
            await sut.CreateAsync(entity);

            await sut.DeleteAsync(entity);
            Func<Task> actual = async () => await sut.GetAsync (entity.Id);

            actual.ShouldThrow<DataException>("because deleted row cannot be found");
        }

        private Supplier CreateCorrectEntity()
        {
            return new Supplier
            {
                 CompanyName = "Da Griffin Framework!",
                 ContactName = "Da Griffin Framework!",
                 ContactTitle = "Da Griffin Framework!",
                 Address = "Da Griffin Framework!",
                 City = "dcacbc33100b473",
                 Region = "9d005e4d58f946d",
                 PostalCode = "b3f7a5a56f",
                 Country = "1dafad266674495",
                 Phone = "Da Griffin Framework!",
                 Fax = "Da Griffin Framework!",
                 HomePage = "Da Griffin Framework!",
            };
        }

        [TestInitialize]
        public void Init()
        {
            _connection = ConnectionFactory.Create();
            // using (var cmd = _connection.CreateCommand())
            // {
            //     cmd.CommandText = "TRUNCATE TABLE Suppliers";
            //     cmd.ExecuteNonQuery();
            // }

            _transaction = _connection.BeginTransaction();
        }

        [TestCleanup]
        public void Cleanup()
        {
            if (_transaction != null)
            {
                _transaction.Dispose();
                _transaction = null;
            }
            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }
    }
}

Action to Take

I’ve created this extension together with Emilie Crocker. This is hopefully just our first collaboration.

Go download the extension. It’s alpha and free.

Do note that it’s currently just for Microsoft SQL Server.

All kind of feedback is most welcome. Just leave a comment on this article.