Locking error in sqlite using c#

0
votes

In my c# application i am inserting data into sqlite database as below

String sqlExpr = "INSERT INTO item (id, typeid, ownerid, created, modifiedby, modified,active,imageuploaded,logouploaded,language_item) VALUES (@Id,@type_TypeId ,@db_currentUser,@dates,@db_id ,@modified,@active_status,@image,@logo,@language)";

using (SQLiteCommand _insertItem = new SQLiteCommand())
{
    _insertItem.CommandText = sqlExpr;
    _insertItem.Parameters.AddWithValue("@Id", Id);
    _insertItem.Parameters.AddWithValue("@type_TypeId", type.TypeId);
    _insertItem.Parameters.AddWithValue("@db_currentUser", db.currentUser.id);
    _insertItem.Parameters.AddWithValue("@dates", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
    _insertItem.Parameters.AddWithValue("@db_id", db.currentUser.id);
    _insertItem.Parameters.AddWithValue("@modified", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
    _insertItem.Parameters.AddWithValue("@active_status", active_status);
    _insertItem.Parameters.AddWithValue("@image", "false");
    _insertItem.Parameters.AddWithValue("@logo", "false");
    _insertItem.Parameters.AddWithValue("@language", language);
    rowsAffected = db.ExecuteNonQuerySQL(_insertItem);
}

And ExecuteNonQuery function as below

public int ExecuteNonQuerySQL(SQLiteCommand cmd)
{
    int ireturn = 0;
    if (conn.DataSource!="local")
        conn = new SQLiteConnection("data source=" + DataFile);
    if (conn.State != ConnectionState.Open)
        Open(DataFile);

    using (SQLiteTransaction dbtrans = conn.BeginTransaction())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        ireturn = cmd.ExecuteNonQuery();
        dbtrans.Commit();
    }
    Close();
    return ireturn;
}

where i am getting error as database locked, please see below error message,

System.Data.SQLite.SQLiteException: The database file is locked database is locked at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() at System.Data.SQLite.SQLiteTransaction.Commit() at ThingzDB.ThingzDatabase.ExecuteNonQuerySQL(SQLiteCommand cmd)

Is something blocking in using statement...The code resides in different classes. please help thanx in advance

2
c#
why are you creating SQLiteCommand in a using statement? - daryal
@daryal, because it is a class implementing the IDisposable interface. It is considered good practice to dispose such resources properly as soon as possible by wrapping them in using statements. - Darin Dimitrov
@DarinDimitrov It implements IDisposable, but only implementing IDisposable is not an explanation by itself, you can use it inside using statements, or not; it only enables to use. - daryal
@daryal, yes, you are not required to do it. It's just that classes that implement this interface usually contain unmanaged resources. And if you don't call it your code will work but you might get memory leaks and unmanaged handlers. So, yeah, you'd better do it. - Darin Dimitrov
@DarinDimitrov I do agree, just wondering whether it is on purpose to release an unmanaged resource or is there some other purpose than ensuring disposal. - daryal

2 Answers

1
votes

As a guess: from the last run of the program, which ended by termination, SQLite have left its lock file (or maybe you have another copy of the same program using this DB still hanging), and you naturally can't open the DB. Try to manually delete the lock file which is besides the db file.

0
votes

Use using statement or dispose and close sqlitecommand and sqliteconnection for example:

SQLiteCommand cmd = new SQLiteCommand();    
//Your query code execution
//Now close connection    
cmd.Dispose();    
_connection.Close();