CODEWITHSUNDEEP
c#
Sangeetha
Sangeetha

Reputation: 591

Locking error in sqlite using c#

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

Upvotes: 0

Views: 3152

Answers (2)

user2440316
user2440316

Reputation:

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();

Upvotes: 0

Vladimir Perevalov
Vladimir Perevalov

Reputation: 4159

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.

Upvotes: 1

Related Questions