[RESOLVED]Asynchronous Pages and SqlDataAdapter

Talarin · August 20th, 2007, 09:55 AM

Hey all,

I just recently read a great article by Jeff Prosise on Asynchronous Programming. I'm developing an external website for our company and using asynchronous pages seems to be the way to go to get efficient use out of my threads, especially as the website will be making some pretty heavy duty database calls. I'm still fairly new to ASP.NET 2.0 and I've run across a problem that I could really use some help with.

Here is the example code the author uses:

Code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        Dim task As New PageAsyncTask(New BeginEventHandler(AddressOf BeginAsyncOperation), _
            New EndEventHandler(AddressOf EndAsyncOperation), _
            New EndEventHandler(AddressOf TimeoutAsyncOperation), _
            Nothing)
        RegisterAsyncTask(task)
    End Sub

    Function BeginAsyncOperation(ByVal sender As Object, ByVal e As EventArgs, ByVal cb As AsyncCallback, ByVal state As Object) As IAsyncResult
        Dim connect As String = WebConfigurationManager.ConnectionStrings("AsyncPubs").ConnectionString
        _connection = New SqlConnection(connect)
        _connection.Open()
        _command = New SqlCommand("SELECT title_id, title, price FROM titles", _connection)
        Return _command.BeginExecuteReader(cb, state)
    End Function

    Sub EndAsyncOperation(ByVal ar As IAsyncResult)
        _reader = _command.EndExecuteReader(ar)
    End Sub

    Protected Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As EventArgs)
        Output.DataSource = _reader
        Output.DataBind()
    End Sub

This actually works quite well, except for my purposes, my grid uses pagination. You can't use pagination with an SqlDataReader. I would have to use an SqlDataAdapter and fill a DataSet and then bind the DataSet to the grid. My problem is I'm not sure how I would do this.

It seems to me, the way this works is the SqlDataReader begins the execution in Function BeginAsyncOperation and the result is passed back to it in the Sub EndAsyncOperation. How would I adapt this to use the SqlDataAdapter instead of the SqlDataReader? The key for this to work seems to be in the BeginExecuteReader and EndExecuteReader methods. I'm not sure if there is a match for these methods with SqlDataAdapter.

Thanks,
Tal

Talarin · August 22nd, 2007, 02:05 PM

For anyone interested, the solution to my problem was quite simple. I used a DataTable and then used the Load method to fill it with the results of the Reader. So basically, I just changed

Code:

Protected Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As EventArgs)
    Output.DataSource = _reader
    Output.DataBind()
End Sub

to

Code:

Protected Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As EventArgs)
    _datatable.Load(_reader)
    Output.DataSource = _datatable
    Output.DataBind()
End Sub

Doing it this way gets around the whole problem of the SqlDataReader not supporting pagination.

Tal