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

Article translations Article translations
Article ID: 316889 - View products that this article applies to.
This article was previously published under Q316889
This article has been archived. It is offered "as is" and will no longer be updated.
This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data
  • System.Data.SqlClient
Expand all | Collapse all

On This Page

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

Properties

Article ID: 316889 - Last Review: February 27, 2014 - Revision: 2.6
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 2.0
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic 2005
Keywords: 
kbnosurvey kbarchive kbenv kbnofix kbprb kbsqlclient kbsystemdata KB316889

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com