Sqlite database lock error using c# application

Question

I n my c# application , i am using sqlite as backend. when i perform simple insert query it takes me too long and end up with database is locked message..I deployed my application in stage server and performing the action remotely..Below is my code please help.

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())
                    //{
                    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);
                        db.Close();


 public int ExecuteNonQuerySQL(SQLiteCommand cmd)
        {
            int ireturn = 0;

            if (conn.State != ConnectionState.Open)//the connection is individual for each session so no need of lock .added the code on 2011-11-08 by Sangeetha
                Open(DataFile);
            //SQLiteConnection C = new SQLiteConnection("Data Source=database.db;Version=3;New=False;Compress=True;");

            using (SQLiteTransaction dbtrans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                using (SQLiteCommand cmd1 = (SQLiteCommand)cmd.Clone())
                {

                    cmd.Dispose();
                    cmd1.Connection = conn;
                    cmd1.Transaction = dbtrans;
                    ireturn = cmd1.ExecuteNonQuery();
                    dbtrans.Commit();
                    cmd1.Dispose();
                }
            }


            return ireturn;
        }

Thank you

why pass the SQLcommand to the method and clone it? Why not just create it from scratch each time? keep the connection open, but not the command — Phil, Sep 26 '12 at 05:53
clone is used because SQLiteCommand _insertItem = new SQLiteCommand(); is created in different class. — user642378, Sep 26 '12 at 06:18
http://stackoverflow.com/questions/17592671/sqlite-database-locked-exception — GGSoft, Feb 07 '15 at 13:42

GGSoft · Answer 1 · 2015-02-07T14:09:38.660

0

Maybe before you have opened a connection and it's still opened. Make sure, all connections and commands are disposed (use USING statement) and close every reader before exiting function.

Or migrate to 'oledb' provider (MS ACCESS)

edited Feb 07 '15 at 14:09

answered Feb 07 '15 at 13:54

GGSoft

439
6
15

Sqlite database lock error using c# application

1 Answers1