如何将集合转换为Ado的DataTable.净SqlDataAdapter.Update

本文关键字：DataTable SqlDataAdapter Update Ado 集合 转换 | 更新日期: 2023-09-27 18:11:02

如何完成下面的函数，该函数接受删除、插入和更新记录的三个集合参数，并将DataAdapter的集合转换为DataTable来更新表?

我在如何用List填充数据表中找到了一种将List转换为数据表的方法。然而，它不设置插入，更新和删除标志在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);
    }
}

或者有更好的方法从这三个集合更新数据库表?(c# 3.5)

如何将集合转换为Ado的DataTable.净SqlDataAdapter.Update

首先，您还需要定义插入，更新和删除命令:

// 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");

或者你可以尝试使用DbCommandBuilder在运行时为你做:

// 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();

接下来，您需要定义DataTables以匹配您的目标表:

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

然后需要向DataTable添加行，并确保将行标记为插入、更新或删除。

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();

它不会像你这样工作，像这样做

 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);
    }
相关文章：