BeginExecuteXmlReader Method
Collapse the table of content
Expand the table of content

SqlCommand.BeginExecuteXmlReader Method ()

 

Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand and returns results as an XmlReader object.

Namespace:   System.Data.SqlClient
Assembly:  System.Data (in System.Data.dll)

<HostProtectionAttribute(SecurityAction.LinkDemand, ExternalThreading := True)>
Public Function BeginExecuteXmlReader As IAsyncResult

Return Value

Type: System.IAsyncResult

An IAsyncResult that can be used to poll or wait for results, or both; this value is also needed when invoking EndExecuteXmlReader, which returns a single XML value.

Exception Condition
InvalidCastException

A SqlDbType other than Binary or VarBinary was used when Value was set to Stream. For more information about streaming, see SqlClient Streaming Support.

A SqlDbType other than Char, NChar, NVarChar, VarChar, or Xml was used when Value was set to TextReader.

A SqlDbType other than Xml was used when Value was set to XmlReader.

SqlException

Any error that occurred while executing the command text.

A timeout occurred during a streaming operation. For more information about streaming, see SqlClient Streaming Support.

InvalidOperationException

The name/value pair "Asynchronous Processing=true" was not included within the connection string defining the connection for this SqlCommand.

The SqlConnection closed or dropped during a streaming operation. For more information about streaming, see SqlClient Streaming Support.

IOException

An error occurred in a Stream, XmlReader or TextReader object during a streaming operation. For more information about streaming, see SqlClient Streaming Support.

ObjectDisposedException

The Stream, XmlReader or TextReader object was closed during a streaming operation. For more information about streaming, see SqlClient Streaming Support.

The BeginExecuteXmlReader method starts the process of asynchronously executing a Transact-SQL statement that returns rows as XML, so that other tasks can run concurrently while the statement is executing. When the statement has completed, developers must call the EndExecuteXmlReader method to finish the operation and retrieve the XML returned by the command. The BeginExecuteXmlReader method returns immediately, but until the code executes the corresponding EndExecuteXmlReader method call, it must not execute any other calls that start a synchronous or asynchronous execution against the same SqlCommand object. Calling the EndExecuteXmlReader before the command's execution is completed causes the SqlCommand object to block until the execution is finished.

The CommandText property ordinarily specifies a Transact-SQL statement with a valid FOR XML clause. However, CommandText can also specify a statement that returns ntext data that contains valid XML.

A typical BeginExecuteXmlReader query can be formatted as in the following C# example:

SqlCommand command = new SqlCommand("SELECT ContactID, FirstName, LastName FROM dbo.Contact FOR XML AUTO, XMLDATA", SqlConn);

This method can also be used to retrieve a single-row, single-column result set. In this case, if more than one row is returned, the EndExecuteXmlReader method attaches the XmlReader to the value on the first row, and discards the rest of the result set.

The multiple active result set (MARS) feature lets multiple actions use the same connection.

Note that the command text and parameters are sent to the server synchronously. If a large command or many parameters are sent, this method may block during writes. After the command is sent, the method returns immediately without waiting for an answer from the server--that is, reads are asynchronous. Although command execution is asynchronous, value fetching is still synchronous.

Because this overload does not support a callback procedure, developers need to either poll to determine whether the command has completed, using the IsCompleted property of the IAsyncResult returned by the BeginExecuteXmlReader method; or wait for the completion of one or more commands using the AsyncWaitHandle property of the returned IAsyncResult.

If you use ExecuteReader or BeginExecuteReader to access XML data, SQL Server returns any XML results greater than 2,033 characters in length in multiple rows of 2,033 characters each. To avoid this behavior, use ExecuteXmlReader or BeginExecuteXmlReader to read FOR XML queries. For more information, see article Q310378, "PRB: XML Data Is Truncated When You Use SqlDataReader," in the Microsoft Knowledge Base at http://support.microsoft.com.

The following console application starts the process of retrieving XML data asynchronously. While waiting for the results, this simple application sits in a loop, investigating the IsCompleted property value. Once the process has completed, the code retrieves the XML and displays its contents.

Imports System.Data.SqlClient
Imports System.Xml

Module Module1

    Sub Main()
        ' This example is not terribly effective, but it proves a point.
        ' The WAITFOR statement simply adds enough time to prove the 
        ' asynchronous nature of the command.
        Dim commandText As String = _
         "WAITFOR DELAY '00:00:03';" & _
         "SELECT Name, ListPrice FROM Production.Product " & _
         "WHERE ListPrice < 100 " & _
         "FOR XML AUTO, XMLDATA"

        RunCommandAsynchronously(commandText, GetConnectionString())

        Console.WriteLine("Press ENTER to continue.")
        Console.ReadLine()
    End Sub

    Private Sub RunCommandAsynchronously( _
     ByVal commandText As String, ByVal connectionString As String)

        ' Given command text and connection string, asynchronously execute
        ' the specified command against the connection. For this example,
        ' the code displays an indicator as it is working, verifying the 
        ' asynchronous behavior. 
        Using connection As New SqlConnection(connectionString)
            Try
                Dim command As New SqlCommand(commandText, connection)
                connection.Open()
                Dim result As IAsyncResult = command.BeginExecuteXmlReader()

                ' Although it is not necessary, the following procedure
                ' displays a counter in the console window, indicating that 
                ' the main thread is not blocked while awaiting the command 
                ' results.
                Dim count As Integer = 0
                While Not result.IsCompleted
                    count += 1
                    Console.WriteLine("Waiting ({0})", count)
                    ' Wait for 1/10 second, so the counter
                    ' does not consume all available resources 
                    ' on the main thread.
                    Threading.Thread.Sleep(100)
                End While

                Using reader As XmlReader = command.EndExecuteXmlReader(result)
                    DisplayProductInfo(reader)
                End Using
            Catch ex As SqlException
                Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message)
            Catch ex As InvalidOperationException
                Console.WriteLine("Error: {0}", ex.Message)
            Catch ex As Exception
                ' You might want to pass these errors
                ' back out to the caller.
                Console.WriteLine("Error: {0}", ex.Message)
            End Try
        End Using
    End Sub

    Private Sub DisplayProductInfo(ByVal reader As XmlReader)
        ' Display the data within the reader.
        While reader.Read()
            ' Skip past items that are not from the correct table.
            If reader.LocalName.ToString = "Production.Product" Then
                Console.WriteLine("{0}: {1:C}", _
                 reader("Name"), CSng(reader("ListPrice")))
            End If
        End While
    End Sub

    Private Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,            
        ' you can retrieve it from a configuration file. 

        ' If you have not included "Asynchronous Processing=true" in the
        ' connection string, the command is not able
        ' to execute asynchronously.
        Return "Data Source=(local);Integrated Security=true;" & _
          "Initial Catalog=AdventureWorks; Asynchronous Processing=true"
    End Function
End Module

.NET Framework
Available since 2.0
Return to top
Show:
© 2016 Microsoft