CODEWITHSUNDEEP
c#sql-serverif-statementsql-updatesql-insert
Jeff
Jeff

Reputation: 29

If value exists then update, else insert value in database

I having a problem that if a value in 4 of my textbox - ID, Type of Room, Rate, Extra Charge; if Type of Room is exist in the database then update, and if not exist then insert to database.

public void existRoomType()
{
    con.Open();
    string typetable = "tblRoomType";
    string existquery = "SELECT*FROM tblRoomType WHERE RoomType = '" + txtRoomType.Text + "'";
    da = new SqlDataAdapter(existquery, con);
    da.Fill(ds, typetable);
    int counter = 0;
    if (counter < ds.Tables[typetable].Rows.Count)
    {
        cmd.Connection = con;
        string edittypequery = "UPDATE tblRoomType SET RoomType = '" + txtRoomType.Text + "', RoomRate = '" + txtRateOfRoom.Text + "', ExtraCharge = '" + txtExtraCharge.Text + "', CancelFee = '" + txtCancelFee.Text + "', MaxOccupant = " + txtMaxOccupants.Text + "" +
            "WHERE TypeID = '" + txtTypeID.Text + "'";
        cmd.CommandText = edittypequery;
        cmd.ExecuteNonQuery();

        MessageBox.Show("Type of Room is added.", "Room Type Management", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    else
    {
        cmd.Connection = con;
        string addtypequery = "INSERT INTO tblRoomType VALUES ('" + txtTypeID.Text + "','" + txtRoomType.Text + "','" + txtRateOfRoom.Text + "','" + txtExtraCharge.Text + "','" + txtCancelFee.Text + "'," + txtMaxOccupants.Text + ")";
        cmd.CommandText = addtypequery;
        cmd.ExecuteNonQuery();

        MessageBox.Show("Type of Room is edited.", "Room Type Management", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    con.Close();
}

If I change the condition if statement from counter < ds.Tables[typetable].Rows.Count to counter > ds.Tables[typetable].Rows.Count, I can add value but I can't edit/update in the database.

Upvotes: 1

Views: 1592

Answers (1)

STW
STW

Reputation: 46434

What you're looking for is an "UPSERT" statement. An upsert combines an insert and update statement, and will perform the relevant action. It's been available since MS SQL 2003, but wasn't fully baked until SQL Server 2008, where the MERGE function was introduced.

Here's a code sample, taken from another answer. This article is also referenced by that answer as providing a good introduction to using the MERGE statement.

MERGE 
   member_topic AS target
USING 
   someOtherTable AS source
ON 
   target.mt_member = source.mt_member 
   AND source.mt_member = 0 
   AND source.mt_topic = 110
WHEN MATCHED THEN 
   UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN 
   INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test')
;

The benefit of this approach is that it only requires a single SQL query, whereas your current approach requires two queries. It also avoids mixing languages, which is generally good for maintainability.

You should also be using Parameterized Queries to pass your variable values to SQL. This will give you protection against SQL injection.

Upvotes: 7

Related Questions