title: Update or delete current row of database results
body: <p>I'm trying to port some old VB6 code to C# and .NET.</p>
  
  <p>There are a number of places where the old code uses a <code>RecordSet</code> to execute a SQL query and then loop through the results. No problem so far, but inside the loop the code makes changes to the current row, updating columns and even deleting the current row altogether.</p>
  
  <p>In .NET, I can easily use a <code>SqlDataReader</code> to loop through SQL query results, but updates are not supported.</p>
  
  <p>So I've been playing with using a <code>SqlDataAdapter</code> to populate a <code>DataSet</code>, and then loop through the rows in a <code>DataSet</code> table. But the <code>DataSet</code> doesn't seem very smart compared to the VB6's old <code>RecordSet</code>. For one thing, I need to provide update queries for each type of edit I have. Another concern is that a <code>DataSet</code> seems to hold everything in memory at once, which might be a problem if there are many results.</p>
  
  <p>What is the best way to duplicate this behavior in .NET? The code below shows what I have so far. Is this the best approach, or is there another option?</p>
  
  <pre><code>using (SqlConnection connection = new SqlConnection(connectionString))
  {
      DataSet dataset = new DataSet();
      using (SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(query, connection)))
      {
          adapter.Fill(dataset);
  
          DataTable table = dataset.Tables[0];
          foreach (DataRow row in table.Rows)
          {
              if ((int)row["Id"] == 4)
              {
                  if ((int)row["Value1"] &gt; 0)
                      row["Value2"] = 12345;
                  else
                      row["Value3"] = 12345;
              }
              else if ((int)row["Id"] == 5)
              {
                   row.Delete();
              }
          }
  
          // TODO:
          adapter.UpdateCommand = new SqlCommand("?", connection);
          adapter.DeleteCommand = new SqlCommand("?", connection);
  
          adapter.Update(table);
      }
  }
  </code></pre>
  
  <p><em>Note: I'm new to the company and can't very well tell them they have to change their connection strings or must switch to Entity Framework, which would be my choice. I'm really looking for a code-only solution.</em></p>