title: How to store results returned from SqlDataReader?
body: <p>I'm a rookie, as evinced by my question, and I'm using a datareader to find the rows associated with a certain subId value. I used a while(dr.read) loop and nested a switch case statement with other readers in each case (code below), but I threw the exception "already an open data reader associated with this command which must be closed first." Is there a way to store the results of the first datareader (the relevant rows where subId = x) in an array, or list, and then close that reader before I enter my switch statement? (I understand what an array is to the extent that I imagine it would work, but I haven't a clue what the syntax would look like).</p>
  
  <pre><code>string viewQuery = "SELECT ProductId FROM SubmissionProducts WHERE SubmissionId =" + x;
  
  
  
              using (SqlCommand viewcmd = new SqlCommand(viewQuery, editConn))
              {
                  SqlDataReader dr = viewcmd.ExecuteReader();
                  while (dr.Read())
                  {
                      switch(dr.GetInt32(0))
                      {
                          case 1:
                              PanelEplShow.Visible = true;
                              using (SqlCommand eplviewcmd = new SqlCommand(epl, editConn))
                              {
                                  SqlDataReader epldr = eplviewcmd.ExecuteReader();
                                  epldr.Read();
                                  LblEplShowEntity.Text = epldr.GetString(0);
                                  LblEplShowTotalEmpl.Text = epldr.GetInt32(1).ToString();
                                  LblEplShowCalEmpl.Text = epldr.GetInt32(2).ToString();
                                  LblEplShowMichEmpl.Text = epldr.GetInt32(3).ToString();
                                  LblEplShowNyEmpl.Text = epldr.GetInt32(4).ToString();
                                  LblEplShowNjEmpl.Text = epldr.GetInt32(5).ToString();
                                  LblEplShowPrimEx.Text = epldr.GetInt32(6).ToString();
                                  LblEplShowLim.Text = epldr.GetInt32(7).ToString();
                                  LblEplShowPrem.Text = epldr.GetInt32(8).ToString();
                                  LblEplShowWage.Text = epldr.GetInt32(9).ToString();
                                  LblEplShowInvestCost.Text = epldr.GetInt32(10).ToString();
                                  epldr.Close();
                              }
                              break;
                          case 2:
                              PanelProfShow.Visible = true;
                              using (SqlCommand profcmd1 = new SqlCommand(prof, editConn))
                              {
                                  SqlDataReader profdr = profcmd1.ExecuteReader();
                                  profdr.Read();
                                  LblProfShowPrimEx.Text = profdr.GetInt32(0).ToString();
                                  LblProfShowType.Text = profdr.GetInt32(1).ToString();
                                  LblProfShowLim.Text = profdr.GetInt32(2).ToString();
                                  LblProfShowRetention.Text = profdr.GetInt32(3).ToString();
                                  LblProfShowAtt.Text = profdr.GetInt32(4).ToString();
                                  LblProfShowPrem.Text = profdr.GetInt32(5).ToString();
                                  LblProfShowSublim.Text = profdr.GetInt32(5).ToString();
                                  LblProfShowEntity.Text = profdr.GetInt32(6).ToString();
                                  profdr.Close();
                              }
                              break;
                          case 3:
                              PanelCrimeShow.Visible = true;
                              using (SqlCommand crimcmd = new SqlCommand(crim, editConn))
                              {
                                  SqlDataReader crimdr = crimcmd.ExecuteReader();
                                  crimdr.Read();
                                  LblCrimeShowEntity.Text = crimdr.GetString(0);
                                  LblCrimeShowEmpl.Text = crimdr.GetInt32(1).ToString();
                                  LblCrimeShowPrimEx.Text = crimdr.GetInt32(2).ToString();
                                  LblCrimeShowLimA.Text = crimdr.GetInt32(3).ToString();
                                  LblCrimeShowDedA.Text = crimdr.GetInt32(4).ToString();
                                  LblCrimeShowPremA.Text = crimdr.GetInt32(5).ToString();
                                  LblCrimeShowLimB.Text = crimdr.GetInt32(6).ToString();
                                  LblCrimeShowDedB.Text = crimdr.GetInt32(7).ToString();
                                  LblCrimeShowPremB.Text = crimdr.GetInt32(8).ToString();
                                  crimdr.Close();
                              }
                              break;
                          case 4:
                              PanelFidShow.Visible = true;
                              using (SqlCommand fidcmd = new SqlCommand(fid, editConn))
                              {
                                  SqlDataReader fiddr = fidcmd.ExecuteReader();
                                  fiddr.Read();
                                  LblFidShowEntity.Text = fiddr.GetString(0);
                                  LblFidShowPrimEx.Text = fiddr.GetInt32(1).ToString();
                                  LblFidShowLim.Text = fiddr.GetInt32(2).ToString();
                                  LblFidShowSir.Text = fiddr.GetInt32(3).ToString();
                                  LblFidShowAtt.Text = fiddr.GetInt32(4).ToString();
                                  LblFidShowPrem.Text = fiddr.GetInt32(5).ToString();
                                  LblFidShowSublim.Text = fiddr.GetInt32(6).ToString();
                                  fiddr.Close();
                              }
                              break;
                          case 5:
                              PanelNotShow.Visible = true;
                              using (SqlCommand notcmd = new SqlCommand(not, editConn))
                              {
                                  SqlDataReader notdr = notcmd.ExecuteReader();
                                  notdr.Read();
                                  LblNotShowPrimEx.Text = notdr.GetInt32(0).ToString();
                                  LblNotShowCov.Text = notdr.GetInt32(1).ToString();
                                  LblNotShowSharedLim.Text = notdr.GetInt32(2).ToString();
                                  LblNotShowTradLim.Text = notdr.GetInt32(3).ToString();
                                  LblNotShowTradSir.Text = notdr.GetInt32(4).ToString();
                                  LblNotShowEplLim.Text = notdr.GetInt32(5).ToString();
                                  LblNotShowEplSir.Text = notdr.GetInt32(6).ToString();
                                  LblNotShowEplPrem.Text = notdr.GetInt32(7).ToString();
                                  LblNotShowSublim.Text = notdr.GetInt32(8).ToString();
                                  notdr.Close();
                              }
                              break;
                          case 6:
                              PanelPrivShow.Visible = true;
                              using (SqlCommand privcmd = new SqlCommand(priv, editConn))
                              {
                                  SqlDataReader privdr = privcmd.ExecuteReader();
                                  privdr.Read();
                                  LblPrivShowPrimEx.Text = privdr.GetInt32(0).ToString();
                                  LblPrivShowSharedLim.Text = privdr.GetInt32(1).ToString();
                                  LblPrivShowTradLim.Text = privdr.GetInt32(2).ToString();
                                  LblPrivShowTradAtt.Text = privdr.GetInt32(3).ToString();
                                  LblPrivShowTradSir.Text = privdr.GetInt32(4).ToString();
                                  LblPrivShowTradPrem.Text = privdr.GetInt32(5).ToString();
                                  LblPrivShowEplLim.Text = privdr.GetInt32(6).ToString();
                                  LblPrivShowEplSir.Text = privdr.GetInt32(7).ToString();
                                  LblPrivShowEplAtt.Text = privdr.GetInt32(8).ToString();
                                  LblPrivShowEplPrem.Text = privdr.GetInt32(9).ToString();
                                  LblPrivShowEplWage.Text = privdr.GetInt32(10).ToString();
                                  LblPrivShowEplSublim.Text = privdr.GetInt32(11).ToString();
                                  LblPrivShowFidLim.Text = privdr.GetInt32(12).ToString();
                                  LblPrivShowFidSir.Text = privdr.GetInt32(13).ToString();
                                  LblPrivShowFidAtt.Text = privdr.GetInt32(14).ToString();
                                  LblPrivShowFidPrem.Text = privdr.GetInt32(15).ToString();
                                  LblPrivShowFidSublim.Text = privdr.GetInt32(16).ToString();
                                  privdr.Close();
                              }
                              break;
                          case 7:
                              PanelPubShow.Visible = true;
                              using (SqlCommand pubcmd = new SqlCommand(pub, editConn))
                              {
                                  SqlDataReader pubdr = pubcmd.ExecuteReader();
                                  pubdr.Read();
                                  LblPubShowMark.Text = pubdr.GetInt32(0).ToString();
                                  LblPubShowTick.Text = pubdr.GetInt32(1).ToString();
                                  LblPubShowTrad.Text = pubdr.GetInt32(2).ToString();
                                  LblPubShowDic.Text = pubdr.GetInt32(3).ToString();
                                  LblPubShowLim.Text = pubdr.GetInt32(4).ToString();
                                  LblPubShowSecSir.Text = pubdr.GetInt32(5).ToString();
                                  LblPubShowAllSir.Text = pubdr.GetInt32(6).ToString();
                                  LblPubShowPrem.Text = pubdr.GetInt32(7).ToString();
                                  LblPubShowPrimEx.Text = pubdr.GetInt32(8).ToString();
                                  LblPubShowAtt.Text = pubdr.GetInt32(9).ToString();
                                  LblPubShowSublim.Text = pubdr.GetInt32(10).ToString();
                                  pubdr.Close();
                              }
                              break;
                          default:
                              break;
                      }
                  }
                  dr.Close();`
  </code></pre>