Clearcode
March 14th, 2005, 08:41 AM
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
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