Menu
I am trying to use Dapper.net with Oracle.
From this post i understood that we could use parameters with no prefixes and dapper would then work for both sql serverand oracle
I'm having a hard time making it to work without the explicit oracle named parameters prefix :
The following query
sqlConnection.Query("Select * FROM document WHERE id = param1", new { param1 = 963 });
Throws ORA-00904: "PARAM1": invalid identifier
If i try with the @ prefix it throws ORA-00936: missing expression
If i use the : prefix it works as expected. But i do not want my queries to be dependent (as far as possible) upon Oracle or Sql Server.
I am using the latest nuget package version Dapper.dll 1.12.1.1
What am I doing wrong or did i misunderstand this post?
503
Dapper provides support for executing parameterised queries in a number of ways. Parameter values can be passed to commands as anonymous types: Dapper also provides a DynamicParameters class, which represents a "bag" of parameter values. You can pass an object to its constructor. Suitable objects include a Dictionary<string, object>:
Parameter values can be passed to commands as anonymous types: Dapper also provides a DynamicParameters class, which represents a "bag" of parameter values. You can pass an object to its constructor. Suitable objects include a Dictionary<string, object>:
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.
An example is as follows: Dapper replaces values with literals before issuing the SQL, so the RDBMS actually sees something like: This is particularly useful when allowing RDBMS systems to not just make better decisions, but to open up query plans that actual parameters prevent.
Yes, you misunderstood the post. The SQL is passed through as-is, and must contain the correct :param1 or @param1 etc. The "no prefix at all" is talking about the code that you don't see - specifically, making sure that the code does (via some mechanism):
cmd.Parameters.Add("param1", 963);
vs
cmd.Parameters.Add("@param1", 963);
vs
cmd.Parameters.Add(":param1", 963);
The first (no prefix) is the correct and preferred option.
If you want the SQL in your code to be parameter agnostic, you could use the information from here: Get the parameter prefix in ADO.NET
The SQL is rarely close enough, however, that just looking up the parameter prefix will fix all problems.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
