2

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");
}
Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
drac
  • 347
  • 2
  • 7
  • 15
  • 1
    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

3 Answers3

8

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();
Abbas
  • 14,186
  • 6
  • 41
  • 72
rcs
  • 6,713
  • 12
  • 53
  • 75
  • 1
    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
  • 1
    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
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.

Ali Ezzat Odeh
  • 2,093
  • 1
  • 17
  • 17
Markus
  • 20,838
  • 4
  • 31
  • 55
-1

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();
}
James
  • 1
  • 5