C# MySqlCommandBuilder.GetInsertCommand方法代码示例

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

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

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

        public void TestBatchingMixed()
        {
            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 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;
 }

        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)
//.........这里部分代码省略.........

    /// <summary>
    /// Commits any changes to segment data set to the database
    /// </summary>
    public void CommitChanges()
    {
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString;
        MySqlConnection connection = new MySqlConnection(connString);

        MySqlDataAdapter dsAdapter = new MySqlDataAdapter();
        dsAdapter.SelectCommand = new MySqlCommand("SELECT * FROM Segments WHERE Segments.Trip_ID = " + this.ID, connection);

        MySqlCommandBuilder dsBuilder = new MySqlCommandBuilder(dsAdapter);

        dsBuilder.GetUpdateCommand();
        dsBuilder.GetInsertCommand();
        dsBuilder.GetDeleteCommand();

        try
        {
            dsAdapter.Update(this.SegmentDataSet, "Segments");
        }
        catch (Exception e)
        {
            System.Diagnostics.Debug.Write(e.ToString());
        }
        finally
        {
            dsBuilder.Dispose();
            dsAdapter.Dispose();
            connection.Dispose();
        }
    }