C# MySqlDataAdapter.Update方法代码示例

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlConnection myConn = new MySqlConnection();
                myConn.Host = "192.168.69.8";
                myConn.Port = 3306;
                myConn.UserId = "cody";
                myConn.Password = "greenstreetelite";
                myConn.Open();

                MySqlCommand myCommand = new MySqlCommand(" SELECT * FROM registration.regUser ;", myConn);
                MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
                myDataAdapter.SelectCommand = myCommand;
                DataTable dbDataSet = new DataTable();
                myDataAdapter.Fill(dbDataSet);
                BindingSource bSource = new BindingSource();

                bSource.DataSource = dbDataSet;
                dataGridView1.DataSource = bSource;
                myDataAdapter.Update(dbDataSet);

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

    public void MultiWord()
    {
      execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME,  `multi word` int, PRIMARY KEY(id))");

      MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
      MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
      DataTable dt = new DataTable();
      da.Fill(dt);

      DataRow row = dt.NewRow();
      row["id"] = 1;
      row["name"] = "Name";
      row["dt"] = DBNull.Value;
      row["tm"] = DBNull.Value;
      row["multi word"] = 2;
      dt.Rows.Add(row);
      da.Update(dt);
      Assert.AreEqual(1, dt.Rows.Count);
      Assert.AreEqual(2, dt.Rows[0]["multi word"]);

      dt.Rows[0]["multi word"] = 3;
      da.Update(dt);
      cb.Dispose();
      Assert.AreEqual(1, dt.Rows.Count);
      Assert.AreEqual(3, dt.Rows[0]["multi word"]);
    }

        public void MultiWord()
        {
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
                DataTable dt = new DataTable();
                da.Fill(dt);

                DataRow row = dt.NewRow();
                row["id"] = 1;
                row["name"] = "Name";
                row["dt"] = DBNull.Value;
                row["tm"] = DBNull.Value;
                row["multi word"] = 2;
                dt.Rows.Add(row);
                da.Update(dt);
                Assert.AreEqual(1, dt.Rows.Count);
                Assert.AreEqual(2, dt.Rows[0]["multi word"]);

                dt.Rows[0]["multi word"] = 3;
                da.Update(dt);
                cb.Dispose();
                Assert.AreEqual(1, dt.Rows.Count);
                Assert.AreEqual(3, dt.Rows[0]["multi word"]);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
        }

        public void AutoIncrementColumnsOnInsert()
        {
            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(100), PRIMARY KEY(id))");
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns[0].AutoIncrement = true;
            Assert.IsTrue(dt.Columns[0].AutoIncrement);
            dt.Columns[0].AutoIncrementSeed = -1;
            dt.Columns[0].AutoIncrementStep = -1;
            DataRow row = dt.NewRow();
            row["name"] = "Test";

            try
            {
                dt.Rows.Add(row);
                da.Update(dt);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            dt.Clear();
            da.Fill(dt);
            Assert.AreEqual(1, dt.Rows.Count);
            Assert.AreEqual(1, dt.Rows[0]["id"]);
            Assert.AreEqual("Test", dt.Rows[0]["name"]);
            cb.Dispose();
        }

        public void AutoIncrementColumnsOnInsert()
        {
            execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(100), PRIMARY KEY(id))");
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            da.InsertCommand = cb.GetInsertCommand();
            da.InsertCommand.CommandText += "; SELECT last_insert_id()";
            da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns[0].AutoIncrement = true;
            Assert.IsTrue(dt.Columns[0].AutoIncrement);
            dt.Columns[0].AutoIncrementSeed = -1;
            dt.Columns[0].AutoIncrementStep = -1;
            DataRow row = dt.NewRow();
            row["name"] = "Test";

            dt.Rows.Add(row);
            da.Update(dt);

            dt.Clear();
            da.Fill(dt);
            Assert.AreEqual(1, dt.Rows.Count);
            Assert.AreEqual(1, dt.Rows[0]["id"]);
            Assert.AreEqual("Test", dt.Rows[0]["name"]);
            cb.Dispose();
        }

        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 BulkCopyCTC(List<EmployeeDet> list)
        {
            DataTable dt =new DataTable();
            dt.Columns.Add(new DataColumn("employee_id",typeof(System.String)));
            dt.Columns.Add(new DataColumn("employee_name",typeof(System.String)));
            dt.Columns.Add(new DataColumn("emp_ctc",typeof(System.Decimal)));

            foreach(EmployeeDet item in list)
            {
            DataRow dr = dt.NewRow();
            dr["employee_id"]= item.GetID();
            dr["employee_name"]= item.GetName();
            dr["emp_ctc"]= item.GetCTC();
            dt.Rows.Add(dr);
            }

            MySqlConnection con =new MySqlConnection(newConnectionUtils().GetConnectionString());
            if(con.State==ConnectionState.Open)
            {
            con.Close();
            }
            con.Open();
            MySqlCommand cmd =new MySqlCommand("SP_InsertCTC", con);
            cmd.CommandType=CommandType.StoredProcedure;

            cmd.UpdatedRowSource=UpdateRowSource.None;

            cmd.Parameters.Add("?e_id",MySqlDbType.String).SourceColumn="employee_id";
            cmd.Parameters.Add("?e_name",MySqlDbType.String).SourceColumn=  "employee_name";
            cmd.Parameters.Add("?emp_ctc",MySqlDbType.Decimal).SourceColumn=  "emp_ctc";

            MySqlDataAdapter da =new MySqlDataAdapter();
            da.InsertCommand= cmd;
            da.UpdateBatchSize=100;
            int records = da.Update(dt);
            con.Close();
        }

        public void DifferentDatabase()
        {
            if (Version < new Version(4, 1)) return;

            execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME,  `multi word` int, PRIMARY KEY(id))");
            execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
            execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
            execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");

            conn.ChangeDatabase(database1);

            MySqlDataAdapter da = new MySqlDataAdapter(
                String.Format("SELECT id, name FROM `{0}`.Test", database0), conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);

            ds.Tables[0].Rows[0]["id"] = 4;
            DataSet changes = ds.GetChanges();
            da.Update(changes);
            ds.Merge(changes);
            ds.AcceptChanges();
            cb.Dispose();

            conn.ChangeDatabase(database0);
        }

        public void MultiUpdate()
        {
            execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME,  `multi word` int, PRIMARY KEY(id))");
            execSQL("INSERT INTO  Test (id, name) VALUES (1, 'test1')");
            execSQL("INSERT INTO  Test (id, name) VALUES (2, 'test2')");
            execSQL("INSERT INTO  Test (id, name) VALUES (3, 'test3')");
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.Fill(dt);

            dt.Rows[0]["id"] = 4;
            dt.Rows[0]["name"] = "test4";
            dt.Rows[1]["id"] = 5;
            dt.Rows[1]["name"] = "test5";
            dt.Rows[2]["id"] = 6;
            dt.Rows[2]["name"] = "test6";
            DataTable changes = dt.GetChanges();
            da.Update(changes);
            dt.AcceptChanges();

            dt.Rows[0]["id"] = 7;
            dt.Rows[0]["name"] = "test7";
            dt.Rows[1]["id"] = 8;
            dt.Rows[1]["name"] = "test8";
            dt.Rows[2]["id"] = 9;
            dt.Rows[2]["name"] = "test9";
            changes = dt.GetChanges();
            da.Update(changes);
            dt.AcceptChanges();
            cb.Dispose();
        }

        public void InsertDateTimeValue()
        {
            using (MySqlConnection c = new MySqlConnection(conn.ConnectionString +
                ";allow zero datetime=yes"))
            {
                c.Open();
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, dt FROM Test", c);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("id", typeof(int)));
                dt.Columns.Add(new DataColumn("dt", typeof(DateTime)));

                da.Fill(dt);

                DateTime now = DateTime.Now;
                DataRow row = dt.NewRow();
                row["id"] = 1;
                row["dt"] = now;
                dt.Rows.Add(row);
                da.Update(dt);

                dt.Clear();
                da.Fill(dt);
                cb.Dispose();

                Assert.AreEqual(1, dt.Rows.Count);
                Assert.AreEqual(now.Date, ((DateTime)dt.Rows[0]["dt"]).Date);
            }
        }

        public void TestAllowZeroDateTime()
        {
            execSQL("TRUNCATE TABLE Test");
            execSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");

            using (MySqlConnection c = new MySqlConnection(
                conn.ConnectionString + ";pooling=false;AllowZeroDatetime=true"))
            {
                c.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    reader.Read();

                    Assert.IsTrue(reader.GetValue(1) is MySqlDateTime);
                    Assert.IsTrue(reader.GetValue(2) is MySqlDateTime);

                    Assert.IsFalse(reader.GetMySqlDateTime(1).IsValidDateTime);
                    Assert.IsFalse(reader.GetMySqlDateTime(2).IsValidDateTime);

                    try
                    {
                        reader.GetDateTime(1);
                        Assert.Fail("This should not succeed");
                    }
                    catch (MySqlConversionException)
                    {
                    }
                }

                DataTable dt = new DataTable();
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
                da.Fill(dt);
                dt.Rows[0]["id"] = 2;
                DataRow row = dt.NewRow();
                row["id"] = 3;
                row["d"] = new MySqlDateTime("2003-9-24");
                row["dt"] = new MySqlDateTime("0000/0/00 00:00:00");
                dt.Rows.Add(row);

                da.Update(dt);

                dt.Clear();
                da.Fill(dt);
                Assert.AreEqual(2, dt.Rows.Count);
                MySqlDateTime date = (MySqlDateTime)dt.Rows[1]["d"];
                Assert.AreEqual(2003, date.Year);
                Assert.AreEqual(9, date.Month);
                Assert.AreEqual(24, date.Day);
                cb.Dispose();
            }
        }

        public void UsingFunctions()
        {
            execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME,  `multi word` int, PRIMARY KEY(id))");
            execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
            execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
            execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name, now() as ServerTime FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.Fill(dt);

            dt.Rows[0]["id"] = 4;
            da.Update(dt);

            da.SelectCommand.CommandText = "SELECT id, name, CONCAT(name, '  boo') as newname from Test where id=4";
            dt.Clear();
            da.Fill(dt);
            Assert.AreEqual(1, dt.Rows.Count);
            Assert.AreEqual("test1", dt.Rows[0]["name"]);
            Assert.AreEqual("test1  boo", dt.Rows[0]["newname"]);

            dt.Rows[0]["id"] = 5;
            da.Update(dt);

            dt.Clear();
            da.SelectCommand.CommandText = "SELECT * FROM Test WHERE id=5";
            da.Fill(dt);
            Assert.AreEqual(1, dt.Rows.Count);
            Assert.AreEqual("test1", dt.Rows[0]["name"]);

            da.SelectCommand.CommandText = "SELECT *, now() as stime FROM Test WHERE id<4";
            cb = new MySqlCommandBuilder(da);
            cb.ConflictOption = ConflictOption.OverwriteChanges;
            da.InsertCommand = cb.GetInsertCommand();
        }

        public void UpdateReturnFirstRecord()
        {
            string createTable =
            "CREATE TABLE `bugtable` ( " +
              "`id_auto` int(11) NOT NULL AUTO_INCREMENT," +
              "`field` varchar(50) DEFAULT NULL," +
              "counter int NOT NULL DEFAULT 0," +
              "PRIMARY KEY (`id_auto`)" +
            ")";

            string procGetAll =
            "CREATE PROCEDURE sp_getall_bugtable()" +
            " BEGIN " +
                "select * from bugtable;" +
            " END ";

            string procUpdate =
            "CREATE PROCEDURE sp_updatebugtable(" +
                "in p_id_auto int, " +
                "in p_field varchar(50)) " +
            "BEGIN " +
                "update bugtable set field = p_field, counter = counter+1 where id_auto=p_id_auto; " +
                "select * from bugtable where id_auto=p_id_auto; " + /*retrieve updated row*/
            "END ";

            execSQL(createTable);
            execSQL(procGetAll);
            execSQL(procUpdate);

            /* Add one row to the table */
            MySqlCommand cmd = new MySqlCommand(
                "insert into bugtable(field) values('x')", conn);
            cmd.ExecuteNonQuery();

            DataSet ds = new DataSet();
            MySqlDataAdapter da = new MySqlDataAdapter();

            da.SelectCommand = conn.CreateCommand();
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.CommandText = "sp_getall_bugtable";

            da.UpdateCommand = conn.CreateCommand();
            da.UpdateCommand.CommandType = CommandType.StoredProcedure;
            da.UpdateCommand.CommandText = "sp_updatebugtable";
            da.UpdateCommand.Parameters.Add("p_id_auto", MySqlDbType.Int32, 4, "id_auto");
            da.UpdateCommand.Parameters.Add("p_field", MySqlDbType.VarChar, 4, "field");
            da.UpdateCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

            da.Fill(ds, "bugtable");
            DataTable table = ds.Tables["bugtable"];
            DataRow row = table.Rows[0];
            row["field"] = "newvalue";
            Assert.AreEqual(row.RowState, DataRowState.Modified);
            Assert.AreEqual((int)row["counter"], 0);

            da.Update(table);

            // Verify that "counter" field was changed by updating stored procedure.
            Assert.AreEqual((int)row["counter"], 1);
        }

        public void UpdateDataSet()
        {
            execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))");
            execSQL("INSERT INTO Test VALUES( 1, NULL, 'Text field' )");

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            DataTable dt = new DataTable();
            da.Fill(dt);

            string s = (string)dt.Rows[0][2];
            Assert.AreEqual("Text field", s);

            byte[] inBuf = Utils.CreateBlob(512);
            dt.Rows[0].BeginEdit();
            dt.Rows[0]["blob1"] = inBuf;
            dt.Rows[0].EndEdit();
            DataTable changes = dt.GetChanges();
            da.Update(changes);
            dt.AcceptChanges();

            dt.Clear();
            da.Fill(dt);
            cb.Dispose();

            byte[] outBuf = (byte[])dt.Rows[0]["blob1"];
            Assert.AreEqual(inBuf.Length, outBuf.Length,
                      "checking length of updated buffer");
            for (int y = 0; y < inBuf.Length; y++)
                Assert.AreEqual(inBuf[y], outBuf[y], "checking array data");
        }

        public void SpecialCharactersInFieldNames()
        {
            execSQL("CREATE TABLE Test (`col%1` int PRIMARY KEY, `col()2` int, `col<>3` int, `col/4` int)");

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            cb.ToString();  // keep the compiler happy
            DataTable dt = new DataTable();
            da.Fill(dt);
            DataRow row = dt.NewRow();
            row[0] = 1;
            row[1] = 2;
            row[2] = 3;
            row[3] = 4;
            dt.Rows.Add(row);
            da.Update(dt);
        }