Connecting to Oracle using ODP.NET

Question

What can be improved in my code:

 using (var oracleConnection = new OracleConnection(ConnectionString))
 {
 using (var oracleCommand = oracleConnection.CreateCommand())
 {
 oracleConnection.Open();
 oracleCommand.CommandText = 
 "SELECT * 
 FROM table_sample Table_Sample 
 WHERE table_sample.id > 1000";
 using (var reader = oracleCommand.ExecuteReader())
 {
 while (reader.Read())
 {
 int id = reader.GetValue(0);
 }
 }
 }
 }

As the most "dangerous" thing I see the ugly string statement for database query.

I dont want to use Entity Framework for Oracle - cause its not as actual as one for MS SQL Server. And also some Oracle features are not supported.

• ChrisWue: You mean like having parametrized queries? Check the docs: msdn.microsoft.com/en-us/library/…
• MikroDel: @ChrisWue maybe something that looks like EF syntax. Or anything else but not such a long string
• MikroDel: @ChrisWue have look at your link - as I understand it is only the opportunity to use parameters, but string itself stay as ugly as before :) But thanks for this link!
• svick: AFAIK, you can use EF with other databases, including Oracle.
• MikroDel: @svick its true. But the support for Oracle is not the best one. It is possible that in case of DB First some Oracle features cannot be used.

Answer 1

You're using using blocks to dispose your disposables, which is excellent. However these blocks increase the nesting of your code; since there's nothing between using (var oracleConnection = new OracleConnection(ConnectionString)) and using (var oracleCommand = oracleConnection.CreateCommand()) you could drop the curly braces and "stack" them, like this:

 using (var oracleConnection = new OracleConnection(ConnectionString))
 using (var oracleCommand = oracleConnection.CreateCommand())
 {
 ...
 }

Within that scope, you're reassigning variable Id at each row that gets read; ultimately the value of Id will be that of the last row that was read. I doubt this is the intended behavior.

As for the string query, I agree it's "dangerous" - I prefer (by far) to use an object-relational mapper such as Entity Framework (which as @svick has mentioned has an Oracle provider), but never used it for anything other than SQL Server. I believe you could look into NHibernate as well, or shop around - something like ".net ORM for oracle" should find you some interesting links :)

Answer 2

You have one or two issues in your SQL query:

• Pointless table aliasing: You aliased the table table_sample as Table_Sample, which is pointless since identifiers in Oracle are case-insensitive.
• Reliance on case-insensitivity of identifiers: Once you alias the table name, you are supposed to use the alias, not the original. Therefore, if you keep the pointless alias, your WHERE-clause should be WHERE Table_Sample > 1000 to be consistent. Your query happens to work, but only because Oracle identifiers are case-insensitive.

The most obvious solution is to get rid of the alias:

"SELECT * FROM table_sample WHERE table_sample.id > 1000"

Answer 3

I would not use select * as this will retrieve all the columns, instead you should be explicit as to what columns you want to retrieve.

Another potential issue with select * and then calling reader.getValue([index]) is that you are relying on the index you are using for each column to be the same as the the order which they are in the table - this isn't maintainable and open to bugs, again explicitly stating the columns will ensure that the order you are expecting the column data to be in is correct.