Using OracleDataReader and OracleConnection (or any .Net connection and reader object).
It's always important to close connections and readers. Aside from it being sloppy coding not to do so. Failure to do so can prevent the release of connections back to the connection pool. Here is an example of using a reader that using an OracleConnection and OracleDataReader object. I use the “using“ statement to make sure everything is cleaned up. Instead of a “using“ statement you can also use a try/finally, but the syntax of “using“ is just so much cleaner.
try
{
using(OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try{oracleConn.Open();}
catch{throw new LogonException();}
// create the sql statement....i always prefer StringBuilder over string
StringBuilder sql = new StringBuilder();
sql.AppendFormat( "select NVL(SUM(SOME_COLUMN),0) from SOME_TABLE where COLUMN_NAME=",name );
// build the command object
OracleCommand cmd = new OracleCommand(sql.ToString(),oracleConn);
cmd.CommandType = CommandType.Text;
using(OracleDataReader reader=cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
// read to get first (and only) record.
reader.Read();
// return a new object instance. i could check for DBNull, but my sqlstatement will guarantee a value.
return reader.GetDouble(0);
}
}
}
catch( LogonException )
{
// LogonException is an exception defined in my DataTier.
// just rethrow it. it is from our internal code block
throw;
}
catch( Exception ex )
{
// DataSourceException is an exception defined in my DataTier.
throw new DataSourceException( ex );
}