Thursday, October 22, 2009

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


13 comments:

  1. nice to meet u!

    I don't understand "GetInsertCommand()"

    where is the sql insert ?!

    ReplyDelete
  2. Hi, nice to meet you too!

    I think that I don't understand your question.

    The class MySqlCommandBuilder automatically creates the SQL INSERT template.
    It is invoked when you use DataGridView control or Rows object to add new rows, and then call Save().
    Is this what you meant?

    ReplyDelete
  3. hy
    i don't know how use this "mySqlCommandBuilder"
    know you help me?

    ReplyDelete
  4. why dont you give example when using DateTime Anda Decimal variable Not only Varchar

    ReplyDelete
  5. thanks, this helped me set up my datagridview

    ReplyDelete
  6. Thanks .. it's woooooooooooooorked :D

    ReplyDelete
  7. Thank you very much. It worked for me.

    ReplyDelete
  8. Thanks you very much. You save my time..

    ReplyDelete
  9. i am very new in c# and mysql. How do i get data in my grid and how do i delete and update the rows ??

    I have written ur code, dragged the datagridview in my form but how to get the data in it and do rest of the stuff ??

    Do i have to do something related to databinding source or nothing is needed ??

    Here is my code - http://pastebin.com/TAC55Z1U
    This is how my form looks at the moment - http://i.imgur.com/KBk35.jpg

    ReplyDelete
  10. You're a life saver !! Thankyou very much

    ReplyDelete
  11. Thankyou very much

    ReplyDelete
  12. That's great! Before this variant, I tried to update DataGridView using own command and get synchronization errors.

    ReplyDelete