Need Help On How To If Record Exists Update Else Insert

Question

Need on the best way to going about programming this in c#. I'm trying to do an if record exists update statement else insert statement, Updated code but still have issues with insert/updating into database. Nothing shows in database after execution from this code:

        String updCmdTxt = "UPDATE..";
        String insertCmdTxt = "INSERT ..";
        using (var scope = new TransactionScope())
        {
            using (var con = new OracleConnection(strConnection))
            {
                bool isDuplicate = false;
                var insertcmd = new OracleCommand(insertCmdTxt, con);
                try
                {


                    con.Open();
                          ......add params

                    insertcmd.ExecuteNonQuery();

                    con.Close();
                    Response.Redirect("test.aspx?Id=" + labelRID.Text);
                }
                catch (OracleException x)
                {
                    isDuplicate = true; //determine if the exception is about duplicates.
                    if (!isDuplicate)
                    {
                        throw;
                    }
                }
                finally
                {
                    insertcmd?.Dispose();
                }
                if (isDuplicate)
                {
                    using (var updcmd = new OracleCommand(updCmdTxt, con))

                        {

                       ....
                      updcmd.ExecuteNonQuery();


                    }
                }
                scope.Complete();
                Response.Redirect("test.aspx?Id=" + labelRID.Text);

            }
        }
    }

Is there a better way to program this and if so how?

2 things: 1. Make sure your `rid` field has a unique constraint on it. 2. In your `else` block (`INSERT` operation) catch and handle `duplicate row exception`. These matter if there's a possibility that between your first check `SELECT COUNT` and your insert/update operation another client/connection/thread might have already made an insert. — Arash Motamedi, Jan 01 '17 at 23:38
Also an optimization: you can reuse the same connection you used for checking count to insert or update. Just be aware that if you're going to retrieve an auto-incremented ID from the last inserted row, you'll have to make sure the connection is not being used on another thread. — Arash Motamedi, Jan 01 '17 at 23:42
Do you have an example of how to do the oracle merge statement within c# — , Jan 02 '17 at 00:26
Still having trouble with this. It's not inserting or updating into the database. Look at my updated code above and let me know what I'm doing wrong. Thanks — , Jan 04 '17 at 15:42
Remove `Response.Redirect` after insert command. I think It does not work because this method interrupts handler execution and `scope.Complete()` is not executed. — Yuriy Tseretyan, Jan 04 '17 at 20:29

score 1 · Answer 1 · answered Jan 02 '17 at 00:38

1

Use a MERGE statement, which combines the functionality of an INSERT and an UPDATE statement. For example:

MERGE INTO CONTRACT_INFO c
  USING (SELECT 12345 FROM DUAL AS RID) d
    ON (d.RID = c.RID)
  WHEN MATCHED THEN
    UPDATE
      SET CONTRACT_INFO_FIELD_1 = something,
          CONTRACT_INFO_FIELD_2 = something_else
  WHEN NOT MATCHED THEN
    INSERT (RID, CONTRACT_INFO_FIELD_1, CONTRACT_INFO_FIELD_2)
    VALUES (d.RID, something, something_else);

Best of luck.

answered Jan 02 '17 at 00:38

Bob Jarvis - Слава Україні

48,992
9
77
110

