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<Something>, close your SqlDataReader again as quickly as possible, and return that list back to the caller... — marc_s
– marc_s, Commented Nov 14, 2011 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
– Funky, Commented Nov 14, 2011 at 14:42

Brandon Moore · Accepted Answer · 2011-11-14 09:01:18Z

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, 2011 at 9:01

Brandon Moore

8,84015 gold badges69 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brandon Moore Over a year ago

Oh, and like DeveloperX said don't use the 'using' statement because you need the connection to stay open until the datareader is done.

Dmitry Kruglov · Accepted Answer · 2011-11-14 08:57:12Z

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, 2011 at 8:57

Dmitry Kruglov

3181 silver badge10 bronze badges

Comments

MBen · Accepted Answer · 2011-11-14 08:57:37Z

2

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

answered Nov 14, 2011 at 8:57

MBen

3,99623 silver badges25 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:03:10Z

2

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

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Nov 14, 2011 at 8:57

Sandeep Pathak

10.8k8 gold badges47 silver badges59 bronze badges

1 Comment

Piotr Auguscik Over a year ago

Its not the reader, but connection being closed and disposed.

codingbadger · Accepted Answer · 2011-11-14 13:08:07Z

2

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

edited Nov 14, 2011 at 13:08

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

answered Nov 14, 2011 at 8:57

DeveloperX

4,69319 silver badges22 bronze badges

Comments

Kangkan · Accepted Answer · 2011-11-14 09:04:49Z

1

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

answered Nov 14, 2011 at 9:04

Kangkan

15.6k11 gold badges79 silver badges116 bronze badges

Collectives™ on Stack Overflow

c# SqlDatareader in my base class returns null connection error

6 Answers 6

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related