C# SQLite Database During Update

Question

My SQLite query hangs then locks during my ExecuteNonQuery() in WriteToDB() below. It only seems to lock during the UPDATE and has no problem with the INSERT. This is only running in a single thread. When it hangs, I can see the journal being created in the SQLite database directory as if it keeps trying to write. It throws a SQLiteException with ErrorCode=5, ResultCode=Busy.

    public String WriteToDB()
    {
        String retString = "";

        //see if account exists with this email
        String sql = "";
        bool aExists = AccountExists();

        if (!aExists)
        {
            sql = "INSERT INTO accounts (email, password, proxy, type, description) VALUES ('" + Email + "', '" + Password + "', '" + Proxy + "', 'dev', '" + Description + "');";
            retString = "Added account";
        }
        else
        {
            sql = "UPDATE accounts SET password='" + Password + "', proxy='" + Proxy + "', description='" + Description + "' WHERE (email='" + Email + "' AND type='dev');";
            retString = "Updated account";
        }

        using (SQLiteConnection dbconn = new SQLiteConnection("Data Source=" + Form1.DBNAME + ";Version=3;"))
        {
            dbconn.Open();
            using (SQLiteCommand sqlcmd = new SQLiteCommand(sql, dbconn))
            {
                sqlcmd.ExecuteNonQuery(); //this is where it locks. Only on update.
            }
        }
        return retString;
    }

    //Test to see if Email exists as account
    public bool AccountExists()
    {
        int rCount = 0;
        String sql = "SELECT COUNT(email) FROM accounts WHERE email='" + Email + "' AND type='dev';";
        using (SQLiteConnection dbconn = new SQLiteConnection("Data Source=" + Form1.DBNAME + ";Version=3;"))
        {
            dbconn.Open();
            using (SQLiteCommand sqlcmd = new SQLiteCommand(sql, dbconn))
            {
                rCount = Convert.ToInt32(sqlcmd.ExecuteScalar());
            }
        }
        if (rCount > 0)
            return true;
        return false;
    }

Code 5 usually means that the connection already open. I personally like to keep the connection to the SQLite open while the app is running, and only close it when the app is closed. SQLite is serverless which means it is just a file, opening it each time doesn't make too much sense to me — Alex
– Alex, Commented Feb 9, 2017 at 7:25
Probably the update command is trying to write on the accounts table while the select command of your AccountExists function is considered still active. For a start try to rewrite your code without opening the connection for second time inside WriteTODB function — jambonick
– jambonick, Commented Feb 9, 2017 at 8:45
Thanks for the responses. Sergio I get "database is locked" error 5 "busy". Alex: So I should open it once at start and share it with all the classes that need it? That would actually clean things up but I wasn't sure that was good practice. jambonick. I will try passing the connection as an argument to see if it does anything. — blagh
– blagh, Commented Feb 9, 2017 at 9:19

blagh · Accepted Answer · 2017-02-09 09:34:15Z

1

Oh man I feel dumb. I thought I posted all relevant code but all the code I posted works just fine. I had:

SQLiteDataReader dbReader = sqlcmd.ExecuteReader()

instead of

using (SQLiteDataReader dbReader = sqlcmd.ExecuteReader())

In another function. I thought it was an issue with the UPDATE because that was the place where the lock took place. Thanks for the responses and hopefully this reminds reminds everyone to use using() blocks with SQLite the first time!

answered Feb 9, 2017 at 9:34

blagh

711 gold badge1 silver badge10 bronze badges

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

Collectives™ on Stack Overflow

C# SQLite Database During Update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related