Click here to Skip to main content
15,031,495 members
Please Sign up or sign in to vote.
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

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.
   
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!
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.
   
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 :(

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900