C# MySqlClient.MySqlCommandBuilder类代码示例

        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 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 DataTable SqlDataTable(string strname, string str, out DataSet ds, out MySqlDataAdapter da)
 {
     try
     {
         conn.Open();
         da = new MySqlDataAdapter(str, connstr);
         MySqlCommandBuilder thisBuilder = new MySqlCommandBuilder(da);
         ds = new DataSet();
         da.Fill(ds, strname);
         DataTable mytable = new DataTable();
         mytable = ds.Tables[0];
         return mytable;
     }
     catch (Exception ex)
     {
         da = null;
         ds = null;
         System.Windows.Forms.MessageBox.Show(ex.Message);
         return null;
     }
     finally
     {
         conn.Close();
     }
 }

        public static DataTable getCommand()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            try
            {
                // Ouverture de la connexion
                M_Connexion.Gestion.Open();
                // Requête SQL
                String ReqSQL = "SELECT * FROM commande WHERE status = ?";

                MySqlDataAdapter da = new MySqlDataAdapter(ReqSQL, M_Connexion.Gestion);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

                da.SelectCommand.Parameters.AddWithValue("@status", "En cours");

                da.Fill(ds, "commande");
                dt = ds.Tables[0];
                // Fermeture de la connexion
                M_Connexion.Gestion.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erreur :" + ex.Message);
                M_Connexion.Gestion.Close();
            }
            return dt;
        }

        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);
        }

        public static DataTable getCategoriesTab()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            try
            {
                // Ouverture de la connexion
                M_Connexion.Gestion.Open();
                // Requête SQL
                String ReqSQL = "SELECT * FROM categorie";

                MySqlDataAdapter da = new MySqlDataAdapter(ReqSQL, M_Connexion.Gestion);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

                da.Fill(ds, "categorie");
                dt = ds.Tables[0];
                // Fermeture de la connexion
                M_Connexion.Gestion.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erreur :" + ex.Message);
            }
            return dt;
        }

        internal static void SaveFromCache(string strTableName)
        {
            try
            {

                // Creates the data adapter and sets it with the update commands
                MySqlDataAdapter daAdapter = GetAdapter(strTableName);
                MySqlCommandBuilder cbBuilder = new MySqlCommandBuilder(daAdapter);

                // Updating table as is
                int nRowsUpdated = daAdapter.Update(Cache.SDB.Tables[strTableName]);

                Globals.LogFiles["DataBaseLog"].AddMessages(Globals.DbActivity.WRITE.ToString() +
                                                            " at " + DateTime.Now,
                                                            "Command: Adapter.Update(" + strTableName + ")",
                                                            "Result: " + nRowsUpdated.ToString());
            }
            // In the event of a databse exception
            catch (MySqlException e)
            {
                Globals.LogFiles["ErrorLog"].AddError(e.ErrorCode, e.Message, DateTime.Now);
                Globals.LogFiles["ErrorLog"].AddMessage(e.StackTrace);
            }
            // If any other exception occurs
            catch (Exception e)
            {
                Globals.LogFiles["ErrorLog"].AddError(Globals.ErrorCodes.SQL_ERROR, e.Message, DateTime.Now);
                Globals.LogFiles["ErrorLog"].AddMessages(e.StackTrace, e.InnerException.Message);
            }
        }

        //meat and soft drinks
        private void button10_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            if (tablecheck == 2)
            {
                //meat
                try
                {
                    string myConnection = conection;
                    MySqlConnection myConn = new MySqlConnection(myConnection);
                    MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
                    //myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn);
                    MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Meat + Fish' ;", myConn);
                    MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
                    myConn.Open();

                    MySqlDataReader reader = comand.ExecuteReader();

                    while (reader.Read())
                    {
                        listBox1.Items.Add(reader.GetString(1));
                        listBox1.Items.Add(reader.GetString(2));
                        listBox1.Items.Add(reader.GetString(6));
                    }
                    myConn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else if (tablecheck == 4)
            {
                //soft drinks
                try
                {
                    string myConnection = conection;
                    MySqlConnection myConn = new MySqlConnection(myConnection);
                    MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
                    //myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn);
                    MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Soft Drinks' ;", myConn);
                    MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
                    myConn.Open();

                    MySqlDataReader reader = comand.ExecuteReader();

                    while (reader.Read())
                    {
                        listBox1.Items.Add(reader.GetString(1));
                        listBox1.Items.Add(reader.GetString(2));
                        listBox1.Items.Add(reader.GetString(6));
                    }
                    myConn.Close();
                }
                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();
        }

 //Consultas  a la Base  de Datos (Poder llenar el DataGridView)
 public void consultar(string sql, string tabla)
 {
     ds.Tables.Clear();
     da = new MySqlDataAdapter(sql, con);
     cmb = new MySqlCommandBuilder(da);
     da.Fill(ds, tabla);
 }

        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);
            }
        }

        private void GetData(string selectCommand)
        {
            try
            {
                // Specify a connection string. Replace the given value with a
                // valid connection string for a Northwind SQL Server sample
                // database accessible to your system.
                String connectionString = "server=localhost;uid=root;pwd=root;database=esalon;";

                // Create a new data adapter based on the specified query.
                dataAdapter = new MySqlDataAdapter(selectCommand, connectionString);

                // Create a command builder to generate SQL update, insert, and
                // delete commands based on selectCommand. These are used to
                // update the database.
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);

                // Populate a new data table and bind it to the BindingSource.
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(table);
                bindingSource1.DataSource = table;
            }
            catch (MySqlException)
            {
                MessageBox.Show("Unable to connect to the database. Please check your connection string");
            }
        }

        /// <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 DataTable RetDataTable(string sql)
        {
            data = new DataTable();
            da = new MySqlDataAdapter(sql, conn);
            cb = new MySqlCommandBuilder(da);
            da.Fill(data);

            return data;
        }

 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);
 }