How to abort a SQL Query Via System.Data.OracleClient

Using BackgroundWorker CancelAsync to abort a SQL Query

Page 1 of 1

0 Replies - 1049 Views - Last Post: 29 October 2009 - 04:45 AM Rate Topic: -----

#1 RiversRule  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 28-October 09

How to abort a SQL Query Via System.Data.OracleClient

Posted 29 October 2009 - 04:45 AM

'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
'The CountRecords method accepts a SQL statement in the form 'SELECT Count(*) from ...'
'Class: SCADAEventDB
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)
'Class: SCADAEventManager
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



Is This A Good Question/Topic? 0
  • +

Page 1 of 1