c#如果记录存在则更新否则插入新记录

c# update if record exists else insert new record

提问by drac

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

我只是在想，比如 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");
}

采纳答案by rcs

You can add some SELECTquery before your INSERTstatement. So if the SELECTquery 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

您可以在SELECT语句之前添加一些查询INSERT。因此，如果SELECT查询返回多于一行，则意味着您已经在数据库中拥有该记录，需要更新。所以，一般来说它会像

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();

回答by Markus

The most efficient way would be to put the functionality into a Stored Procedure, for instance (pseudo-code): IF EXISTS(SELECT * FROM Orders WHERE OrderNo = @orderNo)UPDATE ...

最有效的方法是将功能放入存储过程中，例如（伪代码）： IF EXISTS(SELECT * FROM Orders WHERE OrderNo = @orderNo)UPDATE ...

ELSEINSERT ...

ELSEINSERT ...

If you cannot create a new stored procedure, you can also create a command that contains this Statement though readability is typically worse.
Both approaches require only one DB-request.

如果您无法创建新的存储过程，您也可以创建一个包含此语句的命令，尽管可读性通常较差。
这两种方法都只需要一个 DB 请求。