HangfireIO/Hangfire — pull request #258 by andrecarlucci (2014-11-27 19:59:24+00:00)
Title: Hangfire.Sql: easier way to add IDb providers. Oracle support included! 

Hi @odinserj,

I created the Hangfire.Sql as a common project for IDb providers. (we can rename it later to SqlStorage as you suggested).

It turns out Dapper is very poor regarding Oracle support, so it was impossible to just swap sqls, I had to make some methods virtual and override them with different implementations.

Some examples:
- Query sometimes doesn't work and Oracle would return strings instead of int. Don't ask me why.
- I can't use "start", "end", "from", "to" as parameter's names so I had to change them.
- QueryMultiple doesn't work at all. I changed them to multiple selects (only in Oracle).
- EnlistTransaction method doesn't exist for IDbConnections, so there was no way to use implicit transactions. I changed that to connection.BeginTransaction everywhere. @frankhommers did the same in his Postgree provider.

There is a class called SqlBook that contains all sql statements. New providers can extend it and replace the sqls as I did for the Oracle one. If it's impossible to just swap the sql, create a subclass and override the specific method of some Hangfire.Sql class.

After refactoring the project to use explicit transactions, I start getting many errors since SqlServer doesn't support concurrent transactions for the same connection. I changed the storage to use a connection provider instead of reusing the same connection everywhere. This leads to many performance improvements since we create, use and dispose (put it back at the connection pool) the connection as fast as we can.

TODO for Oracle (I can do it):
- create an OracleDistributedLock implementation
- refactor the integration tests so we can run them on any database

Please, take a look at what I did and let's discuss good ideas :)
Thanks so much for this great project.