c# sqlite database locked when inserting

Question

I am using SQLite in a C#

I have an insert query in a win form that works but on another win form that doesn't. when I have read about the issue it says it might be because there is an open connection somewhere but I have checked it all and even used SQLiteConnection.ClearAllPools(); after every close. now the 1st winform have only 1 open / close, the other one has 3.

I have tried the query using SQLite Browser to make sure it wasn't an issue with the query and it worked successfully. now when I debugged the issue occurs when this line executes cmd.ExecuteNonQuery(); that, of course, executes the query (insert into ....). so I tried changing the type of the table primary key (from varchar to integer). and I still have the issue. below is a sum of how it is.

myconnection = new SQLiteConnection(connectionString);
myconnection.Open();

//select stuff here

//verifications here
//insert inside verification

//finally { myconnection.close(); }

If you have pending changes in a DB Browser the db is locked until you update/apply/write those changes. The code posted isnt very useful other than to say you should be wrapping the connection and command objects in `using` blocks so they are disposed. Dont do that to the connection if you have a DataAdapter you have configured to do all your INSERTs, etc for you — Ňɏssa Pøngjǣrdenlarp, Jul 21 '16 at 00:59
i can provide the sql & the code but its pointless due to it being selects and inserts. i ll try the using statement. — I_Tried_Thats_Why_Im_Here, Jul 21 '16 at 01:05

score 0 · Accepted Answer · answered Jul 21 '16 at 00:59

0

Did you try wrapping your connection in a using statement?

using(var myconnection = new SQLiteConnection(connectionString))
{

    myconnection.Open();

    //Your code here

}

This will call Dispose method of the connection regardless of execution path, which could possibly be doing more than just closing the connection.

answered Jul 21 '16 at 00:59

konkked

3,161
14
19

this actually worked, when debugging i found the `myConnection` had "stuff" even thought i used `dispose` (& `close` before). – I_Tried_Thats_Why_Im_Here Jul 21 '16 at 11:54

score 0 · Answer 2 · answered Aug 06 '21 at 07:40

Be aware to click on Write changes on SQLite browser,
if it is running and there are any unsaved changes!

In my case it was very stupid of me, I was making changes in the SQLite browser and did not click on write changes, which locked the DB to be modified by the services. After I clicked the Write changes button, all the post requests worked as expected.
According to @Rohan Shenoy in this topic: SQLite Database Locked exception

score -1 · Answer 3 · answered Sep 17 '17 at 12:28

may be you should also try this

using (var con = new SQLiteConnection { ConnectionString = "connectionstring" })
{
     using(var cmd = new SQLiteCommand { Connection = con })
     {
         // check state connection if open then close, else close proceed
         if(con.State == ConnectionState.Open)
           con.Close();

         //then
         try
         {
            // try connection to Open
            con.Open();
         }
         catch
         {
            //catch if found error, message : 'Invalid Connection string'
         }

         ........ // insert query here

     } // Close Command

}  // Close Connection

c# sqlite database locked when inserting

3 Answers3