Using Dapper-Extensions with Microsoft Access (*.mdb)

[Steinblock]

Today I started a project, where I have to perform CRUD operations on a legacy access database.

I ran into some small issues that could be resolved very quickly.

Problem: Insert operations fail, because of wrong syntax

The generated code looked like this

INSERT INTO [TABLE] ([TABLE].[FIELD1], [TABLE].[FIELD2]) VALUES (...)

This seems to be an issue with access because it does not like the [TABLE].[FIELD1] syntax

Solution: Use a custom ISqlDialect

    public class AccessSqlDialect : SqlServerDialect
    {
        public override string GetColumnName(string prefix, string columnName, string alias)
        {
            return base.GetColumnName(null, columnName, alias);
        }
    }

Now I only had to tell DapperExtensions to use my class

DapperExtensions.DapperExtensions.SqlDialect = new AccessSqlDialect();

Problem: Insert operations mix up columns

This one is potentially dangerous. The insert operations inserts values in wron columns. I have not found the real cause which could be inside dapper or even within the oledb driver. The following code:

public class Product
{
   public string Name {get;set;}
   public string Code {get;set;}
}

public void TestInsert()
{

    var conn = CreateConnection();

    var product = new Product();
    product.Code = "123";
    product.Name = "Some product";

    conn.Insert(product);
}

the generated code looks good: INSERT INTO [PRODUCT] ([NAME], [CODE]) VALUES (@Name, @Code)

Eventually the code that performs the insert is this one (Inside DapperImplementer.cs)
connection.Execute(sql, entity, transaction, commandTimeout, CommandType.Text);

This method is inside Dapper itself. The second parameter is a dynamic, so the raw code would look like

connection.Execute(sql, new { Name = "Some Product", Code = "123" }, ...

I suppose the Names are ignored and the order is important. However, the parameters from my class are passed in alphabetical order, which causes the insert to create a new record with Code = "Some Product" and Name = "123"

Solution: Use a custom ISqlGenerator

I just interited from SqlGeneratorImpl and copied the Insert code. The only real change is that I added OrderBy(p => p.Name) to the columns assignment.

    public class AccessSqlGeneratorImpl : SqlGeneratorImpl
    {

        public AccessSqlGeneratorImpl(IDapperExtensionsConfiguration configuration)
            : base(configuration)
        {
        }

        public override string Insert(Mapper.IClassMapper classMap)
        {
            var columns = classMap.Properties.Where(p => !(p.Ignored || p.IsReadOnly || p.KeyType == KeyType.Identity)).OrderBy(p => p.Name);
            if (!columns.Any())
            {
                throw new ArgumentException("No columns were mapped.");
            }

            var columnNames = columns.Select(p => GetColumnName(classMap, p, false));
            var parameters = columns.Select(p => Configuration.Dialect.ParameterPrefix + p.Name);

            string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
                                       GetTableName(classMap),
                                       columnNames.AppendStrings(),
                                       parameters.AppendStrings());

            Console.WriteLine(sql);

            return sql;
        }

    }

These changes have been easy to implement. My issues were

• The ExtensionMethod AppendStrings comes from ReflectionHelper which is internal
  --> I had to copy the extension method to my code
• I could not inject my AccessSqlGeneratorImpl into DapperExtensions because DapperImplementator is internal

I used this workaround to fix this

            DapperExtensions.DapperExtensions.InstanceFactory = config =>
            {

                var generator = new AccessSqlGeneratorImpl(config);
                var typeName = "DapperExtensions.DapperImplementor";
                var assembly = typeof(DapperExtensions.DapperExtensionsConfiguration).Assembly;
                var instance  = assembly.CreateInstance(typeName, true,
                    BindingFlags.Default | BindingFlags.CreateInstance | BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public, 
                    null,
                    new Object[] { generator },
                    null, 
                    null);

                return (IDapperImplementor)instance;
            };

instead of

DapperExtensions.DapperExtensions.InstanceFactory = config => new DapperImplementor(new AccessSqlGeneratorImpl(config));

Conclusion

I don't see any issued with making both classes public, so I would love to see this in the near future, so I can streamlin my codebase.

[tmsmith]

These classes will be made public with the next release