[RiversRule]

'The following codes are samples from the application which emulate the problem
'we are experiencing.  Essentially, the code seems to work in that the relevant 
'data tier elements appear to be 'switched off' as required, however, the worker thread
'does not return control to the UI for a some time afterwards. This does not cause 
'a problem where small data periods (the primary SQL criteria is a time interval) are 
'involved,however as the time interval and the amount of data involved increases
'so does the time delay between aborting the query and control returning to the UI.
'The delay for say 6 months worth of data can be several minutes, which is not 
'acceptable to the user-base

'This method is from the data tier of the application architecture
'and is intended to allow us to abort an executing query.
'The data tier object in this case is SCADAEventDB.  The class has
'overloaded methods which allow us to pass and request either dataset
'content or datareader based content, hence the code used.
Class: SCADAEventDB
Public Overridable Sub StopExecute()
	If Not _myDataReader Is Nothing Then
		CType(_myDataReader, IDisposable).Dispose()
	End If
	If Not _myDataSet Is Nothing Then
		CType(_myDataSet, IDisposable).Dispose()
	End If
	If Not _myCommand Is Nothing Then
		_myCommand.Cancel()
	End If
	_myConnection.Close()
	_myDataReader = Nothing
	_myConnection = Nothing
	_myDataAdapter = Nothing
	_myDataSet = Nothing
End Sub

'This method is from the middle tier of the application architecture, its purpose is 
to allow us to abort an executing SQL query within the affected data tier object
Class: SCADAEventManager
Public Overridable Sub StopExecute()
	'_myScadaEventDB is an instance of the relevant data object within the data tier 
	If Not _myScadaEventDB Is Nothing Then
		_myScadaEventDB.StopExecute()
		_myScadaEventDB = Nothing
		_myDataset = Nothing
	End If
End Sub


'This method is from the data tier of the application architecture, its purpose is 
'to allow us to count the number of records to be returned by a SQL statement
Public Function CountRecords(ByVal SQLStatement) As DataSet

	Dim myDataset As New DataSet
	_myConnection = New OracleClient.OracleConnection
	_myConnection.ConnectionString = _connectStr

	Try
		_myDataAdapter = New OracleDataAdapter(SQLStatement, _myConnection)
		_myDataAdapter.Fill(myDataset)
		_myConnection.Close()
		Catch ex As Exception
		Throw ex
		Finally
		CType(_myConnection, IDisposable).Dispose()
	End Try

	Return myDataset

	GC.Collect()

End Function

'This method is from the middle tier of the application architecture, its purpose is 
'to allow us to field the result of the call to the CountRecords method in the data tier
'(see above)
Public Sub CountRecords(ByVal queryType As Integer)

	_myDataset = New DataSet
	Try
		_myDataset = _myScadaEventDB.CountRecords(_sqlText)
		If _myDataset.Tables(0).Rows.Count > 0 Then
			If queryType = Enums.QueryType.General Then
				_recordCount = _myDataset.Tables(0).Rows(0).Item("RecordCount")
			Else
				_recordCount = _myDataset.Tables(0).Rows.Count
			End If
		Else
			_recordCount = 0
		End If
	Catch ex As Exception
		Throw ex
	Finally

	End Try

End Sub


'This sub is launched from a button on the main query screen
'the sub first launches the StopExecute method for the eventmanager
'then dependant on the IsBusy state of the background workers
'issues CancelAsyncs for and disposes of the background worker instances
Private Sub StopQuery()
If Not _Stopped Then
	'_eventMngr is an object existing in the middle tier of the application architecture
	'and incorporates all the business rules for dealing with elements of the Data Access Layer
	If Not _eventMngr Is Nothing Then
		_eventMngr.StopExecute()
		_eventMngr = Nothing
	End If
	Beep()
	ReviseStatus(1, "Current Query: '" & _queryTitle & "' - " & Len(uxSQLStatementText.Text) & " Characters", Color.Black)
	ReviseStatus(2, "Query aborted!  Please wait for background process to complete...", Color.Red)
	uxProgressBar.Visible = False
	If uxBWScan.IsBusy Then
		uxBWScan.CancelAsync()
		uxBWScan.Dispose()
	End If
	If uxBWFetch.IsBusy Then
		uxBWFetch.CancelAsync()
		uxBWFetch.Dispose()
	End If
End If

_Stopped = True

End Sub


'The following two methods are from the main query application and consist of 
'code which are implicitly bound to the uxBWSan backgroundworker object.  
'Essentially we have the same problem with aborting both the scan and fetch 
'backgroundworker processes, however for brevity and practicallity I have included 
'only the scan element of the process

