up vote 0 down vote favorite

I have a base class that contains a SqlDataReader which gets the data using a stored procedure, this works great until I return the data reader back up saying the connection is null.

Does anyone have any ideas? here's my code:

public SqlDataReader GetDataReader(string QueryName, SqlParameter[] Params)
{
   SqlConnection conn = new SqlConnection(this.ConnectionString);
   SqlDataReader reader;

   using (conn)
   {
      SqlCommand command = new SqlCommand(QueryName,conn);
      command.CommandType = CommandType.StoredProcedure;

      if(Params !=null)
          command.Parameters.Add(Params);

      conn.Open();

      reader = command.ExecuteReader();                
   }

   // conn.Close();

   return reader;
}

If you notice, I have the close part commented out, this was me trying to get it to work, for some reason when returning the datareader back up it is set to close???

Thanks!

share|improve this question
1  
Quite honestly: I would never pass around an "opened" SqlDataReader - that's a horrible design and just a recipe for disaster. Instead, have your method actually read the data, put it into a List<Something>, close your SqlDataReader again as quickly as possible, and return that list back to the caller... – marc_s Nov 14 '11 at 9:16
what if you have the reader in a base class? Surely thats better design than copying and pasting the code everytime you want to access data as opposed to just using the method in the bass class which returns a reader – Funky Nov 14 '11 at 14:42

6 Answers

up vote 2 down vote accepted

You want to do the following:

    SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection)

and don't close the connection. When you close the datareader it will close it for you if you use the code above.

share|improve this answer
Oh, and like DeveloperX said don't use the 'using' statement because you need the connection to stay open until the datareader is done. – Brandon Moore Nov 14 '11 at 9:02
up vote 2 down vote

when you use using (conn) it automatically disposes the conn object

share|improve this answer
up vote 2 down vote

Because your connection is closed and disposed in using block before returning the reader . See this SO post

share|improve this answer
Its not the reader, but connection being closed and disposed. – Piotr Auguscik Nov 14 '11 at 9:01
There is using(conn) not using(reader) – Piotr Auguscik Nov 14 '11 at 9:03
fine , updated , thanks. – Muse Nov 14 '11 at 9:04
up vote 1 down vote

DataReader is a connected object. Means to get the data from the DataReader, the connection underneath need to be open at that moment. This is not so if you work with DataSets, which work in disconnected mode. You are closing the connection in your code before you return the DataReader. So the DataReader cannot give you any data.

A better design alternative might be providing the connection from outside (dependency injection).

share|improve this answer
up vote 2 down vote

You are using "using" which does the same things as calling conn.Close().

share|improve this answer
up vote 2 down vote

When using (conn) block finishes, it closes the database connection, that's why you are getting that error. Just delete that line.

share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.