Update or delete current row of database results

Question

I'm trying to port some old VB6 code to C# and .NET.

There are a number of places where the old code uses a RecordSet 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.

In .NET, I can easily use a SqlDataReader to loop through SQL query results, but updates are not supported.

So I've been playing with using a SqlDataAdapter to populate a DataSet, and then loop through the rows in a DataSet table. But the DataSet doesn't seem very smart compared to the VB6's old RecordSet. For one thing, I need to provide update queries for each type of edit I have. Another concern is that a DataSet seems to hold everything in memory at once, which might be a problem if there are many results.

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?

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"] > 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);
    }
}

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.

Answer 1

ADO.NET DataTable and DataAdapter provide the closest equivalent of ADO Recordset with applies separation of concens principle. DataTable contains the data and provides the change tracking information (similar to EF internal entity tracking) while DataAdapter provides a standard way to populate it from database (Fill method) and apply changes back to the database (Update method).

With that being said, what are you doing is the intended way to port the ADO Recordset to ADO.NET. The only thing you've missed is that you are not always required to specify Insert, Update and Delete commands. As soon as your query is querying a single table (which I think was a requirement to get updateable Recordset anyway), you can use another ADO.NET player called DbCommandBuilder:

Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated database.

Every database provider provides implementation of this abstract class. The MSDN example for SqlCommandBuilder is almost identical to your sample, so all you need before calling Update is (a bit counterintuitive):

var builder = new SqlCommandBuilder(adapter); 

and that's it.

Behind the scenes,

The DbCommandBuilder registers itself as a listener for RowUpdating events that are generated by the DbDataAdapter specified in this property.

and dynamically generates the commands if they are not specifically set in the data adapter by you.

Answer 2

I came up with an (untested) solution for a data table. It does require you to do some work, but it should generate update and delete commands for each row you change or delete automatically, by hooking up to the RowChanged and RowDeleted events of the DataTable.

Each row will get it's own command, equivalent to ADODB.RecordSet update / delete methods.

However, unlike the ADODB.RecordSet methods, this class will not change the underling database, but only create the SqlCommands to do it. Of course, you can change it to simply execute them on once they are created, but as I said, I didn't test it so I'll leave that up to you if you want to do it. However, please note I'm not sure how the RowChanged event will behave for multiple changes to the same row. Worst case it will be fired for each change in the row.

The class constructor takes three arguments:

1. The instance of the DataTable class you are working with.
2. A Dictionary<string, SqlDbType> that provides mapping between column names and SqlDataTypes
3. An optional string to represent table name. If omitted, the TableName property of the DataTable will be used.

Once you have the mapping dictionary, all you have to do is instantiate the CommandGenerator class and iterate the rows in the data table just like in the question. From that point forward everything is automated.

Once you completed your iteration, all you have to do is get the sql commands from the Commands property, and run them.

public class CommandGenerator
{
    private Dictionary<string, SqlDbType> _columnToDbType;
    private string _tableName;
    private List<SqlCommand> _commands;

    public CommandGenerator(DataTable table, Dictionary<string, SqlDbType> columnToDbType, string tableName = null)
    {
        _commands = new List<SqlCommand>();
        _columnToDbType = columnToDbType;
        _tableName = (string.IsNullOrEmpty(tableName)) ? tableName : table.TableName;

        table.RowDeleted += table_RowDeleted;
        table.RowChanged += table_RowChanged;
    }

    public IEnumerable<SqlCommand> Commands { get { return _commands; } }

    private void table_RowChanged(object sender, DataRowChangeEventArgs e)
    {
        _commands.Add(GenerateDelete(e.Row));
    }

    private void table_RowDeleted(object sender, DataRowChangeEventArgs e)
    {
        _commands.Add(GenerateDelete(e.Row));
    }

