读取器关闭时读取尝试无效-但未关闭读取器

本文关键字：读取 无效 | 更新日期: 2023-09-27 18:05:31

在我的c#应用程序中，我与SQL Compact数据库接口。在这种情况下，我有三个表;customer, list, customerlist(客户和列表是多对多关系)

我有一个函数，当我想删除一个列表时调用它。这个函数也从customerlist表中删除相关的条目，只是为了清洁(因为如果列表本身被删除，它们就会失效)。

我的代码如下:

    private void clearRedundantSubscriptions()
    {
        string sql;
        // Check if there are any entries in customerlist table which point to non-existing lists
        try
        {
            sql = "select distinct cl.listid from customerlist cl inner join list l on cl.listid != l.listid";
            SqlCeCommand cmdGetDisusedLists = new SqlCeCommand(sql, DbConnection.ceConnection);
            SqlCeDataReader reader = cmdGetDisusedLists.ExecuteReader();
            while (reader.Read())
            {
                DbFunctions.DeleteList(reader.GetInt32(0), false, false);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error cleaning up list entries." + ex.Message);
        }
        return;
    }
    public static bool DeleteList(int id, bool display, bool close)
    {
        string sql;
        string title = "";
        bool ranOk = false;
        try
        {
            sql = "select ShortDesc from list where listid=" + id;
            DbFunctions.runSQL(sql, out title);
            sql = "delete from list where ListId=" + id;
            SqlCeCommand cmdDelList = new SqlCeCommand(sql, DbConnection.ceConnection);
            cmdDelList.ExecuteNonQuery();
            sql = "delete from customerlist where listid=" + id;
            SqlCeCommand cmdDelEntries = new SqlCeCommand(sql, DbConnection.ceConnection);
            cmdDelEntries.ExecuteNonQuery();
            if (display)
                General.doneWork(title + " list deleted.");
            ranOk = true;
        }
        catch (Exception ex)
        {
            if (display)
                MessageBox.Show("Unable to delete list. " + ex.Message);
        }
        finally
        {
            if (close)
                DbConnection.closeConnection();
        }
        return ranOk;
    }
    public static void closeConnection()
    {
        if (_sqlCeConnection != null)
            _sqlCeConnection.Close();
    }

你会注意到在我的删除列表函数中，我传入了一个名为'close'的bool形参。我添加这是因为关闭连接到数据库，而在阅读器内部导致上述错误。所以现在，如果我想在阅读器中使用这个函数，我调用deleelist函数并确保'close'参数作为false传入。这不是问题——这意味着DbConnection。closecnection NOT在这个函数中被调用。

所以我没有关闭阅读器，也没有关闭数据库连接。知道为什么我仍然得到这个错误吗?

读取器关闭时读取尝试无效-但未关闭读取器

我已经修改了你的代码，试试这个。

我不知道你的DbFunctions.runSQL方法中发生了什么，所以你可能需要在调用之前重新打开连接。

private void clearRedundantSubscriptions()
{
    string sql;
    // Check if there are any entries in customerlist table which point to non-existing lists
    var list = new List<int>();
    try
    {
        if (DbConnection.ceConnection.State == ConnectionState.Closed)
            DbConnection.ceConnection.Open();
        sql = "select distinct cl.listid from customerlist cl inner join list l on cl.listid != l.listid";
        SqlCeCommand cmdGetDisusedLists = new SqlCeCommand(sql, DbConnection.ceConnection);
        SqlCeDataReader reader = cmdGetDisusedLists.ExecuteReader();
        while (reader.Read())
        {
            list.Add(reader.GetInt32(0));
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error cleaning up list entries." + ex.Message);
        throw;
    }
    finally
    {
        DbConnection.closeConnection();
    }
    foreach(var id in list)
    {
        DeleteList(id,false);
    }
    return;
}
public static bool DeleteList(int id, bool display)
{
    string sql;
    string title = "";
    bool ranOk = false;
    try
    {
        sql = "select ShortDesc from list where listid=" + id;
        DbFunctions.runSQL(sql, out title);

        sql = "delete from list where ListId=" + id;
        SqlCeCommand cmdDelList = new SqlCeCommand(sql, DbConnection.ceConnection);
        cmdDelList.ExecuteNonQuery();
        sql = "delete from customerlist where listid=" + id;
        SqlCeCommand cmdDelEntries = new SqlCeCommand(sql, DbConnection.ceConnection);
        cmdDelEntries.ExecuteNonQuery();
        if (display)
            General.doneWork(title + " list deleted.");
        ranOk = true;
    }
    catch (Exception ex)
    {
        if (display)
            MessageBox.Show("Unable to delete list. " + ex.Message);
    }
    finally
    {
            DbConnection.closeConnection();
    }
    return ranOk;
}
public static void closeConnection()
{
    if (_sqlCeConnection != null)
        _sqlCeConnection.Close();
}
相关文章：