Dapper Multi-Database optimization

Dapper is a new ORM tool that has sprung up over the past 2 years, with the flexibility of the ADO-high performance and reflection mapping entities, ideal for programmers who prefer native SQL, and it's very small and lightweight. The purpose of my blog is not to introduce dapper, but to introduce me to the bug and some optimizations I encountered during the process of migrating the database using dapper, dapper has some problems with multi-database support, and I did the following 5 optimizations.

One: Dapper core inventory in an important bug is the default return type of each database caused by different, such as count,sum and some functions in different database return type is different.  For example, select COUNT (1) from Table; SQL Server return value type is Int,oracle is Decimal,mysql is a long. So

Connection. Query<int> ("SELECT count (*) from Table");

This statement runs under SQL Server without problems, and other databases will have an error due to type mismatch. Similarly, dapper requires that the type of the entity object be strictly corresponding to the database type one by one (mainly on the Int,byte and enum types). The workaround is to change the cast type to a generic conversion and return this sentence in the dapper code to Val is DBNull? Null:val;

Instead: Return val is DBNull? Null:Convert.ChangeType (val, type);

Two: There is no function to provide parameter prefix conversion, which can be replaced by self-writing method in the SetupCommand method.

Three: Dapper only provide the most basic ORM framework, to add and remove changes do not provide a simple method, Dapper extension class is to provide such functionality, but there is a shortage of this extension class: The change will be the entity all the fields are stitched into SQL, can not only change the part of the field, Treat fields of type nullable as normal fields. Here I have optimized: when stitching SQL to determine whether the value of the field is empty, empty fields are not processed.

Four: The official Dapper Extension class parameter prefix is used for "@", which does not conform to the specification, instead by

IDbConnection type judgment plus prefix.

Five: Add each database common paging method and top method.

There is only one reason to do so much optimization: the ability to migrate databases seamlessly. If you've been using SQL Server for a long time, you don't have to change

Dapper Multi-Database optimization