How is it possible that my SQLCommand + ExecuteScalar/ExecuteReader can return a result from another call?

I have 2 pieces of code which seem to be experiencing what can only be described as multi-threading issues.

I do not believe I have written any multi-threading code, but my code is running within the context of a webserver and hence the multi-threading is essentially part of the eco system.

The Code is SQL based, using ADO.Net.

(Note: I appreciate the potential for SQL injection attacks, but for now let us assume that this is dealt with because the SQL for these SQL commands cannot be directly influenced by user input)

GetField takes a SQLConnection and some SQL.

• It assumes that the SQL is structured as "Select top 1 SomeField from SomeTable".
• It creates a SQLCommand around this SQL
• It executes the command's ExecuteScalar function.
• It returns the value provided by ExecuteScalar

GetRecord similarly takes a SQLConnection and some SQL.

• It assumes that the SQL is structured as "Select SomeFields from SomeTable".
• It executes the command's ExecuteReader function.
• It parses the result provided by ExecuteReader into a known structure
• It returns the known structure

The problem I'm having is that occasionally the ExecuteReader function in GetRecord appears to be retrieving the result one would expect from GetField

This then falls over trying to parse the data into the known structure.

I cannot reproduce this reliably and so I'm asking for help.

Does anyone have any ideas why this might be happening?

FWIW my back end SQL is SQL2008

Update: FWIW, the objects which开发者_高级运维 own these methods, have their own connection field which is initialized to a new SQLConnection object which itself is fed a connection string from a central location.

Update: Here is an example of the sort of DA object I'm talking about.

Imports System.Data.SqlClient
Public Module SomeModule
    Public Function GlobalConnection() As SqlConnection
        Return New SqlConnection(GetSQLStringFromConfig())
    End Function
End Module
Public Class ExampleDA
    Protected Con As SqlConnection
    Public Sub New()
        Me.Con = GlobalConnection()
    End Sub
    Public Function GetRecord(ByVal SQL As String) As String()
        Dim Close As Boolean = EnsureConnectionOpen(Con)
        Dim dc As New SqlCommand(SQL, Con)
        Try
            Dim DcExecuteReader As SqlDataReader
            If Close Then
                DcExecuteReader = dc.ExecuteReader(CommandBehavior.CloseConnection)
            Else
                DcExecuteReader = dc.ExecuteReader()
            End If
            ' ToStringArray doesn't exist but it gets the general point across.
            Return DcExecuteReader.ToStringArray() 
        Catch ex As SqlException
            Throw
        End Try
    End Function

    Public Function GetField(ByVal SQL As String, ByVal DefaultValue As Object) As Object
        Dim Close As Boolean = EnsureConnectionOpen(Con)
        Dim dc As SqlCommand = New SqlCommand(SQL, Con)
        Dim Result As Object = GetField(dc, DefaultValue)
        If Close Then Con.Close()
        Return Result
    End Function

    Private Function GetField(ByVal Command As SqlCommand, Optional ByVal DefaultValue As Object = Nothing) As Object
        Try
            Dim ReturnValue As Object
            Dim Close As Boolean = EnsureConnectionOpen(Command.Connection)
            ReturnValue = Command.ExecuteScalar()
            If ReturnValue Is Nothing Then
                ReturnValue = DefaultValue
            End If
            If Close Then
                Command.Connection.Close()
            End If
            If IsDBNull(ReturnValue) Then
                Return DefaultValue
            Else
                Return ReturnValue
            End If
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
            Throw
        End Try
    End Function
    Private Function EnsureConnectionOpen(ByRef Con As SqlConnection) As Boolean
        If Con.State <> ConnectionState.Open Then
            Con.Open()
            Return True
        End If
        Return False
    End Function
End Class

How are you handling connections the database?

One database connections = one process for SQL and these are not shared in anyway. To get "mixed" results will require that your are overwriting some context of a connection that is shared.

In other words, SQLConnection is not thread safe

How is the connection stored/shared between the functions?

Are you running under MARS? If not, try enabling it in your connection string (MultipleActiveResultSets=True).

Note that IIS may/will switch the execution context from one thread to another at its own will during execution. As such, any shared connection will eventually be shared between two threads and may fail. The use of MARS and/or explicitly new'ed connections should alleviate this.

You have to use one connection for one thing at one time, otherwise you may end up with unwanted results. Suppose someone opens a transaction on the connection without the other func knowing about?

You should always call GetConnection, since the backend does connection-pooling anyways. So performance-wise, it will most likely not matter.

hth

Mario

OK, that's asking for trouble! Rewrite your GetXXX methods along these lines (sorry for C# syntax but I am not familiar with VB ;-) )

GetField( string sqlCommand )
{
   SqlConnection conn = new SqlConnection( connectionString );
   conn.Open();

   try
   {
       using ( SqlCommand cmd = conn.CreateCommand() )
       {
           cmd.CommandText = cmd;

           cmd.ExecuteScalar();


           //here be your code..

       }

   }
   finally
   { conn.Close();
   }
}

Do not store connections, do not do anything fency with them. Open and close as needed, the library does a command pooling behind the scenes!

hth

Mario