C# MySqlDataAdapter类代码示例

    public int fjöldi_rada()
    {
        MySqlConnection connection;

         string connectionString = "Server=10.0.105.33;Database=Leikur;Uid=first;Pwd=first;";

         connection = new MySqlConnection(connectionString);

         connection.Open();

         string query = @"SELECT * FROM spilari";
         MySqlCommand cmd = new MySqlCommand(query, connection);

         cmd.ExecuteNonQuery();

         MySqlDataReader queryCommandReader = cmd.ExecuteReader();

         DataTable dataTable = new DataTable();
         dataTable.Load(queryCommandReader);

         MySqlDataAdapter adapter = new MySqlDataAdapter();
         DataSet ds = new DataSet();
         adapter.SelectCommand = cmd;
         adapter.Fill(ds, "SQL Temp Table");
         adapter.Dispose();
         cmd.Dispose();

         return ds.Tables[0].Rows.Count;
    }

        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 FunctionsReturnStringAndDecimal()
        {
            execSQL("CREATE TABLE bug52187a (a decimal(5,2) not null)");
            execSQL("CREATE TABLE bug52187b (b decimal(5,2) not null)");
            execSQL("insert into bug52187a values (1.25)");
            execSQL("insert into bug52187b values (5.99)");

            CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
            CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
            CultureInfo c = new CultureInfo("pt-PT");
            Thread.CurrentThread.CurrentCulture = c;
            Thread.CurrentThread.CurrentUICulture = c;

            string connStr = GetConnectionString(true) + ";functions return string=true";
            try
            {
                using (MySqlConnection con = new MySqlConnection(connStr))
                {
                    con.Open();
                    MySqlDataAdapter da = new MySqlDataAdapter(
                        "select *,(select b from bug52187b) as field_b from bug52187a", con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Assert.AreEqual(1, dt.Rows.Count);
                    Assert.AreEqual(1.25, dt.Rows[0][0]);
                    Assert.AreEqual(5.99, dt.Rows[0][1]);
                }
            }
            finally
            {
                Thread.CurrentThread.CurrentCulture = curCulture;
                Thread.CurrentThread.CurrentUICulture = curUICulture;
            }
        }

        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 BulkLoadColumnOrder()
        {
            execSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
                        n3 VARCHAR(250), PRIMARY KEY(id))");

            // first create the external file
            string path = Path.GetTempFileName();
            StreamWriter sw = new StreamWriter(path);
            for (int i = 0; i < 20; i++)
                sw.WriteLine(i + ",col3,col2,col1");
            sw.Flush();
            sw.Close();

            MySqlBulkLoader loader = new MySqlBulkLoader(conn);
            loader.TableName = "Test";
            loader.FileName = path;
            loader.Timeout = 0;
            loader.FieldTerminator = ",";
            loader.LineTerminator = Environment.NewLine;
            loader.Columns.Add("id");
            loader.Columns.Add("n3");
            loader.Columns.Add("n2");
            loader.Columns.Add("n1");
            int count = loader.Load();
            Assert.AreEqual(20, count);

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            Assert.AreEqual(20, dt.Rows.Count);
            Assert.AreEqual("col1", dt.Rows[0][1]);
            Assert.AreEqual("col2", dt.Rows[0][2]);
            Assert.AreEqual("col3", dt.Rows[0][3].ToString().Trim());
        }

        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 AutoIncrementColumnsOnInsert2()
        {
            execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL " +
                "AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            MySqlCommand cmd = (MySqlCommand)(cb.GetInsertCommand() as ICloneable).Clone();
            cmd.CommandText += "; SELECT last_insert_id() as id";
            cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
            da.InsertCommand = cmd;

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

            DataRow row = dt.NewRow();
            row["name"] = "Test";
            dt.Rows.Add(row);
            da.Update(dt);
            Assert.AreEqual(1, dt.Rows[0]["id"]);
            Assert.AreEqual("Test", dt.Rows[0]["name"]);

            row = dt.NewRow();
            row["name"] = "Test2";
            dt.Rows.Add(row);
            da.Update(dt);
            Assert.AreEqual(2, dt.Rows[1]["id"]);
            Assert.AreEqual("Test2", dt.Rows[1]["name"]);

            Assert.AreEqual(1, dt.Rows[0]["id"]);
        }

        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 Bug19481()
        {
            execSQL("DROP TABLE Test");
            execSQL("CREATE TABLE Test(ID INT NOT NULL AUTO_INCREMENT, " +
                "SATELLITEID VARCHAR(3) NOT NULL, ANTENNAID INT, AOS_TIMESTAMP DATETIME NOT NULL, " +
                "TEL_TIMESTAMP DATETIME, LOS_TIMESTAMP DATETIME, PRIMARY KEY (ID))");
            execSQL("INSERT INTO Test VALUES (NULL,'224','0','2005-07-24 00:00:00'," +
                "'2005-07-24 00:02:00','2005-07-24 00:22:00')");
            execSQL("INSERT INTO Test VALUES (NULL,'155','24','2005-07-24 03:00:00'," +
                "'2005-07-24 03:02:30','2005-07-24 03:20:00')");
            execSQL("INSERT INTO Test VALUES (NULL,'094','34','2005-07-24 09:00:00'," +
                "'2005-07-24 09:00:30','2005-07-24 09:15:00')");
            execSQL("INSERT INTO Test VALUES (NULL,'224','54','2005-07-24 12:00:00'," +
                "'2005-07-24 12:01:00','2005-07-24 12:33:00')");
            execSQL("INSERT INTO Test VALUES (NULL,'155','25','2005-07-24 15:00:00'," +
                "'2005-07-24 15:02:00','2005-07-24 15:22:00')");
            execSQL("INSERT INTO Test VALUES (NULL,'094','0','2005-07-24 17:00:00'," +
                "'2005-07-24 17:02:12','2005-07-24 17:20:00')");
            execSQL("INSERT INTO Test VALUES (NULL,'224','24','2005-07-24 19:00:00'," +
                "'2005-07-24 19:02:00','2005-07-24 19:27:00')");
            execSQL("INSERT INTO Test VALUES (NULL,'155','34','2005-07-24 21:00:00'," +
                "'2005-07-24 21:02:33','2005-07-24 21:22:55')");
            execSQL("INSERT INTO Test VALUES (NULL,'094','55','2005-07-24 23:00:00'," +
                "'2005-07-24 23:00:45','2005-07-24 23:22:23')");

            DateTime date = DateTime.Parse("7/24/2005");
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(CultureInfo.InvariantCulture,
                @"SELECT ID, ANTENNAID, TEL_TIMESTAMP, LOS_TIMESTAMP FROM Test
                WHERE TEL_TIMESTAMP >= '{0}'", date.ToString("u"));
            MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), conn);
            DataSet dataSet = new DataSet();
            da.Fill(dataSet);
        }

    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 ShowCreateTable()
		{
			MySqlDataAdapter da = new MySqlDataAdapter("SHOW CREATE TABLE Test", conn);
			DataTable dt = new DataTable();
			da.Fill(dt);

			Assert.AreEqual(1, dt.Rows.Count);
			Assert.AreEqual(2, dt.Columns.Count);
		}

    public void ShowCreateTable()
    {
      execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
      MySqlDataAdapter da = new MySqlDataAdapter("SHOW CREATE TABLE Test", conn);
      DataTable dt = new DataTable();
      da.Fill(dt);

      Assert.AreEqual(1, dt.Rows.Count);
      Assert.AreEqual(2, dt.Columns.Count);
    }

 internal static void fill(string queryString, DataTable toReturn)
 {
     using (var conn = new MySqlConnection(connString)) {
         conn.Open();
         conn.ChangeDatabase(Server.MySQLDatabaseName);
         using (MySqlDataAdapter da = new MySqlDataAdapter(queryString, conn)) {
             da.Fill(toReturn);
         }
         conn.Close();
     }
 }

        public void CommentsInSQL()
        {
            string sql = "INSERT INTO Test /* my table */ VALUES (1 /* this is the id */, 'Test' );" +
                "/* These next inserts are just for testing \r\n" +
                "   comments */\r\n" +
                "INSERT INTO \r\n" +
                "  # This table is bogus\r\n" +
                "Test VALUES (2, 'Test2')";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.ExecuteNonQuery();

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            DataTable table = new DataTable();
            da.Fill(table);
            Assert.AreEqual(1, table.Rows[0]["id"]);
            Assert.AreEqual("Test", table.Rows[0]["name"]);
            Assert.AreEqual(2, table.Rows.Count);
            Assert.AreEqual(2, table.Rows[1]["id"]);
            Assert.AreEqual("Test2", table.Rows[1]["name"]);
        }

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