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