SQLDataReader.Close 不会立即关闭
我正在使用 SQLDataReader 从表中读取有限数量的行,以便我可以将它们分页到网格视图中。一旦我读完我想要的行数,我想退出;但是 Reader.Close(或 Reader.Dispose)不会立即返回。
我这样做是为了展示各种读取数据方式的比较,所以我并不是在寻找替代解决方案。我只想在获得所需的行数时退出代码。
这是代码:
SqlDataReader Reader = null;
SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ZipData"].ConnectionString);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("ZipCode", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("City", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("State", System.Type.GetType("System.String")));
try
{
string Query = string.Format("{0} WHERE ZipCode Like @ZipCode AND City Like @City AND State Like @State ORDER BY {1}", SQLQuery, (string.IsNullOrEmpty(sortType)) ? "ZipCode" : sortType);
SqlCommand Command = new SqlCommand(Query, Connection);
Command.Parameters.Add(new SqlParameter("@ZipCode", filter.Zip + "%"));
Command.Parameters.Add(new SqlParameter("@City", filter.City + "%"));
Command.Parameters.Add(new SqlParameter("@State", filter.State + "%"));
Connection.Open();
Reader = Command.ExecuteReader();
int CurrentRow = 0;
DataRow row;
while (Reader.Read())
{
if (CurrentRow >= startRowIndex)
{
if (dt.Rows.Count > maximumRows) break;
row = dt.NewRow();
row["ZipCode"] = (string)Reader["ZipCode"];
row["City"] = (string)Reader["City"];
row["State"] = (string)Reader["State"];
dt.Rows.Add(row);
}
CurrentRow++;
}
Reader.Close();
}
catch (SqlException ex)
{
ErrorMessage = ex.Message;
}
finally
{
Connection.Close();
}
return dt;
I am using a SQLDataReader to read a limited number of rows from a table so that I can page them into a gridview. Once I have read the number of rows I want, I want to exit; however the Reader.Close (or Reader.Dispose) does not return immediately.
I am doing this to show comparisons of various ways of reading data, so I am not looking for alternative solutions. I simply want to exit the code when I have the desired number of rows.
Here is the code:
SqlDataReader Reader = null;
SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ZipData"].ConnectionString);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("ZipCode", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("City", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("State", System.Type.GetType("System.String")));
try
{
string Query = string.Format("{0} WHERE ZipCode Like @ZipCode AND City Like @City AND State Like @State ORDER BY {1}", SQLQuery, (string.IsNullOrEmpty(sortType)) ? "ZipCode" : sortType);
SqlCommand Command = new SqlCommand(Query, Connection);
Command.Parameters.Add(new SqlParameter("@ZipCode", filter.Zip + "%"));
Command.Parameters.Add(new SqlParameter("@City", filter.City + "%"));
Command.Parameters.Add(new SqlParameter("@State", filter.State + "%"));
Connection.Open();
Reader = Command.ExecuteReader();
int CurrentRow = 0;
DataRow row;
while (Reader.Read())
{
if (CurrentRow >= startRowIndex)
{
if (dt.Rows.Count > maximumRows) break;
row = dt.NewRow();
row["ZipCode"] = (string)Reader["ZipCode"];
row["City"] = (string)Reader["City"];
row["State"] = (string)Reader["State"];
dt.Rows.Add(row);
}
CurrentRow++;
}
Reader.Close();
}
catch (SqlException ex)
{
ErrorMessage = ex.Message;
}
finally
{
Connection.Close();
}
return dt;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
查看微软的文档后,可以通过取消sql命令来加快关闭连接的速度。
http://msdn.microsoft.com/en -us/library/system.data.sqlclient.sqldatareader.close.aspx
因此,您应该能够在关闭之前添加此内容以实现您想要的结果。
命令.取消()
After looking at Microsoft's documentation, you can speed up closing the connection by cancelling the sql command.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.close.aspx
So, you should be able to add this prior to closing to achieve your desired outcome.
Command.Cancel()
我知道您不想要替代解决方案,但为什么不使用 SELECT TOP 200 或 SQL 中的任何 MaximumRows 呢?这几乎会改善一切; SQL Server、网络和本地计算机上的负载较少...
测试您的代码后,我没有发现关闭阅读器或连接的问题。你能更好地描述这个问题吗?
I know you didn't want alternate solutions, but why not use SELECT TOP 200 or whatever maximumRows is in the SQL? This would improve just about everything; less load on SQL Server, network, and local computer...
After testing your code, I didn't see a problem with closing the reader nor connection. Can you better describe the issue?