C＃SQLite数据库已被锁定 [英] C# SQLite database is locked

问题描述

我有一个简单的更新代码，但我不知道由于某些原因不起作用。我收到：数据库被锁定。有谁能够帮我？这是代码：

I have a simple update code but I don't know from some reasons is not working. I receive: database is locked. Can anybody help me? This is the code:

try
                       {
                           SQLiteCommand sel1 = new SQLiteCommand("select totalsim from accounts where username='DPaul'",Conexiune.getConnection());
                           SQLiteDataReader readt = sel1.ExecuteReader();
                           while (readt.Read())
                           {
                               int totalsimm = Convert.ToInt32(readt["totalsim"]);
                               totalsimm++;
                               using (SQLiteCommand update = new SQLiteCommand("UPDATE accounts SET totalsim=@totalsimm where username = 'DPaul'", Conexiune.getConnection()))
                               {
                                   update.Parameters.Add(new SQLiteParameter("@totalsimm", totalsimm));
                                   Int32 rows = update.ExecuteNonQuery();
                               }
                           }
                       }
                       catch (Exception ex)
                       {
                           MessageBox.Show(ex.Message);
                       }

我还有一个选择但是在一个单独的函数中。

I have one more select but is in a separate function.

推荐答案

你不能这样做。

你打开连接来处理外部中的读者，而循环，你试图从该循环内的同一个连接更新同一个表 - 这些操作是不兼容的，所以你得到一个数据库被锁定错误。



最好的解决方案是转储Reader，然后通过DataAdapter加载DataTable，然后使用DataTable数据循环并执行UPDATE语句。





好吧，不是我放弃，我厌倦了这个更新:)）我试图在很多方面改变代码，但没有成功。这是我最后一次尝试。我得到了同样的错误。我试图关闭并重新打开连接，而不是关闭，但是没有成功。我尝试使用DataAdapter，使用DataReader但是它没有用。我做不知道别的尝试。而且我只需要从一行中更改一个值，不明白为什么要使用foreach循环。



为什么要使用foreach循环？因为任何形式的查询都会返回多个行 - 可能为零（如果没有匹配），一个（如果只有一个）或更多（如果有多个值） - 这是好的做法不要假设它会返回一个且只返回一个，即使你的程序逻辑说它会。数据错误发生！：笑：



所以首先使用DataAdapter而不是DataReader：

You can't do that.
You have the connection open to handle the reader in the outer while loop, and you are trying to UPDATE the same table from the same connection within that loop - these operations are incompatible, so you get a "Database is locked" error.

The best solution would be to dump the Reader, and load a DataTable via a DataAdapter instead, and then use the DataTable data to loop and do the UPDATE statements.


"Well, is not about I give up, I'm sick of this update :)) I tried to change the code in so many ways, but without success. This is my last try. I get the same error. I tried to close and reopen connection, than close is again, but with no succees. I tried with DataAdapter, with DataReader but it didn't work. I don't know to else to try. And I have to change only a value from a single row, don't understand why to use foreach loop.."

Why a foreach loop? Because a query of any form returns a number of rows - which may be zero (if there is no match), one (if there is only one), or more (if there is more than one value) - and it's alwasy good practice to not assume that it will return one and only one, even if your program logic says it will. Data errors happen! :laugh:

So start by using a DataAdapter instead of the DataReader:

string collect = "SELECT option1,option2,option3 FROM questions WHERE question=@QU";
SQLiteCommand comp = new SQLiteCommand(collect, Conexiune.getConnection());
comp.CommandType = CommandType.Text;
comp.Parameters.AddWithValue("@QU", SimulatorManager.Intrebare);
SQLiteDataAdapter da = new SQLiteDataAdapter(comp);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows)
    {
    ...
    }

（我对你的方式做了一些改动：SQL动词通常是大写的，以便它更容易从SQL语句中选择它们。并且永远不会连接字符串以形成SQL语句 - 这种方式会导致SQL注入攻击，只需键入文本框就可以破坏或破坏数据库。 总是 使用参数化查询！）



然后在循环中，您只需使用行信息来决定是否需要更新：

(I've made some changes to the way you did it: SQL verbs are normally in UPPERCASE to make it easier to pick them out of an SQL statement. And never, ever concatenate strings to form an SQL statement - that way leads to SQL injection attacks which can damage or destroy your database, just by typing in text boxes. Always use parameterized queries!)

Then in the loop you just use the row info to decide if an update is needed:

int op1 = (int)row["option1"];
int op2 = (int)row["option2"];
int op3 = (int)row["option3"];
...

到目前为止有意义吗？

Make sense so far?

这篇关于C＃SQLite数据库已被锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！