'Event search is launched by the main application screen and initiates the call to
'the uxBWScan background worker
Private Sub EventSearch(ByVal mySQL As String)

	Const moduleName As String = "EventSearch"

	Try
		_eventMngr = New ScadaEventManager(_CurrentDB, _userName, _myPwd)
		_eventMngr.SQL = mySQL

		'Set the flag and stop query tool status accordingly
		_Stopped = False
		uxStopQueryTool.Enabled = True

		'activate the timer object to ensure that query menu and tools 
		'remain disabled until all background processing is complete
		uxBackWorkTimer.Enabled = True

		Call ReviseStatus(2, "Scanning for data.  Please wait...", Color.Black, True, True)

		'Force the thread to sleep for half a second so the user can see the scanning state taking place
		Threading.Thread.Sleep(500)

		'Launch the background worker to retrieve a count of the required data from the database
		uxBWScan.RunWorkerAsync(_eventMngr)
	Catch ex As Exception

		MsgBox(ex.Message, MsgBoxStyle.Exclamation, My.Application.Info.ProductName)
		LogMessage(ex.Message & ".  Thrown in module " & Me.Name.ToString & "." & moduleName)

	Finally

	End Try

End Sub


Private Sub uxBWScan_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles uxBWScan.DoWork

	Try
		e.Argument.CountRecords(_queryTypeID)
		e.Result = e.Argument.RecordCount

	Catch ex As Exception
		'Likely that the query has been aborted by the user so just swallow the exception
		If e.Result Is Nothing Then
			'MsgBox("Scan aborted by user", MsgBoxStyle.Exclamation, My.Application.Info.ProductName)
		Else
			MsgBox(ex.Message, MsgBoxStyle.Exclamation, My.Application.Info.ProductName)
		End If
	Finally
	End Try
End Sub

[JackOfAllTrades]

This looks like VB.NET, so I'll move it there.

Are you receiving any errors? Does this code not work that way you intended it? When asking for help there are a couple items that are vital in order for someone to properly help you:

• Post the code you're having problems with
  
• Post the exact error you're receiving, if you are receiving one
  
• If no error explain what the code is doing versus what you want it to do
  
• Post your question in the body of your post, not the description field

[RiversRule]

RiversRule, on 28 Oct, 2009 - 09:21 AM, said:

'The following codes are samples from the application which emulate the problem
'we are experiencing.  Essentially, the code seems to work in that the relevant 
'data tier elements appear to be 'switched off' as required, however, the worker thread
'does not return control to the UI for a some time afterwards. This does not cause 
'a problem where small data periods (the primary SQL criteria is a time interval) are 
'involved,however as the time interval and the amount of data involved increases
'so does the time delay between aborting the query and control returning to the UI.
'The delay for say 6 months worth of data can be several minutes, which is not 
'acceptable to the user-base

'This method is from the data tier of the application architecture
'and is intended to allow us to abort an executing query.
'The data tier object in this case is SCADAEventDB.  The class has
'overloaded methods which allow us to pass and request either dataset
'content or datareader based content, hence the code used.
Class: SCADAEventDB
Public Overridable Sub StopExecute()
	If Not _myDataReader Is Nothing Then
		CType(_myDataReader, IDisposable).Dispose()
	End If
	If Not _myDataSet Is Nothing Then
		CType(_myDataSet, IDisposable).Dispose()
	End If
	If Not _myCommand Is Nothing Then
		_myCommand.Cancel()
	End If
	_myConnection.Close()
	_myDataReader = Nothing
	_myConnection = Nothing
	_myDataAdapter = Nothing
	_myDataSet = Nothing
End Sub

'This method is from the middle tier of the application architecture, its purpose is 
to allow us to abort an executing SQL query within the affected data tier object
Class: SCADAEventManager
Public Overridable Sub StopExecute()
	'_myScadaEventDB is an instance of the relevant data object within the data tier 
	If Not _myScadaEventDB Is Nothing Then
		_myScadaEventDB.StopExecute()
		_myScadaEventDB = Nothing
		_myDataset = Nothing
	End If
End Sub


'This method is from the data tier of the application architecture, its purpose is 
'to allow us to count the number of records to be returned by a SQL statement
Public Function CountRecords(ByVal SQLStatement) As DataSet

	Dim myDataset As New DataSet
	_myConnection = New OracleClient.OracleConnection
	_myConnection.ConnectionString = _connectStr

	Try
		_myDataAdapter = New OracleDataAdapter(SQLStatement, _myConnection)
		_myDataAdapter.Fill(myDataset)
		_myConnection.Close()
		Catch ex As Exception
		Throw ex
		Finally
		CType(_myConnection, IDisposable).Dispose()
	End Try

	Return myDataset

	GC.Collect()

