MySQL / C＃Commit DataGridView对单元格编辑的更改？ [英] MySQL / C# Commit DataGridView Changes on cell edit?
问题描述
我有一个包含5个dataGridViews的Form，当你点击进入单元格时，我需要能够保存对Views的更改，类似于你在Excel中的工作方式。
这是我的连接信息：
I have a Form with 5 dataGridViews, and I need to be able to save the changes to the Views when you click into a cell, similar to how you would work in Excel.
Here is my connection info:
public static MySqlConnection db = new MySqlConnection();
public string jobnumber = "";
public string str = "SERVER=192.168.1.149; DATABASE=starflitesystems; UID=iuapp; " +
"Password=iuapp";
我的表单开放代码：
My Form Open Code:
public frmPricingTemplate_Start(string s)
{
InitializeComponent();
createTempTable();
setDGVQueries();
hidePanels(pnlBasePackage);
btnReset(btnBasePackage);
this.WindowState = FormWindowState.Maximized;
jobnumber = s;
txtJobNumber.Text = s;
comboBox1.Visible = false;
comboBox2.Visible = false;
dataGridRefresh();
dg2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dg3.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dg4.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dg5.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
我的数据库查询：
My Database Queries:
public void setDGVQueries()
{
/* Strings for dataGridViews */
selectDGV1 = "SELECT `Group`, Material, `Sub-Material` " +
"FROM temporary_table " +
"WHERE tab='" + activeTab + "';";
selectDGV2 = "SELECT Quantity as `Quantity`, Cost as `Cost` " +
"FROM temporary_table " +
"WHERE tab='" + activeTab + "';";
selectDGV3 = "SELECT Quantity2 as `Quantity`, Cost2 as `Cost` " +
"FROM temporary_table " +
"WHERE tab='" + activeTab + "';";
selectDGV4 = "SELECT Quantity3 as `Quantity`, Cost3 as `Cost` " +
"FROM temporary_table " +
"WHERE tab='" + activeTab + "';";
selectDGV5 = "SELECT Quantity as `Quantity`, Cost as `Cost` " +
"FROM temporary_table " +
"WHERE tab='" + activeTab + "';";
}
我的数据库刷新功能：
My Database Refresh Function:
public void dataGridRefresh()
{
/* Define all dataTables for the dataGridViews */
MySqlDataAdapter return1 = new MySqlDataAdapter(selectDGV1, str);
DataTable dt1 = new DataTable("base");
return1.Fill(dt1);
MySqlDataAdapter return2 = new MySqlDataAdapter(selectDGV2, str);
DataTable dt2 = new DataTable("base");
return2.Fill(dt2);
MySqlDataAdapter return3 = new MySqlDataAdapter(selectDGV3, str);
DataTable dt3 = new DataTable("base");
return3.Fill(dt3);
MySqlDataAdapter return4 = new MySqlDataAdapter(selectDGV4, str);
DataTable dt4 = new DataTable("base");
return4.Fill(dt4);
MySqlDataAdapter return5 = new MySqlDataAdapter(selectDGV5, str);
DataTable dt5 = new DataTable("base");
return5.Fill(dt5);
/* Set DataSources for all datagridViews */
dg1.DataSource = dt1;
dg2.DataSource = dt2;
dg3.DataSource = dt3;
dg4.DataSource = dt4;
dg5.DataSource = dt5;
}
基本上，这会将某些信息加载到我的dataGridViews中，但现在，在加载数据之后，我需要能够点击进入单元格，编辑它，并在焦点离开单元格后自动将更改提交到表格。
我看了在网上关于它的一些事情，但他们似乎都使用了不同的方法连接到数据库，所以我认为它们不适合我。
我的理论是我需要使用`for each（dg1.SelectedRows中的row r）{} Block，但我不确定该怎么做。
非常感谢任何和所有帮助。
Basically, this loads certain information into my dataGridViews, but now, after that data is loaded, I need to be able to click into a cell, edit it, and have it automatically commit that change to the table once the focus leaves the cell.
I looked up a few things online about it, but they all appear to have used a different method to connect to the db, so I don't think they will work for me.
My theory is that I need to use a `for each (row r in dg1.SelectedRows){} Block, but I am unsure of exactly what to do.
Any and all help is greatly appreciated.
推荐答案
这种方法应该有效。但不保证。
我希望它有所帮助，
George
一些指示。
1.为DataTable调用AcceptChanges
这将确保行保持不变状态。
2.必须存在一个或多个主键。
参见DataTable.PrimaryKey
This approach "should" work. No guarantees, though.
I hope it helps,
George
Some pointers.
1. Call AcceptChanges for theDataTable
This will make sure the rows are in an unchanged state.
2. One or more primary key must exist.
SeeDataTable.PrimaryKey
MySqlDataAdapter return1 = new MySqlDataAdapter(selectDGV1, str);
DataTable dt1 = new DataTable("base");
return1.Fill(dt1);
dt1.AcceptChanges();
// I don't know your primary key
dt1.PrimaryKey = new DataColumn[] {new DataColumn("???")};
// You can use the same event implementation for all grids
private void dg_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
// Who called me?
DataGridView dg = (sender as DataGridView);
if (dg == null)
return;
DataTable dt = null;
dt = (dg.DataSource as DataTable);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
// Assuming the user cannot add or delete rows, only modify
if (dr.RowState == DataRowState.Modified)
{
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
foreach (DataColumn dc in dr.Table.Columns)
{
// Skip the primary key
if (dt.PrimaryKey.Contains(dc))
continue;
sbColumns.Append(dc.ColumnName);
sbColumns.Append(",");
sbValues.Append(dr[dc.ColumnName].ToString());
sbValues.Append(",");
}
// Create the WHERE columns and values
StringBuilder sbPrimaryKey = new StringBuilder();
foreach (DataColumn dcPrim in dt.PrimaryKey)
{
if (sbPrimaryKey.Length > 0)
sbPrimaryKey.Append(" AND ");
sbPrimaryKey.AppendFormat("{0} = '{1}'", dcPrim.ColumnName, dr[dcPrim]);
}
string sqlUpdate = String.Format("UPDATE {0} ({1}) VALUES ({2}) WHERE {3};",
dt.TableName, sbColumns.ToString().TrimEnd(','),
sbValues.ToString().TrimEnd(','),
sbPrimaryKey);
// Send the data to the data base
...
// Make sure the data tables is updated
dt.AcceptChanges();
}
}
}
}
这篇关于MySQL / C＃Commit DataGridView对单元格编辑的更改？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
