通过对WPF Datagrid的编辑来更新数据库 [英] Updating Database through edits to WPF Datagrid
问题描述
我已经看到了很多关于此的文章，但无法使它起作用。我的C＃WPF应用程序中有一个窗口，在加载时，该窗口会根据预定义的查询成功填充数据表（由上一个窗口中的用户选择更改）
I have seen a ton of write ups on this and yet cannot get this to work. I have a window in my C# WPF application that, on load, successfully fills a datatable based on a pre-defineds query (altered by user selections from previous window)
填充逻辑在这里：
private void FillDataGrid()
{
string ConString = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
string CmdString = string.Empty;
using (SqlConnection con = new SqlConnection(ConString))
{
CmdString = $@"select PGCL.Client_Name, PGG.Guarantee_Title, PGG.Guarantee_Desc_Org, PGGM.Target, PGG.Timing_Measurement, PGP.Month, PGP.Year, PGP.Quarter, PGP.Numerator, PGP.Denominator, PGP.Performance_Percent, PGP.Achieved_Flag, PGP.Performance_ID
from PGG
inner join PGC on PGC.Contract_ID = PGG.Contract_ID
inner join PGCL on PGCL.Client_ID = PGC.Contract_ID
inner join PGGM on PGGM.Guarantee_ID = PGG.Guarantee_ID
inner join PGP on PGP.Guarantee_ID = PGG.Guarantee_ID
Where PGP.Month = {this.monthNum.ToString()} and PGP.year = {this.yearNum.ToString()}
and PGC.Active_Flag = 'y'
and PGG.Department_Responsible_ID = {this.deptID.ToString()}
Order by Client_Name, Guarantee_Order asc";
SqlCommand cmd = new SqlCommand(CmdString, con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("PG");
sda.Fill(dt);
DGPG.ItemsSource = dt.DefaultView;
}
}
这部分效果很好。
我在PGP表中有5列（从上别名），我想根据窗口关闭时的用户输入动态地（通过几个步骤）向其中写入值（并提示保存）。建议使用哪种方法？
I have 5 columns in the PGP (aliased from above) table that I want to dynamically write values (in a few steps) to based on user input at the close of the window (with a prompt to save). What method would be recommended for doing this?
即：如果用户在第5行的分子输入值为'5'，在第10行的分母输入值为'7' ，我希望发生以下情况：
i.e.: if a user entered the value '5' for for Numerator on row 5 and '7' for denominator on row 10, i want the following to happen:
1）使用PGP.ID，插入到PGP_History表中（用于历史更改日志）
2）更新PGP表的值也相应。
1) using the PGP.ID, insert into the PGP_History table (for historical logs of changes) 2) UPDATE PGP table values accordingly.
如果有人能让我朝这个方向前进，我将不胜感激？
I would greatly appreciate it if someone could get me going in this direction?
谢谢，
Wes
Thank you, Wes
我想我已经取得了一些进步，但是坚持
I think I have made some progress but am stuck on the below, now:
private void FillDataGrid()
{
string ConString = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
string CmdString = string.Empty;
using (SqlConnection con = new SqlConnection(ConString))
{
CmdString = $@"select PGCL.Client_Name, PGG.Guarantee_Title, PGG.Guarantee_Desc_Org, PGGM.Target, PGG.Timing_Measurement, PGP.Month, PGP.Year, PGP.Quarter, PGP.Numerator, PGP.Denominator, PGP.Performance_Percent, PGP.Achieved_Flag, PGP.Performance_ID
from PGG
inner join PGC on PGC.Contract_ID = PGG.Contract_ID
inner join PGCL on PGCL.Client_ID = PGC.Contract_ID
inner join PGGM on PGGM.Guarantee_ID = PGG.Guarantee_ID
inner join PGP on PGP.Guarantee_ID = PGG.Guarantee_ID
Where PGP.Month = {this.monthNum.ToString()} and PGP.year = {this.yearNum.ToString()}
and PGC.Active_Flag = 'y'
and PGG.Department_Responsible_ID = {this.deptID.ToString()}
Order by Client_Name, Guarantee_Order asc";
SqlCommand cmd = new SqlCommand(CmdString, con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("PG");
sda.Fill(dt);
DGPerformanceGuarantees.ItemsSource = dt.DefaultView;
}
}
private void Commit_Updates_Click(object sender, RoutedEventArgs e)
{
string ConString = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
//string CmdString = string.Empty;
using (SqlConnection con = new SqlConnection(ConString))
{
//Update Logic
SqlCommand update = new SqlCommand("UPDATE PGP SET PGP.NUMERATOR = @Numerator, DENOMINATOR = @Denominator, PERFORMANCE_PERCENT = @Percent, ACHIEVED_FLAG = '@Achieved' WHERE PERFORMANCE_ID = @Performance_ID", con);
update.Parameters.Add(new SqlParameter("@Numerator", SqlDbType.Int, 5, "NUMERATOR"));
update.Parameters.Add(new SqlParameter("@Denominator", SqlDbType.Int, 5, "DENOMINATOR"));
update.Parameters.Add(new SqlParameter("@Percent", SqlDbType.Decimal, 18, "PERFORMANCE_PERCENT"));
update.Parameters["@Percent"].Precision = 18;
update.Parameters["@Percent"].Scale = 2;
update.Parameters.Add(new SqlParameter("@Achieved", SqlDbType.NVarChar, 1, "ACHIEVED_FLAG"));
update.Parameters.Add(new SqlParameter("@Performance_ID", SqlDbType.Int, 5, "Performance_ID"));
sda.UpdateCommand = update;
sda.Update(dt);
}
FillDataGrid();
}
我添加了一个按钮来触发更新。显然，这不能编译，因为在按钮处理程序的上下文中不存在SQL数据适配器（sda）。
I added a button to trigger the update to occur. Obviously this does not compile because the SQL Data Adapter (sda) is does not exist in the context of the button handler.
我该怎么办？ p>
What can I do to resolve this?
推荐答案
有很多方法可以给这只猫换皮。
Well there's a lot of ways to skin this cat.
如果您想在每次更改单元格时进行更新，我将订阅 DataGrid CellEditEnding 事件基本上会在用户对单元格进行更改和提交时通知您。 （检查 DataGridCellEditEndingEventArgs 参数以找出实际编辑的列和单元格）。这样，您就可以编写有针对性的 UPDATE 语句，并且仅更新已更改的单元格。
If you wanted to do an update every time a cell is changed, I would subscribe to the DataGrid's CellEditEnding event which will basically inform you whenever a change to the cell has been made and committed by the user. (Check the DataGridCellEditEndingEventArgs argument to find out which column and cell were actually edited). With that, you can compose a targeted UPDATE statement and only update the cell that changed.
但是，听起来好像您想在窗口关闭时进行批量更新。在这种情况下，假设您将其指定为<$，则 DataTable 将通过 DataGrid 跟踪对数据所做的更改。 c $ c> ItemsSource 。当您准备提交更改时，请使用 DataTable.GetChanges 来获取新的 DataTable ，其中仅包含已更改的行，因为上次调用 DataDatable.AcceptChanges 的时间。执行更新（这次再次使用 SqlDataAdapter 。请参见更新方法。）这样做的好处是可以在一次操作中处理所有更新和删除操作。一个缺点是-我相信-该行的每一列都将被更新，因此，每次更新都会增加传输到数据库服务器的信息量。无论如何，在更新完成后，请调用 DataTable.AcceptChanges 。
However, it sounds like you want to do a bulk update when the window closes. In that case, DataTable will track changes made to the data via the DataGrid given that you assigned it as the ItemsSource. When you're ready to commit changes, use DataTable.GetChanges to get a new DataTable with just the changed rows since the last time DataDatable.AcceptChanges was called. Perform your update (use SqlDataAdapter again this time. See the Update method.) This has the advantage of handling all updates and deletions in a single operation. One disadvantage is that - I believe - every column of the row is going to be updated, so this increases the amount of information that is transmitted to the database server with each update. In any event, then when your update is complete, call DataTable.AcceptChanges.
这应该可以帮助您正确的轨道上。希望这会有所帮助。
That should get you on the right track. Hope this helps.
这篇关于通过对WPF Datagrid的编辑来更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
