Receiving "database is locked" error with SQLite and C#

Question

Here is the code that I am using:

    public bool save(string name, string type, string city, string org)
    {
        BLL.maxkey maxkey = new BLL.maxkey();
        int maxid = maxkey.getMaxKey(1);

        {
            SQLiteConnection sqConnection = new SQLiteConnection(@"Data Source= C:\Users\BigDaddyDuergar\Documents\Visual Studio 2012\Projects\MET Character Manager\MET Character Manager\METCMDB");
            sqConnection.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqConnection;
            SQLiteTransaction trans;

            trans = sqConnection.BeginTransaction();
            cmd.Transaction = trans;
            int rows = 0;

            try
            {
                cmd.CommandText = "insert into game values(@gameid, @gamename, @gametype, @gamecity, @gameorg)";
                cmd.Parameters.AddWithValue("@gameid", maxid);
                cmd.Parameters.AddWithValue("@gamename", name);
                cmd.Parameters.AddWithValue("@gametype", type);
                cmd.Parameters.AddWithValue("@gamecity", city);
                cmd.Parameters.AddWithValue("@gameorg", org);

                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                error error = new error(ex);
            }
            finally
            {
                sqConnection.Close();
            }

            if (rows == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


    }

And also:

    public void updateMaxKey(int tablenum)
    {
        //using (var conn = new SQLiteConnection(@"Data Source= C:\Users\BigDaddyDuergar\Documents\Visual Studio 2012\Projects\MET Character Manager\MET Character Manager\METCMDB"))
        //using (var cmd = conn.CreateCommand())
        {
            SQLiteConnection sqConnection = new SQLiteConnection(@"Data Source= C:\Users\BigDaddyDuergar\Documents\Visual Studio 2012\Projects\MET Character Manager\MET Character Manager\METCMDB");
            sqConnection.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqConnection;
            SQLiteTransaction trans;

            trans = sqConnection.BeginTransaction();
            cmd.Transaction = trans;

            try
            {

                cmd.CommandText = "update maxnumkey set maxnum = (maxnum + 1) where tableid = @tableid";
                cmd.Parameters.AddWithValue("@tableid", tablenum);
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                error error = new error(ex);
            }
            finally
            {
                sqConnection.Close();
            }
        }
    }

    public int returnMaxKey(int tablenum)
    {
        int maxrows = 0;

        {
            SQLiteConnection sqConnection = new SQLiteConnection(@"Data Source= C:\Users\BigDaddyDuergar\Documents\Visual Studio 2012\Projects\MET Character Manager\MET Character Manager\METCMDB");
            sqConnection.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqConnection;
            SQLiteTransaction trans;

            trans = sqConnection.BeginTransaction();
            cmd.Transaction = trans;


            try
            {
                cmd.CommandText = "select maxnum from maxnumkey where tableid = @tableid";
                cmd.Parameters.AddWithValue("@tableid", tablenum);

                SQLiteDataReader sqReader = cmd.ExecuteReader();
                sqReader.Read();
                maxrows = sqReader.GetInt32(0);
                trans.Commit();


            }
            catch (Exception ex)
            {
                trans.Rollback();
                error error = new error(ex);
            }
            finally
            {
                sqConnection.Close();
            }

            if (maxrows > 0)
            {
                return maxrows;
            }
            else
            {
                return -1;
            }
        }


    }

I can run the updatemaxkey function, followed by the returnmaxkey function with no errors. I verify in the database that the updates were processed correctly, and that the information returned matches. I try to go into the SAVE function, and on trans.Commit() I get a "Database is locked" error.

I can't seem to find anything anywhere that elaborates on this and gives any direction to how to alleviate this problem.

If you need any further clarification just ask. I just started using sqlite about 12 hours ago, so I'm hoping it's something simple I'm just overlooking.

Thanks!

Answer 1

Basic .NET:

• Dispose disposable objects. Commands, Connection are such.

• That is easiest using a "using" statement. Kills half of your code.

• This is absolutely critical for a transaction.

I can only bet that the SqlConncetion - due to the violation of the fundamental .NET principle that requires you to dipose objects implementing IDisposable - does not release a file share in case you reopen it and - that blocks the database (which can not handle multiple parallel accesses very well).

Nothing really SqlCe specific - your C# code violates a core .NET principle.