SQLite could not open database error

Question

0.00/5 (No votes)

See more:

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:

C#

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();

Posted 21-Jul-13 4:01am

tokano

Add a Solution

2 solutions

Solution 1

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.

Posted 21-Jul-13 4:08am

Ron Beyer

Comments

tokano 21-Jul-13 10:32am

I have tried it this way but the error is still there. The first time i've programmed the function it went to 340k entries. So 100k missing, no error till there! But now the error appears sooner..

The problem is still there, huh :(

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-07-21T04:18:00

Solution 2

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.

Posted 21-Jul-13 4:18am

OriginalGriff

Comments

tokano 21-Jul-13 10:36am

I see where this is going, 1 million records? Nice, just the thing i need. I will analyze the code and try to implement it in my project. Thanx for the link. I will report back for results!

Greets

OriginalGriff 21-Jul-13 10:39am

You're welcome!

tokano 21-Jul-13 13:00pm

Right on Griff! :) Works like a charm. Its not faster on my case, cause i have to loop trought records, but, it fill the whole CSV to the database without errors! Thank you very much!

OriginalGriff 21-Jul-13 14:28pm

Welcome!