5

I wanted to use nested SqlDataReader in the code below but I couldn't make it.I get "System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first" with the code. Any Suggestions

            c = "select user_Reps.rep_key,Officers.Officer_Name from user_Reps left join Officers on user_Reps.rep_key = Officers.Officer_code where [user_key]="+userCode;
            if (c == null)
                c = sr.ReadToEnd();
            try
            {

                SqlCommand cmd = new SqlCommand(c, cn);
                cn.Open();
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                SqlDataReader rdr2;
                while (rdr.Read())
                {
                    string c2 = "select Active_Clients.Clients_code,Active_Clients.Clients_Name,Active_Clients.Geo_code  from Active_Clients where Active_Clients.[Officer_code] =" + rdr.GetValue(0) + " order by Clients_Name";
                    SqlCommand cmd2 = new SqlCommand(c2, cn);

                    rdr2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection);


                    Dictionary<string, object> d = new Dictionary<string, object>(rdr.FieldCount+rdr2.FieldCount);
                    while (rdr2.Read())
                    {
                        int i = 0;
                        for (; i < rdr.FieldCount; i++)
                        {
                            d[rdr.GetName(i)] = rdr.GetValue(i);
                        }

                        for (; i < rdr2.FieldCount; i++)
                        {
                            d[rdr2.GetName(i)] = rdr2.GetValue(i);
                        }

                        list.Add(d);
                    }




                    rdr2.Close();

                    //list.Add(d);
                }
                JavaScriptSerializer j = new JavaScriptSerializer();
                Response.Write(j.Serialize(list.ToArray()));

            }
Abdelwahed
  • 1,694
  • 4
  • 21
  • 31

3 Answers3

18

The error message is a bit misleading. Unless you have MultipleActiveResultSets=True in your connection string, you can have only one active result set per connection. This is still the case, if every reader hat it's own separate SqlCommand object.

Mithrandir
  • 24,869
  • 6
  • 50
  • 66
  • Cool, didn't know about this setting. [This answer](http://stackoverflow.com/questions/510899/multipleactiveresultsets-true-or-multiple-connections) has some more elaboration. – Kirk Woll Jun 01 '12 at 16:48
  • great.. agree with kirk ..din't know about this ! Ty Mith – Sunny Dec 01 '12 at 08:59
5

You cannot have two open DataReaders on the same connection at the same time.

A common solutions to this problem would be to load the contents of the first DataReader into a DataTable or List<> before opening the next DataReader. You could also open a new connection for the nested query.

Edit Or, as @Mithrandir mentioned, you can use the MultipleActiveResultSets=true on the connection string.

Steve Czetty
  • 6,147
  • 9
  • 39
  • 48
  • Sometimes changing connection strings, e.g., to add the MARS feature, is problematic, so these other two ideas mentioned are excellent options for different scenarios. Here are some other answers that include code: For the approach of loading the first DataReader into a DataTable: http://stackoverflow.com/a/11961818/1455215 For the approach of creating a second connection on the fly inside the datareader loop: http://stackoverflow.com/a/11961818/1455215 – Developer63 Nov 13 '15 at 18:46
1

Assuming it's on a sql server version 2005 or higher, enabling Multiple active resultsets (MARS) should do the trick I think. http://msdn.microsoft.com/en-us/library/h32h3abf%28v=vs.80%29.aspx. If MARS is not an option, a different connection should be opened for the second command.

Me.Name
  • 12,259
  • 3
  • 31
  • 48