c# update if record exists else insert new record(c# 如果记录存在则更新,否则插入新记录)
问题描述
I have code that inserts data into a table when a user enters certain values into three boxes on the page.
The boxes are order number, total weight and tracking reference.
I now need to add further functionality to this code and check first to see if the order number exists, if it does i need to update the columns, if it doesn't I need to insert a new row and add data to that.
I was thinking simply, something like IF results = 0, Insert NEW, ELSE update
How can I modify my code to do this?
protected void Page_Load(object sender, EventArgs e)
{
errorLabel.Visible = false;
successLabel.Visible = false;
errorPanel.Visible = false;
}
protected void submitBtn_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
int _orderID = Convert.ToInt32(orderID.Text);
string _trackingID = trackingNumber.Text;
DateTime _date = DateTime.UtcNow;
int _weightID = Convert.ToInt32(weightID.Text);
SqlConnection myConnection = new SqlConnection("Data Source=localhost\Sqlexpress;Initial Catalog=databasename;User ID=username;Password=password");
SqlCommand myCommand = new SqlCommand("INSERT into Shipment (TrackingNumber, OrderId, ShippedDateUtc, CreatedOnUtc, TotalWeight) VALUES (@tracking, @order, @date, @date, @weight)", myConnection);
try
{
myConnection.Open();
myCommand.Parameters.AddWithValue("@order", _orderID);
myCommand.Parameters.AddWithValue("@tracking", _trackingID);
myCommand.Parameters.AddWithValue("@date", _date);
myCommand.Parameters.AddWithValue("@weight", _weightID);
int rowsUpdated = myCommand.ExecuteNonQuery();
myConnection.Close();
if (rowsUpdated > 0)
{
alertdiv.Attributes.Add("class", "alert alert-success form-signin");
successLabel.Text = "Thank you, tracking details have been updated";
successLabel.Visible = true;
errorPanel.Visible = true;
}
else
{
alertdiv.Attributes.Add("class", "alert alert-error form-signin");
errorLabel.Text = "Oh dear, the order number is not recognised, please check and try again";
errorLabel.Visible = true;
errorPanel.Visible = true;
}
orderID.Text = "";
trackingNumber.Text = "";
weightID.Text = "";
}
catch (Exception f)
{
errorLabel.Text = "This order number does not exist, please check";
errorLabel.Visible = true;
errorPanel.Visible = true;
return;
}
}
}
protected void Signout_Click(object sender, EventArgs e)
{
FormsAuthentication.SignOut();
Response.Redirect("Login.aspx");
}
You can add some SELECT
query before your INSERT
statement. So if the SELECT
query returns more than one row, it means that you already have that record in the DB, and need to update. So, in general it will be like
SqlCommand cmdCount = new SqlCommand("SELECT count(*) from Shipment WHERE OrderId = @order", myConnection);
cmdCount.Parameters.AddWithValue("@order", _orderID);
int count = (int)cmdCount.ExecuteScalar();
if (count > 0)
{
// UPDATE STATEMENT
SqlCommand updCommand = new SqlCommand("UPDATE Shipment SET TrackingNumber = @tracking, ShippedDateUtc = @date, TotalWeight = @weight", myConnection);
updCommand.Parameters.AddWithValue("@order", _orderID);
updCommand.Parameters.AddWithValue("@tracking", _trackingID);
updCommand.Parameters.AddWithValue("@date", _date);
updCommand.Parameters.AddWithValue("@weight", _weightID);
int rowsUpdated = myCommand.ExecuteNonQuery();
}
else
{
// INSERT STATEMENT
SqlCommand insCommand = new SqlCommand("INSERT into Shipment (TrackingNumber, OrderId, ShippedDateUtc, CreatedOnUtc, TotalWeight) VALUES (@tracking, @order, @date, @date, @weight)", myConnection);
insCommand.Parameters.AddWithValue("@order", _orderID);
insCommand.Parameters.AddWithValue("@tracking", _trackingID);
insCommand.Parameters.AddWithValue("@date", _date);
insCommand.Parameters.AddWithValue("@weight", _weightID);
int rowsUpdated = myCommand.ExecuteNonQuery();
}
Edit: Or much shorter:
SqlCommand command;
if (count > 0)
{
command = new SqlCommand("UPDATE Shipment SET TrackingNumber = @tracking, ShippedDateUtc = @date, TotalWeight = @weight WHERE OrderId = @order", myConnection);
}
else
{
command = new SqlCommand("INSERT into Shipment (TrackingNumber, OrderId, ShippedDateUtc, CreatedOnUtc, TotalWeight) VALUES (@tracking, @order, @date, @date, @weight)", myConnection);
}
command.Parameters.AddWithValue("@order", _orderID);
command.Parameters.AddWithValue("@tracking", _trackingID);
command.Parameters.AddWithValue("@date", _date);
command.Parameters.AddWithValue("@weight", _weightID);
int rowsUpdated = command.ExecuteNonQuery();
这篇关于c# 如果记录存在则更新,否则插入新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:c# 如果记录存在则更新,否则插入新记录


- MoreLinq maxBy vs LINQ max + where 2022-01-01
- 带有服务/守护程序应用程序的 Microsoft Graph CSharp SDK 和 OneDrive for Business - 配额方面返回 null 2022-01-01
- 输入按键事件处理程序 2022-01-01
- C#MongoDB使用Builders查找派生对象 2022-09-04
- 如何用自己压缩一个 IEnumerable 2022-01-01
- 在哪里可以找到使用中的C#/XML文档注释的好例子? 2022-01-01
- Web Api 中的 Swagger .netcore 3.1,使用 swagger UI 设置日期时间格式 2022-01-01
- C# 中多线程网络服务器的模式 2022-01-01
- WebMatrix WebSecurity PasswordSalt 2022-01-01
- 良好实践:如何重用 .csproj 和 .sln 文件来为 CI 创建 2022-01-01