Using Dapper with Oracle

Question

We use Oracle as our database provider and have looked into replacing some of our data access layer (hard to maintain, harder to merge XSD's) with a saner repository based pattern using Dapper at the bottom layer. However, we have hit a number of issues when using it with oracle.

• Named Parameters: these seem to be ignored, whenever they are used in a query Oracle seems to interpret them in any order it fancies. The SqlMapper returns correctly named parameters, they just aren't interpreted correctly in Oracle

• The "@" naming convention for variables is incompatible with oracle named parameters. It expects to see ":" in front of any parameters

Has anybody previously encountered this and have any workarounds?

Answer 1

IMO, the correct approach here is not to (as per the accepted answer) use the database specific parameter prefix (so @ for sql-server, : for oracle) - but rather: use no prefix at all. So ultimately this is:

il.Emit(OpCodes.Ldstr, prop.Name);

(etc)

In particular, a static property would be bad as it would limit you to one vendor per AppDomain.

Dapper has been updated with this change. It also now dynamically detects BindByName and sets it accordingly (all without needing a reference to OracleCommand).