Insert, Update, Delete for Oracle are not working in .NET C#?

Question

It has been a while that i'm dealing with oracle and .net and they don't seem to be a perfect match together. That's this strange thing, i'm not finding any reason why it happens or how to fix it.

I do simple insert, update and delete and they are not working. It fails on the

cmd.ExecuteNonQuery();

Here's the piece of code:

sqlCommand = string.Format(@" INSERT INTO TABLE_1
                              (ID, NAME, DESCRIPTION)
                              VALUES ((SELECT MAX(ID)+1 FROM TABLE_1),'{0}','{1}')", name, description);

using (OracleConnection conn = new OracleConnection(connectionString))
{
 OracleCommand cmd = new OracleCommand(sqlCommand, conn);
 cmd.CommandType = commandType;

 try
 {
  conn.Open();
  result = cmd.ExecuteNonQuery();                    
 }
 catch (Exception ex) { throw;}
 finally
 {
  conn.Close();
 }

a simple insert, right?! when i debug, i get the cmd.Text value (that would be the sqlCommand), and i do execute it in the oracle db, it goes just fine. As i go the point of executing it in .Net it gives up.

Is this a known situation? Is there any solution, any explanation for it?

Thnx in advance

Answer 1

This has nothing to do with your question but:

You should be using a sequence instead of selecting (SELECT MAX(ID)+1 FROM TABLE_1) to genereate the id

Answer 2

Well, i think i just came out with a reasonable explanation:

the database should have been busy doing another update-delete or maybe insert operation, so you were waiting infinitely for it to do the update from the application.

i kind have your problem too. My question is:

How can we avoid this waits, or get a message "i'm bussy-try later" from the db, so that the users are aware of what happens?

Answer 3

I think you table is locked by someone. Or does the table have bitmap indexes? Bitmap indexes shouldn't be used in an environment where multiple user mutate data simultaneously because they lock a lot. Use BTree indexes in an oltp environment.

This has nothing to do with your question but:

When you work with Oracle you have to use parameterized queries instead of string.Format(..{}...). Parameterized queries are much faster because it means that Oracle doesn't have to parse every sql statement.

and do something like

create sequence table_1_seq

insert into table_1 (id, , ) values (table_1_seq.nextval, , ) to fill the id.

Instead of

(SELECT MAX(ID)+1 FROM TABLE_1)

because that doesn't work in a multi user environment.

Edit 1

You can run this select to find out if there are bitmap indexes present:

select index_name,table_name from all_indexes 
where index_type = 'BITMAP';

Answer 4

Depending on how you're doing this; you can use:

    catch (Exception ex)
    {
System.Data.OracleClient.OracleException oEx = (System.Data.OracleClient.OracleException)ex.InnerException;

      if (oEx.Message.IndexOf("ORA-0054") != 0)
      {
         .... do something here...    
      }

.. which will detect whether a lock has occurred. YMMV though as I've used this only on Oracle 9i.