Is `MERGE` an atomic operation in Oracle? I haven't dealt with Oracle, but in MSSQL it is not atomic. – Yuriy Tseretyan Jan 02 '17 at 01:19
I doubt it - however, as with any other DML statement it is transactioned. Why does it matter to you? [And have you checked this answer?](http://stackoverflow.com/questions/4226830/can-i-do-an-atomic-merge-in-oracle) – Bob Jarvis - Слава Україні Jan 02 '17 at 03:53

score 0 · Accepted Answer · edited May 23 '17 at 12:08

0

It is hard to give advice without knowing all the details such as probability of duplicates, how many requests per seconds etc. There are a few common techniques I use to achieve that:

a stored procedure that encapsulates 'upsert' workflow. See Oracle: how to UPSERT
implement upsert logic on the client (your case) but to guarantee consistency you need to start transaction and close it after you performed operations.

If existing row in the table is more probable, use update before insert to avoid frequent exceptions. Otherwise you can optimize workflow by using insert before update.

Pessimistic solution

String updCmdTxt = "UPDATE ....";
String insertCmdTxt = "INSERT ....";
using (var scope = new TransactionScope())
{ 
   using (var con = new OracleConnection(conStr)){
      int rows;
      using (var updcmd = new OracleCommand(updCmdTxt, con)){
         //Set parameters ...
         rows = updcmd.ExecuteNonQuery();
      }
      //If update command did not affect any rows, insert
      if (rows == 0) {
         using (var insertcmd = new OracleCommand(insertCmdTxt, con)){
            // Set parameters...
            insertcmd.ExecuteNonQuery();
         }
      }
      scope.Complete();
   }
}

Optimistic solution

String updCmdTxt = "UPDATE ....";
String insertCmdTxt = "INSERT ....";
using (var scope = new TransactionScope())
{
    using (var con = new OracleConnection(conStr))
    {
        bool isDuplicate = false;
        var insertcmd = new OracleCommand(insertCmdTxt, con);
        try
        {
            // Set parameters...
            insertcmd.ExecuteNonQuery();
        }
        catch (OracleException x)
        {
            isDuplicate = ...; //determine if the exception is about duplicates.
            if (!isDuplicate)
            {
                throw;
            }
        }
        finally
        {
            insertcmd?.Dispose();
        }
        if (isDuplicate)
        {
            using (var updcmd = new OracleCommand(updCmdTxt, con))
            {
                //Set parameters ...
                updcmd.ExecuteNonQuery();
            }
        }
        scope.Complete();
    }
}

edited May 23 '17 at 12:08

Community

1
1

answered Jan 02 '17 at 00:21

Yuriy Tseretyan

1,676
16
17

As far as duplicates I would like to avoid duplicates. The probability is not likely. That's about as best as I could tell you on that. – Jan 02 '17 at 00:30
I meant duplicates in the meaning of inserting a record that is already in the table. The example I provided will allow you to avoid duplicates as well as race conditions. – Yuriy Tseretyan Jan 02 '17 at 00:32
I think this will work and help. One more question. I get an error on updCmdText: – Jan 02 '17 at 00:58
I think this will work and help. One more question. I get an error on updCmdText: 'The name doesn't exist in current context'. Does this need to be set somewhere? – Jan 02 '17 at 00:59
`updCmdText` as well as `insertCmdTxt` are String variable that contains update and insert command – Yuriy Tseretyan Jan 02 '17 at 01:11
Got another issue. It seems that when I create a new record and when i step thru the code in VS it doesn't go to the insert statement. It goes to the Update statement every time. Do I need a condition somewhere in the code. is (rows == 0) actually equal to 0. – Jan 03 '17 at 18:03
@MarcusDardy my solution assumes to do update before insert. I would call it 'pessimistic approach'. I updated my response by 'optimistic' approach. If your code goes to only `update` statement then your update command affected some records and it means that it updated existing record. Try to review your update statement if you sure that there was no duplicate. – Yuriy Tseretyan Jan 03 '17 at 18:15
What goes on this line isDuplicate = ...;, should that be = true? Get error on if (isDuplicate) line, the name 'IsDuplicate doesn't exist in current context'. Should that be set to something? – Jan 03 '17 at 18:48
If insert command throws an exception that tells that you violate unique key constraint, you should set up `isDuplicate` to true. – Yuriy Tseretyan Jan 03 '17 at 18:53
Still having trouble with this. It's not inserting or updating into the database. Look at my updated code above and let me know what I'm doing wrong. Thanks – Jan 03 '17 at 21:48
Yuri, hopefully you are still around. I updated the code in my OP and it doesn't seem to update/insert into the database. I check my database and the new records or updated record doesn't show in the db table. Is there something else I need to add to the code that I've updated. – Jan 04 '17 at 15:23
@MarcusDardy, lets discuss it in a chat. Unfortunately, you do not have permissions to use SO's chat. So, go to https://codeshare.io/2W7vb2 and ping me by comment. – Yuriy Tseretyan Jan 04 '17 at 17:51
I posted the code on the codeshare link but don't know what to do afterwards. Does the code sit there or do I need to create a log in for you to see it? – Jan 04 '17 at 20:13
Thanks for your help Yuri – Jan 04 '17 at 21:34

Need Help On How To If Record Exists Update Else Insert

2 Answers2