    private SqlCommand GenerateUpdate(DataRow row)
    {

        var table = row.Table;
        var cmd = new SqlCommand();
        var sb = new StringBuilder();
        sb.Append("UPDATE ").Append(_tableName).Append(" SET ");

        var valueColumns = table.Columns.OfType<DataColumn>().Where(c => !table.PrimaryKey.Contains(c));

        AppendColumns(cmd, sb, valueColumns, row);
        sb.Append(" WHERE ");
        AppendColumns(cmd, sb, table.PrimaryKey, row);

        cmd.CommandText = sb.ToString();
        return cmd;
    }

    private SqlCommand GenerateDelete(DataRow row)
    {
        var table = row.Table;
        var cmd = new SqlCommand();
        var sb = new StringBuilder();
        sb.Append("DELETE FROM ").Append(_tableName).Append(" WHERE ");
        AppendColumns(cmd, sb, table.PrimaryKey, row);
        cmd.CommandText = sb.ToString();
        return cmd;
    }

    private void AppendColumns(SqlCommand cmd, StringBuilder sb, IEnumerable<DataColumn> columns, DataRow row)
    {
        foreach (var column in columns)
        {
            sb.Append(column.ColumnName).Append(" = @").AppendLine(column.ColumnName);
            cmd.Parameters.Add("@" + column.ColumnName, _columnToDbType[column.ColumnName]).Value = row[column];
        }
    }
}

As I wrote, this is completely untested, but I think it should be enough to at least show the general idea.

Answer 3

Your constraints:

• Not using Entity Framework

• DataSet seems to hold everything in memory at once, which might be a problem if there are many results.

• a code-only solution ( no external libraries)

Plus

• The maximum number of rows that a DataTable can store is 16,777,216 row MSDN

• To get high performance

  //the main class to update/delete sql batches without using DataSet/DataTable.
  
  
  public class SqlBatchUpdate
      {
          string ConnectionString { get; set; }
          public SqlBatchUpdate(string connstring)
          {
              ConnectionString = connstring;
          }
  
          public int RunSql(string sql)
          {
              using (SqlConnection con = new SqlConnection(ConnectionString))
              using (SqlCommand cmd = new SqlCommand(sql, con))
              {
                  cmd.CommandType = CommandType.Text;
                  con.Open();
                  int rowsAffected = cmd.ExecuteNonQuery();
                  return rowsAffected;
              }
          }
      }
  
  //------------------------
  // using the class to run a predefined patches
  
   public class SqlBatchUpdateDemo
      {       
         private string connstring = "myconnstring";
  
         //run batches in sequence
      public void RunBatchesInSequence()
      {
          var sqlBatchUpdate = new SqlBatchUpdate(connstring);
  
          //batch1
          var sql1 = @"update mytable set value2 =1234 where id =4  and Value1>0;";
          var nrows = sqlBatchUpdate.RunSql(sql1);
          Console.WriteLine("batch1: {0}", nrows);
  
          //batch2
          var sql2 = @"update mytable set value3 =1234 where      id =4   and Value1 =0";
            nrows = sqlBatchUpdate.RunSql(sql2);
          Console.WriteLine("batch2: {0}", nrows);
  
          //batch3
          var sql3 = @"delete from  mytable where id =5;";
            nrows = sqlBatchUpdate.RunSql(sql3);
          Console.WriteLine("batch3: {0}", nrows);
  
  
      }
  
          // Alternative: you can run all batches as one 
              public void RunAllBatches()
              {
                  var sqlBatchUpdate = new SqlBatchUpdate(connstring );
                  StringBuilder sb = new StringBuilder();
                  var sql1 = @"update mytable set value2 =1234 where id =4  and Value1>0;";
                  sb.AppendLine(sql1);
  
                  //batch2
                  var  sql2 = @"update mytable set value3 =1234 where     id =4   and Value1 =0";
                  sb.AppendLine(sql2);
  
                  //batch3
                 var sql3 = @"delete from  mytable where  id =5;";
                  sb.AppendLine(sql3);
  
                  //run all batches
                  var   nrows = c.RunSql(sb.ToString());
                  Console.WriteLine("all patches: {0}", nrows);
              }
  
  
      }
  

I simulated that solution and it's working fine with a high performance because all updates /delete run as batch.