How to update SQL Server database with C # Datagridview binding source
I am writing a Winforms application in C # that allows the user to edit and update the database using a datagridview.
The problem is, I cannot get it to work. The only thing I managed to achieve was to update the datagridview, but when I go to the database table, there is no change to the data. I have a search on many sites discussing this issue but still didn't work for me.
Things I've tried so far:
- database binding to datagridview
- changing the property
Copy to Output Directory
toCopy if newer
- with
dataAdapter.Update((DataTable)bindingSource1.DataSource)
or without an update request. - execute update request without
dataAdapter.update(...)
.
Here is my code:
public Form1()
{
InitializeComponent();
GetData("SELECT * FROM Table1");
}
void GetData(string selectCommand)
{
SqlConnection conn = new SqlConnection(Properties.Settings.Default.NewDBTESTConnectionString);
dataAdapter = new SqlDataAdapter(selectCommand, conn);
commandBuilder = new SqlCommandBuilder(dataAdapter);
table = new DataTable();
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
dataGridView1.DataSource = bindingSource1;
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
dataAdapter.Update((DataTable)bindingSource1.DataSource);
}
source to share
Without the call, bindingSource1.EndEdit
your underlying DataTable does not see any changes, and therefore the Update command does not need to update anything.
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
bindingSource1.EndEdit();
DataTable dt = (DataTable)bindingSource1.DataSource;
// Just for test.... Try this with or without the EndEdit....
DataTable changedTable = dt.GetChanges();
Console.WriteLine(changedTable.Rows.Count);
int rowsUpdated = da.Update(dt);
Console.WriteLine(rowsUpdated);
}
source to share
You can have a save or refresh button to execute the code like this:
bindingSource1.EndEdit();
DataTable dt = (DataTable)bindingSource1.DataSource;
dataAdaper.Update(dt);
You will save all the changes you made, but if you sort the datagridview columns first, then change the data, then run the save code above, you will skip one record, the top first before sorting. To fix this, you have to sort it and then save. The best way to do this is if you know which column to sort, you need to sort it when you load the data into the datagridview.
Jia Zhuang
source to share