c# update if record exists else insert new record

Question

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");
}

Answer 1

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

Answer 2

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 ...

ELSE INSERT ...

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.