SET statements do not have connection-wide ccope with ExecuteReader() of SQLCommand class (316889)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.Net 2.0
  • Microsoft Visual Basic .NET (2002)
  • Microsoft Visual Basic .NET (2003)
  • Microsoft Visual Basic 2005
This article was previously published under Q316889
This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data
  • System.Data.SqlClient

SYMPTOMS

When you are using SET statements that are followed by resultsets that are returning statements such as SELECT, existing Microsoft SQL tools return non-resultset queries. This is not true when you are using the SqlCommand.ExecuteReader method. When SET statements are used with SqlCommand.ExecuteReader, the SQL statements that contain SET statements such as "SET statistics profile on" return results from the set statements. The preceding SQL statements (such as SELECT * FROM TableA) that are executed in the same connection do not see results from the "SET statistics profile on" statement. "SET statistics profile on" is expected to be scoped to the connection. The real scope that is seen here ("SET statistics profile on") is scoped to the statement.

CAUSE

The ExecuteReader method of the SqlCommand object does not support the use of SET statements. When you are using SET statements, they are only scoped to the statement and not to the connection.

RESOLUTION

When you are executing queries that do not return results, use the ExecuteNonQuery method because this method has a connection-wide scope. 
   Execute             Command                      Active SET statements
   ----------          ------------------           ---------------------
   ExecuteReader       Set xxx SELECT ...           xxx
   ExecuteNonQuery     Set yyy UPDATE ...           yyy
   ExecuteReader       Set zzz SELECT ...           yyy zzz
   ExecuteNonQuery     UPDATE ...                   yyy
The current connection would have yyy set.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to reproduce the behavior

  1. Create a Visual Basic .NET Windows Application project, and then add the following namespaces to the very top of the code window:
    Imports System
Imports System.Data
Imports System.Data.SqlClient
  2. Add the following segment of code inside of "Public Class Form1" (just below "Inherits System.Windows.Forms.Form"), and note that you must modify the connection string appropriately for your environment:
            Dim myConnection As New SqlConnection("server=(local);database=pubs;Integrated Security=SSPI;")
        Dim myReader As SqlDataReader
        Dim myCommand As New SqlCommand()
        Dim i As Integer
  3. Double-click the form, and then add the following code to the form Load event:
            Try
            myConnection.Open()
            myCommand.CommandText = "SET statistics profile on "
            myCommand.Connection = myConnection
            myReader = myCommand.ExecuteReader()
            'use ExecuteNonQuery as a workaround to set statistics profile on for the connection
            'myCommand.ExecuteNonQuery()
            myReader.Close()

            myCommand.CommandText = "SELECT * FROM Titles"
            myCommand.Connection = myConnection
            myReader = myCommand.ExecuteReader()

            i = 0
            Debug.WriteLine("====Looping through the first resultset on MyCommand=====")
            Do While (myReader.Read())
                For i = 0 To myReader.FieldCount - 1
                    Debug.WriteLine(myReader(i).ToString())
                Next i
            Loop
            myReader.NextResult()

            i = 0
            'set statistics profile will not be set here with ExecuteReader()
            Debug.WriteLine("=====Looping through the second resultset on MyCommand to get statistics======")
            Do While (myReader.Read())
                For i = 0 To myReader.FieldCount - 1
                    Debug.WriteLine(myReader(i).ToString())
                Next i
            Loop
            myReader.Close()

            myCommand.CommandText = "select * from authors"
            myCommand.Connection = myConnection
            myReader = myCommand.ExecuteReader()

            i = 0
            Debug.WriteLine("====Looping through the first resultset on MyCommand2=====")
            Do While (myReader.Read())
                For i = 0 To myReader.FieldCount - 1
                    Debug.WriteLine(myReader(i).ToString())
                Next i
            Loop
            myReader.NextResult()

            'set statistic will not be set here with ExecuteReader()
            'when using ExecuteNonQuery you will see that statistics profile is set within the connection scope
            Debug.WriteLine("=====Looping through the second resultset on MyCommand2 to get statistics======")
            i = 0
            Do While (myReader.Read())
                For i = 0 To myReader.FieldCount - 1
                    Debug.WriteLine(myReader(i).ToString())
                Next i
            Loop
            myReader.Close()

        Catch e1 As Exception
            Dim errmessage = e1.ToString()
            MessageBox.Show(errmessage)
        Finally
            myConnection.Close()
        End Try
  4. Press F5 to run the application.
  5. On the View menu, point to Other Windows, and then click Output to examine the results.
  6. To see the workaround, comment or uncomment the following in the first block of code. Comment out: 
    myReader = myCommand.ExecuteReader()
 myReader.Close()
    Uncomment: 
    'myCommand.ExecuteNonQuery()

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

313590 Roadmap for ADO.NET

313483 Roadmap for ADO.NET DataAdapter objects

Modification Type:MinorLast Reviewed:10/3/2006
Keywords:kbenv kbnofix kbprb kbSqlClient kbSystemData KB316889 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.