Synopsis

I have a small local SQLite DB with a set records that are used to populate a ComboBoxList. The list is populated, and the DB connection released. No DB locking issues observed.

Then when a record is selected from the list, that record is retrieved and used to populate a LocalDBInstallRecord object. However, after populating the object, the DB connection seems to remain open and prevents any further access.

The Code

    public LocalDBInstallRecord GetRecord(string recordID)
    {
        LocalDBInstallRecord lir = new LocalDBInstallRecord();
        string query = "SELECT * FROM InstallationRecords WHERE RecordID = "   recordID;

        using (SQLiteConnection localDBConnection = new SQLiteConnection(ConnectionStr))
        {
            localDBConnection.Open();
            using (SQLiteCommand cmd = new SQLiteCommand(query, localDBConnection))
            {
                using (SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo))
                {
                    while (rdr.Read())
                    {
                        lir.RecordID = (uint)rdr.GetInt32(rdr.GetOrdinal("RecordID"));
                        lir.DateCreated = rdr.GetDateTime(rdr.GetOrdinal("DateCreated"));
                        lir.Model = CheckDBNull(rdr, "Model");
                        lir.SCFirmwareVer = CheckDBNull(rdr, "SCFirmwareVer");
                        lir.DispFirmwareVer = CheckDBNull(rdr, "DispFirmwareVer");
                        lir.UCCFirmwareVer = CheckDBNull(rdr, "UCCFirmwareVer");
                        lir.Title = CheckDBNull(rdr, "Title");
                        lir.FOC = rdr.GetBoolean(rdr.GetOrdinal("FOC"));
                        lir.MCManufacturer = CheckDBNull(rdr, "MCManufacturer");
                        lir.MCType = CheckDBNull(rdr, "MCType");
                        lir.RailConvertor = CheckDBNull(rdr, "RailConvertor");
                        lir.PlantID = CheckDBNull(rdr, "PlantID");
                        lir.PlantOwner = CheckDBNull(rdr, "PlantOwner");
                        lir.PlantOwnerID = CheckDBNull(rdr, "PlantOwnerID");
                        lir.BoomLength = rdr.GetFloat(rdr.GetOrdinal("BoomLength"));
                        lir.ArticLength = rdr.GetFloat(rdr.GetOrdinal("ArticLength"));
                        lir.DipperLength = rdr.GetFloat(rdr.GetOrdinal("DipperLength"));
                        lir.MachineRecord = ReadDBBlob(rdr, "MachineRecord");
                        lir.DutyRecord = ReadDBBlob(rdr, "DutyRecord");
                        lir.CalibRecord = ReadDBBlob(rdr, "CalibRecord");
                    }
                }
            }
        }
        return lir;
    }

Called Functions...

CheckDBNull

    private static string CheckDBNull(SQLiteDataReader rdr, string col)
    {
        string str = null;

        if (!rdr.IsDBNull(rdr.GetOrdinal(col)))
            str = rdr.GetString(rdr.GetOrdinal(col));

        return str;
    }

ReadDBBlob

    private static byte[] ReadDBBlob(SQLiteDataReader rdr, string col)
    {
        byte[] data = null;
        if (!rdr.IsDBNull(rdr.GetOrdinal(col)))
        {
            SQLiteBlob blob = rdr.GetBlob(rdr.GetOrdinal(col), true);
            data = new byte[blob.GetCount()];
            blob.Read(data, blob.GetCount(), 0);
        }
        return data;
    }

My Thoughts

I'm sure there's easier ways to load the object, but that's not the issue...

I think the DB connection is being held open because the SQLiteDataReader can't be disposed of cleanly. I suspect that something in this function (in the LocalDBInstallRecord object) has got hold of an object by reference. I just can't see what.

I've tried not accessing the DateTime object in the record, but that's not it.

Should I be using a different method to access the DB in this case?

Any suggestions greatly appreciated, I think it needs a fresh set of eyes. Thanks.

Further Research... I've found that if I try and us the SQLite DB with another tool, whilst the GetRecord() function has hold of the reader, I'm able to read the DB, but can't write. I get a database locked (RELEASE 'RESTOREPOINT';) error message from "DB Browser SQLite" application. But I think that's just because it's waiting for the reader to release.

CodePudding user response：

The problem came in the function ReadDBBlob. The SQLiteBlob is IDisposable, and it's use wasn't encapsulated in a using clause. As a result the SQLiteBlob wasn't being diposed of, and this held the reader and hence kept the connection open...

New ReadDBBlob

    private static byte[] ReadDBBlob(SQLiteDataReader rdr, string col)
    {
        byte[] data = null;
        if (!rdr.IsDBNull(rdr.GetOrdinal(col)))
        {
            using (SQLiteBlob blob = rdr.GetBlob(rdr.GetOrdinal(col), true))
            {
                data = new byte[blob.GetCount()];
                blob.Read(data, blob.GetCount(), 0);
            }
        }
        return data;
    }

I hope this saves someone some time.