C #: SQLite database is always locked

Question

I wrote a simple application (call it app1) that reads the SQLite database and displays the contents in a gridview. I have a separate C # console application (app2) that needs to be written to the same database. The problem is related to the error app2 with the error "database is locked." I see, as soon as I start application1, the userdb-journal file is created. I assume the problem is that application 1 is opening the database but not releasing it? This is the code I use to populate the table that I snap to the grid in app1.

public DataTable GetAllPeople() { var connectionString = "Data Source=" + dbPath + ";Version=3"; using (SQLiteDataAdapter sqlDataAdapter = new SQLiteDataAdapter("SELECT id,FirstName,LastName,Address FROM Users", connectionString)) { using (DataTable dataTable = new DataTable()) { sqlDataAdapter.Fill(dataTable); // code to add some new columns here return dataTable; } } } 

Here is the code that fills the gridview:

  private void Form1_Load(object sender, EventArgs e) { UserDatabase db = new UserDatabase(); db.Initialize(); dataGridView1.DataSource = db.GetAllPeople(); } 

How can I fix that application2 can read and write to the database while application1 is running?

EDIT It appears that this log file is created only by application2. I noticed only a database lock error when starting application1, but maybe app1 is a red herring. App2 has multi-threading. Maybe I should start a new question about app2 and multi-threaded access?

EDIT Thanks for all the comments. I put a lock on all db accesses and wrapped everything in the order of use. Everything seems to be working now.

Answer 1

Here is the code, easily set the parameters in the connection string builder and create an SQLiteConnection with it.

  SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder(); connBuilder.DataSource = filePath; connBuilder.Version = 3; connBuilder.CacheSize = 4000; connBuilder.DefaultTimeout = 100; connBuilder.Password = "mypass"; using(SQLiteConnection conn = new SQLiteConnection(connBuilder.ToString())) { //... } 

Sincerely.

Answer 2

Did you ask SQLITE to wait and try again if db is locked? Here's how to do it in C

  // set SQLite to wait and retry for up to 100ms if database locked sqlite3_busy_timeout( db, 100 ); 

The fact is that SQLITE quickly blocks db when it is accessed. If another thread or process accesses it while blocking, SQLITE will return an error by default. But you can make him wait and try again automatically using the above call. This solves many of these problems.