Getting an error: Invalid attempt to call Read when reader is closed

Question

I have 2 select statements in a stored procedure and getting result set in a data reader. My objective is to merge the data from 2 selects into a single JSON string.

I am using nested do while and at the end of the while, I am getting an error

Invalid attempt to call Read when reader is closed.

Below is the code:

try
        {
            
                con.Open();
            
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SPWorkMetaData";
            
            
            rd = cmd.ExecuteReader();
            
            List<Dictionary<String, Object>> tableRow = new List<Dictionary<string, object>>();
            List<Dictionary<String, Object>> tableRow1 = new List<Dictionary<string, object>>();
            Dictionary<String, Object> rows;
            
            if (rd.HasRows)
            {
                do 
                {
                    dt = new DataTable();
                    dt.Load(rd);

                    foreach (DataRow dr in dt.Rows)
                    {
                        rows = new Dictionary<string, object>();
                        int i = 1;
                        foreach (DataColumn col in dt.Columns)
                        {
                            rows.Add(col.ColumnName, dr[col].ToString());
                            i = i + 1;
                        }
                        tableRow.Add(rows);
                        status = "true";
                    }

                    retvalue = serializer.Serialize(tableRow).ToString();
                    //response = "{\"status\":\"" + status + "\",\"data\":" + retvalue + "}";
                    do
                    {
                        DataTable dt1 = new DataTable();
                        dt1.Load(rd);
                        foreach (DataRow dr in dt1.Rows)
                        {
                            Dictionary<String, Object> rows1 = new Dictionary<String, Object>();
                            int i = 1;
                            foreach (DataColumn col in dt1.Columns)
                            {
                                rows1.Add(col.ColumnName, dr[col].ToString());
                                i = i + 1;
                            }
                            tableRow1.Add(rows1);
                            status = "true";
                        }
                        retvalue = serializer.Serialize(tableRow).ToString() + serializer.Serialize(tableRow1).ToString();
                        response = "{\"status\":\"" + status + "\",\"data\":" + retvalue + "}";
                    }
                    while (rd.Read());
                }
                while (rd.NextResult()) ;
               
                
            }

        }

Does the error means to convey that SQL connection is closed, if so, then it is mentioned that I am closing connection only in the finally block.

Need to understand this, kindly provide some guidance.

Thanks.

Answer 1

Well, you don't note if the two tables returned are the same data structure?

and that then begs the question why a union query is not being used?

Also I don't think (doubt) you want to serialized a data row, since a data row has "extra" things like is the row dirty (been changed) and quite a few more "extra" attributes that I doubt you want to become part of the json result for the client.

So, lets pull the two tables (and ignore WHY a union query is not being used here!!!).

But, say I have two tables. tblHotels, and People - both of them have FirstName, lastname columns.

So, in theory, we need a clean data structure if we going to get a "reasonable" json string.

So, I would suggest this code:

    class PersonName
    {
        public string FirstName = "";
        public string LastName = "";
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand("Test2", conn))
            {
                SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
                conn.Open();
                DataSet1 ds = new DataSet1();
                da.Fill(ds);

                List<PersonName> MyNames = new List<PersonName>();

                // merge records form both tables (FirstName, LastName) into list
                foreach (DataRow OneRow in ds.Tables[0].Rows)
                {
                    PersonName OneName = new PersonName();
                    OneName.FirstName = OneRow["FirstName"].ToString();
                    OneName.LastName = OneRow["LastName"].ToString();
                    MyNames.Add(OneName);
                }

                // now merge 2nd table
                foreach (DataRow OneRow in ds.Tables[1].Rows)
                {
                    PersonName OneName = new PersonName();
                    OneName.FirstName = OneRow["FirstName"].ToString();
                    OneName.LastName = OneRow["LastName"].ToString();
                    MyNames.Add(OneName);
                }

                // at this point, we now have a merge of both tables
                JavaScriptSerializer js = new JavaScriptSerializer();

                string MyJSON = js.Serialize(MyNames);
            }
        }

    }