13

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?

Wolfwyrd
  • 15,716
  • 5
  • 47
  • 67

2 Answers2

15

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).

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • does this mean, i can use dapper with oracle with no modification need? – Benny Jan 13 '14 at 07:36
  • @Benny that is the idea, yes – Marc Gravell Jan 13 '14 at 07:37
  • I am still confused, what do you mean by not to use the database specific parameter prefix, can you give an example? thanks – Benny Jan 14 '14 at 15:22
  • @Benny no, because the example is in the code you don't ever see. Your query must include the prefix. The "no prefix" is what happens in cmd.Parameters.Add(...) - the difference between Add("@foo") vs Add("foo"). The latter is more correct. – Marc Gravell Jan 14 '14 at 19:17
  • 1
    I see now, so the no prefix only works when using cmd.Parameters.Add(). if in the direct query sql command, e.g. 'select * from foo where col = @foo1', i still have to use parameter prefix, but this way means the sql query command is bind to a specific db provider, '@' is for sql server, ':' is for oracle, the sql command is not trans-db, :( – Benny Jan 15 '14 at 06:05
  • @Benny my response there is that it is a **very small** minority of cases where the only difference is the `@foo` vs `:foo`. Most interesting SQL will have platform specific features *anyway*. Fixing the parameter naming won't address any of that. – Marc Gravell Jan 15 '14 at 09:53
6

Resolution of the named parameter issue turned out to be because Oracle commands require the BindByName property set to true. To resolve this required a tweak to the SqlMapper itself. This is a bit nasty as the tweak isnt portable (it relies on a type check for a specific Oracle Command) but it works for our needs for the moment. The change involves updating the SetupCommand method, after creating the command form the connection object we type check and set the flag like so (~ln 635):

var cmd = cnn.CreateCommand();
if (cmd is OracleCommand)
{
    ((OracleCommand)cmd).BindByName = true; // Oracle Command Only
}

Finally to address the issue of the "@" to ":" problem in parameter names involved altering the CreateParamInfoGenerator method. I added a static string - DefaultParameterCharacter setting its value to ":" then modified ln 530 from:

il.Emit(OpCodes.Ldstr, "@" + prop.Name); // stack is now [parameters] [c

to

il.Emit(OpCodes.Ldstr, DefaultParameterCharacter + prop.Name); // stack is now [parameters] [command] [name] (Changed @ to : for oracle)

and ln 546 from:

il.Emit(OpCodes.Ldstr, "@" + prop.Name); // stack is now [parameters] [parameters] [parameter] [parameter] [name] (Changed @ to : for oracle)

to:

il.Emit(OpCodes.Ldstr, DefaultParameterCharacter + prop.Name); // stack is now [parameters] [parameters] [parameter] [parameter] [name] (Changed @ to : for oracle)

This made dapper work flawlessly with Oracle commands

Wolfwyrd
  • 15,716
  • 5
  • 47
  • 67
  • ouch ... what a gotcha ... I don't mind pulling some of this into dapper as long as will pull in no dependencies ... also ... what is the story with stored procs in oracle then – Sam Saffron Jun 02 '11 at 10:53
  • Good question, not had to dapper one of them up yet. I'll try one this afternoon and post my findings up here. – Wolfwyrd Jun 02 '11 at 11:29
  • Here's a cleaner way of sorting the BindByName problem, this is portable - var cmd = cnn.CreateCommand(); var bindByName = cmd.GetType().GetProperties().Where(propertyInfo => propertyInfo.Name == "BindByName").FirstOrDefault(null); if (bindByName != null) { bindByName.SetValue(cmd, true, null); } (We reflect on the command to see if it has the BindByName property then set it if it does. This goes in the SetupCommand method) – Wolfwyrd Jun 02 '11 at 11:44
  • actually, we would have to bake a dynamic method there to keep it fast – Sam Saffron Jun 02 '11 at 12:09
  • Just run a stored proc. Runs fine with the same changes. – Wolfwyrd Jun 02 '11 at 15:51
  • @Sam @Wolfwyrd I have a cunning plan for BindByName - however, can you advise on whether the oracle parameters work if you just specify them as "foo" (in the .ParameterName, still :foo in the sql) instead of "@foo" / ":foo"? That would be our best option (if it works) – Marc Gravell Jun 02 '11 at 18:15
  • @Marc - Stripping the prefix out of the param name but keeping it in the SQL works fine in Oracle. Seems promising – Wolfwyrd Jun 06 '11 at 08:12
  • @Wolfwyrd - cool; I've already added the code to dynamically toggle `BindByName` - I'll remove the `@`s and check it still works ;p – Marc Gravell Jun 06 '11 at 09:04
  • @Wolfwyrd the change to remove the `@` is now in (to google-code, not to nuget). Let me know how you get on. – Marc Gravell Jun 06 '11 at 12:18
  • Nice one, thanks. I've flagged up your better code answer appropriately – Wolfwyrd Jun 06 '11 at 12:42
  • @Marc - Yep, just pulled it down and it works like a charm. Nice support :) – Wolfwyrd Jun 07 '11 at 08:16
  • @MarcGravell, wolfwyrd: i'm trying to use the named parameters without the : prefix in oracle with no success. I posted a question here http://stackoverflow.com/questions/20962405/dapper-net-oracle-parameter. I'd highly appreciate if you'd care to take a look. Thanks – Luis Filipe Jan 07 '14 at 11:13
  • Some further discussion can be found [here](https://stackoverflow.com/q/3876856/1394393). – jpmc26 Jul 03 '17 at 22:17