Oracle DataReader Closing Connection before I can read data

I have a function that returns an OracleDataReader object. Here it is :

 public OracleDataReader executeCommand(string query)
    {
        using (conn)
        {
            conn.ConnectionString = connectionString;
            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = query;
            OracleDataReader reader = cmd.ExecuteReader();
            return reader;
        }
    }

When I tried to to get data with reader in another class i get an exception:

public Person GetPersonById(int id)
    {
        OracleDBOp db = new OracleDBOp();
        String query = "select * from test_person where id=" + id;
        OracleDataReader reader = db.executeCommand(query);
        Person person = null;
        person = new Person(Convert.ToInt32(reader["id"]),reader["first_name"].ToString(),reader["last_name"].ToString());
        return person;
    }

Exception Invalid attempt to GetOrdinal when reader is closed.

What is the problem? I am not closing the reader?

Answers


Your code closes the connection as soon as the using scope is exited, so any attempts to read will fail:

using (conn)
{
   conn.ConnectionString = connectionString;
   conn.Open();
   ...
   return reader;
} --> Disposes of connection, which closes it, so reader can't read.

One option is to instead of placing the connection in the using block, if you intend passing the reader outside the control of the method which owns the connection, you can specify CommandBehavior.CloseConnection to close the connection when the reader is closed, but do NOT close or dispose of the connection i.e.

OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;

IMO, a safer pattern (provided that you play nicely with the iterator, e.g. with foreach), without leaving the responsibility of the disposal of resources to the caller, and without passing the reader outside of a function, would be to use yield return, although this will change the way your code works:

  public IEnumerable<Person> RetrievePeople()
  {
      using (var conn = new OracleConnection(connString))
      {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
          cmd.CommandText = query;
          using (var reader = cmd.ExecuteReader())
          {
            while (reader.Read())
            {
               yield return new new Person(
                 Convert.ToInt32(reader["id"]),
                 reader["first_name"].ToString(),
                 reader["last_name"].ToString());
            }
          }
        }
     }
  }

This way you don't need to pass the reader around, yet, the connection will be remain open until the iterator completes. This has the benefit of retaining the lazy evaluation approach of the reader, without actually passing the reader around, and without the issue of who is going to clean up the connections afterwards.


Need Your Help

Java - How to keep it running without console window

java parameters console

I developed a java application that needs to be run with additional arguments.

Error - A SignInResponse message may only redirect within the current web application - MVC 2.0 application

c# asp.net-mvc federated-identity adfs2.0 sts-securitytokenservice

I have a situation where we have a MVC 2 application(I tried this with a basic MVC 2 app without any extra stuff, still same problem) and am using adfs 2 for authenticating my users.