Silverlight Knowledge Base

Querying Data in an Asynchronous Page

• How To Set Up Your Own Power Station In Your Back Yard

The data source controls don't have any asynchronous support. However, many of the underlying ADO.NET classes, including SqlCommand and SqlDataReader, have asynchronous support. The following page takes advantage of the BeginReader() and EndReader() methods of the SqlDataReader. To allow the asynchronous query, you need to explicitly enable it in the connection string, as shown in the following snippet from the web.config file:

<connectionStrings>

<add name="NorthwindAsync" connectionString="Data Source=localhost; Initial Catalog=Northwind;Integrated Security=SSPI;Asynchronous Processing=true"

providerName="System.Data.SqlClient"/> </connectionStrings>

The first step is to register the methods that perform the asynchronous task. This step is the same in any asynchronous web page:

Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As EventArgs) Handles Me.Load 'Register the asynchronous methods for later use 'This method returns immediately.

Page.AddOnPreRenderCompleteAsync _ (New BeginEventHandler(AddressOf BeginTask), _ New EndEventHandler(AddressOf EndTask)) End Sub

When the BeginTask() method is called, you can launch the asynchronous operation:

' The AD0.NET objects need to be accessible in several different ' event handlers, so they must be declared as member variables. Private con As SqlConnection Private cmd As SqlCommand Private reader As SqlDataReader

Private Function BeginTask(sender As Object, _

e As EventArgs, cb As AsyncCallback, state As Object) As IAsyncResult ' Create the command. Dim connectionString As String = _ WebConfigurationManager.ConnectionStrings _ ("NorthwindAsync").ConnectionString con = New SqlConnection(connectionString) cmd = New SqlCommand("SELECT * FROM Employees", con)

' Open the connection.

' This part is not asynchronous.

con.0pen()

' Run the query asynchronously.

' This method returns immediately and provides ASP.NET ' with the IAsyncResult object it needs to track progress. Return cmd.BeginExecuteReader(cb, state) End Function

The EndTask() method fires automatically when the IAsyncResult object indicates the BeginExecuteReader() method has finished its work and retrieved all the data:

Private Sub EndTask(ar As IAsyncResult) ' You can now retrieve the DataReader. reader = cmd.EndExecuteReader(ar) End Sub

If you want to perform more page processing, you can handle the Page.PreRenderComplete event. In this example, this is the point where the grid is filled with the retrieved data:

Protected Sub Page_PreRenderComplete _ (sender As Object, e As EventArgs) _ Handles Me.PreRenderComplete grid.DataSource = reader grid.DataBind() con.CloseQ End Sub

Finally, you need to override the Dispose() method of the page to ensure that the connection is closed in the event of an error:

Public Overrides Sub DisposeQ If con IsNot Nothing Then con.CloseQ End If

MyBase.DisposeQ End Sub

Overall, the asynchronous data retrieval makes this page more complex. The actual binding needs to be performed by hand (rather than using a data source control), and it spans several methods. However, the end result is a more scalable web application, assuming the query takes a significant amount of time to execute.

0 0