C# – i got “Invalid attempt to call Read when reader is closed” when using sqldatareader how to solve it in a three tier architecture

asp.netc

when i click on linkbutton it gives error that is "Invalid attempt to call Read when reader is closed" my DAL method which returns dr is

private SqlDataReader getDownload(string sql)
{
SqlDataReader dr;
using (SqlConnection con = ConnectionManager.GetDatabaseConnection())
{
SqlCommand cmd = new SqlCommand("getInfo", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@query", SqlDbType.VarChar).Value = sql;
cmd.Connection = con;
dr = cmd.ExecuteReader();
}
return dr;
}

another DAL method is

public SqlDataReader getDownload(int auto_id)
{
string sql = "select mfile_name,file_data from Viva_Notice where auto_id=" + auto_id;
SqlDataReader dr = getDownload(sql) ;
return dr;
}

my BLL method is

public SqlDataReader getDownload(int field)
{
GetPostAssign mGetPostAssign = new GetPostAssign();
SqlDataReader dr = mGetPostAssign.getDownload(field);
return dr;
}

when i am calling it then getting "Invalid attempt to call Read when reader is closed"

protected void lnkDownload_Click(object sender, EventArgs e)
{
try
{
LinkButton lnkbtn = sender as LinkButton;
GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;
if (gvrow.RowIndex < 0)
return;
int field = Convert.ToInt32(lnkbtn.Attributes["RowIndex"]);
SqlDataReader dr = MclsAssignment.getDownload(field);
if (dr.Read())
{
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + dr["mfile_name"] + "\"");
Response.BinaryWrite((byte[])dr["file_data"]);
Response.End();
}
}
catch (Exception)
{    
throw;
}
}

Best Answer

This is really a design issue - you can't really return a "live" data reader if you intend on destroying the connection, the SqlDataReader is dependant on it i.e. 

SqlDataReader dr;
using (SqlConnection con = ConnectionManager.GetDatabaseConnection())
{
    SqlCommand cmd = new SqlCommand("getInfo", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@query", SqlDbType.VarChar).Value = sql;
    cmd.Connection = con;
    dr = cmd.ExecuteReader();
} // the SqlConnection is disposed here
return dr; // dr is now invalid

On top of that, you are leaking your implementation detail through to your BLL/UI layers by returning SqlDataReader. You should read the data whilst the connection is active and return the actual data instead to keep things nice & clean e.g.

public class Download
{
     public string Name { get; set; }
     public byte[] Data { get; set; }
}
...
private Download getDownload(string sql)
{
    using (SqlConnection con = ConnectionManager.GetDatabaseConnection())
    using (SqlCommand cmd = new SqlCommand("getInfo", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@query", SqlDbType.VarChar).Value = sql;
        con.Open();
        Using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                return new Download
                {
                    Name = (string)dr["mfile_name"],
                    Data = (byte[])dr["file_data"]
                };
            }
        }
    }
}
Related Topic