title: Multiple OleDbCommands producing System Resource Exceeded
body: <p>So basically I have a C# project, that iterates through every student (300 students) within a table called <code>Student</code> within a Microsoft Access 2016 database. In a single iteration for a single student by using other tables like <code>Mathematics</code>, <code>Reading</code> that have a 1-to-1 relationship with the <code>Student</code> table, to grab the data that belongs to that student. </p>
  
  <pre><code>                try
              {
                  OleDbCommand allStudents = new OleDbCommand("SELECT [NSN]"
                   + " FROM [Student]; ");
  
                  allStudents.Connection = conn;
                  OleDbDataAdapter allData = new OleDbDataAdapter(allStudents);
                  DataTable allTable = new DataTable();
                  allData.Fill(allTable);
  
                  foreach (DataRow dr in allTable.Rows)
                  {
                      string NSN = dr["NSN"].ToString();
  
                      OleDbCommand cmd = new OleDbCommand("SELECT * "
                          + "FROM (((([Student] s "
                          + "INNER JOIN [Student Extra] se ON se.[NSN] = s.[NSN]) "
                          + "INNER JOIN [Reading] r ON r.[NSN] = s.[NSN])"
                          + "INNER JOIN [Writing] w ON w.[NSN] = s.[NSN])"
                          + "INNER JOIN [Mathematics] m ON m.[NSN] = s.[NSN]) "
  
                          + "WHERE s.[NSN] = '" + NSN + "'; ");
                      cmd.Connection = conn;
                      OleDbDataAdapter daa = new OleDbDataAdapter(cmd);
                      DataTable dtt = new DataTable();
                      daa.Fill(dtt);
  
  
                      foreach (DataRow drr in dtt.Rows)
                      {
                          firstName = drr["Preferred Name"].ToString();
                          gender = drr["Gender"].ToString();
                          room = drr["Room Number"].ToString();
                          NSAchieve = drr["National Standard Achieve"].ToString();
                          NSProgress = drr["National Standard Progress"].ToString();
  </code></pre>
  
  <p>The above code is only a snippet of the code I have, but this is basically where the function will start.</p>
  
  <p>By using this data, I want to be able to go through several <code>SELECT</code> statements for other tables and compare them and produce a calculated value.</p>
  
  <pre><code>Dictionary&lt;string, OleDbCommand&gt; d = new Dictionary&lt;string, OleDbCommand&gt;(); 
  cmd = new OleDbCommand("SELECT [Achievement Statement]"
          + " FROM [National Standard Codes]"
          + " WHERE [National Standard Code] = '" + readingNSAchievementCode + "'; ");
          d["readingNSAchievementOTJ"] = cmd;
  
          cmd = new OleDbCommand("SELECT [" + NSAchieve + "]"
         + " FROM [Reading National Standards]"
         + " WHERE [Assessment] = '" + readingFinalAssessment + "'; ");
          d["readingNSAchievementComp"] = cmd;
  
          cmd = new OleDbCommand("SELECT [Timeframe]"
          + " FROM [Reading Statements]"
          + " WHERE [Year Code] = '" + NSProgress + "'; ");
          d["readingNSProgressTimeframe"] = cmd;
  </code></pre>
  
  <p>There are several more commands, (approx &lt;150). I use a <code>Dictionary</code> to store my Commands, and then execute the commands in a <code>FOREACH</code> loop.</p>
  
  <pre><code>foreach(KeyValuePair&lt;string, OleDbCommand&gt; pair in d)
          {
              try
              {
                  string v = pair.Key;
                  OleDbCommand dbCmd = pair.Value;
  
  
                  dbCmd.Connection = conn;
                  OleDbDataReader reader = dbCmd.ExecuteReader();
                  reader.Read();
                  readingDict[v] = reader.GetString(0);
  
              }
              catch (Exception e)
              {
                  MessageBox.Show("Error at " + pair.Key + "\n\n Here is message " + e);
              }
          }
  </code></pre>
  
  <p>After executing and getting my value, I want to store my data into another table called <code>Calculated</code>.</p>
  
  <pre><code>            string insert1 = "INSERT INTO [Calculated] (";
          int i = 0;
          Dictionary&lt;string, string&gt; dict = createDictionary(NSN);
          int len = dict.Count / 2;
          foreach (KeyValuePair&lt;string, string&gt; pair in dict)
          {
              string field = pair.Key;
              string value = pair.Value;
  
              if (i == (len - 1))
              {
                  insert1 += "[" + field + "])";
                  break;
              }
              else
              {
                  insert1 += "[" + field + "], ";
              }
  
              i++;
          }
          insert1 += " VALUES (";
          i = 0;
          foreach (KeyValuePair&lt;string, string&gt; pair in dict)
          {
              string field = pair.Key;
              string value = pair.Value;
  
              if (i == len - 1)
              {
                  insert1 += "'" +  value + "')";
                  break;
              }
              else
              {
                  insert1 += "'" + value + "', ";
              }
  
              i++;
          }
  </code></pre>
  
  <p>I build my <code>INSERT INTO</code> query, and then I execute using an <code>OleDbCommand</code>. This needs to repeat 300 times, but for development purposes currently I only have 5 students in my <code>Student</code> table. However when executing after the 4th student it will always consistently give me an error <code>System Resources Exceeded</code> always at a specific <code>OleDbCommand</code>. I have tested each command separately, so there is no issue with the way the <code>OleDbCommand</code>s are written.</p>
  
  <p>I have tried searching on here, and tried to encase the first code snippet in a <code>using</code> statement, using <code>using (OleDbConnection conn = new OleDbConnection(connectionStr))</code> but as I am still a novice at <code>C#</code>, I am unable to produce a solution.</p>