c# 如果记录存在则更新，否则插入新记录 [英] c# update if record exists else insert new record

问题描述

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# 如果记录存在则更新，否则插入新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！