阅读器关闭时尝试读取无效 - 但不关闭阅读器?
问题描述:
在我的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();
}
你会在我deletelist功能注意到,我通过一个名为“关闭”一个布尔参数。我添加了这个,因为在阅读器内关闭与数据库的连接导致了上述错误。所以,现在,如果我想在阅读器中使用这个函数,我调用deleteList函数并确保'close'参数被传入为false
。这不是问题 - 这意味着DbConnection.closeConnection是不是在此函数中调用。
所以我没有关闭阅读器,也没有数据库连接。那么,为什么我仍然得到这个错误的任何想法?
答
我修改了你的代码,试试看。
我不知道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();
}
您忘记了添加最重要的信息:_Where_你会得到异常吗? – 2013-03-22 11:15:08
第二遍读者:( – 2013-03-22 11:16:30
)您正在重新使用其他sql命令的相同连接,首先阅读列表,然后使用DeleList()。 – 2013-03-22 11:17:58