MySql Change DataGridView Information

Question

I wrote a simple program that populates a Datagrid from MySQL.

In one of my columns I have a Boolean (0 or 1)

Once the Datagrid populates with the query I have check boxes for the boolean.

Does anyone know how to use a function when you check the check boxes for the boolean? (This is to send an update of just the boolean of the column.)

To sum up, When the check-box is checked it changes the boolean value from 0 to 1 in the MySQL database.

The reason I am having an issue with this is simply because the Datagrid does not populate until I run the application. Therefore, I cannot figure out how to change it while in visual studio.

How the DGV is populated:

MySqlConnection conn = new MySqlConnection(dbConnection);
conn.Open();
MySqlCommand cmd = new MySqlCommand();

cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter();

//SQL Query String
string sqlSelectAll = "SELECT * FROM `Tasks` WHERE `Completed` = 0";
da.SelectCommand = new MySqlCommand(sqlSelectAll, conn);

DataTable table = new DataTable();
da.Fill(table);

BindingSource bSource = new BindingSource();
bSource.DataSource = table;

dataGridView1.DataSource = bSource;

Any Help would be grateful.

Note:

I've tried using the data configuration manager in VS2015, which resulted in a completely different error that has nothing to do with this. So that's out of the question.

Answer 1

If you were to persist that DataAdapter it will apply all changes to the underlying DataTable for you - multiples in fact:

First class level object to be reused:

public partial class Form1 : Form

    MySQLDataAdapter myDA;
    DataTable myDT;
    string dbConnStr = "server=..."

Then where you initialize everything:

string sql = "SELECT A, B, C, D... FROM `Tasks`";

// create the DT
myDT = new DataTable();

// The Adapter can create its own Connection 
//     and SelectCommand
myDA = new MySqlDataAdapter(sql, dbConnStr);

MySqlCommandBuilder cb = new MySqlCommandBuilder(myDA);

// "teach" the DA how to Update and Add:
myDA.UpdateCommand = cb.GetUpdateCommand();
myDA.InsertCommand = cb.GetInsertCommand();
myDA.DeleteCommand = cb.GetDeleteCommand();

myDA.Fill(myDT);
myDA.FillSchema(myDT, SchemaType.Source);

dgv1.DataSource = myDT;

This should create all the needed columns in the DGV and fill it with rows. That part you have/know. Click 2-3 checkboxes to change a value, then on a button press add this:

int rows = myDA.Update(myDT);

Under Debug if you examine the value of rows, it will be as many as the number of checkboxes you changed. It would also INSERT, DELETE or UPDATE any number of rows where that action was needed.

• There is no need to create a connection. Using the MySqlDataAdapter(string, string) constructor overload, you pass the connection string and it creates a connection for itself to use.
• Likewise, it creates a DBCommand object for the SELECT command
• MySQLCommandBuilder then uses that SelectCommand to create Update, Delete etc commands the DataAdapter will use. This allows you to issue 1 (one) Update method call to update 1, 10 or 1000 rows.
• When dealing with a single table, you may not need any other SQL in your app
• The DataAdapter has the connection string, so it opens and closes its own connections.

If you define the PrimaryKey for the DataTable, the DA can also refresh the data - if changes are made to the table outside the app such as by another user, then you can pick up those new rows. After you update with your own changes:

var rows = myDA.Fill(myDT);

This is an actual refresh - it will only load new rows and any changes to any existing rows. Note that the WHERE clause in the original SQL still applies.

As you can see, the DataAdapter is capable of much more than just filling DataTables; if that is all you need to do, you can use a DbCommand and DataReader:

myDt.Load(cmd.ExecuteReader()):