C# DbDataAdapter.Update方法代码示例

 public static int UpdateDataTable(DbDataAdapter dbDataAdapter, DataTable dataTable)
 {
     if (dbConnection.State != ConnectionState.Open)
         dbConnection.Open();
     int iUpdate = dbDataAdapter.Update(dataTable);
     dbConnection.Close();
     return iUpdate;
 }

        public int UpdateDataBase(DbDataAdapter da, DataTable dt)
        {
            int updateCount = 0;

            try
            {
                DataTable dtChanges = dt.GetChanges();

                if (dtChanges != null && dtChanges.Rows.Count >= 0)
                {
                    updateCount = da.Update(dtChanges);
                    dt.AcceptChanges();
                }
            }
            catch (Exception)
            {
                dt.RejectChanges();
                throw;
            }
            return updateCount;
        }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="dt"></param>
 /// <param name="strCmdText"></param>
 /// <returns></returns>
 public bool Update(DataTable dt, string strCmdText)
 {
     bool bResult = false;
     try
     {
         Database db = GetDataBase();
         _cmd = db.DbProviderFactory.CreateCommand();
         _cmd.Connection = db.CreateConnection();
         _cmd.CommandText = strCmdText;
         _cmd.CommandTimeout = TimeOut;
         _da = db.DbProviderFactory.CreateDataAdapter();
         _da.SelectCommand = _cmd;
         _da.Update(dt);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         Dispose();
     }
     return bResult;
 }

        //--пока этот метод вызывается из CreateTable, проверку на _conn.Open делать не надо, но как только будет отдельно, надо будет делать!
        protected virtual void InsertDataToDb(DataTable table, string parameters_prefix)
        {
            string insert_sql = string.Format("insert into {0} values(", table.TableName);
            Array insert_params = new DbParameter[table.Columns.Count];
            for (int i = 0; i < table.Columns.Count; i++)
            {
                insert_sql = string.Format("{0}{2}{1}", insert_sql, table.Columns[i].ColumnName + (i + 1 != table.Columns.Count ? "," : ")"), parameters_prefix);
                DbParameter par = CreateParameter(string.Format("{1}{0}", table.Columns[i].ColumnName, parameters_prefix), Utilites.SystemTypeToDbTypeConverter.Convert(table.Columns[i].DataType), table.Columns[i].MaxLength);
                par.SourceColumn = table.Columns[i].ColumnName;
                insert_params.SetValue(par, i);
            }

            _DA = CreateDataAdapter("");
            var ins_cmd = CreateCommand(insert_sql);
            ins_cmd.Parameters.AddRange(insert_params);

            _DA.InsertCommand = ins_cmd;
            _DA.Update(table);
        }

        //----------------------------------------------------------------------------------------------------
        /// <summary>
        /// 테이블 갱신(INSERT,UPDATE,DELETE...)
        /// </summary>
        /// <param name="table">테이블 객체</param>
        /// <param name="adapter">어답터</param>
        /// <returns>성공유무</returns>
        //----------------------------------------------------------------------------------------------------
        public bool UpdateTable( DataTable table, DbDataAdapter adapter )
        {
            DataTable changed_table = table.GetChanges();
            if( changed_table==null ) return true;

            // 업데이트
            DbTransaction tran = (DbTransaction)m_connection.BeginTransaction();
            try
            {
                //adapter.SelectCommand.Transaction = tran;
                adapter.InsertCommand.Transaction = tran;
                adapter.UpdateCommand.Transaction = tran;
                adapter.DeleteCommand.Transaction = tran;

                adapter.Update(changed_table);
                tran.Commit();
            }
            catch( Exception ex )
            {
                tran.Rollback();
                Log( ex.ToString() );
                m_error = ex.Message;
                return false;
            }
            table.AcceptChanges();
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataAdapter"></param>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public Int32 Save(DbDataAdapter dataAdapter, DataTable dataTable)
        {
            var previousState = ConnectionState;
            int affectedRecordCount = -1;
            DateTimeOffset? startTime = null;
            DateTimeOffset? endTime = null;

            try
            {
                var e = Database.OnCommandExecuting(new CommandExecutingEventArgs(MethodName.Save, ConnectionString, dataAdapter));
                if (e != null && e.Cancel == true) { return -1; }

                Open();
                if (dataAdapter.InsertCommand != null)
                {
                    dataAdapter.InsertCommand.Connection = this.Connection;
                    dataAdapter.InsertCommand.Transaction = this.Transaction;
                }
                if (dataAdapter.UpdateCommand != null)
                {
                    dataAdapter.UpdateCommand.Connection = this.Connection;
                    dataAdapter.UpdateCommand.Transaction = this.Transaction;
                }
                if (dataAdapter.DeleteCommand != null)
                {
                    dataAdapter.DeleteCommand.Connection = this.Connection;
                    dataAdapter.DeleteCommand.Transaction = this.Transaction;
                }
                startTime = DateTimeOffset.Now;
                affectedRecordCount = dataAdapter.Update(dataTable);
                endTime = DateTimeOffset.Now;
            }
            catch (Exception ex)
            {
                this.CatchException(new CommandErrorEventArgs(MethodName.Save, ConnectionString, ex, dataAdapter));
            }
            finally
            {
                if (previousState == ConnectionState.Closed &&
                    ConnectionState == ConnectionState.Open)
                {
                    Close();
                }
            }
            if (startTime.HasValue == true && endTime.HasValue == true)
            {
                Database.OnCommandExecuted(new CommandExecutedEventArgs(MethodName.Save, ConnectionString, startTime.Value, endTime.Value, dataAdapter));
            }
            return affectedRecordCount;
        }

        private void DoSaveDataRow(DbDataAdapter adap, DbCommandBuilder dbCmdBuilder, string tablename, DataRow row)
        {
            if (row == null
                || row.RowState == DataRowState.Unchanged
                || row.RowState == DataRowState.Detached)
            {
                return;
            }
            try
            {
                dbCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                dbCmdBuilder.DataAdapter = adap;

                adap.SelectCommand.CommandText = string.Format("SELECT TOP 1 * FROM [{0}]", tablename);
                adap.Update(new DataRow[] { row });
            }
            catch (Exception ex)
            {
                throw new DbAccessException(adap.SelectCommand.Connection, "Save DataRow error.", ex, adap.SelectCommand, adap.InsertCommand, adap.UpdateCommand, adap.DeleteCommand);
            }
        }

    public void Update_DeleteRow()
    {
      m_cmd = m_conn.CreateCommand();

      DataTable dt = new DataTable();
      m_adapter = GetDataAdapter();
      m_cmd.CommandText = "SELECT * FROM employee";
      m_adapter.SelectCommand = m_cmd;
      DbCommandBuilder builder = GetCommandBuilder(m_adapter);
      m_adapter.Fill(dt);

      DateTime now = DateTime.Now;
      DateTime doj = new DateTime(now.Year, now.Month, now.Day,
                                  now.Hour, now.Minute, now.Second);
      DateTime dob = new DateTime(now.Year, now.Month, now.Day,
                                  now.Hour, now.Minute, now.Second);
      dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0));

      try {
        DataRow newRow = dt.NewRow();
        newRow["id"] = 6002;
        newRow["fname"] = "boston";
        newRow["dob"] = dob;
        newRow["doj"] = doj;
        newRow["email"] = "[email protected]";
        dt.Rows.Add(newRow);
        m_adapter.Update(dt);

        // check row inserted
        DbCommand cmd = CheckNewEmployeeRow(6002, dob, doj);

        foreach (DataRow row in dt.Rows) {
          if (((int)row["id"]) == 6002) {
            row.Delete();
          }
        }
        m_adapter.Update(dt);

        m_reader = cmd.ExecuteReader();
        Assert.IsFalse(m_reader.Read());
        m_reader.Close();

        // also try explicitly setting the DeleteCommand
        //m_adapter.DeleteCommand = builder.GetDeleteCommand();

        newRow = dt.NewRow();
        newRow["id"] = 6002;
        newRow["fname"] = "boston";
        newRow["dob"] = dob;
        newRow["doj"] = doj;
        newRow["email"] = "[email protected]";
        dt.Rows.Add(newRow);
        m_adapter.Update(dt);

        // check row inserted
        cmd = CheckNewEmployeeRow(6002, dob, doj);

        foreach (DataRow row in dt.Rows) {
          if (((int)row["id"]) == 6002) {
            row.Delete();
          }
        }
        m_adapter.Update(dt);

        m_reader = cmd.ExecuteReader();
        Assert.IsFalse(m_reader.Read());
        m_reader.Close();

        // now with useColumnsForParameterNames as true
        //m_adapter.DeleteCommand = builder.GetDeleteCommand(true);

        newRow = dt.NewRow();
        newRow["id"] = 6002;
        newRow["fname"] = "boston";
        newRow["dob"] = dob;
        newRow["doj"] = doj;
        newRow["email"] = "[email protected]";
        dt.Rows.Add(newRow);
        m_adapter.Update(dt);

        // check row inserted
        cmd = CheckNewEmployeeRow(6002, dob, doj);

        foreach (DataRow row in dt.Rows) {
          if (((int)row["id"]) == 6002) {
            row.Delete();
          }
        }
        m_adapter.Update(dt);

        m_reader = cmd.ExecuteReader();
        Assert.IsFalse(m_reader.Read());
        m_reader.Close();

        // now explicitly set the DeleteCommand using GFXDCommandBuilder
        if (builder is GFXDCommandBuilder) {
          m_adapter.DeleteCommand = ((GFXDCommandBuilder)builder)
            .GetDeleteCommand();

          newRow = dt.NewRow();
//.........这里部分代码省略.........

    public void Update_UpdateRow()
    {
      m_cmd = m_conn.CreateCommand();

      DataTable dt = new DataTable();
      m_adapter = GetDataAdapter();
      m_cmd.CommandText = "SELECT * FROM employee";
      m_adapter.SelectCommand = m_cmd;
      DbCommandBuilder builder = GetCommandBuilder(m_adapter);
      m_adapter.Fill(dt);

      DateTime now = DateTime.Now;
      DateTime doj = new DateTime(now.Year, now.Month, now.Day,
                                  now.Hour, now.Minute, now.Second);
      DateTime dob = new DateTime(now.Year, now.Month, now.Day,
                                  now.Hour, now.Minute, now.Second);
      dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0));

      try {
        DataRow newRow = dt.NewRow();
        newRow["id"] = 6002;
        newRow["fname"] = "boston";
        newRow["dob"] = dob;
        newRow["doj"] = doj;
        newRow["email"] = "[email protected]";
        dt.Rows.Add(newRow);
        m_adapter.Update(dt);

        foreach (DataRow row in dt.Rows) {
          if (((int)row["id"]) == 6002) {
            row["lname"] = "de Icaza";
          }
        }
        m_adapter.Update(dt);

        DbCommand cmd = m_conn.CreateCommand();
        cmd.CommandText = "SELECT id, fname, lname, dob, doj, email" +
          " FROM employee WHERE id = 6002";
        m_reader = cmd.ExecuteReader();
        Assert.IsTrue(m_reader.Read(), "#A1");
        Assert.AreEqual(6002, m_reader.GetValue(0), "#A2");
        Assert.AreEqual("boston", m_reader.GetValue(1), "#A3");
        Assert.AreEqual("de Icaza", m_reader.GetValue(2), "#A4");
        Assert.AreEqual(dob, m_reader.GetValue(3), "#A5");
        Assert.AreEqual(doj, m_reader.GetValue(4), "#A6");
        Assert.AreEqual("[email protected]", m_reader.GetValue(5), "#A7");
        Assert.IsFalse(m_reader.Read(), "#A8");
        m_reader.Close();

        // also try explicitly setting the UpdateCommand
        //m_adapter.UpdateCommand = builder.GetUpdateCommand();

        foreach (DataRow row in dt.Rows) {
          if (((int)row["id"]) == 6002) {
            row["lname"] = "none";
          }
        }
        m_adapter.Update(dt);

        m_reader = cmd.ExecuteReader();
        Assert.IsTrue(m_reader.Read(), "#B1");
        Assert.AreEqual(6002, m_reader.GetValue(0), "#B2");
        Assert.AreEqual("boston", m_reader.GetValue(1), "#B3");
        Assert.AreEqual("none", m_reader.GetValue(2), "#B4");
        Assert.AreEqual(dob, m_reader.GetValue(3), "#B5");
        Assert.AreEqual(doj, m_reader.GetValue(4), "#B6");
        Assert.AreEqual("[email protected]", m_reader.GetValue(5), "#B7");
        Assert.IsFalse(m_reader.Read(), "#B8");
        m_reader.Close();

        // now with useColumnsForParameterNames as true
        //m_adapter.UpdateCommand = builder.GetUpdateCommand(true);

        foreach (DataRow row in dt.Rows) {
          if (((int)row["id"]) == 6002) {
            row["lname"] = "de Icaza";
          }
        }
        m_adapter.Update(dt);

        m_reader = cmd.ExecuteReader();
        Assert.IsTrue(m_reader.Read(), "#C1");
        Assert.AreEqual(6002, m_reader.GetValue(0), "#C2");
        Assert.AreEqual("boston", m_reader.GetValue(1), "#C3");
        Assert.AreEqual("de Icaza", m_reader.GetValue(2), "#C4");
        Assert.AreEqual(dob, m_reader.GetValue(3), "#C5");
        Assert.AreEqual(doj, m_reader.GetValue(4), "#C6");
        Assert.AreEqual("[email protected]", m_reader.GetValue(5), "#C7");
        Assert.IsFalse(m_reader.Read(), "#C8");
        m_reader.Close();

        // now explicitly set the UpdateCommand using GFXDCommandBuilder
        if (builder is GFXDCommandBuilder) {
          m_adapter.UpdateCommand = ((GFXDCommandBuilder)builder)
            .GetUpdateCommand();

          foreach (DataRow row in dt.Rows) {
            if (((int)row["id"]) == 6002) {
              row["lname"] = "none";
            }
//.........这里部分代码省略.........

    public void BatchSizeMixedChangesTest()
    {
      m_adapter = GetDataAdapter("select * from employee");
      GetCommandBuilder(m_adapter);
      Assert.AreEqual(1, m_adapter.UpdateBatchSize,
                      "#A1 The default value should be 1");
      m_adapter.UpdateBatchSize = 7;
      Assert.AreEqual(7, m_adapter.UpdateBatchSize,
                      "#A2 The value should be 7 after setting the property" +
                      " UpdateBatchSize to 7");

      for (int times = 1; times <= 100; ++times) {
        DbCommand cmd = m_conn.CreateCommand();
        DataTable dt = new DataTable();
        m_adapter.Fill(dt);
        DateTime now = DateTime.Now;
        DateTime doj = new DateTime(now.Year, now.Month, now.Day,
                                    now.Hour, now.Minute, now.Second);
        DateTime dob = new DateTime(now.Year, now.Month, now.Day,
                                    now.Hour, now.Minute, now.Second);
        dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0));

        TrackRandom rnd = new TrackRandom();
        int startInsertId = 1000, endInsertId = 1500;
        int startUpdateId = 1133, endUpdateId = 1437;
        int startDeleteId = 1251, endDeleteId = 1355;
        //int startInsertId = 100, endInsertId = 130;
        //int startUpdateId = 109, endUpdateId = 125;
        //int startDeleteId = 113, endDeleteId = 123;

        int rowId, endRowId;
        int insertRowId = startInsertId;
        int updateRowId = startUpdateId;
        int deleteRowId = startDeleteId;
        int sumRowIds;
        int currentBatch;
        bool changed;

        try {
          // the inserts/updates/deletes are performed in random batches of
          // sizes ranging from 3-15
          while (insertRowId <= endInsertId || updateRowId <= endUpdateId
                 || deleteRowId <= endDeleteId) {
            currentBatch = rnd.Next(3, 15);
            changed = false;
            endRowId = insertRowId + currentBatch;
            while (insertRowId <= endRowId && insertRowId <= endInsertId) {
              DataRow newRow = dt.NewRow();
              newRow["id"] = insertRowId;
              newRow["fname"] = "gfxd" + insertRowId;
              newRow["dob"] = dob;
              newRow["doj"] = doj;
              newRow["email"] = "test" + insertRowId + "@vmware.com";
              dt.Rows.Add(newRow);
              ++insertRowId;
              changed = true;
            }

            if (changed && rnd.Next(5) == 1) {
              m_adapter.Update(dt);
              // check that we have no parameters left after batch execution
              Assert.AreEqual(0, m_adapter.InsertCommand.Parameters.Count,
                              "#B1 Should have no parameters after batching");
              // a rough check for successful inserts
              sumRowIds = 0;
              for (rowId = startInsertId; rowId < insertRowId; ++rowId) {
                if (rowId < startDeleteId || rowId >= deleteRowId) {
                  sumRowIds += rowId;
                }
              }
              cmd.CommandText = "select sum(id) from employee where id >= " +
                startInsertId;
              Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(),
                              "#B2 All inserts not done?");
            }

            // now check for a set of updates
            changed = false;
            endRowId = updateRowId + rnd.Next(0, currentBatch);
            while (updateRowId <= endRowId && updateRowId < insertRowId
                   && updateRowId <= endUpdateId) {
              foreach (DataRow row in dt.Rows) {
                if (row.RowState != DataRowState.Deleted) {
                  rowId = (int)row["id"];
                  if (rowId == updateRowId) {
                    row["lname"] = "gem" + rowId;
                    changed = true;
                    break;
                  }
                }
              }
              ++updateRowId;
            }

            if (changed && rnd.Next(5) == 2) {
              m_adapter.Update(dt);
              // check that we have no parameters left after batch execution
              Assert.IsTrue(m_adapter.UpdateCommand == null
                            || m_adapter.UpdateCommand.Parameters.Count == 0,
                              "#C1 Should have no parameters after batching");
//.........这里部分代码省略.........

    public void BatchSizeInsertUpdateDeleteTest()
    {
      m_adapter = GetDataAdapter("select * from employee");
      GetCommandBuilder(m_adapter);
      Assert.AreEqual(1, m_adapter.UpdateBatchSize,
                      "#A1 The default value should be 1");
      m_adapter.UpdateBatchSize = 3;
      Assert.AreEqual(3, m_adapter.UpdateBatchSize,
                      "#A2 The value should be 3 after setting the property" +
                      " UpdateBatchSize to 3");

      // some inserts that should be sent in batches
      DataTable dt = new DataTable();
      m_adapter.Fill(dt);
      DateTime now = DateTime.Now;
      DateTime doj = new DateTime(now.Year, now.Month, now.Day,
                                  now.Hour, now.Minute, now.Second);
      DateTime dob = new DateTime(now.Year, now.Month, now.Day,
                                  now.Hour, now.Minute, now.Second);
      dob.Subtract(new TimeSpan(20 * 365, 0, 0, 0));

      int startInsertId = 1000, endInsertId = 1500;
      int startUpdateId = 1273, endUpdateId = 1355;
      int startDeleteId = 1133, endDeleteId = 1437;
      int rowId;
      int sumRowIds = 0;
      try {
        for (rowId = startInsertId; rowId <= endInsertId; ++rowId) {
          DataRow newRow = dt.NewRow();
          sumRowIds += rowId;
          newRow["id"] = rowId;
          newRow["fname"] = "gfxd" + rowId;
          newRow["dob"] = dob;
          newRow["doj"] = doj;
          newRow["email"] = "test" + rowId + "@vmware.com";
          dt.Rows.Add(newRow);
        }
        m_adapter.Update(dt);

        // check that we have no parameters left after batch execution
        Assert.AreEqual(0, m_adapter.InsertCommand.Parameters.Count,
                        "#B1 Should have no parameters after batching");
        // a rough check for successful inserts
        DbCommand cmd = m_conn.CreateCommand();
        cmd.CommandText = "select sum(id) from employee where id >= " +
          startInsertId;
        Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(),
                        "#B2 All inserts not done?");

        // now check for a set of updates
        int sumUpdatedRowIds = 0;
        for (int expectedId = startUpdateId; expectedId <= endUpdateId;
             ++expectedId) {
          sumUpdatedRowIds += expectedId;
          foreach (DataRow row in dt.Rows) {
            rowId = (int)row["id"];
            if (rowId == expectedId) {
              row["lname"] = "gem" + rowId;
              break;
            }
          }
        }
        m_adapter.Update(dt);

        // check that we have no parameters left after batch execution
        Assert.AreEqual(0, m_adapter.UpdateCommand.Parameters.Count,
                        "#C1 Should have no parameters after batching");
        // a rough check for successful updates
        cmd.CommandText = "select sum(id) from employee" +
          " where lname like 'gem%'";
        Assert.AreEqual(sumUpdatedRowIds, cmd.ExecuteScalar(),
                        "#C2 Updates not successful?");

        // lastly check for a set of deletes
        foreach (DataRow row in dt.Rows) {
          rowId = (int)row["id"];
          if (rowId >= startDeleteId && rowId <= endDeleteId) {
            row.Delete();
            sumRowIds -= rowId;
          }
        }
        m_adapter.Update(dt);

        // check that we have no parameters left after batch execution
        Assert.AreEqual(0, m_adapter.DeleteCommand.Parameters.Count,
                        "#D1 Should have no parameters after batching");
        // a rough check for successful deletes
        cmd.CommandText = "select sum(id) from employee where id > 4";
        Assert.AreEqual(sumRowIds, cmd.ExecuteScalar(),
                        "#D2 Some deletes not successful?");
      } finally {
        CleanEmployeeTable();
      }
    }

 public bool delete(DataRow dr, DataTable dt, DbDataAdapter da)
 {
     try
     {
         dt.Rows.Remove(dr);
         da.Update(dt);
         return true;
     }
     catch (Exception ex)
     {
         cError.mngError(ex, "delete", c_module, "");
         return false;
     }
 }