Monday, March 06, 2006

Identifier Quoting with OracleClient implementation in .net

There are a couple of very annoying programming choices in the OracleClient provided in the .net framework.

1) QuotePrefix and QuoteSuffix on the OracleCommandBuilder return String.Empty if they have not been set by a user of the builder. Yet, if you QuoteIdentifier(string) you will get back a quoted string - using the default Oracle identifier of double quotes. This works the same way as OleDb and Odbc because those don't have a default, but breaks with the more intelligent design of the SqlClient which will return the correct brackets when queried.

2) Oracle has an odd habit of upcasing any identifier which has not been quoted. That being the case if you have mixed case identifiers you need to quote them. Unfortunately, The OracleCommandBuilder doesn't allow any way to quote identifiers. According to the documentation, the column and table names are retrieved in a case-sensitive manner, ah how true (retrieved fromDbDataReader.GetSchemaTable) - yet they are set in the generated Sql statements as they are received - which Oracle then Up-cases making them incorrect. A simple property on the builder of "QuoteAllIdentifiers" would have solved this problem. As it is, you cannot use mixed case identifiers with the OracleCommandBuilder. I tried using mappings with an escaped quote on each identifier to no avail.

3) Finally, if you attempt to use the UnquoteIdentifier(string) method on the OracleCommandBuilder, it throws an exception! WTF! Again, this is in contrast to the SqlClient implementation which correctly just returns your string.
Submit this story to DotNetKicks


j. montgomery said...

Is this with the MS Oracle client in .NET 1.1 or 2.0 (or both). Also have you tried ODP.NET (Oracle Data Provider) from Oracle and does it have the same problem?

gabe19 said...

This is the .net provider in the 2.0 framework. It has nothing to do with ODP which I have used for comparison's sake on other items, but have not investigated for these issues.