概要
我有一個小型本地 SQLite 資料庫，其中包含一組用于填充 ComboBoxList 的記錄。該串列已填充，并已釋放 DB 連接。沒有觀察到資料庫鎖定問題。
然后，當從串列中選擇一條記錄時，將檢索該記錄并用于填充 LocalDBInstallRecord 物件。但是，填充物件后，資料庫連接似乎保持打開狀態并阻止任何進一步訪問。
編碼
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;
}
呼叫函式...
檢查DBNull
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;
}
讀取DBBlob
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;
}
我的想法
我確信有更簡單的方法來加載物件，但這不是問題......
我認為 DB 連接保持打開狀態，因為 SQLiteDataReader 無法干凈地處理。我懷疑這個函式中的某些東西（在 LocalDBInstallRecord 物件中）已經通過參考獲得了一個物件。我就是看不到什么。
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.
uj5u.com熱心網友回復：
問題出在函式 ReadDBBlob 中。SQLiteBlob 是 IDisposable，它的使用沒有封裝在 using 子句中。結果，SQLiteBlob 沒有被處理掉，這保持了讀者，因此保持了連接打開......
新建 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;
}
我希望這可以節省一些時間。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/365107.html
標籤：c# sqlite database-connection