c# - 使用语句后，Sqlite 数据库保持打开状态并且文件锁定

Question

我对某些 sqlite 代码有疑问，下面的函数中的某些内容似乎导致数据库在应该由 using 语句关闭后保持打开状态，我可以通过调用来解决该问题GC.collect() 在调用函数中，但我想尝试找出问题的原因。我尝试在 using 语句之后添加对 GC.Collect() 的调用，但这似乎还不够，asm 假设有东西阻止 GC 清理？

    /// <summary>
    /// Gets the Points of Interest for a given Category
    /// </summary>
    /// <param name="rootPath">Target Path</param>
    /// <param name="category">Category to search</param>
    /// <returns>A collection of Points of interest</returns>
    public static Collection<PointOfInterest> GetPointsOfInterest(string rootPath, PointOfInterestCategory category)
    {
        if (category == null)
        {
            throw new ArgumentNullException("category");
        }

        Collection<PointOfInterest> pointsOfInterest = new Collection<PointOfInterest>();

        string databaseLocation = string.Format(Resources.DataFilePath, rootPath, "poidata.db");
        using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", databaseLocation)))
        {
            connection.Open();
            using (SQLiteCommand command = new SQLiteCommand(
                "SELECT latmin + (latmax - latmin / 2) as lat, lonmin + (lonmax - lonmin / 2) as lon, city, street, housenr, name FROM poicoord JOIN poidata ON poicoord.poiid = poidata.poiid JOIN poiname on poiname.docid = poicoord.poiid WHERE type = @category",
                connection))
            {
                command.Parameters.Add(new SQLiteParameter("category", DbType.Int32) { Value = category.Id });

                SQLiteDataReader reader = command.ExecuteReader();

                int latOrdinal = reader.GetOrdinal("lat");
                int lonOrdinal = reader.GetOrdinal("lon");
                int nameOrdinal = reader.GetOrdinal("name");
                int houseNrOrdinal = reader.GetOrdinal("housenr");
                int streetOrdinal = reader.GetOrdinal("street");
                int cityOrdinal = reader.GetOrdinal("city");

                while (reader.Read())
                {
                    pointsOfInterest.Add(new PointOfInterest()
                    { 
                        Latitude = reader.GetDouble(latOrdinal), 
                        Longitude = reader.GetDouble(lonOrdinal),
                        Name = reader.GetString(nameOrdinal),
                        HouseNumber = reader.IsDBNull(houseNrOrdinal) ? string.Empty : reader.GetString(houseNrOrdinal),
                        Street = reader.IsDBNull(streetOrdinal) ? string.Empty : reader.GetString(streetOrdinal),
                        City = reader.IsDBNull(cityOrdinal) ? string.Empty : reader.GetString(cityOrdinal),
                    });
                }
            }
        }

        return pointsOfInterest;
    }

Answer 1

尝试实现 using 语句，而不是仅仅将读取器分配给变量:

using (SQLiteDataReader reader=command.ExecuteReader()) {
    ...
}

或手动关闭阅读器:

reader.Close();