C# DataGridView and MySql

This code demonstrates how to bind a DataGridView control to a MySql table.
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=7604&lngWId=10

Usage:

// bind DataGridView control

Bind();

// now user can modify table using the DataGridView control.

// a faster way to modify table, is to use the Rows property

// DataGridView control will be updated automatically

Rows.Add("a", "new", "row");

// to save changes:

Save();

A few notes about command builder:

• The command builder object MySqlCommandBuilder doesn't work with complex queries, in that case you should write your own commands for Update,Delete,Insert
• When writing a custom Update command, notice there is a difference between the new and old values
• In order to save time and effort, one could write a simple query and use the command builder object results to create the more complex query
• I already have a class for it, I'll publish it later

Here is the code:

MySqlConnection mySqlConnection;

MySqlDataAdapter mySqlDataAdapter;

MySqlCommandBuilder mySqlCommandBuilder;

DataTable dataTable;

BindingSource bindingSource;

public void Bind()

{

    mySqlConnection = new MySqlConnection(

        "SERVER=localhost;" +

        "DATABASE=;" +

        "UID=;" +

        "PASSWORD=;");

    mySqlConnection.Open();

    string query = "SELECT * FROM

";

    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;

    dataGridView1.DataSource = bindingSource;

}

public DataRowCollection Rows

{

    get { return dataTable.Rows; }

}

public void Save()

{

    mySqlDataAdapter.Update(dataTable);

}