Running and controling a console app asynchronously

[Clearcode]

I have written a program which reads it sql scripts and passes them to the command line to be executed by OSQL.exe

The testbed code is like:-

        Dim _DDLExecutor As New DDLExecutor("osql.exe", "-Smyserver -E")
        _DDLExecutor.Execute()
        _DDLExecutor.AddCommand("USE master")
        _DDLExecutor.AddCommand("go")
        _DDLExecutor.AddCommand("select * sysobjects")
        _DDLExecutor.AddCommand("go")
        While _DDLExecutor.PendingCommands
            Application.DoEvents()
        End While
        _DDLExecutor.AddCommand("exit")

and the definition of the clever asynchronous ddl executor class is:-

Imports System.Diagnostics
Imports System.IO
Imports System.Threading
Imports System.ComponentModel
Imports System.Collections

'\\ --[DDLExecutor]--------------------------------------------
'\\ Class to run DDL into a database
'\\ (c) 2005 - Merrion Computing Ltd
'\\ -----------------------------------------------------------
#Region "Private members"

    Private _CommandLine As String
    Private _Parameters As String

    Private _ProgramThread As Thread

    Private _StdOutputHandler As Thread
    Private _StdErrHandler As Thread
    Private _StdInputHandler As Thread

    Private _pRun As Process

    Private _IsRunning As Boolean

    Private _Input As New Queue()

#End Region

#Region "Public interface"
    '\\ --[Execute]----------------------------------------------
    '\\ Runs the DDL into the database
    '\\ ---------------------------------------------------------
    Public Sub Execute()

        _ProgramThread = New Thread(AddressOf ExecuteAsynch)
        _ProgramThread.Start()

    End Sub

    Private Sub ExecuteAsynch()
        Dim piRun As New ProcessStartInfo()

        With piRun
            .RedirectStandardInput = True
            .RedirectStandardOutput = True
            .RedirectStandardError = True
            .FileName = _CommandLine
            .Arguments = _Parameters '\\ -S{servername} -U{username} etc.
            .UseShellExecute = False '\\ Wish to pass input in from a stream...
        End With

        _pRun = New Process()
        With _pRun
            .StartInfo = piRun
            '\\ Start the ISQL/OSQL process
            _IsRunning = True
            Try
                .Start()
            Catch e As Win32Exception
                If DatabaseInstallerSettings.ApplicationTracing.TraceError Then
                    Trace.WriteLine(e.Message, Me.GetType.ToString)
                    Trace.Flush() '\\ Save the trace file because the app is probably going to close now...
                End If
                Throw
            End Try
            '\\ Start all the stdIn, stdOut and StdErr threads...
            _StdErrHandler = New Thread(AddressOf HandleStdError)
            _StdErrHandler.Start()
            _StdOutputHandler = New Thread(AddressOf HandleStandardOutput)
            _StdOutputHandler.Start()
            _StdInputHandler = New Thread(AddressOf HandleStandardInput)
            _StdInputHandler.Start()

            .WaitForExit()
            _IsRunning = False
        End With
    End Sub

    Public Sub AddCommand(ByVal Command As String)
        _Input.Enqueue(Command)
    End Sub

    Public ReadOnly Property PendingCommands() As Boolean
        Get
            Return (_Input.Count > 0)
        End Get
    End Property

    Private Sub HandleStdError()
        Dim sError As String

        While _IsRunning
            Try
                sError = _pRun.StandardError.ReadLine
                While Not sError Is Nothing
                    If DatabaseInstallerSettings.ApplicationTracing.TraceError Then
                        Trace.WriteLine(sError, _pRun.ProcessName)
                    End If
                    sError = _pRun.StandardError.ReadLine
                End While
            Catch e As Exception
                If DatabaseInstallerSettings.ApplicationTracing.TraceError Then
                    Trace.WriteLine(e.Message, Me.GetType.ToString)
                End If
                Throw
            End Try
        End While
        If Not _StdErrHandler Is Nothing Then
            _StdErrHandler.Join()
        End If

    End Sub

    Private Sub HandleStandardOutput()
        Dim sOutput As String

        While _IsRunning
            Try
                sOutput = _pRun.StandardOutput.ReadLine
                While Not sOutput Is Nothing
                    If DatabaseInstallerSettings.ApplicationTracing.TraceVerbose Then
                        Trace.WriteLine(sOutput, _pRun.ProcessName)
                    End If
                    sOutput = _pRun.StandardOutput.ReadLine
                End While
            Catch e As Exception
                If DatabaseInstallerSettings.ApplicationTracing.TraceError Then
                    Trace.WriteLine(e.Message, Me.GetType.ToString)
                End If
                Throw
            End Try
        End While
        If Not _StdOutputHandler Is Nothing Then
            _StdOutputHandler.Join()
        End If
    End Sub

    Private Sub HandleStandardInput()

        Dim sCommand As String

        While _IsRunning
            If _Input.Count > 0 Then
                sCommand = _Input.Dequeue
                Try
                    _pRun.StandardInput.WriteLine(sCommand)
                Catch e As Exception
                    If DatabaseInstallerSettings.ApplicationTracing.TraceError Then
                        Trace.WriteLine(e.Message, Me.GetType.ToString)
                    End If
                    Throw
                End Try
            Else
                '\\ Wait for more commands to be added....
                _StdInputHandler.Sleep(100)
            End If
        End While
        If Not _StdInputHandler Is Nothing Then
            _StdInputHandler.Join()
        End If

    End Sub
