Running Stored Procedures in Async?

[adshocker]

Hi,

I created a Sub in my WinForm which basically executes a stored procedure using Connections, Commands, Parameters etc. Command.CommandType is set to StoredProcedures always but command text may vary. Now I want to be able to run more than 1 StoredProcedure in parallel but I also need a way to terminate a running StoredProcedure if needed.

What methods / approaches can I use for this requirement?

I'm currently using Visual Studion 2008 Professional and Oracle Express Edition 11g for my database.

Thanks.

[jmcilhinney]

The SqlClient provider for SQL Server that is built into the .NET Framework has the standard ExecuteReader and ExecuteNonQuery methods on its SqlCommand class, which are synchronous, and it also has asynchronous analogues: BeginExecuteReader/EndExecuteReader and BeginExecuteNonQuery/EndExecuteNonQuery. It also has the standard Cancel method. Hopefully you're using ODP.NET from Oracle, so you should check whether that has similar asynchronous methods on its OracleCommand class. If not then you will just have to use the standard synchronous methods and call them on a secondary thread, which you can start any of the usual ways, e.g. BackgroundWorker, ThreadPool, Thread, etc.

[adshocker]

Hi jmcilhinney,

Thanks for the suggestions.

Yes I'm using ODP. I just checked and unfortunately the OracleCommand Class from ODP does not have this method. I am looking at Thread but still somehow challenged on how to be able to manage threads.

[jmcilhinney]

Show me an example of how you would execute this sproc synchronously and I'll show you how to adapt that to execute is asynchronously.

[adshocker]

Show me an example of how you would execute this sproc synchronously and I'll show you how to adapt that to execute is asynchronously.

Hi jmcilhinney,

This is what I had in mind so far. So basically I have a TextBox "ProcedureName" and Button "RunButton". This is just a sample of course. So basically I enter the stored procedure name in the TextBox and click the RunButton and enter another procedure name and click the button again and so on. I am using Thread to be able to do this however, my problem is if I have created several threads, I'm not sure how I can abort previous threads that I've created.

Sub StartProgram(ProcedureName As String)
    Using Connection As New Oracle.DataAccess.Client.OracleConnection(My.MySettings.Default.ConnectionString)
      Connection.Open()
      Using Command As New Oracle.DataAccess.Client.OracleCommand
        With Command
          .CommandType = CommandType.StoredProcedure
          .CommandText = ProcedureName
          .Connection = Connection
          .ExecuteNonQuery()
        End With
      End Using
      Connection.Close()
    End Using
  Catch ex As Exception
    Debug.WriteLine(ex.Message)
  End Try
End Sub

Public Sub RunButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RunButton.Click
  Dim t As New System.Threading.Thread(AddressOf StartProgram)
  Dim args As Object = New Object() {ProcedureName.Text}
  t.Start(args)
End Sub

Thanks.

[jmcilhinney]

You don't want to abort threads. You want to call the Cancel method of the command object. As such, you'll need to store them somewhere that they can be accessed when required. That means that you'll need a collection of some sort assigned to a member variable. The StartProgram method can add the command it creates to that collection so that it can be accessed from another method on another thread to call its Cancel method. Once the command completes, it can be removed from the collection.

[adshocker]

You don't want to abort threads. You want to call the Cancel method of the command object. As such, you'll need to store them somewhere that they can be accessed when required. That means that you'll need a collection of some sort assigned to a member variable. The StartProgram method can add the command it creates to that collection so that it can be accessed from another method on another thread to call its Cancel method. Once the command completes, it can be removed from the collection.

Will the command object have some sort of Unique Identifier that I can store in a database table? Currently this is what I'm doing when running actual Executable files initiated using Process object. Fortunately this returns a PID which I use later on when I need to kill the Process.