Please take a stab at this VB.Net Oracle-related sample and help me with String.Format.
- by Hamish Grubijan
If the database is not Oracle, it is MS SQl 2008.
My task: if Oracle, add two more parameters when calling a stored proc.
Oracle and MSFT stored procs are generated; Oracle ones have 3 extra parameters:
Vret_val out number,
Vparam2 in out number,
Vparam3 in out number,
... the rest
(The are not actually named Vparam2 and Vparam3, but this should not matter).
So, the code for a helper VB.Net class that calls a stored proc:
Imports System.Data.Odbc
Imports System.Configuration
Dim objCon As OdbcConnection = Nothing
Dim objAdapter As OdbcDataAdapter
Dim cmdCommand As New OdbcCommand
Dim objDataTable As DataTable
Dim sconnection As String
Try
sconnection = mConnectionString
objAdapter = New OdbcDataAdapter
objCon = New OdbcConnection(sconnection)
objCon.Open()
objAdapter.SelectCommand = cmdCommand
objAdapter.SelectCommand.Connection = objCon
objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
objAdapter.SelectCommand.CommandTimeout = Globals.mReportTimeOut
If Not mIsOracle Then
objAdapter.SelectCommand.CommandText = String.Format("{{call {0}}}", spName)
Else
Dim returnValue As New OdbcParameter
returnValue.Direction = ParameterDirection.Output
returnValue.ParameterName = "@Vret_val"
returnValue.OdbcType = OdbcType.Numeric
objAdapter.SelectCommand.Parameters.Add(returnValue)
objAdapter.SelectCommand.CommandText = String.Format("{{call {0}(?)}}", spName)
End If
Try
objDataTable = New DataTable(spName)
objAdapter.Fill(objDataTable)
Catch ex As Exception
...
Question: I am puzzled as to what String.Format("{{call {0}(?)}}", spName) does, in particular the (?) part. My understanding of the String.Format is that it will simply replace {0} with spName. The {{, }}, and (?) do throw me off because { reminds me of formatting, (?) hints at some advanced regex use.
Unfortunately I am getting little help from a key person who is on vacation without a leash [smart]phone.
I am guessing that I simply add 5 more lines for each additional parameter, and change String.Format("{{call {0}(?)}}", spName) to String.Format("{{call {0}(?,?,?)}}", spName). I forgot to mention that I am coding this "blindly" - I have a compiler to help me, but no environment set up to test this.
This will be over in a few days, but I need to do my best to try finishing it on time :)
Thanks.
