Currently there is no data adapter for Linq to Oracle. This means when connecting to Oracle you need to use ADO.NET or OLDDB. I have been trying to connect to a function that look like
FUNCTION getContract(i_businessID IN tblContract.BusinessID%TYPE,
i_periodFrom IN VARCHAR2,
i_periodTo IN VARCHAR2)
RETURN NUMBER;
The OLEDB Way
Use the following imports:
Imports System.Data.Common
Imports System.Data
Imports System.Data.OracleClient
Imports System.Data.OleDb
Imports System.Data.OracleClient.OracleType
Setup the connection string:
_DBConnStr = "Provider=MSDAORA;Data Source=" + ServerName + ";User Id=" + UserName + ";Password=" + Password + ""
The the function looks like:
Public Function GetContract(ByRef SPInput As sp_GetContractInfo.TableStructure, ByRef Message As String) As Integer
Try
Dim cnOra As New OleDbConnection
cnOra.ConnectionString = _DBConnStr
cnOra.Open()
Dim oCmd As New OleDbCommand
oCmd.CommandText = "{? = call Schema.Package.getContract(?,
Dim o_retValParam As OleDbParameter
o_retValParam = New OleDbParameter("RETVAL", System.Data.OleDb.OleDbType.VarNumeric)
o_retValParam.Direction = ParameterDirection.ReturnValue
o_retValParam.Size = 32
oCmd.Parameters.Add(o_retValParam)
Dim i_businessIDParam As OleDbParameter
i_businessIDParam = New OleDbParameter("i_businessID", System.Data.OleDb.OleDbType.VarChar)
i_businessIDParam.Direction = ParameterDirection.Input
i_businessIDParam.Size = SPInput.i_BusinessId.Length
oCmd.Parameters.Add(i_businessIDParam)
'i_businessID IN 'tblContract.BusinessID%TYPE
Dim i_periodFromParam As OleDbParameter
i_periodFromParam = New OleDbParameter("i_periodFrom", System.Data.OleDb.OleDbType.VarChar)
i_periodFromParam.Direction = ParameterDirection.Input
i_periodFromParam.Size = 19
oCmd.Parameters.Add(i_periodFromParam)
' i_periodFrom IN VARCHAR2,
Dim i_periodToParam As OleDbParameter
i_periodToParam = New OleDbParameter("i_periodTo", System.Data.OleDb.OleDbType.VarChar)
i_periodToParam.Direction = ParameterDirection.Input
i_periodToParam.Size = 19
oCmd.Parameters.Add(i_periodToParam)
' i_periodTo IN VARCHAR2,
Dim i_periodFrom As String = SPInput.i_PeriodFrom.ToString("yyyy-MM-dd") + " 00:00:00"
Dim i_periodTo As String = SPInput.i_PeriodTo.ToString("yyyy-MM-dd") + " 23:59:59"
oCmd.Parameters("i_businessID").Value = SPInput.i_BusinessId
oCmd.Parameters("i_periodFrom").Value = i_periodFrom
oCmd.Parameters("i_periodTo").Value = i_periodTo
oCmd.Connection = cnOra
Dim Result As Integer
oCmd.ExecuteNonQuery()
oCmd.ToString()
Return Result
Catch ex As Exception
Message= ex.ToString
Return -1
End Try
Return True
End Function
The ADO.NET way
Imports System
Imports System.Data.OracleClient
Imports System.Data
_DBConnStr = "Data Source=" + ServerName + ";User ID=" + UserName + ";Password=" + Password + ""
Then the function looks like
Public Function GetContract(ByRef SPInput As sp_GetContractInfo.TableStructure, ByRef Message As String) As Integer
Try
Dim cnOra As New OracleCommand
Dim conOra As New OracleConnection(_DBConnStr)
cnOra.Connection = conOra
cnOra.CommandText = "SchemaName.PackageName.getContract"
cnOra.CommandType = CommandType.StoredProcedure
cnOra.Parameters.Add("RETVAL", OracleType.Number).Direction = ParameterDirection.ReturnValue
cnOra.Parameters.Add("i_businessID", OracleType.VarChar, SPInput.i_BusinessId.Length).Direction = ParameterDirection.Input
cnOra.Parameters.Add("i_periodFrom", OracleType.VarChar, 19).Direction = ParameterDirection.Input
cnOra.Parameters.Add("i_periodTo", OracleType.VarChar, 19).Direction = ParameterDirection.Input
Dim i_periodFrom As String = SPInput.i_PeriodFrom.ToString("yyyy-MM-dd") + " 00:00:00"
Dim i_periodTo As String = SPInput.i_PeriodTo.ToString("yyyy-MM-dd") + " 23:59:59"
cnOra.Parameters("i_businessID").Value = SPInput.i_BusinessId
cnOra.Parameters("i_periodFrom").Value = i_periodFrom
cnOra.Parameters("i_periodTo").Value = i_periodTo
cnOra.Connection.Open()
cnOra.ExecuteNonQuery()
Dim Result As Integer
Result = cnOra.Parameters("RETVAL").Value
Return Result
Catch ex As Exception
Message = ex.Message
Return -1
End Try
Return True
End Function
