4

I have C# application we've been coding in ADO.NET. I've been using the IDbCommand, and IDbConnection interfaces to create cross-database code.

It's all worked wonderfully so far (across Firebird, SQLite, SQL Server 2005, and 2008, Access 2007, and Access 2010, and Oracle 11g).

Problem I have, is I now have an Oracle 10g database I need to support.

All the "normal" stuff, creating connections, and commands works fine, however, when I go to create a parameter using the interface IDataParameter and cmd.CreateParamater() fails on 10g, because of parameter syntax in the query (I'm using parameterized queries).

Apparently, Oracle 10g, out of the box, doesn't support using the @ sign. Oracle 11g, SQL Server, and all the others mentioned do.

For instance, the following query will fail in 10g:

select * from Products where ProductId = @ProductId

But, if I use the colon, it succeeds just fine, using the above mentioned ado.net interfaces, so this query will succeed:

select * from Products where ProductId = :ProductId

Unfortunately, the colon doesn't work in most of the other database implementations.

Or is there an option that can be flipped in the Oracle 10g Database that allows for @ delimiter to be used in place of the : delimiter for parameters.

The current solution I have is less than ideal, I have the customer/client initializing the property ParameterDelimiter (that I default to the @ sign), and use a string.Format, to insert the ParameterDelimiter.

Is there any standard way of doing this that I'm missing, without having the customer have to pass me a delimiter, or without having my base libraries know about the database implementation? (For instance, including ODP.NET and checking against an OracleConnection)

Brian Deragon
  • 2,929
  • 24
  • 44
  • Is this SQL text you have embedded in your application? ("select * from..." – Peter Ritchie Dec 17 '11 at 15:42
  • Yes, I'm using a parameterized query, I'm generating the parameters, and substituting them with values they've passed in. – Brian Deragon Dec 17 '11 at 15:49
  • You might have to check the type of the connection and generate queries specific to Oracle. If you're going for abstraction of database classes, I would suggest creating a query generation classes accessed through an abstraction like an interface that you create when you create the IDbConnection... – Peter Ritchie Dec 17 '11 at 15:55
  • I can't check the OracleConnection type as that would require adding a reference to ODP.NET (I mentioned this in the question). Hence why I put the parameter delimiter in there. – Brian Deragon Dec 17 '11 at 16:08
  • We use the .Net connection strings element in app.config for our connection strings; this exposese a provider attribute. We check this provider attribute when creating our parameters, and substitute in @ or : (or _) depending on the provider (oracle, SQL Server, MySql). ALternatively, I believe Spring.Net gets around this by subclassing the entire hierarchy where necessary - so if you as for a DbConnection you get IDbConnection but underneath it's SqlServerConnection, OracleConnection, and, correspondingly, IDbParameter is SqlParameter or OracleParameter, and so on. – dash Dec 17 '11 at 16:20
  • @dash Theoeretically, that should be true for me too, I use a passed in IDbConnection, when I call the create command, and use the IDbCommand, I should really be using the underlying OracleConnection and OracleCommand classes. When i call the IDbCommand.CreateParamater, it should be returning to me an OracleParameter in the form of an IDataParameter. The problem isn't in the interface, it's in the fact that Oracle doesn't recognize an @ sign in it's queries. – Brian Deragon Dec 17 '11 at 16:28
  • 1
    I've never used Oracle, but can you use `?` and positional parameters? E.g. `select * from Products where ProductId = ?` – Michiel van Oosterhout Dec 17 '11 at 16:31
  • @michielvoo I don't know honestly, I'll give it a try! – Brian Deragon Dec 17 '11 at 16:34
  • You can use position parameters, but the answer below is brilliant - thanks Michielvoo! – dash Dec 17 '11 at 16:41

1 Answers1

8

For what it's worth, I did find this post:

Which parameter marker should I use? ADO.NET 2.0, Sql Server @, Oracle : (link is dead)

mentioned in this question:

Get the parameter prefix in ADO.NET

With the following code 'asking' the connection object for the information:

string format = connection
  .GetSchema("DataSourceInformation")
  .Rows[0]["ParameterMarkerFormat"]
  .ToString();

So that should be the 'standard way of doing this', also without having the customer pass the information and without having to know about the database implementation.

Edit: it must be added that System.Data.SqlClient.SqlConnection apparently returns {0} instead of @{0}.

Community
  • 1
  • 1
Michiel van Oosterhout
  • 22,839
  • 15
  • 90
  • 132