C# MySqlCommandBuilder.GetUpdateCommand方法代码示例

        public void Bind()
        {
            mySqlConnection = new MySqlConnection(
                "SERVER=localhost;" +
                "DATABASE=baza;" +
                "UID=root;");
            mySqlConnection.Open();

            string query = "SELECT * FROM cennik";

            mySqlDataAdapter = new MySqlDataAdapter(query, mySqlConnection);
            mySqlCommandBuilder = new MySqlCommandBuilder(mySqlDataAdapter);

            mySqlDataAdapter.UpdateCommand = mySqlCommandBuilder.GetUpdateCommand();
            mySqlDataAdapter.DeleteCommand = mySqlCommandBuilder.GetDeleteCommand();
            mySqlDataAdapter.InsertCommand = mySqlCommandBuilder.GetInsertCommand();

            dataTable = new DataTable();
            mySqlDataAdapter.Fill(dataTable);

            bindingSource = new BindingSource();
            bindingSource.DataSource = dataTable;

            dataGridView3.DataSource = bindingSource;
        }

        public MySqlTableContext( DataTable dataTable, MySqlConnection connection )
        {
            this.Connection = connection;

            this.DataTable = dataTable;
            this.DataAdapter = new MySqlDataAdapter(
                string.Format( "SELECT * FROM {0} WHERE 1=0",
                this.DataTable.TableName ), this.Connection );

            this.DataAdapter.UpdateBatchSize = 50;

            // Using workaround for MySQL Connector bug described at:
            // http://bugs.mysql.com/bug.php?id=39815
            // Dispose the builder before setting adapter commands.
            MySqlCommandBuilder builder = new MySqlCommandBuilder( this.DataAdapter );
            MySqlCommand updateCommand = builder.GetUpdateCommand();
            MySqlCommand insertCommand = builder.GetInsertCommand();
            MySqlCommand deleteCommand = builder.GetDeleteCommand();
            builder.Dispose();
            this.DataAdapter.UpdateCommand = updateCommand;
            this.DataAdapter.InsertCommand = insertCommand;
            this.DataAdapter.DeleteCommand = deleteCommand;

            this.DataAdapter.RowUpdating += new MySqlRowUpdatingEventHandler( DataAdapter_RowUpdating );
            this.DataAdapter.RowUpdated += this.OnRowUpdated;

            // Create a command to fetch the last inserted id
            identityCommand = this.Connection.CreateCommand();
            identityCommand.CommandText = "SELECT LAST_INSERT_ID()";

            this.RefreshIdentitySeed();
        }

        public void UpdateData(string tableName, string tableColumn, int limit, Action<DataRow> action)
        {
            var dataSet = new DataSet();

            using (var connection = new MySqlConnection(GetConnectionString()))
            {
                connection.Open();
                var q = string.Format(Query, tableName, tableColumn, limit);
                var adapter = new MySqlDataAdapter
                    {
                        SelectCommand = new MySqlCommand(q, connection) { CommandTimeout = 300 },
                    };
                var builder = new MySqlCommandBuilder(adapter);

                adapter.Fill(dataSet);

                // Code to modify data in the DataSet here.
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    action(row);
                }

                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.Update(dataSet);
            }
        }

        public void LoadMySql(string serverName,// Адрес сервера (для локальной базы пишите "localhost")
            string userName, // Имя пользователя
            string dbName,//Имя базы данных
            int port, // Порт для подключения
            string password,
            string _table)
        {
            string connStr;
            string strTable;

            DataTable table;

            connStr = "Database="+dbName+";Data Source=" + serverName + ";User Id=" + userName + ";Password=" + password;
            conn = new MySqlConnection(connStr);
            strTable = _table;
            string sql = "SELECT * FROM " + strTable; // Строка запроса
            conn.Open();
            MyData = new MySqlDataAdapter(sql,conn);
            MySqlCommandBuilder builder = new MySqlCommandBuilder(MyData);
            MyData.InsertCommand = builder.GetInsertCommand();
            MyData.UpdateCommand = builder.GetUpdateCommand();
            MyData.DeleteCommand = builder.GetDeleteCommand();
            table = new DataTable();
            MyData.Fill(table);
            UpdateGrid(table);
        }

 private void frmrfid_Load(object sender, EventArgs e)
 {
     string strConn = "server=localhost;user id=root;database=pharma;password=;";
     ad = new MySqlDataAdapter("select * from `rfid`", strConn);
     MySqlCommandBuilder builder = new MySqlCommandBuilder(ad);
     ad.Fill(this.newDataSet.rfid);
     ad.DeleteCommand = builder.GetDeleteCommand();
     ad.UpdateCommand = builder.GetUpdateCommand();
     ad.InsertCommand = builder.GetInsertCommand();
     MySqlDataAdapter ad3;
 }

 /// <summary>
 /// Metodo que actualiza un cambio del datagrid en la BD.
 /// </summary>
 /// <param name="sender"></param> Boton Actualizar.
 /// <param name="e"></param> Evento del boton.
 private void buttonModificar_Click(object sender, RoutedEventArgs e)
 {
     try
     {
         MySqlCommandBuilder builder = new MySqlCommandBuilder(adaptador);
         adaptador.UpdateCommand = builder.GetUpdateCommand();
         int numeroCambios = adaptador.Update(dt);
         if (numeroCambios > 0)
             MessageBox.Show("Actualizado");
         else
             MessageBox.Show("No se ha actualizado ningun registro");
     }
     catch (Exception ex)
     {
         MessageBox.Show("Error al modificar la tabla: " + ex.ToString());
     }
 }

        public void BatchUpdatesAndDeletes()
        {
            execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
            execSQL("INSERT INTO test VALUES (1, 'boo'), (2, 'boo'), (3, 'boo')");

            MySqlTrace.Listeners.Clear();
            MySqlTrace.Switch.Level = SourceLevels.All;
            GenericListener listener = new GenericListener();
            MySqlTrace.Listeners.Add(listener);

            string connStr = GetConnectionString(true) + ";logging=true;allow batch=true";
            using (MySqlConnection c = new MySqlConnection(connStr))
            {
                c.Open();
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", c);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
                da.UpdateCommand = cb.GetUpdateCommand();
                da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                da.UpdateBatchSize = 100;

                DataTable dt = new DataTable();
                da.Fill(dt);

                dt.Rows[0]["name"] = "boo2";
                dt.Rows[1]["name"] = "boo2";
                dt.Rows[2]["name"] = "boo2";
                da.Update(dt);
            }

            Assert.AreEqual(1, listener.Find("Query Opened: UPDATE"));
        }

        public void TestBatchingMixed()
        {
            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE Test (id INT, name VARCHAR(20), PRIMARY KEY(id))");
            execSQL("INSERT INTO Test VALUES (1, 'Test 1')");
            execSQL("INSERT INTO Test VALUES (2, 'Test 2')");
            execSQL("INSERT INTO Test VALUES (3, 'Test 3')");

            MySqlDataAdapter dummyDA = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(dummyDA);

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test ORDER BY id", conn);
            da.UpdateCommand = cb.GetUpdateCommand();
            da.InsertCommand = cb.GetInsertCommand();
            da.DeleteCommand = cb.GetDeleteCommand();
            da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
            da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
            da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

            DataTable dt = new DataTable();
            da.Fill(dt);

            dt.Rows[0]["id"] = 4;
            dt.Rows[1]["name"] = "new test value";
            dt.Rows[2]["id"] = 6;
            dt.Rows[2]["name"] = "new test value #2";

            DataRow row = dt.NewRow();
            row["id"] = 7;
            row["name"] = "foobar";
            dt.Rows.Add(row);

            dt.Rows[1].Delete();

            da.UpdateBatchSize = 0;
            da.Update(dt);

            dt.Rows.Clear();
            da.Fill(dt);
            Assert.AreEqual(3, dt.Rows.Count);
            Assert.AreEqual(4, dt.Rows[0]["id"]);
            Assert.AreEqual(6, dt.Rows[1]["id"]);
            Assert.AreEqual(7, dt.Rows[2]["id"]);
            Assert.AreEqual("Test 1", dt.Rows[0]["name"]);
            Assert.AreEqual("new test value #2", dt.Rows[1]["name"]);
            Assert.AreEqual("foobar", dt.Rows[2]["name"]);
        }

        private void GetData()
        {
            table_names = new String[] {
                "client_balance",
                "client_cashflow",
                "client_cashflow_type",
                "client_info",
                "commodity_category",
                "futures_account_balance",
                "futures_account_info",
                "futures_cashflow",
                "futures_cashflow_type",
                "futures_contracts",
                "futures_transactions",
                "futures_verbose_positions",
                "options_contracts",
                "options_direction_type",
                "options_transactions",
                "options_types",
                "options_verbose_positions"
            };

            view_names = new String[]
            {
                "client_balance_join",
                "client_cashflow_view",
                "commodity_category_view",
                "futures_account_balance_view",
                "futures_cashflow_view",
                "futures_contracts_view",
                "futures_positions_summary",
                "futures_transactions_view",
                "futures_verbose_positions_view",
                "options_contracts_view",
                "options_direction_type_view",
                "options_positions_summary",
                "options_transactions_view",
                "options_types_view",
                "options_verbose_positions_view",
                "non_trade_dates",
                "business_state_view",
                "option_settle_info_view",
                "future_settle_info_view",
                "business_current_state",
                "option_position_settle_info",
                "future_position_settle_info",
                "business_overview"
            };
            String selectString = "";
            foreach (String t in table_names)
            {
                selectString = String.Format("select * from {0};", t);
                MySqlCommand command = new MySqlCommand(selectString, this.sql_connection);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                command.CommandType = System.Data.CommandType.Text;
                adapter.SelectCommand = command;
                MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();
                adapter.FillSchema(this, System.Data.SchemaType.Source, t);
                adapter.FillSchema(display_ds, SchemaType.Source, t);
                adapter.Fill(this, t);
                adapter.Fill(display_ds, t);
                adapterDict.Add(t, adapter);
            }
            foreach (String t in view_names)
            {
                selectString = String.Format("select * from {0};", t);
                if (t == "futures_verbose_positions_view")
                    selectString = "SELECT * FROM futures_verbose_positions;";
                else if (t == "business_current_state")
                    selectString = "SELECT * FROM accum_business_pnl ORDER BY settle_day DESC LIMIT 1";
                MySqlCommand command = new MySqlCommand(selectString, this.sql_connection);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                command.CommandType = System.Data.CommandType.Text;
                adapter.SelectCommand = command;
                if (t == "non_trade_dates")
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
                    adapter.InsertCommand = builder.GetInsertCommand();
                    adapter.UpdateCommand = builder.GetUpdateCommand();
                    adapter.DeleteCommand = builder.GetDeleteCommand();
                }
                adapter.FillSchema(this, System.Data.SchemaType.Source, t);
                adapter.FillSchema(display_ds, SchemaType.Source, t);
                adapter.Fill(this, t);
                adapter.Fill(display_ds, t);
                adapterDict.Add(t, adapter);
            }
            foreach (System.Data.DataTable table in this.Tables)
            {
                foreach (System.Data.DataColumn col in table.Columns)
                {
                    col.AllowDBNull = true;
                }
            }
            foreach (System.Data.DataTable table in this.display_ds.Tables)
            {
                foreach (System.Data.DataColumn col in table.Columns)
//.........这里部分代码省略.........

        private void button4_Click(object sender, EventArgs e)
        {
            dgvSauer.Visible = false;
            dataGridView1.Visible = true;
            dbcMySql = new MySqlConnection(myconnectionstring);
            dbcMySql.Open();

            string strQuery = "";

            strQuery = "SELECT * FROM Doedsau WHERE flokkID='" + strFlokkID + "'";

            sqlAdapter = new MySqlDataAdapter(strQuery, dbcMySql);
            sqlCommandBuilder = new MySqlCommandBuilder(sqlAdapter);

            sqlAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();

            dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            bindingSource = new BindingSource();
            bindingSource.DataSource = dataTable;

            dataGridView1.DataSource = bindingSource;

            dbcMySql.Close();

            dataGridView1.Columns["flokkID"].Visible = false;

            MySqlConnection dbconn = new MySqlConnection(myconnectionstring);

            MySqlCommand cmdSauID = dbconn.CreateCommand();
            MySqlDataReader ReaderSauID;
            cmdSauID.CommandText = "SELECT * FROM Sauer WHERE flokkID='" + strFlokkID + "'";
            dbconn.Open();
            ReaderSauID = cmdSauID.ExecuteReader();
            ReaderSauID.Read();
            String strSauID = ReaderSauID.GetValue(0).ToString();
            dbconn.Close();

            webBrowser1.ScriptErrorsSuppressed = true;
            webBrowser1.Navigate("http://folk.ntnu.no/kenneaas/sau/doedSauID/index.php?sauID=" + strSauID);
        }

        /* Zapis zmian w tabeli towary (przycisk ZAPISZ) */
        private void saveChanges_towary()
        {
            //SqlDataAdapter generuje update na podstawie poprzedniego zapytania
            try
            {
                DataTable changes = ((DataTable)dataGridView_towary.DataSource).GetChanges(); //zmiany w tabeli towary

                if (changes != null)
                {
                    MySqlCommandBuilder mcb = new MySqlCommandBuilder(Database.mySqlDataAdapter_content);
                    Database.mySqlDataAdapter_content.UpdateCommand = mcb.GetUpdateCommand();
                    Database.mySqlDataAdapter_content.Update(changes);
                    ((DataTable)dataGridView_towary.DataSource).AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                Alert.Warning(ex.Message);
            }
        }

 /// <summary>
 ///     Update table when DataGridView row changed
 /// </summary>
 /// <param name="view">DataGridView control</param>
 public void Update(DataGridView view)
 {
     if (this.m_mysqlConnection != null && this.m_mysqlConnection.State == ConnectionState.Open)
     {
         var changes = ((DataTable)view.DataSource).GetChanges();
         if (changes != null)
         {
             var commandBuilder = new MySqlCommandBuilder(this.m_mysqlAdapter);
             this.m_mysqlAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
             this.m_mysqlAdapter.Update(changes);
             ((DataTable)view.DataSource).AcceptChanges();
         }
     }
 }

        public void fillDataGridSearchSheep(DataGridView dgwSearchSheep, String strFlokkID)
        {
            dbcMySql = new MySqlConnection(myconnectionstring);
            dbcMySql.Open();

            string strQuery = "";

            strQuery = "SELECT * FROM Sauer WHERE flokkID='" + strFlokkID + "'";

            sqlAdapter = new MySqlDataAdapter(strQuery, dbcMySql);
            sqlCommandBuilder = new MySqlCommandBuilder(sqlAdapter);

            sqlAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();

            dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            bindingSource = new BindingSource();
            bindingSource.DataSource = dataTable;

            dgwSearchSheep.DataSource = bindingSource;

            dbcMySql.Close();

            dgwSearchSheep.Columns["flokkID"].Visible = false;
        }

        public void fillDataGridViewMapSearch(DataGridView dgvSauer, String strSearchName, String strFlokkID)
        {
            dbcMySql = new MySqlConnection(myconnectionstring);
            dbcMySql.Open();

            string strQuery = "";

            strQuery = "SELECT * FROM Sauer WHERE flokkID='"+strFlokkID+"' AND (navn LIKE '%" + strSearchName + "%' OR sauID LIKE '%" + strSearchName + "%')";

            sqlAdapter = new MySqlDataAdapter(strQuery, dbcMySql);
            sqlCommandBuilder = new MySqlCommandBuilder(sqlAdapter);

            sqlAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();

            dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            bindingSource = new BindingSource();
            bindingSource.DataSource = dataTable;

            dgvSauer.DataSource = bindingSource;

            dbcMySql.Close();

            dgvSauer.Columns["flokkID"].Visible = false;
        }

 private void frmpayrolldetails_Load(object sender, EventArgs e)
 {
     // TODO: This line of code loads data into the 'newDataSet.payrolldetails' table. You can move, or remove it, as needed.
     this.payrolldetailsTableAdapter.Fill(this.newDataSet.payrolldetails);
     string strConn = "server=localhost;user id=root;database=pharma;password=;";
     ad = new MySqlDataAdapter("select * from `payrolldetails`", strConn);
     MySqlCommandBuilder builder = new MySqlCommandBuilder(ad);
     ad.Fill(this.newDataSet.payrolldetails);
     ad.DeleteCommand = builder.GetDeleteCommand();
     ad.UpdateCommand = builder.GetUpdateCommand();
     ad.InsertCommand = builder.GetInsertCommand();
     MySqlDataAdapter ad3;
 }