Click here to Skip to main content
15,891,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello, everyone.
I have some problems in c# SQLite data update and creating.
I'm using SQLite as a database in my c# windows form application.
By the way, sometimes I've got a "database is locked" error in SQLite, when I try to insert or update data.
While searching the way on google, I got know that it will be able to happen because multi-threads is working or the database is in processing.
But I can't find what thread or process hurt this database updating correctly.
I want to prevent the error and finally solve this problem asa.
Thanks for your time.

What I have tried:

static SQLiteConnection CreateConnection(string dbName)
{

    SQLiteConnection sqlite_conn;
    // Create a new database connection:
    sqlite_conn = new SQLiteConnection("Data Source=" + dbName + ".db; PRAGMA journal_mode = WAL; Version = 3; New = True; Compress = True; Connection Timeout=0 ");
    // Open the connection:
    try
    {
        sqlite_conn.Open();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
    return sqlite_conn;
}



sqlite_conn = CreateConnection(constants.dbName);
InsertLog(sqlite_conn, 2, "xxx", "yyyyy");


public void InsertLog(SQLiteConnection conn, int logType, string logTitle, string logContent, string logContent_2 = "")
{
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }
    try
    {
        DateTime logDate = DateTime.Now;
        SQLiteCommand sqlite_cmd;
        string Createsql = "INSERT INTO " + constants.tbNames[14] + " (logType, logTitle, logContent, logContent_2, logDate) VALUES (@logType, @logTitle, @logContent, @logContent_2, @logDate)";
        sqlite_cmd = conn.CreateCommand();
        //sqlite_cmd.CommandTimeout = 0;
        sqlite_cmd.CommandText = Createsql;
        sqlite_cmd.Parameters.AddWithValue("@logType", logType);
        sqlite_cmd.Parameters.AddWithValue("@logTitle", logTitle);
        sqlite_cmd.Parameters.AddWithValue("@logContent", logContent);
        sqlite_cmd.Parameters.AddWithValue("@logContent_2", logContent_2);
        sqlite_cmd.Parameters.AddWithValue("@logDate", logDate);

        sqlite_cmd.ExecuteNonQuery();

    }
    catch (Exception e)
    {
        conn.Close();
        //Console.WriteLine("db_error==" + e);
        return;
    }
    conn.Close();
    return;
}
Posted
Updated 28-Nov-20 22:20pm
v2

1 solution

 
Share this answer
 
Comments
Member 14844136 30-Nov-20 16:50pm    
Hi, RickZeeland.
Thanks for your attention and answer me.
In fact, I've regarded and used many articles including the one you pointed already.
But I can't solve this problem completely.
You can see that PRAGMA journal_mode = WAL in my code but it is also not working for me. From several searches online, I know there happen this problem only when inserting or updating the database table but not selecting.
It said that Sqlite don't support multi operation at the same time.
Can you explain in detail again after review my code?
Thank you for your reply again.
Best Regards.

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