SqDataReader closed issue in .net

Question

i have created a function which executes query and returns SqlDataReader, now i am using that in another function work with the returned data, but i gets the error saying reader is already closed. here is the functions:

public static SqlDataReader ExecuteReader(string procedure, SqlParameter[] parameters, CommandType commandType)
    {
        SqlDataReader reader = null;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(procedure, connection))
            {
                connection.Open();
                if(parameters != null)
                {
                    if (commandType == CommandType.StoredProcedure)
                        command.Parameters.AddRange(parameters);
                }
                reader = command.ExecuteReader();
            }
        }
        return reader;
    }

here is the code where i am calling the SqlDataReader

using (SqlDataReader reader = SqlHelper.ExecuteReader("select top 10 username from users", null, System.Data.CommandType.Text))
        {
            Response.Write(reader.IsClosed); //This returns True

        }

hi just check my edit you can makse use of commandbehaviour for this issue have look code in my edit. that will help you — Pranay Rana, Aug 21 '12 at 19:24

Pranay Rana · Accepted Answer · 2012-08-23T10:09:05.610

EDIT

ExecuteReader with CommanBehavior ( automatically close connection after reading data)

To over come connection closing proble just make use of CommandBheviour

- CommandBehavior.CloseConnection 
   When you pass above values as argument to ExecuteReader 
     1. there is no need to close connection explicitly connection get close when you close your reader.

code will be like this no need to close connection explicitly

public void CreateMySqlDataReader(string mySelectQuery,string myConnectionString) 
{
   SqlConnection myConnection = new SqlConnection(myConnectionString);
   SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
   myConnection.Open();
   SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
   while(myReader.Read()) 
   {
      Console.WriteLine(myReader.GetString(0));
   }
   myReader.Close();
   //Implicitly closes the connection because CommandBehavior.CloseConnection was specified.
}

its causing problem because you are closing connection

SqlReader always make use of open connection i.e live connection which is open when you use this

using (SqlConnection connection = new SqlConnection(connectionString)) 
        {
}

it dispose connection object which is used by reader object that why its returing IsColosed as true

If you wanto return value the objec than make use of DataTable which is disconnected data object and doens makse use of connection

Modified code

  public static DataTable ExecuteReader(string procedure, SqlParameter[] parameters, CommandType commandType)
  {
     DataTable dt = null;
     using (SqlConnection connection = new SqlConnection(connectionString))
     {
         using (SqlCommand command = new SqlCommand(procedure, connection))
         {
              connection.Open();
              if(parameters != null)
              {
            if (commandType == CommandType.StoredProcedure)
            command.Parameters.AddRange(parameters);
              }
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(dt);
           }
         }
            return dt;
        }

thanks @Pranay its working now, thats what the problem was, but if the connection is not closed wont it throw error if i call the same function again, as the connection is already opened and it will again attempt to open it, or shall i need to check for opened connection and closed it if opened, before attempting to open connection — Abbas, Aug 21 '12 at 19:11
You don't need to open the connection before using DbDataAdapter.Fill Method (DataTable) as it will open the connection if required and leave the connection in the state it was before the call. (Documentation: first remark in http://msdn.microsoft.com/en-us/library/905keexk%28v=vs.100%29 .) — Andrew Morton, Aug 21 '12 at 19:39
@AndrewMorton - but "using" close connection...i.e. dispose connection boject over here — Pranay Rana, Aug 21 '12 at 19:42

score 1 · Answer 2 · answered Aug 21 '12 at 19:19

DataReader needs an Open Connection. What you can do it either return a DataTable or Have custom class to represent the results of your SQL query and return an instance of that.

Create a Class to represent your Entity

public class Customer
{
  public int ID { set;get;}
  public string Name { set;get;}
}

And inside your method;

public List<Customer> GetCustomer()
{
  List<Customer> custList=new List<Customer>();
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
    using (SqlCommand command = new SqlCommand("yourParameterizedSQLQuery", 
                                                                  connection))
    {
     //Add parameters if needed
     connection.Open();

    using (var reader = cmd.ExecuteReader())
    {
       if (reader.HasRows)
       {
           cust=new Customer();
           while(reader.Read())
           {
             var cust=new Customer();
             // TO DO :Do db null checking before reading 
             cust.ID=reader.GetInt32(reader.GetOrdinal("ID"));
             cust.Name=reader.GetString(reader.GetOrdinal("Name"));
             custList.Add(cust);
           }
       }
     }
    }
  }  
 return custList;   
 }

score 0 · Answer 3 · answered Aug 21 '12 at 19:09

The problem is that you have using SqlConnection which closes the connection to your database when leaving the scope.

SqlDataReader needs a "still open" connection. Returning it to the parent does not keep the connection open.

Your choice are basically to return a DataSet, which is an "unconnected" data source or change the way you manage your connection to open it, use the SqlDataReader, close the connection.

score 0 · Answer 4 · answered Aug 21 '12 at 19:12

You may have to leave the connection open and let the calling code close the connection associated with the reader.

I had this challenge so I change my return type to DataTable

reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
return dt;

That way I don't have to worry about closing the connection outside that method

SqDataReader closed issue in .net

4 Answers4

EDIT