C# SQLite, database being left locked after a read

Question

I have read some of the related posts about this and didn't quite understand. What is happening is it appears that after this access to the database, the database is being left locked so that it can be read, but not written to. After exiting the application, the database is unlocked again. Can you look at it and talking to me like I am ignorant, point out the error of my way of handling this?

 public static Partner GetOnePartner(string code)
 {
    Partner partner = new Partner();
    SQLiteConnection connection = GroomwatchDB.GetConnection();
    string sqlStatement = "SELECT * FROM partners WHERE partner_code = @partner_code";

    SQLiteCommand command = new SQLiteCommand(sqlStatement, connection);
    command.Parameters.Add(new SQLiteParameter("@partner_code"));
    command.Parameters["@partner_code"].Value = code;

    try
    {
        connection.Open();
        SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
        if(reader.Read())
        {
            partner.Code = reader["partner_code"].ToString();
            partner.Last_name = reader["last_name"].ToString();
            partner.First_name = reader["first_name"].ToString();
            partner.Pay_rate = (double)reader["pay_rate"];
            partner.Active = reader["active"].ToString();
        }
        else
        {
            partner.Code = code;
            partner.Last_name = "Not Found";
        }

    }
    catch (SQLiteException ex)
    {
       throw ex;
    }
    finally
    {
        connection.Close();
    }

    return partner;

}

When I had to guess: Try an reader.Close() after your else branch. At least it cannot go worse. — Thomas Krojer, Mar 05 '15 at 06:47
If SQLiteDataReader, SQLiteCommand or SQLiteConnection implement the IDisposable interface you could use the using statement to ensure they are disposed. — Annihil, Mar 05 '15 at 07:16
What is the connection string? Do you use write ahead logging? What does the `GetConnection` method do? — Panagiotis Kanavos, Mar 05 '15 at 07:48
Are you trying to access the database from different threads? Do you use transactions? SQLite allows multiple readers but only a single writer which probably means that you have a connection opened for writing left over somewhere — Panagiotis Kanavos, Mar 05 '15 at 07:52
Please use `using` to dispose *all* disposable objects - connection, command and reader. — Panagiotis Kanavos, Mar 05 '15 at 07:56

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

You should correctly use your connection by using the IDisposable pattern.

In fact, every classes that implements the IDisposable interface needs to be call with a using. This ensure that the methods Dispose() is call, and so the unmanaged resources are being cleared (and you don't end with an open file) :

public static Partner GetOnePartner(string code)
 {
    Partner partner = new Partner();
    string sqlStatement = "SELECT * FROM partners WHERE partner_code = @partner_code";
    using(SQLiteConnection connection = GroomwatchDB.GetConnection())
    using(SQLiteCommand command = new SQLiteCommand(sqlStatement, connection))
    {
        command.Parameters.Add(new SQLiteParameter("@partner_code"));
        command.Parameters["@partner_code"].Value = code;
        connection.Open();
        using(SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
        {
            if(reader.Read())
            {
                partner.Code = reader["partner_code"].ToString();
                partner.Last_name = reader["last_name"].ToString();
                partner.First_name = reader["first_name"].ToString();
                partner.Pay_rate = (double)reader["pay_rate"];
                partner.Active = reader["active"].ToString();
            }
            else
            {
                partner.Code = code;
                partner.Last_name = "Not Found";
            }
        }
    }
    return partner;
}

References :

edited May 23 '17 at 12:12

Community

1
1

answered Mar 05 '15 at 07:29

Cyril Gandon

16,830
14
78
122

Since he's closing the connection in the `finally` section, would this change really help with his problem? Note that I understand that an exception before he enters the `try/finally` block will in fact leave the database connection open, but since he's not saying that it is crashing I'm assuming the code runs to completion. In other words, I fully agree with switching to the `IDisposable` pattern like you show, I'm just not sure it will help with his problem, assuming the problem really is in the posted code. – Lasse V. Karlsen Mar 05 '15 at 07:40
It may solve the problem. As a comment in "Do I have to Dispose the SQLiteCommand objects?" shows, forgetting to dispose SQLiteCommand was causing issues for the poster even though the connection was disposed. – Panagiotis Kanavos Mar 05 '15 at 07:56
@Cyril Gandon, I have now read about 'using' in this context. None of my books addressed it. Thank you. It seems to make sense. It disposes of the object as soon as it goes out of scope and releases its resources (connections etc.) I will have to learn more before I get all the appropriate time to use it, but I understand this situation a little. I will try it this evening and report back. In your code, you do not close the connection. Shouldn't I make a call to Close() just out of good form? – Keith Kolbo Mar 05 '15 at 15:19
That's the beauty of it, good IDisposable object Close themself in their Dispose() methods (Note that Dispose() method is called when you hit the closed brace). See for example http://stackoverflow.com/questions/630955/how-to-implement-dispose-pattern-with-close-method-correctly-ca1063 – Cyril Gandon Mar 05 '15 at 15:22
@Cyril Gandon -- I have to apologize. Your solution was correct except for the one set of missing brackets. I had made another error in implementing it. When I looked back at it it was obvious. Now it does not leave it locked. Thanks a million!! – Keith Kolbo Mar 06 '15 at 02:21

C# SQLite, database being left locked after a read

1 Answers1

Linked