Oracle 的插入、更新、删除在 .NET C# 中不起作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1542594/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert, Update, Delete for Oracle are not working in .NET C#?
提问by Ada
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.
我处理 oracle 和 .net 已经有一段时间了，它们似乎并不完美匹配。这是一件奇怪的事情，我没有找到它发生的任何原因或如何解决它。
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.
一个简单的插入，对吧？！当我调试时，我得到了 cmd.Text 值（那将是 sqlCommand），并且我确实在 oracle db 中执行了它，它运行得很好。当我在 .Net 中执行它时，它放弃了。
Is this a known situation? Is there any solution, any explanation for it?
这是已知情况吗？有什么解决办法，有什么解释吗？
Thnx in advance
提前谢谢
回答by tuinstoel
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.
我想你的桌子被人锁了。或者表有位图索引吗？位图索引不应该在多个用户同时改变数据的环境中使用，因为它们锁定了很多。在 oltp 环境中使用 BTree 索引。
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.
当您使用 Oracle 时，您必须使用参数化查询而不是 string.Format(..{}...)。参数化查询要快得多，因为这意味着 Oracle 不必解析每个 sql 语句。
and do something like
并做类似的事情
create sequence table_1_seq
insert into table_1 (id, , ) values (table_1_seq.nextval, , )to fill the id.
insert into table_1 (id, , ) values (table_1_seq.nextval, , )填写身。
Instead of
代替
(SELECT MAX(ID)+1 FROM TABLE_1)
because that doesn't work in a multi user environment.
因为这在多用户环境中不起作用。
Edit 1
编辑 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';
回答by Rob van Laarhoven
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
您应该使用序列而不是选择 (SELECT MAX(ID)+1 FROM TABLE_1) 来生成 id
回答by Ada
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?
我们如何避免这种等待，或者从数据库中收到一条消息“我很忙，稍后再试”，以便用户知道会发生什么？
回答by pierre
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.
.. 它将检测是否发生了锁定。YMMV 虽然我只在 Oracle 9i 上使用过它。
回答by pawel-kuznik
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.
我有同样的问题。我不知道如何解决它。当我在没有 sqldeveloper 运行的情况下运行程序时，它运行得很好。我对这个问题的回答：关闭任何其他使用从您的计算机连接到 oracle 的程序。对我来说一切顺利。
回答by kashmir
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;
回答by Pushpankar
Commit any query used in Oracle client like Toad or SQL-Developer
提交在 Oracle 客户端（如 Toad 或 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.
如果您想并行使用 oracle 客户端（如 Toad 或 sSQL-Developer）和 .net，则不允许并行使用，然后在使用 .net 之前在 oracle 客户端中使用 commit。
Then try using it with .net - that will work.
然后尝试将它与 .net 一起使用 - 这将起作用。
