C# OleDbCommandBuilder.GetUpdateCommand方法代码示例

        // update by the dataset from the Form
        public void Update(string dbName, string tabName)
        {
            OleDbConnection con = getCon(dbName);
            string sql = "select * From " + tabName;
            sda = new OleDbDataAdapter(sql, con);

            OleDbCommandBuilder builder = new OleDbCommandBuilder(sda);
            sda.InsertCommand = builder.GetInsertCommand();
            sda.DeleteCommand = builder.GetDeleteCommand();
            sda.UpdateCommand = builder.GetUpdateCommand();

            this.ds = new DataSet();
            sda.Fill(this.ds, tabName);
        }

        private void mineralsBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            DataSet dataSet = new DataSet();
            string queryString = "SELECT * FROM minerals";
            string connectionString = @"Data Source=remote.failgaming.com,1433\athena;Initial Catalog=ore_stock;User ID=test;Password=FailZ0rz69";
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = new OleDbCommand(queryString, connection);
                OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
                adapter.Fill(dataSet);
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.Update(dataSet);
            }

            this.Validate();
            this.mineralsBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.ore_stockDataSet1);
        }

        public void Insert(DataTable dt, String tableName)
        {
            using (connection = new OleDbConnection(connString))
            {
                connection.Open();
                String query = "SELECT * FROM " + tableName;

                using(OleDbCommand command = new OleDbCommand(query, connection))
                {
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                    {
                        using (OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adapter))
                        {
                            adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
                            adapter.InsertCommand = commandBuilder.GetInsertCommand();
                            adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                        }
                    }
                }
            }
        }

        public void ExtractTableParameters(string TableName, System.Data.IDbDataAdapter adapter, out DatabaseCache InsertCache, out DatabaseCache DeleteCache, out DatabaseCache UpdateCache, out DatabaseCache IsExistCache, out System.Data.DataTable dt)
        {
            adapter.SelectCommand.CommandText = "select top 1 * from " + TableName;

            DataSet ds = new DataSet();

            dt = adapter.FillSchema(ds, SchemaType.Source)[0];

            dt.TableName = TableName;

            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter as OleDbDataAdapter);

            builder.ConflictOption = ConflictOption.OverwriteChanges;
            //builder.SetAllValues = false;
            OleDbCommand InsertCmd = builder.GetInsertCommand(true);
            builder.ConflictOption = ConflictOption.OverwriteChanges; InsertCache = new DatabaseCache(InsertCmd.CommandText, InsertCmd.Parameters);
            InsertCache.CurrentTable = dt;

            foreach (DataColumn c in dt.Columns)
            {
                if (c.AutoIncrement)
                {
                    InsertCache.IsHaveAutoIncrement = true;
                    InsertCache.SQL += ";Select @@IDENTITY;";
                    break;
                }
            }

            OleDbCommand UpdateCmd = builder.GetUpdateCommand(true);
            UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters);
            UpdateCache.CurrentTable = dt;

            OleDbCommand DeleteCmd = builder.GetDeleteCommand(true);
            DeleteCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
            DeleteCache.CurrentTable = dt;

            IsExistCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
            IsExistCache.CurrentTable = dt;
            IsExistCache.SQL = IsExistCache.SQL.Replace("DELETE FROM [" + TableName + "]", "Select count(1) from [" + TableName + "] with(nolock) ");
        }

        public static bool OpenConnexion()
        {
            _bddConnection = new OleDbConnection(ConnexionWords);
            _bddCommand = new OleDbCommand
            {
                Connection = _bddConnection,
                CommandText = "SELECT * from Clients",
                CommandType = CommandType.Text
            };
            _bddAdapter = new OleDbDataAdapter(_bddCommand);
            OleDbCommandBuilder bCB = new OleDbCommandBuilder(_bddAdapter);
            _bddDataTable = new DataTable("Clients");


            if (_bddConnection == null)
            {
            }
            else if (_bddConnection.State == ConnectionState.Open)
            {
                new ClientListException("La base de donnée est déja ouverte !");
                return false;
            }
            if (!File.Exists(Settings.Default.BDDPath))
            {
                new ClientListException("La base de donnée n'existe pas !");
                return false;
            }

            _bddAdapter.UpdateCommand = bCB.GetUpdateCommand();
            _bddAdapter.DeleteCommand = bCB.GetDeleteCommand();
            _bddAdapter.InsertCommand = bCB.GetInsertCommand();

            _bddConnection.Open();
            _bddAdapter.Fill(_bddDataTable);
            return true;
        }

        /// <summary>
        /// Pre Condition: The paremeters will be in order of passing the dataset and the string table name
        /// Post Condition: The update dataset will be persisted in the data base
        /// Description: This method will update the database based on the update
        /// </summary>
        /// <param name="pDataSet"> The dataset that contains the updated records</param>
        /// <param name="pStrTableName"> The table name will be updated in the database </param>
        public void SaveData(DataSet pDataSet, string pStrTableName)
        {
            string strQuery = "SELECT * FROM " + pStrTableName;

            OleDbDataAdapter dbDA = new OleDbDataAdapter(strQuery, _dbConn);


            try
            {

                // setup the command builders
                OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA);
                dbDA.InsertCommand = dbBLD.GetInsertCommand();
                dbDA.UpdateCommand = dbBLD.GetUpdateCommand();
                dbDA.DeleteCommand = dbBLD.GetDeleteCommand();

                // subsrcibe to the OleDBRowUpdateEventHandler
                dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(onRowUpdated);
                _dbConn.Open();
                dbDA.Update(pDataSet, pStrTableName);
                pDataSet.Tables[pStrTableName].AcceptChanges();
                _dbConn.Close();
                pStrTableName = null;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            finally
            {
                _dbConn.Close();
            }
        }

        /// <summary>
        /// Procedure for saving all data collected from frmEditWindow
        /// </summary>
        private void Save()
        {
            //
            // Saving collected data to dataGridView1
            //
            dataGridView1.Rows[_activeRow].Cells[0].Value = _id;
            dataGridView1.Rows[_activeRow].Cells[1].Value = _name;
            dataGridView1.Rows[_activeRow].Cells[2].Value = _description;
            dataGridView1.Rows[_activeRow].Cells[3].Value = _link;

            int[] res = _checkbox_array;
            Array.Sort(res);
            //
            // The CATEGORIES cell should take ints of categories comma separated
            //
            dataGridView1.Rows[_activeRow].Cells[4].Value = string.Join(",", res);

            //
            // Absolutely MUST command before updating dda. No programmatical updates would take place in dataGridView without this method!
            //
            bs.EndEdit();

            try
            {
                    dda = new OleDbDataAdapter("SELECT * FROM [data]", connectionString);
                    cb = new OleDbCommandBuilder(dda);
                    cb.GetUpdateCommand();
                    //
                    // A little trick to get UpdateCommand. No updates without these methods could be possible!
                    //
                    dda.UpdateCommand = cb.GetUpdateCommand();
                    dda.Update(dtab);
                    dtab = new DataTable();
                    dda.Fill(dtab);

            }
            catch (OleDbException exc)
            {
                  MessageBox.Show(exc.Message, "OledbException Error");
            }
            catch (Exception e)
            {
                  MessageBox.Show(e.Message);
            }
              //
              // We don't forget to show the result of update
              //
              ShowData();
        }

    public string NewsSetArchive(string tbl, int wh, string legal)
    {
        string msg = string.Empty;

        if (tLegal == legal.Trim() && isDaysLeft)
        {
            tbl = tbl.Trim();

            string sqlStr = "SELECT * FROM " + tbl;

            try
            {
                OleDbConnection cnn = new OleDbConnection(Base.cnnStr);
                OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
                OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda);

                cnn.Open();

                OleDbCommand cmd = new OleDbCommand(sqlStr, cnn);
                OleDbDataReader drr = cmd.ExecuteReader();

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow dr;

                ocb.QuotePrefix = "[";
                ocb.QuoteSuffix = "]";
                oda.Fill(ds, tbl);

                dt = ds.Tables[tbl];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dr = dt.Rows[i];
                    if (Convert.ToInt32(dr["id"]) == wh)
                    {
                        dr.BeginEdit();
                        dr["archived"] = !Convert.ToBoolean(dr["archived"]);
                        dr.EndEdit();
                        break;
                    }
                }
                oda.UpdateCommand = ocb.GetUpdateCommand();

                if (oda.Update(ds, tbl) == 1)
                {
                    ds.AcceptChanges();
                    msg = "Updated";
                }
                else
                {
                    ds.RejectChanges();
                    msg = "Rejected";
                }

                cnn.Close();
                drr.Close();

                ds.Dispose();
                cmd.Dispose();
                drr.Dispose();
                ocb.Dispose();
                oda.Dispose();
                cnn.Dispose();
                dt.Dispose();

                ds = null;
                ocb = null;
                oda = null;
                dr = null;
                dt = null;
                cmd = null;
                drr = null;
                cnn = null;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            finally
            {
                tbl = null;
                sqlStr = null;
            }
        }
        else
            msg = errInvalidLegal;

        return msg;
    }

        // Sauvegarde tous les changements effectué dans le dataset
        public void SaveDataSet(string tableName, DataSet dataSet)
        {
            if (dataSet.HasChanges() == false)
                return;

            switch (connType)
            {
                case ConnectionType.DATABASE_MSSQL:
                    {
                        try
                        {
                            var conn = new SqlConnection(connString);
                            var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn);
                            var builder = new SqlCommandBuilder(adapter);

                            adapter.DeleteCommand = builder.GetDeleteCommand();
                            adapter.UpdateCommand = builder.GetUpdateCommand();
                            adapter.InsertCommand = builder.GetInsertCommand();

                            lock (dataSet) // lock dataset to prevent changes to it
                            {
                                adapter.ContinueUpdateOnError = true;
                                DataSet changes = dataSet.GetChanges();
                                adapter.Update(changes, tableName);
                                PrintDatasetErrors(changes);
                                dataSet.AcceptChanges();
                            }

                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            throw new DatabaseException("Can not save table " + tableName, ex);
                        }

                        break;
                    }
                case ConnectionType.DATABASE_ODBC:
                    {
                        try
                        {
                            var conn = new OdbcConnection(connString);
                            var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn);
                            var builder = new OdbcCommandBuilder(adapter);

                            adapter.DeleteCommand = builder.GetDeleteCommand();
                            adapter.UpdateCommand = builder.GetUpdateCommand();
                            adapter.InsertCommand = builder.GetInsertCommand();

                            DataSet changes;
                            lock (dataSet) // lock dataset to prevent changes to it
                            {
                                adapter.ContinueUpdateOnError = true;
                                changes = dataSet.GetChanges();
                                adapter.Update(changes, tableName);
                                dataSet.AcceptChanges();
                            }

                            PrintDatasetErrors(changes);

                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            throw new DatabaseException("Can not save table ", ex);
                        }

                        break;
                    }
                case ConnectionType.DATABASE_MYSQL:
                    {
                        return;
                    }
                case ConnectionType.DATABASE_OLEDB:
                    {
                        try
                        {
                            var conn = new OleDbConnection(connString);
                            var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
                            var builder = new OleDbCommandBuilder(adapter);

                            adapter.DeleteCommand = builder.GetDeleteCommand();
                            adapter.UpdateCommand = builder.GetUpdateCommand();
                            adapter.InsertCommand = builder.GetInsertCommand();

                            DataSet changes;
                            lock (dataSet) // lock dataset to prevent changes to it
                            {
                                adapter.ContinueUpdateOnError = true;
                                changes = dataSet.GetChanges();
                                adapter.Update(changes, tableName);
                                dataSet.AcceptChanges();
                            }

                            PrintDatasetErrors(changes);

                            conn.Close();
                        }
                        catch (Exception ex)
//.........这里部分代码省略.........

        public static int Save(DataTable tb,string tbName)
        {
            if (连接数据库() == true)
            {

                tb.TableName = "dbo." + tbName;
                OleDbDataAdapter da = new OleDbDataAdapter(string.Format("select * from {0}", "dbo." + tbName), 数据库操作对象);
                da.MissingSchemaAction = MissingSchemaAction.Ignore;
                OleDbCommandBuilder CommandBuilder = new OleDbCommandBuilder(da);
                da.InsertCommand = CommandBuilder.GetInsertCommand();
                da.DeleteCommand = CommandBuilder.GetDeleteCommand();
                da.UpdateCommand = CommandBuilder.GetUpdateCommand();
                return da.Update(tb);
            }
            return -1;
        }

        /// <summary>
        /// Pre-condition:  The parameters will be in the order of dataset and string.
        /// Post-condition: The update dataset will be persisted in the database.
        /// Description:    This method will update the database based on the updated 
        ///                 records in the dataset for the specified table.
        /// </summary>
        /// <param name="pDataSet">The dataset that contains the updated records.</param>
        /// <param name="pStrTableName">The table name that will be updated.</param>
        public void saveData(DataSet pDataSet, string pStrTableName)
        {
            //specify select statement for our data adapter
            string strSQL = "SELECT * FROM " + pStrTableName;

            // create an instance of the data adapter
            OleDbDataAdapter dbDA = new OleDbDataAdapter(strSQL, _dbConn);

            try
            {
                // setup the command builder - not suitable for large databases
                OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA);
                dbDA.InsertCommand = dbBLD.GetInsertCommand();
                dbDA.UpdateCommand = dbBLD.GetUpdateCommand();
                dbDA.DeleteCommand = dbBLD.GetDeleteCommand();

                // subscribe to the OleDbRowUpdateEventHandler
                dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(dbDA_RowUpdated);

                // update the database using the Update method of the data adapter
                if (_dbConn.State == ConnectionState.Closed) _dbConn.Open();
                // update the database
                dbDA.Update(pDataSet, pStrTableName);
                // close the connection
                _dbConn.Close();
                // refresh the dataset
                pDataSet.Tables[pStrTableName].AcceptChanges();
            }
            catch (Exception e)
            {
                _dbConn.Close();
                MessageBox.Show("An error occurred. Contact your system administrator.");
                //FileLogger fw = new FileLogger("ErrorLog", "Text");
                //fw.write(e.ToString());
            }
        }

 // Define the parameters for the UPDATE command in different ways
 private static void AddParameters(OleDbCommandBuilder cb)
 {
     try
     {
         cb.GetUpdateCommand().Parameters.Add("@return", OleDbType.Char, 1, "G_RETRN");
     }
     catch (Exception e)
     {
         Console.WriteLine(e.Message);
     }
 }

        protected void btnUpdateTask_Click(object sender, EventArgs e)
        {
            if (titleTaskTextBox.Text == "")
            {
                lblTaskNotice.Text = "Task Title is required!";
                addandEditTaskPopup.Show();
            }
            else if (complexityTaskTextBox.Text != "" && !IsNumeric(complexityTaskTextBox.Text))
            {
                lblTaskNotice.Text = "Task Complexity must be a number!";
                addandEditTaskPopup.Show();
            }
            else if (estimationHourTaskTextBox.Text != "" && !IsNumeric(estimationHourTaskTextBox.Text))
            {
                lblTaskNotice.Text = "Estimation hour must be a number!";
                addandEditTaskPopup.Show();
            }
            else if (spentTimeTaskTextBox.Text != "" && !IsNumeric(spentTimeTaskTextBox.Text))
            {
                lblTaskNotice.Text = "Spent time must be a number!";
                addandEditTaskPopup.Show();
            }
            else if (deadlineTaskTextBox.Text != "" && !IsDate(deadlineTaskTextBox.Text))
            {
                lblTaskNotice.Text = "Task Due date must be a date!";
                addandEditTaskPopup.Show();
            }
            else
            {
                string id = addandEditTaskLegend.InnerText.Remove(0, 14);
                DataRow row = myDataSet.Tables["myRawTasks"].Select("TaskID = " + id)[0];

                row["TaskTitle"] = titleTaskTextBox.Text;
                row["TaskAssigneeID"] = Convert.ToInt32(assigneeTaskDropDownList.SelectedValue);
                row["TaskStatusID"] = Convert.ToInt32(statusTaskDropDownList.SelectedValue);
                if (statusTaskDropDownList.SelectedValue == "3")
                    row["TaskCompletedDate"] = DateTime.Today;
                else
                    row["TaskCompletedDate"] = DBNull.Value;
                if (complexityTaskTextBox.Text != "")
                    row["TaskComplexity"] = Convert.ToInt32(complexityTaskTextBox.Text);
                else
                    row["TaskComplexity"] = DBNull.Value;
                if (estimationHourTaskTextBox.Text != "")
                    row["TaskEstimationHour"] = Convert.ToInt32(estimationHourTaskTextBox.Text);
                else
                    row["TaskEstimationHour"] = DBNull.Value;
                if (spentTimeTaskTextBox.Text != "")
                    row["TaskSpentTime"] = Convert.ToInt32(spentTimeTaskTextBox.Text);
                else
                    row["TaskSpentTime"] = DBNull.Value;
                if (deadlineTaskTextBox.Text != "")
                    row["TaskDueDate"] = Convert.ToDateTime(deadlineTaskTextBox.Text);
                else
                    row["TaskDueDate"] = DBNull.Value;

                myAdapter.SelectCommand.CommandText = "Select * From Tasks";
                OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter);
                myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
                myAdapter.Update(myDataSet, "myRawTasks");
                myDataSet.Clear();

                getDatabase();
                ScriptManager.RegisterStartupScript(updatePanel, updatePanel.GetType(), "refreshBoard", "refreshBoard();", true);
                addandEditTaskPopup.Hide();
            }
        }

        protected void btnUpdateBacklog_Click(object sender, EventArgs e)
        {
            if (titleTextBox.Text == "")
            {
                lblBacklogNotice.Text = "Backlog Title is required!";
                addandEditBacklogPopup.Show();
            }
            else if (complexityTextBox.Text != "" && !IsNumeric(complexityTextBox.Text))
            {
                lblBacklogNotice.Text = "Backlog Complexity must be a number!";
                addandEditBacklogPopup.Show();
            }
            else if (deadlineTextBox.Text != "" && !IsDate(deadlineTextBox.Text))
            {
                lblBacklogNotice.Text = "Backlog Due date must be a date!";
                addandEditBacklogPopup.Show();
            }
            else
            {
                string id = addandEditBacklogLegend.InnerText.Remove(0, 17);
                DataRow row = myDataSet.Tables["myRawBacklogs"].Select("BacklogID = " + id)[0];

                row["SwimlaneID"] = Convert.ToInt32(swimlaneDropDownList.SelectedValue);
                row["BacklogTitle"] = titleTextBox.Text;
                row["BacklogDescription"] = descriptionTextBox.Text;
                row["BacklogColor"] = colorDropDownList.SelectedValue.Split(',')[1].ToString();
                row["BacklogColorHeader"] = colorDropDownList.SelectedValue.Split(',')[0].ToString();
                if (complexityTextBox.Text != "")
                    row["BacklogComplexity"] = Convert.ToInt32(complexityTextBox.Text);
                else
                    row["BacklogComplexity"] = DBNull.Value;
                if (deadlineTextBox.Text != "")
                    row["BacklogDueDate"] = Convert.ToDateTime(deadlineTextBox.Text);
                else
                    row["BacklogDueDate"] = DBNull.Value;
                row["BacklogAssigneeID"] = Convert.ToInt32(assigneeDropDownList.SelectedValue);

                myAdapter.SelectCommand.CommandText = "Select * From Backlogs";
                OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter);
                myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
                myAdapter.Update(myDataSet, "myRawBacklogs");
                myDataSet.Clear();

                getDatabase();
                ScriptManager.RegisterStartupScript(updatePanel, updatePanel.GetType(), "refreshBoard", "refreshBoard();", true);
                addandEditBacklogPopup.Hide();
            }
        }

    public string PreferencesSet(string tag, string val, string legal)
    {
        string msg = string.Empty;

        if (tLegal == legal.Trim() && isDaysLeft)
        {
            tag = tag.Trim();
            val = val.Trim();

            string tbl = "preferences";
            string sqlStr = "SELECT * FROM " + tbl;

            try
            {
                OleDbConnection cnn = new OleDbConnection(Base.cnnStr);
                OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
                OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda);
                OleDbCommand cmd = new OleDbCommand(sqlStr, cnn);
                cnn.Open();
                OleDbDataReader drr = cmd.ExecuteReader();

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow dr;

                ocb.QuotePrefix = "[";
                ocb.QuoteSuffix = "]";
                oda.Fill(ds, tbl);

                dt = ds.Tables[tbl];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dr = dt.Rows[i];

                    if (dr["tag"].ToString().Trim() == tag)
                    {
                        dr.BeginEdit();
                        dr["val"] = val;
                        dr.EndEdit();

                        oda.UpdateCommand = ocb.GetUpdateCommand();

                        if (oda.Update(ds, tbl) == 1)
                        {
                            ds.AcceptChanges();
                            msg = "OK";
                        }
                        else
                        {
                            ds.RejectChanges();
                            msg = "Rejected";
                        }
                        
                        break;
                    }
                }

                drr.Close();
                cnn.Close();

                cmd.Dispose();
                drr.Dispose();
                ds.Dispose();
                ocb.Dispose();
                oda.Dispose();
                cnn.Dispose();
                dt.Dispose();

                cmd = null;
                drr = null;
                ds = null;
                ocb = null;
                oda = null;
                dr = null;
                dt = null;
                cnn = null;
            }
            catch(Exception ex)
            {
                msg = ex.Message;
            }
            finally
            {
                tbl = null;
                sqlStr = null;
            }
        }

        return msg;
    }