SQLite无法打开数据库错误 [英] SQLite could not open database error

问题描述

大家好！



我尝试将带有440k行的CSV文件插入SQLite数据库。 insert语句运行良好且数据被插入但是错误（见图片）随机出现，有时当db为1mb大时，有时当它的20mb大时。



我以这种方式使用SQLite：

 使用（< span class =code-keyword> var  sqlite_conn =  new  SQLiteConnection（  Data Source = database.db; Version = 3; New = True; Compress = True; Synchronous = Off;））
 {
 sqlite_conn.Open （）; 
 
 尝试 
 {
 使用（CachedCsvReader csv =  new  CachedCsvReader（ new  StreamReader（  csv）， true ，delimiter））
 {
  while （csv.ReadNextRecord（））
 {
 
 使用（SQLiteCommand comm =  new  SQLiteCommand（sqlite_cmd.CommandText，sqlite_conn））
 {
 comm.ExecuteNonQuery（）; 
} 
 
} 
 
 
 
} 
} 
  catch （Exception ex）
 {
 MessageBox.Show（ex.ToString（））; 
} 
 sqlite_conn.Close（）; 
} 

错误图片：http://shrani.si/f/2Q/JO/wLsad16/untitled.png [ ^ ]



任何人都有线索，为什么会发生这种情况？函数在一个线程中运行，但这不是问题。



错误发生在行comm.ExecuteNonQuery（）;

解决方案

这不应该是一个问题，但这是很多小插入！不幸的是，我从未见过和SqBite版本的SqlBulkCopy类，但它可能值得通过参数块作为单片单插入操作 - 这里有一个例子：http://procbits.com/2009/09/08/sqlite-bulk-insert [ ^ ]这可能会有所帮助。



我从未试图在一次操作中添加400K记录，但它应该在SqLite的功能范围内。值得尝试参数化版本只是为了减少文件访问次数 - 这可以解决问题。

使用sqlite_conn.CreateCommand（）而不是使用新的SQLiteCommand。



每次使用新的SQLiteCommand（）时，它可能会尝试创建一个新连接，因为您已经有一个打开的连接使用它来创建命令。



您也不必在外部使用语句结束时显式关闭连接，使用将在退出块后自动关闭连接。 / BLOCKQUOTE>

Hello all!

I try to insert into SQLite databse a CSV file with 440k rows. The insert statement works well and the data is being insered but the error (see picture) appears randomly, sometimes when the db is 1mb big, sometimes when its 20mb big.

I use the SQLite in this way:

using (var sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;Synchronous=Off;"))
{
    sqlite_conn.Open();

    try
    {
      	using (CachedCsvReader csv = new CachedCsvReader(new StreamReader("csv"), true,delimiter))
    	{
           while (csv.ReadNextRecord())
           {

                 using (SQLiteCommand comm = new SQLiteCommand(sqlite_cmd.CommandText, sqlite_conn))
                 {
                     comm.ExecuteNonQuery();
                 }

           }



        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    sqlite_conn.Close();
}

Picture of the error: http://shrani.si/f/2Q/JO/wLsad16/untitled.png[^]

Anybody having a clue, why this is happening? The funciton is running in a thread, but that cannot be the problem.

The error occurs at line comm.ExecuteNonQuery();

解决方案

It's shouldn't give a problem, but that is a lot of small inserts! Unfortunately, I've never seen and SqLite version of the SqlBulkCopy class, but it might be worth doing it as a monolithic single insert operation via parameter blocks - there is an example here: http://procbits.com/2009/09/08/sqlite-bulk-insert[^] which may help.

I've never tried to add 400K records in one operation, but it should be well within the capabilities of SqLite. It's worth trying a parametrized version just to reduce the number of file accesses - that may cure the problem.

Instead of using new SQLiteCommand, use sqlite_conn.CreateCommand().

It may be trying to create a new connection everytime you are using the new SQLiteCommand(), since you already have an open connection use it to create the commands.

You also don't have to explicitly close the connection at the end of your outside using statement, the using will automatically close the connection after it exits the block.

这篇关于SQLite无法打开数据库错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！