SqlDataReader object throwing an exception every time an operation is performed on it

Question

I am working on a project in VS13 and SQL Server 2012 and I am facing a little problem. I can't seem to be read data from my database. It throws exception whenever I try to do any operation on the SqlDataReader object I use to read the data.

The exception I am getting is InvalidOperationException.

Look at this code of mine, I call this function with the SQL query as the parameter and stores the returned object in another SqlDataReader object.

private SqlDataReader reader (string sqCommand)
{
        myConnection.Open();
        string string1;
        string1 = sqCommand;
        SqlDataReader a = null;

        try
        {
            SqlCommand Newcommand = new SqlCommand(string1, myConnection);
            a = Newcommand.ExecuteReader();
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        }

        myConnection.Close();

        if (a.Read())      //**statement 1**
            return a;
        else
            return null;
    }

I gets the exception in the statement 1 marked in the code above and in every operation I perform on the object a or the object that receives this.

Can anyone please tell me what is wrong with my code or provide any other explanation? If more other parts of the code is needed for the purpose of finding for the error, I can provide it.

Thanks for the time and the help you may provide. :)

Have a look on this post may be this will help you. http://stackoverflow.com/questions/23187029/sqldatareader-invalidoperationexception — Fanjo Lama, Apr 03 '16 at 10:36

score 4 · Accepted Answer · answered Apr 03 '16 at 10:37

4

Your connection needs to stay open while you're using the SqlDataReader. A more conventional use for your SqlDataReader would be as follows:

private List<object> Reader(string sqCommand)
{
    using (SqlConnection myConnection = new SqlConnection(ConnectionString))
    {
        myConnection.Open();

        using (SqlCommand cmd = new SqlCommand(sqCommand, myConnection))
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            List<object> list = new List<object>();

            while (reader.Read())
            {
                list.Add(reader[0]);
            }

            return list;
        }
    }
}

answered Apr 03 '16 at 10:37

Kirill Shlenskiy

9,367
27
39

Thanks for your help. i figured the connection being open while i use the reader is necessary a minute ago while trying to fix it. i just happen to read the information provided by the exception and it said the connection is closed so it cant do what i wanted it to do. i actually fixed the problem by opening the connection in the form_load function and closing it at the end(while exiting) as this is just a login form. – Sahil Soni Apr 03 '16 at 11:04
@SahilSoni, even though you've gotten the code to work as you mentioned, I suggest you refactor according to Kirill's example. A best practice is to follow a "open late close early" pattern, leveraging IDisposable (using) to manage resources. – Dan Guzman Apr 03 '16 at 13:19
@DanGuzman okay, i will do the necessary modifications for "open late close early". the project i am working on is kinda a waste, it wont be used anywhere, but i have to make this because of my college's curriculum. I have another project on m mind that i will be working on from June and that will be BIG! I will post my queries on this site if i wont be able to fix them myself. – Sahil Soni Apr 04 '16 at 17:25

SqlDataReader object throwing an exception every time an operation is performed on it

1 Answers1