c# SqlDatareader in my base class returns null connection error

Question

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!

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`, close your `SqlDataReader` again as quickly as possible, and return that list back to the caller... — marc_s, Nov 14 '11 at 09: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

score 2 · Answer 1 · edited May 23 '17 at 12:03

2

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

edited May 23 '17 at 12:03

Community

1
1

answered Nov 14 '11 at 08:57

Sandeep Pathak

10,567
8
45
57

Its not the reader, but connection being closed and disposed. – Piotr Auguscik Nov 14 '11 at 09:01

score 2 · Answer 2 · answered Nov 14 '11 at 08:57

2

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

answered Nov 14 '11 at 08:57

Dmitry Kruglov

318
1
9

score 2 · Answer 3 · answered Nov 14 '11 at 08:57

2

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

answered Nov 14 '11 at 08:57

MBen

3,956
21
25

score 2 · Answer 4 · edited Nov 14 '11 at 13:08

2

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

edited Nov 14 '11 at 13:08

codingbadger

42,678
13
95
110

answered Nov 14 '11 at 08:57

DeveloperX

4,633
17
22

score 2 · Accepted Answer · answered Nov 14 '11 at 09:01

2

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.

answered Nov 14 '11 at 09:01

Brandon Moore

8,590
15
65
120

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 09:02

score 1 · Answer 6 · answered Nov 14 '11 at 09:04

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).

c# SqlDatareader in my base class returns null connection error

6 Answers6