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

When you say "Gives up", what do you mean? Do you get an exception, or does it just hang. If it's an exception, post the full details. Also, it might help if you post a bit more code. Try posting the full routine where you create the SqlCommand object and where you execute it. — Simon P Stevens, Oct 09 '09 at 08:41
no, it just keeps executing for a loooong time, or so does it seems to be. — , Oct 09 '09 at 08:45
I can't see anything obviously wrong with the code (Aside from the SQL injection risk, and that you are unnecessarily catching and throwing an exception). Have you double checked your connection string is correct? — Simon P Stevens, Oct 09 '09 at 09:07
yes, it's ok, that piece of code works fine, the connection is ok. I can retrieve data, but nothing else. the connection opens, it hangs on the cmd.ExecuteNonQuery() — , Oct 09 '09 at 09:12
it's not a permissions issue, i mean i do have rights to read and write data in db — , Oct 09 '09 at 09:20
i'm gettin crazy, it just worked. No changes at all... that's strange, it works for a minute, the other one, it does not. i don't know why and i have to find the explanation. Any ideas? Does it have to do with the .net framework or with the db? — , Oct 09 '09 at 09:27
Like other mentioned, its probably just a lock somewhere... Another thing : use bind variable ! for clarity and security purpose. — guigui42, Oct 09 '09 at 11:15
Do ***not*** use `select max(id) ` to "generate" unique ids. Just don't. It doesn't work correctly and it doesn't scale. Use sequences instead. — , Oct 25 '12 at 06:58
I don't see any commit in your code. Does `.Net` default to autocommit? — , Oct 25 '12 at 06:59

score 1 · Answer 1 · answered Oct 09 '09 at 09:01

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

answered Oct 09 '09 at 09:01

Rob van Laarhoven

8,737
2
31
49

yeah, i know, i've read about that, but i'm rather new to oracle, so to keep doing things faster, i do improvise. Any clue for the question? – Oct 09 '09 at 09:03
As long as you realise this is incorrect in a multi user environment because diffent connections can get the same result from max(id)+1. Where does it hang when you debug? – Rob van Laarhoven Oct 09 '09 at 09:12
thnx, i'll fix that, so i am reading more about oracle. As ai've said it before it hangs at the cmd.ExecuteNonQuery(); so, it's not a known situation... – Oct 09 '09 at 09:19

score 1 · Answer 2 · answered Oct 09 '09 at 10:55

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

score 0 · Answer 3 · edited Sep 21 '12 at 08:51

0

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;

edited Sep 21 '12 at 08:51

Ral Zarek

1,058
4
18
25

answered Sep 01 '12 at 09:42

kashmir

1

score 0 · Answer 4 · answered Oct 09 '09 at 09:41

0

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?

answered Oct 09 '09 at 09:41

Ada

11
2

and take a look to this post http://stackoverflow.com/questions/1172378/oracle-update-hangs – Ada Oct 09 '09 at 09:44

score 0 · Answer 5 · answered Oct 09 '09 at 11:08

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.

score 0 · Answer 6 · answered Dec 20 '10 at 18:24

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.

score -1 · Answer 7 · edited Oct 25 '12 at 07:13

-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.

edited Oct 25 '12 at 07:13

Spontifixus

6,570
9
45
63

answered Oct 25 '12 at 06:51

Pushpankar

1

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

7 Answers7