CODEWITHSUNDEEP
c#sql-server
Sahil Soni
Sahil Soni

Reputation: 41

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

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

Upvotes: 1

Views: 3165

Answers (1)

Kirill Shlenskiy
Kirill Shlenskiy

Reputation: 9587

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;
        }
    }
}

Upvotes: 4

Related Questions