End Function

'This method is from the middle tier of the application architecture, its purpose is 
'to allow us to field the result of the call to the CountRecords method in the data tier
'(see above)
Public Sub CountRecords(ByVal queryType As Integer)

	_myDataset = New DataSet
	Try
		_myDataset = _myScadaEventDB.CountRecords(_sqlText)
		If _myDataset.Tables(0).Rows.Count > 0 Then
			If queryType = Enums.QueryType.General Then
				_recordCount = _myDataset.Tables(0).Rows(0).Item("RecordCount")
			Else
				_recordCount = _myDataset.Tables(0).Rows.Count
			End If
		Else
			_recordCount = 0
		End If
	Catch ex As Exception
		Throw ex
	Finally

	End Try

End Sub


'This sub is launched from a button on the main query screen
'the sub first launches the StopExecute method for the eventmanager
'then dependant on the IsBusy state of the background workers
'issues CancelAsyncs for and disposes of the background worker instances
Private Sub StopQuery()
If Not _Stopped Then
	'_eventMngr is an object existing in the middle tier of the application architecture
	'and incorporates all the business rules for dealing with elements of the Data Access Layer
	If Not _eventMngr Is Nothing Then
		_eventMngr.StopExecute()
		_eventMngr = Nothing
	End If
	Beep()
	ReviseStatus(1, "Current Query: '" & _queryTitle & "' - " & Len(uxSQLStatementText.Text) & " Characters", Color.Black)
	ReviseStatus(2, "Query aborted!  Please wait for background process to complete...", Color.Red)
	uxProgressBar.Visible = False
	If uxBWScan.IsBusy Then
		uxBWScan.CancelAsync()
		uxBWScan.Dispose()
	End If
	If uxBWFetch.IsBusy Then
		uxBWFetch.CancelAsync()
		uxBWFetch.Dispose()
	End If
End If

_Stopped = True

End Sub


'The following two methods are from the main query application and consist of 
'code which are implicitly bound to the uxBWSan backgroundworker object.  
'Essentially we have the same problem with aborting both the scan and fetch 
'backgroundworker processes, however for brevity and practicallity I have included 
'only the scan element of the process

'Event search is launched by the main application screen and initiates the call to
'the uxBWScan background worker
Private Sub EventSearch(ByVal mySQL As String)

	Const moduleName As String = "EventSearch"

	Try
		_eventMngr = New ScadaEventManager(_CurrentDB, _userName, _myPwd)
		_eventMngr.SQL = mySQL

		'Set the flag and stop query tool status accordingly
		_Stopped = False
		uxStopQueryTool.Enabled = True

		'activate the timer object to ensure that query menu and tools 
		'remain disabled until all background processing is complete
		uxBackWorkTimer.Enabled = True

		Call ReviseStatus(2, "Scanning for data.  Please wait...", Color.Black, True, True)

		'Force the thread to sleep for half a second so the user can see the scanning state taking place
		Threading.Thread.Sleep(500)

		'Launch the background worker to retrieve a count of the required data from the database
		uxBWScan.RunWorkerAsync(_eventMngr)
	Catch ex As Exception

		MsgBox(ex.Message, MsgBoxStyle.Exclamation, My.Application.Info.ProductName)
		LogMessage(ex.Message & ".  Thrown in module " & Me.Name.ToString & "." & moduleName)

	Finally

	End Try

End Sub


Private Sub uxBWScan_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles uxBWScan.DoWork

	Try
		e.Argument.CountRecords(_queryTypeID)
		e.Result = e.Argument.RecordCount

	Catch ex As Exception
		'Likely that the query has been aborted by the user so just swallow the exception
		If e.Result Is Nothing Then
			'MsgBox("Scan aborted by user", MsgBoxStyle.Exclamation, My.Application.Info.ProductName)
		Else
			MsgBox(ex.Message, MsgBoxStyle.Exclamation, My.Application.Info.ProductName)
		End If
	Finally
	End Try
End Sub

[RiversRule]

Apologies for any confusion caused, I recognised that I had posted to the wrong forum when I logged in this morning, and prompty re-submitted this item under the VB.NET forum, therefore this particular thread can be deleted. Is this something I can do or is it the responsibility of the moderators on the forum?