title: Loop through datatables to find matching values in one column
body: <p>I have a datatable which currently displays all the records from a database table.  I am using asp.net and ado.net.  The table is populated by a stored procedure which is simply <code>select * from table</code>.  When my view loads the table gets populated with all records.  I want the table to display only related records at a time until those records in the table have been authorised (accepted or rejected and then records are inserted to other tables) or a 'Next' button is clicked.  These records would be grouped by ID.  What ID that shows up first when the view loads just depends on what the first record ID is.  </p>
  
  <p>To give an example of data and how I want it displayed:
  All records:</p>
  
  <pre><code>PageID   AID   Name
  1        60    Book1
  2        60    Book7
  3        50    Book7
  4        60    Book8
  5        70    Book12
  6        70    Book7
  </code></pre>
  
  <p>The first ID is 60(AID) so I would want it to loop through the column, find all that are AID60, display them in the view, once all have been actioned, it would move to AID50 etc etc...</p>
  
  <pre><code>PageID   AID   Name
  1        60    Book1
  2        60    Book7
  4        60    Book8
  </code></pre>
  
  <p>What I have currently in the Controller to get the records is:</p>
  
  <pre><code>public ActionResult toAction()
  {
  
  SqlConnection con = new SqlConnection();
  con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstringpath"].ToString());
  
  List&lt;ActionModel&gt; AList = new List&lt;ActionModel&gt;();
  SqlCommand com = new SqlCommand("Action", con);
  com.CommandType = CommandType.StoredProcedure;
  SqlDataAdapter da = new SqlDataAdapter(com);
  DataTable dt = new DataTable();
  con.Open();
  da.Fill(dt);
  con.Close();
  
  
  foreach (DataRow dtRow in dt.Rows)
  {
      string CurrentAID = dtRow["AID"].ToString();
  
      if (dtRow["AID"].ToString().Equals(CurrentAID))
     {
  
  
  AList = (from DataRow dr in dt.Rows
  
                   select new ActionModel()
                   {
                       PageID = Convert.ToInt32(dr["PageID"]),
                       AID = Convert.ToInt32(dr["AID"]),
                       Name = Convert.ToString(dr["Name"])
  
                   }).ToList();
   }
  }
  
  
  
  return View(AList);
  }
  </code></pre>
  
  <p>I am aware the the above code is incorrect as the foreach loop does not work.  The idea would be to 
  Get the AID of the first record in the table, loop through the rest of the column looking for the same AID and add all records to the list to display to the view.  Once actioned in the view the next AID will populate.  (I have the Accept/Reject/Next buttons already)</p>
  
  <p>I just need to get the foreach working correctly to only show out one AID at a time.  I will not be passing an AID as a parameter so the likes of <code>Select * from Action where AID = @AID</code> will not work.</p>