SQLite: Database Is locked C#

Question

EDIT! Here's the video of the error https://www.youtube.com/watch?v=_mJ3o3ykQrw

Made a method called AddAttendance in my database handler class. I can access it from other class since it's static. Here's the code:

 public static void AddAttendance(string ID, string DateTime, string Action, string Session)
        {
            SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source = " + database + "; Version = 3; New = True; Compress = True;");
            try
            {
                sqlite_conn.Open();
                SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "INSERT INTO AttendanceLog (UserID, DateTime, Action, Session) VALUES (" +
                    "'" + ID + "', '" + DateTime + "', '" + Action + "', '" + Session + "');";
                sqlite_cmd.ExecuteNonQuery();

                sqlite_conn.Close();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            sqlite_conn.Close();
        }

This code is only being accessed by this Dialog Form I made bound to a button:

 private void buttonAccept_Click(object sender, EventArgs e)
        {

            if (textBoxID.Text.Length == 0 || textBoxTimeDate.Text.Length == 0)
            {
                MessageBox.Show("ID or Time and Date cannot be empty!", "Missing field", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (buttonAccept.Text.Equals("Accept"))
            {
                buttonAccept.Text = "Confirm!";
                return;
            }
            DatabaseHandles.AddAttendance(textBoxID.Text, textBoxTimeDate.Text, comboBoxAction.Text, comboBoxSession.Text);
            MessageBox.Show("Record added!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            DialogResult = DialogResult.OK;
        }

If im accessing it from my Form Kiosk mode, it's working just fine, but if I'm accessing it from anywhere else, it reports Database is locked.

What's weird is, when i accessed it first to my Kiosk mode beforehand then to other place, it works just fine.

I dont know what am i missing. Here the same code i use on calling the form:

 AttendanceInsert ai = new AttendanceInsert();
                ai.ShowDialog(); 

Answer 1

The culprit was the reader, although I closed all the connection with the using parameter, i forgot to close all the readers i used, I should be utilizing using here too, but you get the idea. Database is locked with the readers unclosed

 public static void LoadScannedUser(string ID)
        {
            string sql = "SELECT * FROM Users WHERE ID = '" + ID + "'";

            using (var conn = new SQLiteConnection(ConnectionString))
            {
                using (var cmd = conn.CreateCommand())
                {


                    try
                    {
                        cmd.CommandText = sql;
                        conn.Open();
                        SQLiteDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            scannedUser.ID = reader.GetString(1);
                            scannedUser.Password = reader.GetString(2);
                            scannedUser.PPicture = Cryptorizer.Base64ToImage(reader.GetString(3));
                            scannedUser.FName = reader.GetString(4);
                            scannedUser.MName = reader.GetString(5);
                            scannedUser.LName = reader.GetString(6);
                            scannedUser.Agency = reader.GetString(7);
                            scannedUser.Position = reader.GetString(8);
                        }
                        reader.Close();
                    }

                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }

                }
            }

        }

Answer 2

I don't know if it will solve your problem, but there is no reason to not do it right:

public static void AddAttendance(string id, string datetime, string action, string session)
{
    try
    {
        using (var sqlite_conn = new SQLiteConnection("Data Source = " + database + "; Version = 3; New = True; Compress = True;")
        {
            sqlite_conn.Open();
            using (var sqlite_cmd = sqlite_conn.CreateCommand())
            { 
                sqlite_cmd.CommandText = "INSERT INTO AttendanceLog (UserID, DateTime, Action, Session) VALUES (@Id, @DateTime, @Action, @Session);";
                sqlite_cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar)).Value = id;
                sqlite_cmd.Parameters.Add(new SqlParameter("DateTime", SqlDbType.NVarChar)).Value = datetime;
                sqlite_cmd.Parameters.Add(new SqlParameter("Action", SqlDbType.NVarChar)).Value = action;
                sqlite_cmd.Parameters.Add(new SqlParameter("Session", SqlDbType.NVarChar)).Value = session;
                sqlite_cmd.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

This way your connection and SqlCommand are closed and disposed of and you are using a parameterized query, that will prevent SQL Injection. If your database column is not NVarChar, you need to change that. You can also add a length to your parameter definitions.

Answer 3

This locking problem arises from trying to re-use the database connection. ADO.Net has a feature called connection pooling, such that re-using the same conenction object throughout your app is a really bad idea that will actually make things slower, in addition to leading to locking issues like this.

Instead, you really should create a brand new connection object for most queries, open the connection immediately before running the query, and dispose the object as soon as the query is finished. The easiest way to accomplish this is via a using block:

private static string ConnectionString {get;} = "Data Source = " + database + "; Version = 3; New = True; Compress = True;"

public static void AddAttendance(string ID, string dateTime, string Action, string Session)
{
    string sql = "INSERT INTO AttendanceLog (UserID, DateTime, Action, Session) VALUES (@id, @time, @action, @session);";

    using (var conn = new SQLiteConnection(ConnectionString))
    using (var cmd = new SQLiteCommand(sql, conn))
    {
        cmd.Parameters.AddWithValue("@id", ID);
        // Note the DateTime format. It's necessary to store date values in this way in order to preserve cardinality and allow indexes and date math to function properly.
        // Better DB platforms would have a DateTime type to use instead
        cmd.Parameters.AddWithValue("@time", DateTime.Parse(dateTime).ToString("yyyy-MM-dd HH:mm:ss"));
        cmd.Parameters.AddWithValue("@action", Action);
        cmd.Parameters.AddWithValue("@session", session);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

The using block will guarantee the connection is closed properly, even in cases where the sqllite_conn.Close() call might have been missed.

---

Here's the thing: fixing this one query is not enough!

You must fix ALL of your queries to rely on the using block, or you're still at risk for locking. The locking problem will not show on the query that is actually causing the lock, but the query you run after the database was locked.

In this case, it looks like the locking problem might happen at the point where you show the grid. The grid query runs fun, and doesn't show any errors, but doesn't release its locks, and so you get the error trying to add the records because is the next query after that.

The same is true for parameters. It's not enough for just this query to use query parameters instead of string concatenation. You must do this EVERY TIME you substitute data into an SQL string, in EVERY QUERY.