SQLDataReader.Close 不会立即关闭

发布于 2024-12-11 01:02:18 字数 1743 浏览 0 评论 0原文

我正在使用 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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

他是夢罘是命 2024-12-18 01:02:18

查看微软的文档后,可以通过取消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()

情话墙 2024-12-18 01:02:18

我知道您不想要替代解决方案,但为什么不使用 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?

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文