CODEWITHSUNDEEP
c#oracle-database
user187013
user187013

Reputation:

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

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

Upvotes: 0

Views: 7084

Answers (7)

Pushpankar
Pushpankar

Reputation: 1

Commit any query used in Oracle client like Toad or SQL-Developer

parallely using oracle client(like Toad or sSQL-Developer) and .net is disallowed if you want to use both parallely then use commit in oracle client before using with .net.

Then try using it with .net - that will work.

Upvotes: -1

kashmir
kashmir

Reputation: 1

Insert: 

insert into student values('rahul',474,'mca','phase2');

Delete:

delete from student where roll_no=472;

Update:

update student set address='phase7' where roll_no=474;

Upvotes: 0

pawel-kuznik
pawel-kuznik

Reputation: 456

I had the same problem. I didn't have a clue how to solve it. When I run program wihout sqldeveloper running it went just fine. My answer to this question: close any other programas that uses connection to oracle from your computer. It went just fine for me.

Upvotes: 1

pierre
pierre

Reputation: 1245

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.

Upvotes: 0

tuinstoel
tuinstoel

Reputation: 7306

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';

Upvotes: 1

Ada
Ada

Reputation: 11

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?

Upvotes: 0

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

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

Upvotes: 1

Related Questions