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

Hi, just a quick note. It's bad practice to implement data access logic within your click events, this is causing tight coupling and will make it harder for you to maintain the code as the system grows. Rather create classes that will take the responsibility of interacting with your data access layer. Then you can have class that can check if the entity exists and do your normal CRUD operations. — Andre Lombaard, Oct 25 '13 at 10:03

score 8 · Accepted Answer · edited Oct 25 '13 at 10:33

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

I shortened your code but I noticed you didn't put a WHERE clause in the query. So I changed that as well. — Abbas, Oct 25 '13 at 10:35
Thought that would be nicer than giving an answer myself based on yours and getting credit for it. :) — Abbas, Oct 28 '13 at 08:34
Would this create a race condition if two operation attempted it? — NotARobot, Mar 23 '18 at 19:02
There is possibility of race condition, but I think it is outside of the scope of this question. — rcs, Mar 24 '18 at 06:43

score 3 · Answer 2 · edited Apr 27 '17 at 10:51

3

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.

edited Apr 27 '17 at 10:51

Ali Ezzat Odeh

2,093
1
17
17

answered Oct 25 '13 at 10:05

Markus

20,838
4
31
55

score -1 · Answer 3 · answered Apr 20 '22 at 09:59

If you like receive all data and check if exist any record use HasRows.

using (SqlConnection connection = new SqlConnection("server name"))
{
    SqlCommand cmd = new SqlCommand("select * From Shipment where OrderId =@OrderId", connection);

    connection.Open();
    SqlDataReader sdr = cmd.ExecuteReader();

    if (sdr.HasRows==true)//check have any recorder
    {
        while (sdr.Read())
        {
            Debug.Print("Exist recorder, example.: "+sdr["_trackingID"]);
        }
    }
    else
    {
        Debug.Print("not exist recorder");
    }

            
    connection.Close();
}

c# update if record exists else insert new record

3 Answers3

Linked