#End Region

#Region "Constructors"
    Public Sub New(ByVal CommandLine As String, ByVal Parameters As String)
        _CommandLine = CommandLine
        _Parameters = Parameters
    End Sub
#End Region

End Class

As you can see it starts the executable (osql) and then starts three seperate threads which handle the standard input, standard output and standard error. This is working peachily except for one thing :- if the "exit" command is issued while there is still standard output or standard error to be read then those fall over with "the process has exited" errors.

Any ideas how I can modify Public ReadOnly Property PendingCommands() As Boolean such that it returns false unless all the stdError and stdOutput has been read in addition to its current functionality?

Thanks in advance,
Duncan

[Clearcode]

unfortunately one of the commands I am passing in is "exit" which causes ISQL to exit.
At this point the process is no longer executing but due to the asynchronous nature of the process it might still be having it's stdOut or stdErr channels read. This leads to a run time error.

So I have included a property PendingCommands such that I do not send the "exit" function until this is false.

How do I modify that property so it isn't true unless (a) there are no pending commands and (b) neither the stdOut nor stdErr channels are being read?

[Krzemo]

Hmmm ... Interesting piece of code...

But maybe it would be better if U just forget about OSQL.exe?

U can start "worker thread" which opens database connection and sends commands directly to SQL server.
It should be simpler than "osql.exe" solution and IMHO better (grater flexibility, more precise controll,greater speed and extensibility).

Just think about it for a while - maybe it can do better for U... if not, than simply forget about my tip

Best regards,
Krzemo.

[Clearcode]

What would you use to directly control SQL Server (or Sybase XI)?
I can't use SQLDMO as that is not a distributable component.

(The reason I am making this code is to have a database installer program that can create databases and then add tables etc as part of an application installation....)

[Krzemo]

What would you use to directly control SQL Server (or Sybase XI)?
...
(The reason I am making this code is to have a database installer program that can create databases and then add tables etc as part of an application installation....)

Than my solution would be better.

Just use normal .NET data native providers (or OLEDB ).

For example put all your scripts in one file (the same format as in Query Analyzer). It could be compressed and embeded as resource.
Than create stringbuilder.
Than read line by line (appending string to stringbuilder). When line starts with "GO" than discard that line, send command from stringbuilder to SQL server, write results/messages to the log file and prepare stringbuilder for new commands.
That way to the end.
It should be enougth in your case

But that way U can develop also your own commands (not only GO) which can for example starts more threads, etc - just whole workflow like in DTS.

Best regards,
Krzemo.