如何在ASP.NET中处理长时间运行的数据库查询

I was a windows desktop application programmer, and I am new to ASP.NET.

我是一名Windows桌面应用程序程序员,我是ASP.NET的新手。

When I execute a long-running database query at Page_Load(object sender, EventArgs e), users have to wait until the query is done (the query takes a long time).

当我在Page_Load(对象发送者,EventArgs e)执行长时间运行的数据库查询时,用户必须等到查询完成(查询需要很长时间)。

Can I use SqlDataReader to pull data from a table row-by-row in another thread and send the data to users row-by-row? In this way, the users doesn't need to wait until the all rows are pulled from the database.

我可以使用SqlDataReader在另一个线程中逐行从表中提取数据并逐行向用户发送数据吗?通过这种方式,用户无需等到从数据库中提取所有行。

If I write a windows desktop application with the WPF framework, I can pull data from the database row-by-row in another thread and use BeginInvoke pattern to send the data to UI row-by-row.

如果我使用WPF框架编写Windows桌面应用程序,我可以在另一个线程中逐行从数据库中提取数据,并使用BeginInvoke模式逐行将数据发送到UI。

However, when I write a web application with ASP.NET, I don't know what is the best way to do it. Can I pull data row-by-row in another thread and put the pulled data to Session, then I send a XMLHttpRequest periodically to get the pulled data from Session?

但是,当我用ASP.NET编写Web应用程序时,我不知道最好的方法是什么。我可以在另一个线程中逐行提取数据并将提取的数据放入Session,然后我定期发送XMLHttpRequest以从Session获取数据吗?

I am wondering if there is a long-running database query, what is the best practice for dealing with it in ASP.NET?

我想知道是否存在长时间运行的数据库查询,在ASP.NET中处理它的最佳实践是什么?

Thanks in advance.

提前致谢。

1 个解决方案

#1

Can I use SqlDataReader to pull data from a table row-by-row in another thread and send the data to users row-by-row? In this way, the users doesn't need to wait until the all rows are pulled from the database.

我可以使用SqlDataReader在另一个线程中逐行从表中提取数据并逐行向用户发送数据吗?通过这种方式,用户无需等到从数据库中提取所有行。

The problem is ASP.NET applications have a different life-cycle than Windows Form applications. Once you render the page and send a response to the user, you can no longer work with the data. What you can do is use javascript. You can use an UpdatePanel, ASP.NET's implementation of AJAX, or work with XMLHttpRequests. (JQuery, I'm sure you've heard, simplifies these requests using $.ajax). You could have a loading .gif in place of your data, and have javascript fetch the data after the page is ready.

问题是ASP.NET应用程序具有与Windows Form应用程序不同的生命周期。呈现页面并向用户发送响应后,您将无法再使用该数据。你可以做的是使用JavaScript。您可以使用UpdatePanel,ASP.NET的AJAX实现,或使用XMLHttpRequests。 (JQuery,我相信你已经听过,使用$ .ajax简化了这些请求)。您可以使用.gif加载代替您的数据,并在页面准备好后使用javascript获取数据。

Row-by-row loading may be trickier. The code below will start a database call, and return the rendered page before the query has finished:

逐行加载可能比较棘手。下面的代码将启动数据库调用,并在查询完成之前返回呈现的页面:

    protected void Page_Load( object sender, EventArgs e)
    {
        string query = "SELECT * FROM db.Tablename;"
        ThreadPool.QueueUserWorkItem(new WaitCallback (BuildDataSet), query);

    }

    private void BuildDataSet(object query)
    {
        Queue Q = new Queue();

        //Connection String, etc.

        while rdr.Read()
        {
            Q.Enqueue(rdr["Column"];
        }
    }

You would need a second page to handle your XMLHttpRequests fetching the rows. The difficulty will be in persisting this data, identifying it between requests, and possibly memory management.

您需要第二页来处理获取行的XMLHttpRequests。困难在于持久化这些数据,在请求之间识别它,以及可能的内存管理。

The easiest path I can think of is to do a SELECT TOP (50) on page_load, and then ajax in the full data once it has been fetched.

我能想到的最简单的路径是在page_load上执行SELECT TOP(50),然后在获取完整数据后再执行ajax。

#1