博客

一、说明

批量更新和批量插入都可以用MySqlAdapter来做；

注意：DataRow.RowState；

DataRowState.Unchanged、DataRowState.Added、DataRowState.Detached、DataRowState.Modified

如果行的状态是Add，就会将数据新增到表中；是Modified就会更新数据；

public void UpdateRecord(string sql, DataSet newDataSet)
        {
            
            DataTable dt = newDataSet.Tables[0];
            try
            {
                using (MySqlConnection conn = new MySqlConnection(dbConnctionStr))
                {
                    MySqlDataAdapter adapter = new MySqlDataAdapter();
                    adapter.SelectCommand = new MySqlCommand(sql, conn);
                    MySqlCommandBuilder commBuilder = new MySqlCommandBuilder(adapter);
                    commBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                    commBuilder.SetAllValues = true;

                    foreach (DataRow dr in dt.Rows)
                    {
                        dr.AcceptChanges();// 将Rowstate改成UnChanged，这样
                        if (dr.RowState == DataRowState.Unchanged)
                            dr.SetModified();
                    }


                    int row = adapter.Update(dt);
                    dt.AcceptChanges();
                    adapter.Dispose();
                    commBuilder.Dispose();
                    
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            
        }

二、参考文章

MySql：

https://blog.csdn.net/RicardoMTan/article/details/93884631

http://www.csframework.com/archive/1/arc-1-20190118-2553.htm

Oracle：

https://www.cnblogs.com/xishuai/p/3471508.html