Parameter Separators

Question

I have a C # application that we encoded in ADO.NET. I use the IDbCommand and IDbConnection to generate cross-database code.

All this has worked so far (via Firebird, SQLite, SQL Server 2005 and 2008, Access 2007 and Access 2010 and Oracle 11g).

I have a problem: I now have an Oracle 10g database that I need to maintain.

All the “normal” things that make connections and commands work fine, however, when I go to create a parameter using the IDataParameter and cmd.CreateParamater() interfaces, it fails at 10g due to the syntax of the parameter in the request (I 'using parameterized queries).

Apparently, Oracle 10g, out of the box, does not support the use of the @ sign. Oracle 11g, SQL Server and all the others mentioned above.

For example, the following request will fail in 10g:

 select * from Products where ProductId = @ProductId 

But, if I use a colon, it does a great job using the above ado.net interfaces, so this request will succeed:

 select * from Products where ProductId = :ProductId 

Unfortunately, the colon does not work in most other database implementations.

Or there is an option that can be turned over in the Oracle 10g database, which allows you to use the @ separator instead of the separator : for parameters.

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

Is there any standard way to do this that I am missing without the client passing me a delimiter or not knowing that my base libraries are aware of the database implementation? (For example, including ODP.NET and OracleConnection )

Answer 1

For what it's worth, I found this post:

Which parameter marker should be used? ADO.NET 2.0, Sql Server @, Oracle: (link dead)

mentioned in this question:

Get parameter prefix in ADO.NET

In the following code, the "request" of the connection object is for information:

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

So, this should be the “standard way to do this,” also without the client passing the information and not knowing about the database implementation.

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