How to convert collections to DataTable for Ado.Net SqlDataAdapter.Update?

Question

How to finish the following function which accept three collection parameters for deleted, inserted and updated records and convert the collections to a DataTable for DataAdapter to update the table?

I found a way to convert List to DataTable at How to fill a datatable with List<T>. However, it doesn't set the insert, update and delete flags in DataTable?

void Save(
    IEnumerable<int> deleted, 
    IEnumerable<Poco1> inserted, 
    IEnumerable<Poco1> updated)
{
    var dt = new DataTable(); 
    .... // Initialize dt with deleted, inserted and update?

    using (var con = new SqlConnection(ConnectionStr))
    {
        con.Open();
        var da = new SqlDataAdapter("select * from table", con);
        da.Update(dt);
    }
}

Or is there a better way to update the database table from these three collections? (C# 3.5)

Eric Scherrer · Accepted Answer · 2014-06-30T19:43:28.280

First off, your going to want to also define the Insert, Update and Delete commands:

// Create the other commands.
da.InsertCommand = new SqlCommand("...how to insert");

da.UpdateCommand = new SqlCommand("...how to update");

da.DeleteCommand = new SqlCommand("...how to delete");

Alternatively you can try to use DbCommandBuilder to do it for you at runtime:

// Create the DbCommandBuilder.
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = da;

// Get the insert, update and delete commands.
da.InsertCommand = builder.GetInsertCommand();
da.UpdateCommand = builder.GetUpdateCommand();
da.DeleteCommand = builder.GetDeleteCommand();

Next you need to define the DataTables to match the table you are targeting:

DataTable dt = new DataTable();
dt.Columns.Add(add your columns...)

Then you need to add rows to the DataTable, making sure to mark the row as inserted, updated or deleted.

DataRow dr = dt.NewRow();
dr["your column"] = ...
// Don't forget to add the row to the table!
dt.Rows.Add(dr);
// Once the row is added then go ahead and mark it as deleted, modified or new
dr.Delete()
// or
dr.SetAdded();
// or
dr.SetModified();

I got the exception of "Additional information: SetAdded and SetModified can only be called on DataRows with Unchanged DataRowState." when calling `dr.SetModified();`, etc. — ca9163d9, Jul 01 '14 at 16:04
Try calling .AcceptChanges() on the rows before setting their state. — Eric Scherrer, Jul 01 '14 at 16:49

score 0 · Answer 2 · answered Jun 30 '14 at 18:00

It wont work the way you are doing , do like this

 using (var con = new SqlConnection(ConnectionStr))
    {
        con.Open();
        var da = new SqlDataAdapter("select * from table", con);
        var ds=new DataSet();
        da.Fill(ds);
        var dt= ds.Tables[0];
        // all deleted rows 
        foreach(DataRow dr in dt.Rows.ToList())
{
    if(deleted.ToList().Contains((int)dr["id"]))
{
        dr.Delete();
}
//all updated rows 
foreach(var poco in updated.ToList()
{
  DataRow dr = table.Select("id="+poco.id).FirstOrDefault();
  dr["field1]=poco.feild1 
  ....set all updated values 

}
//all inserted rows 
foreach(var poco in inserted.ToList())
{
  var dr= dt.NewRow();
  dr["id"]=poco.id;
  ..set all fields
  dt.Rows.Add(dr);
}
}
        dt.Accept
        da.Update(dt);
    }

How to convert collections to DataTable for Ado.Net SqlDataAdapter.Update?

2 Answers2