C# MySqlDataAdapter.Update方法代码示例

        /// <summary>
        /// Initializes a new instance of the <see cref="ManagerAddExistingItemGui"/> class.
        /// </summary>
        /// <param name="jobID1">The job i d1.</param>
        public ManagerAddExistingItemGui(string jobID1)
        {
            this.jobID = jobID1;
            InitializeComponent();
            this.WindowStartupLocation = WindowStartupLocation.CenterScreen;
            jobid_label.Content = jobID;

            try
            {
                MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
                MySqlConn.Open();
                string Query1 = ("select itemid as `מקט פריט`,itemName as `שם פריט`, item_discription as `תאור פריט` from project.item WHERE itemStatus='בעבודה' group by itemid");
                MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
                MSQLcrcommand1.ExecuteNonQuery();
                MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
                //DataTable dt1 = new DataTable("items");
                mysqlDAdp.Fill(dt);
                Create_DataTable1_Columns_End();
                //   Make_CheckBox1_Columns_False();
                dataGrid1.ItemsSource = dt.DefaultView;
                mysqlDAdp.Update(dt);

                MySqlConn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {

                progressBar1.Value = 15;
                var mydbconnection = new dbconnection();
                progressBar1.Value = 30;
                string query = "SELECT * FROM inventory";
                MySqlCommand cmd = new MySqlCommand(query, mydbconnection.getconnect);
                progressBar1.Value = 50;
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmd;
                DataTable dataset = new DataTable();
                sda.Fill(dataset);
                progressBar1.Value = 80;
                //BindingSource bsource = new BindingSource();
                //bsource.DataSource = dataset;
                inventorygrid.DataSource = dataset;
                sda.Update(dataset);
                progressBar1.Value = 100;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message);
            }
        }

        private void ViewIssued_Load(object sender, EventArgs e)
        {
            try {
                string query;
                if (id == null) {
                    query = "select * from library.borrowed_books;";
                } else {
                    query = "select * from library.borrowed_books where card_no='" + id + "';";
                }
                MySqlConnection myConn = DB.GetDB();

                MySqlCommand cmdDataBase = new MySqlCommand (query, myConn);
                // MySqlDataReader myReader;

                MySqlDataAdapter sda = new MySqlDataAdapter ();
                sda.SelectCommand = cmdDataBase;
                DataTable dbDataset = new DataTable ();
                sda.Fill (dbDataset);
                BindingSource bsource = new BindingSource ();
                bsource.DataSource = dbDataset;
                dataGridView1.DataSource = bsource;
                sda.Update (dbDataset);

            } catch (Exception ex) {
                MessageBox.Show (ex.Message);
            }
        }

        public void AutoIncrementColumns()
        {
            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL auto_increment primary key)");
            execSQL("INSERT INTO Test VALUES(NULL)");

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
            DataRow row = ds.Tables[0].NewRow();
            ds.Tables[0].Rows.Add(row);

            try
            {
                da.Update(ds);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }

            ds.Clear();
            da.Fill(ds);
            Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
            Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]);
            cb.Dispose();
        }

        // public static DataGrid dataGrid1 = new DataGrid();
        /// <summary>
        /// Initializes a new instance of the <see cref="ManagerEMPGui"/> class.
        /// </summary>
        public ManagerEMPGui()
        {
            InitializeComponent();
            this.WindowStartupLocation = WindowStartupLocation.CenterScreen;

            try
            {
                MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
                MySqlConn.Open();
                string Query1 = ("SELECT empid as `תעודת זהות`,emp_firstname as `שם פרטי` ,emp_lastname as `שם משפחה` , emp_insidenum as `מספר עובד` ,emp_address as `כתובת` ,emp_phone as `מספר טלפון`, emp_cellphone as `טלפון נייד`, emp_start_date as `תאריך התחלת עבודה` FROM project.employees ");
                MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
                MSQLcrcommand1.ExecuteNonQuery();
                MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
                //DataTable dt = new DataTable("employess");
                dt.Clear();
                mysqlDAdp.Fill(dt);
                dataGrid1.ItemsSource = dt.DefaultView;
                mysqlDAdp.Update(dt);
                MySqlConn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

 private void fillgridPerg()
 {
     id = -1;
     buttonDel.Visible = false;
     buttonEdit.Visible = false;
     buttonIns.Visible = false;
     buttonCancel.Visible = false;
     DBConnect NewcConnection = new DBConnect();
     NewcConnection.dbConnection();
     MySqlCommand querysql = new MySqlCommand("Select id_quest, id_qs, questao, name_qs From questionarios, questionset where id_qs_fk = id_qs", DBConnect.db);
     try
     {
         MySqlDataAdapter dados = new MySqlDataAdapter();
         dados.SelectCommand = querysql;
         DataTable tabela = new DataTable();
         dados.Fill(tabela);
         BindingSource fonte = new BindingSource();
         fonte.DataSource = tabela;
         dataGridView1.DataSource = fonte;
         this.dataGridView1.Columns[0].Visible = false;
         this.dataGridView1.Columns[1].Visible = false;
         dataGridView1.Columns[2].HeaderText = "Questão";
         dataGridView1.Columns[3].HeaderText = "QuestionSet";
         dados.Update(tabela);
         dataGridView1.AutoResizeColumns();
         dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     DBConnect.db.Close();
     textBoxPerg.Clear();
     comboBox1.SelectedIndex = 0;
 }

        private void fillgridDef()
        {
            id = -1;
            buttonDel.Visible = false;
            buttonEdit.Visible = false;
            buttonIns.Visible = false;
            buttonCancel.Visible = false;
            DBConnect NewcConnection = new DBConnect();
            NewcConnection.dbConnection();
            MySqlCommand querysql = new MySqlCommand("Select * From deficiencias", DBConnect.db);

            try
            {
                MySqlDataAdapter dados = new MySqlDataAdapter();
                dados.SelectCommand = querysql;
                DataTable tabela = new DataTable();
                dados.Fill(tabela);
                BindingSource fonte = new BindingSource();
                fonte.DataSource = tabela;
                dataGridView1.DataSource = fonte;
                this.dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[1].HeaderText = "Deficiencia";
                dados.Update(tabela);
                dataGridView1.AutoResizeColumns();
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            DBConnect.db.Close();
            textBoxDef.Clear();
        }

        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 MainWindow()
        : base(Gtk.WindowType.Toplevel)
    {
        Build ();

        MySqlConnection mySqlConnection = new MySqlConnection (
            "DataSource=localhost;Database=dbprueba;User ID=root;Password=sistemas"
        );
        mySqlConnection.Open ();
        string selectSql = "select * from categoria"; //uso categoria ya que es el nombre que tengo en la BD
        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter (selectSql, mySqlConnection);
        DataSet dataSet = new DataSet ();
        mySqlDataAdapter.Fill (dataSet);

        show (dataSet);

        DataTable dataTable = dataSet.Tables [0];
        DataRow dataRow = dataTable.Rows [0];
        dataRow ["nombre"] = DateTime.Now.ToString ();

        new MySqlCommandBuilder (mySqlDataAdapter);

        mySqlDataAdapter.Update (dataSet);

        mySqlDataAdapter.RowUpdating += delegate(object sender, MySqlRowUpdatingEventArgs e) {
            Console.WriteLine("e.Command.CommandText={0}", e.Command.CommandText);
        };
    }

	public MainWindow (): base (Gtk.WindowType.Toplevel)
	{
		Build ();

		MySqlConnection mySqlConnection = new MySqlConnection(
			"DataSource=localhost;Database=dbprueba;User ID=root;Password=sistemas"
			);

		//DATA ADAPTER
		mySqlConnection.Open ();
		string selectSql = "select * from articulo";
		MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter (selectSql,mySqlConnection);

		//CREAR DATASET
		DataSet dataSet = new DataSet ();
		mySqlDataAdapter.Fill (dataSet);//LLENAMOS EL DATASET CON EL DATA ADAPTER

		show (dataSet);
		DataTable dataTable = dataSet.Tables [0];

		DataRow dataRow = dataTable.Rows [0];
		dataRow ["nombre"] = DateTime.Now.ToString ();

		new MySqlCommandBuilder (mySqlDataAdapter);//NECESARIO PARA EL UPDATE
		mySqlDataAdapter.Update (dataSet);//UPDATE A LA BASE DE DATOS

		mySqlDataAdapter.RowUpdating += delegate(object sender, MySqlRowUpdatingEventArgs e) {
			Console.WriteLine ("e.Command.CommandText={0}", e.Command.CommandText);
	};
	}

        /// <summary>
        /// 插入数据通过Datatable
        /// </summary>
        /// <param name="_dt"></param>
        /// <returns>影响记录条数</returns>
        public override int DataTableInsert(DataTable _dt)
        {
            bool flag = false;
            int _nResult = 0;
            if (_dt == null)
                return _nResult;
            string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName);
            MySqlCommand _Command = (MySqlCommand)CreateCommand(_sCmdText, CommandType.Text);
            MySqlDataAdapter _adapter = new MySqlDataAdapter(_Command);
            MySqlDataAdapter _adapter1 = new MySqlDataAdapter(_Command);
            MySqlCommandBuilder _builder = new MySqlCommandBuilder(_adapter1);

            _adapter.InsertCommand = _builder.GetInsertCommand();

            if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count)
            {
                flag = true;//因为表中有自增字段，所以CommandBuild生成的InserttCommand的参数中少了自增字段
                foreach (DataColumn _dc in _dt.Columns)
                {
                    if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName))
                    {
                        _adapter.InsertCommand.CommandText =
                            _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName);

                        _adapter.InsertCommand.CommandText =
                            _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",@" + _dc.ColumnName);

                        _adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, MySqlDbType.Decimal, _dc.MaxLength, _dc.ColumnName);

                        if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count)
                            break;
                    }
                }
            }

            if (flag)
                this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName));

            this.BeginTransaction();
            try
            {
                _adapter.InsertCommand.Transaction = _Command.Transaction;
                _Command.CommandText = "delete from " + _dt.TableName;
                _Command.ExecuteNonQuery();
                _nResult = _adapter.Update(_dt);
                this.CommitTransaction();
            }
            catch (Exception ex)
            {
                this.RollbackTransaction();
                throw ex;
            }
            finally
            {
                if (flag)
                    this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName));
            }
            return _nResult;
        }

 public int SaveNewItems(ref DataTable NewItemTBL)
 {
     command = new MySqlCommand(SQL_SELECT_ITEM, GetDBConnection());
     adapter = new MySqlDataAdapter();
     adapter.SelectCommand = command;
     builder = new MySqlCommandBuilder(adapter);
     adapter.InsertCommand = builder.GetInsertCommand();
     adapter.ContinueUpdateOnError = true;
     return adapter.Update(NewItemTBL);
 }

 public void commitChanges(String query, DataTable dataTable)
 {
     lock (syncLock)
     {
         var adapter = new MySqlDataAdapter();
         var mcb = new MySqlCommandBuilder(adapter);
         adapter.SelectCommand = new MySqlCommand(query, conn);
         adapter.Update(dataTable);
         dataTable.AcceptChanges();
     }
 }

        public void AdapterConcurrentException()
        {
            execSQL(
            "CREATE TABLE T (" +
            "id_auto int(11) NOT NULL AUTO_INCREMENT," +
            "field varchar(50) DEFAULT NULL," +
            "PRIMARY KEY (id_auto))");

              MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM T", conn);
              da.InsertCommand = conn.CreateCommand();
              da.InsertCommand.CommandText = @"INSERT INTO T(field) VALUES (@p_field);
                                            SELECT * FROM T WHERE [email protected]@IDENTITY";
              da.InsertCommand.Parameters.Add("@p_field", MySqlDbType.VarChar, 50, "field");
              da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

              da.DeleteCommand = conn.CreateCommand();
              da.DeleteCommand.CommandText = "DELETE FROM T WHERE [email protected]_auto";
              da.DeleteCommand.Parameters.Add("@id_auto", MySqlDbType.Int32, 4, "id_auto");

              DataSet ds = new DataSet();
              da.Fill(ds, "T");

              DataTable table = ds.Tables["T"];
              DataRow r = table.NewRow();
              r["field"] = "row";
              table.Rows.Add(r);
              da.Update(table);

              Assert.AreEqual(r.RowState, DataRowState.Unchanged);

              table.Rows[0].Delete();

              r = table.NewRow();
              r["field"] = "row2";
              table.Rows.Add(r);

              da.Update(table); // here was concurrencyviolation
              da.Fill(ds);
              Assert.AreEqual(ds.Tables["T"].Rows.Count, 1);
              Assert.AreEqual(ds.Tables["T"].Rows[0]["field"], "row2");
        }

 public void FillGrid(string sql, ref DataGridView gridView)
 {
     var cmd = new MySqlCommand(sql, this.connection);
     cmd.CommandTimeout = 300;
     var adapter = new MySqlDataAdapter(cmd);
     var dataTable = new DataTable();
     adapter.Fill(dataTable);
     var bindingSource = new BindingSource();
     bindingSource.DataSource = dataTable;
     gridView.DataSource = bindingSource;
     adapter.Update(dataTable);
 }