Parse command line from a third party program scheduler in VB.NET

Question

I have problem creating a console vb.net program that executes a stored procedure in Oracle database. I can do this if the stored procedure will be hard coded in the code. The problem is, the stored procedure will need to be place in a parameter from vendor program. Currently, we use a VB6 program to do this. Now, they want to convert this vb6 program into .NET but I don't know how to do it. I tried the approach below but it doesn't pass the command parameter from the 3rd party program.

Example. This is the screenshot of the 3rd party program:

The PLSQLSUB.exe program is a vb6 that invokes stored procedure CNC_UNLOCK_USERS.

This is the VB6 code, this is currently working but I don't know how to pass the external parameter in VB.NET

Private Sub Form_Load()
  Dim cnTrecs As New ADODB.Connection
  Dim QY As New ADODB.Command

  Dim I As Integer

  Dim Procedures(10) As String

  Dim ColonPos As Integer

  Dim CommLine As String
  Dim CommLength As Integer
  Dim StartPos As Integer
  Dim FileName As String
  Dim CheckForFile As String
  Dim User As String
  Dim Pass As String
  Dim Userid As String
  Dim Password As String

  Dim PLSCount As Integer

  PLSCount = 0

  CommLine = Command()
  CommLength = Len(Trim(CommLine))

  If Trim(CommLine) <> "" Then
    StartPos = 1
    For I = 1 To 10
      ColonPos = InStr(StartPos, Trim(CommLine), ";")

      If ColonPos > 0 Then
        Procedures(I) = Mid(CommLine, StartPos, ColonPos - StartPos)
        PLSCount = PLSCount + 1
        StartPos = ColonPos + 1
      ElseIf I > 1 Then
        If (CommLength - StartPos) > 0 Then
          Procedures(I) = Mid(CommLine, StartPos, CommLength - StartPos)
          MsgBox (Procedures(I))
          PLSCount = PLSCount + 1
          Exit For
        Else
          Exit For
        End If
      Else
        Procedures(I) = Trim(CommLine)
        PLSCount = PLSCount + 1
        Exit For
      End If
    Next
    Exit Sub
  Else
    Unload Me
    Exit Sub
  End If

  CheckForFile = Dir("C:\VB\vbtext1.txt")
  If CheckForFile <> "" Then FileName = "d:\VB\vbtext1.txt"
    Open FileName For Input As #5   ' Open UserID and Password file.
      Line Input #5, Userid
      Line Input #5, Password
    Close #5
  End If
  User = Mid(Userid, 1) ' Set database userID
  Pass = Mid(Password, 1) ' Set database password

  cnTrecs.Open "DSN=PLSQLSUB;" _
                & "Uid=" _
                & Trim$(User) & ";PWD=" _
                & Trim$(Pass)

  For I = 1 To PLSCount ' Loop until end of plsql procedures
    Print #1, "The Stored Procedure " & Procedures(I) & " was submitted for execution     
on/at "; Format(Now, "dd-mmm-yyyy hh:mm:ss")

    Set QY.ActiveConnection = cnTrecs
    QY.CommandType = adCmdStoredProc
    QY.CommandText = Procedures(I)
    QY.Execute
    Print #1, "The Stored Procedure " & Procedures(I) & " completed execution on/at ";   
    Format(Now, "dd-mmm-yyyy hh:mm:ss")
    Print #1, " "
  Next I

  If cnTrecs.State = adStateOpen Then
    cnTrecs.Close
  End If
End Sub

A friend told me about using the below code but I could not able to pass the stored procedure. Any help will be greatly appreciated.

Dim CommandLineArgs As System.Collections.ObjectModel.ReadOnlyCollection(Of String) = My.Application.CommandLineArgs

For i As Integer = 0 To CommandLineArgs.Count - 1
   MessageBox.Show("The stored procedure is: " + CommandLineArgs(i))
Next

Answer 1

If I understood you correctly, you want to convert existing VB6 to VB.NET, i.e. run a stored procedure from VB.NET. Because your SP does not have parameters, use below code:

Using conn As New OracleConnection("Server=YourOracle;Uid=uid;Pwd=pwd")
  conn.Open()
  Dim cmd As New OracleCommand
  cmd.Connection = conn
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = "CNC_UNLOCK_USERS"
  Try
    cmd.ExecuteNonQuery()
  Catch ex As OracleException
    'do something
  End Try
End Using

Based on Executing stored procedure in vb.net (if you later need parameters, please refer to this link). User ID and password are stored in d:\VB\vbtext1.txt, according to your VB6 code. Not sure why it's checking similar location on C:, and never assigns the FileName.

If you need to pass name of stored procedure through command line, you need to switch Startup object to Sub Main (project properties -> Application tab), and then place similar code into a new class:

Public Class Class1
  Shared Sub Main(args() As String)
    'parse args and save it somewhere
    Form1.ShowDialog()
  End Sub
End Class

Before showing a form, make sure you save args(0) as name of your stored procedure to be executed. Or you can have a custom ShowDialog on the form, that takes a parameter of type String, and call that. I would recommend you convert to console application, since it will now show its UI anyway. Then you simply execute above code in Sub Main, changing this line:

cmd.CommandText = "CNC_UNLOCK_USERS"

to this:

cmd.CommandText = args(0)

Answer 2

You can use the Environment.GetCommandLineArgs Method to retrive the CommandLine arguments. In further looking at your question you are doing something similar already, the question I have are you trying to import the StoredProcedure or the Name of the StoredProcedure to be run.