Locking error in sqlite using c#

Question

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

@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
– Darin Dimitrov, Commented Mar 21, 2012 at 15:21
@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, Commented Mar 21, 2012 at 15:25
@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
– Darin Dimitrov, Commented Mar 21, 2012 at 15:27
@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
– daryal, Commented Mar 21, 2012 at 15:31

Vladimir Perevalov · Accepted Answer · 2012-03-21 15:19:44Z

1

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.

answered Mar 21, 2012 at 15:19

Vladimir Perevalov

4,15721 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sangeetha Over a year ago

SQLiteDataReader rdr = db.ExecuteSQL(@"SELECT max(id)+1 FROM item"); int next_id = 0; try { rdr.Read(); next_id = Convert.ToInt32(rdr[0]); rdr.Close(); rdr.Dispose(); } catch (Exception) { } finally { rdr.Close(); rdr.Dispose(); } return next_id; This the query runs before insert

slavoo · Accepted Answer · 2013-09-16 08:22:46Z

0

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

edited Sep 16, 2013 at 8:22

slavoo

6,11464 gold badges40 silver badges41 bronze badges

answered Sep 16, 2013 at 8:04

user2440316

Collectives™ on Stack Overflow

Locking error in sqlite using c#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related