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.

CodePudding user response：

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

    }