如何在SQL数据库中使用linq在一个表中插入数据并更新另一个表
本文关键字:另一个 一个 插入 数据 更新 数据库 SQL linq | 更新日期: 2023-09-27 18:17:16
我有一个保存按钮,将在我的'IncidentActions'表中添加新数据,同时更新我的'Incident'表中的3列。
我该怎么做呢
这是一个c#代码,我正在使用尝试和完成这一点。
protected void cmdSave_Click(object sender, EventArgs e)
{
Context db = new Context();
// This will check the Action from the actions table and
var actionID = (from i in db.Actions
where i.Actions == tbActionType.Text
select i.ActionsID).First();
long ID = Convert.ToInt64(Request.QueryString["IncidentID"]);
// TODO: update the incident table accordingly
IncidentAction act = new IncidentAction
{
IncidentID = ID,
ActionDate = (DateTime)dpActionDate.SelectedDate,
ActionsID = Convert.ToInt32(actionID),
StatusID = statID,
IsPublic = false,
Title = tbTitle.Text.Trim(),
PeriodValue = Convert.ToInt64(txtDuration.Text),
Description = txtDescription.Text.Trim(),
EstimatedCost = txtEstimatedCost.Text == string.Empty ? (decimal?)null : Convert.ToDecimal(txtEstimatedCost.Text),
ActualCost = txtActualCost.Text == string.Empty ? (decimal?)null : Convert.ToDecimal(txtActualCost.Text),
LastUpdated = DateTime.Now,
UpdatedBy = Convert.ToString(loggedInUserName),
CreatedByUserID = Convert.ToInt32(loggedInUserID),
Active = true
};
db.IncidentActions.Add(act);
db.SaveChanges();
Incident inc = new Incident
{
IncidentID = ID,
StatusID = statID_new,
IncidentPendingDate = DateTime.Now
};
db.SaveChanges();
}
}
一个问题似乎是您没有从数据库引用正确的Incident
记录。您可以先查询数据库以获取您想要更新的记录。
如果你想在"同一时间"(即在同一事务中)执行这两个操作,请删除第一个db.SaveChanges()
。然后第二个将同时执行插入和更新。
而且,DateTime.Now
每次调用时都可以返回不同的值。如果希望每个记录上的时间戳都匹配,只需调用DateTime.Now
一次,将结果保存到一个变量中,并在两个地方使用它。
像这样:
Context db = new Context();
var now = DateTime.Now;
IncidentAction act = new IncidentAction
{
IncidentID = ID,
...
LastUpdated = DateTime.Now,
};
db.IncidentActions.Add(act);
Incident inc = db.Incidents.First(i => i.IncidentID == ID);
inc.StatusID = statID_newl
inc.IncidentPendingDate = now;
db.SaveChanges();
我已经整理好了,谢谢。
i changed this code
db.IncidentActions.Add(act);
db.SaveChanges();
Incident inc = new Incident
{
IncidentID = ID,
StatusID = statID_new,
IncidentPendingDate = DateTime.Now
};
db.SaveChanges();
// add to the IncidentAction table.
db.IncidentActions.Add(act);
db.SaveChanges();
// Update the Incident Table.
Incident inc = (from i in db.Incidents
where i.IncidentID == ID
select i).First();
inc.StatusID = statID_new,
inc.IncidentPendingDate = DateTime.Now;
db.SaveChanges();