C# with Oracle ODP.NET

Question

int number;
DateTime dateTime;
using (var oracleConnection = new OracleConnection(ConnectionString))
using (var oracleCommand = oracleConnection.CreateCommand())
{
 oracleConnection.Open();
 oracleCommand.CommandText = "SELECT * FROM FROM MY_TABLE";
 using (var reader = oracleCommand.ExecuteReader())
 {
 while (reader.Read())
 {
 number = reader.GetInt32(0);
 dateTime = reader.GetDateTime(1); 
 }
 }
}

Which is a better way to avoid the calls such as reader.GetInt32(0)?

This way is too hard to read. A better way would be something like reader.GetInt32("ID") or reader.GetDateTime("Begin"), where ID and Begin are column names.

Or should I use enums?

• Jamal: The desire to improve code is implied for all questions on this site. Question titles should reflect the purpose of the code, not how you wish to have it reworked. See How to Ask.

Answer 1

A few comments:

Exceptions:

Keep in mind, even with using-statements, exceptions can happen. Make sure to catch them. I'd suggest, you wrap the whole snippet in a try-catch-block if that didn't happen already.

Types:

Why are you using the dynamyic type? Make it easier for readers and yourself by using the correct type. For yourself? Yes, if you use the explicit type, you get way better IntelliSense proposals.

Accessing:

reading a bit through the msdn documentation for OracleDataReader I found, that there is a property for the reader, named Item with 2 overloads. One being Item[String].

It seems they return the Column Value "in it's native format".

using (OracleDataReader reader = oracleCommand.ExecuteReader())
{
 while (reader.Read())
 {
 number = reader.Item["ID"];
 dateTime = reader.Item["Begin"]; 
 }
}

Update:
@Sandeep claims that the return type is in fact Object. If that is the case, you will have to run a conversion: number = Convert.ToInt32(reader.Item["ID"]);

Furthermore it seems that reader.Item[index] can actually be replaced by reader[index].

Answer 2

One way to use an IDataReader's nice GetX methods is to start off by retrieving the indexes with GetOrdinal calls:

using (var reader = oracleCommand.ExecuteReader())
{
 var ord_id = reader.GetOrdinal("ID");
 var ord_date = reader.GetOrdinal("Begin");
 while (reader.Read())
 {
 number = reader.GetInt32(ord_id);
 dateTime = reader.GetDateTime(ord_date);
 }
}