Oracle has provided a nice set of plugins to allow easy access to Oracle database components within Visual Studio. I've had a bit of a play around with it over the past couple of weeks while our developers evaluate various technologies, so I've put together some different ways of using the functionality. All of these techniques can be gathered from various places around the internet, but I am mainly putting them here for my own reference so I have everything in one place.
If you have VS2008, you can download the Oracle plugins from Oracle's .NET Developer Center (http://www.oracle.com/technology/software/tech/windows/odpnet/index.html). The latest version (11.1.0.6.21) allows connection to Oracle database versions 9i, 10g and 11g.
Once that is installed, you will find that VS allows you to define a Data Source using Oracle ODP.NET drivers.
Once your Data Source is set up, and you have also defined the dataset you wish to use (consisting of tables and views - no packages/procedures yet, but more on that later), you can then simply drag and drop tables from the dataset onto Window Forms to automatically create databound grids, or drag individual fields to create databound controls (the types can be configured to suit).
On the first addition of a table (or field) onto a Form, a function is also created to automatically fill the control with all records from the table, and a call to this function is placed in the Load method of the Form. This is like performing an EXECUTE_QUERY in Oracle Forms with no DEFAULT_WHERE clause, so you will have to create your own filter function to allow a bit more of a parameterized mechanism. Master-Detail relationships are, however, taken care of automatically behind the scenes - as long as the appropriate foreign keys have been set up. For example, if you drag in the DEPT table to create a datagrid for its data, then drag in the embedded EMP table from within the DEPT dataset, the relationship is recognised and the display is synchronized when DEPT entries are cycled through during runtime.
To add a filtered query, open up the Dataset viewer and right-click on the appropriate table. Choose Add->Query... Then flick through the wizard until you get to the SQL statement. Add a WHERE clause - for example "WHERE DNAME LIKE :deptName" - and a proceed through to the end of the wizard, remembering to give the new filter method an appropriate name (eg FillByDeptName). You can then either replace the method used in the Load routine, or add a search criteria field and button to accept a query from the user, then use the new method from there.
Now, this is all well and good for straight direct table and view access, but what about Stored Procedure, Function and Package access? Indeed, some database developers are not given direct access to the tables, but must instead work through a layer of PL/SQL code to get to the data. So we need to be able to call database code.
First, lets look at how to call a simple SQL statement using Visual Basic.
Private Sub getBonusInfo(ByVal ename As String)
' Define variables
Dim oraconnection As OracleConnection
Dim oracommand As OracleCommand
Dim returnJob As String = ""
Dim returnSal As Decimal
Dim returnComm As Decimal
'Define Oracle database connection
oraconnection = New OracleConnection(My.Settings.STConnectionString)
'Open the connection
oraconnection.Open()
Try
'Create a new Oracle command
oracommand = New OracleCommand()
With oracommand
'Associate the command to the Connection opened previously
.Connection = oraconnection
'Define the type of command
.CommandType = CommandType.Text
'Create the command text
.CommandText = "SELECT job, sal, comm FROM bonus " + _
"WHERE ename = :ename"
'Add an input parameter
.Parameters.Add(New OracleParameter(":ename", _
OracleDbType.Varchar2, _
ParameterDirection.Input)).Value = ename
'Execute the Statement
Dim reader As OracleDataReader = .ExecuteReader()
'Interrogate the response for individual returned data elements
While (reader.Read())
returnJob = reader.GetOracleString(0)
returnSal = reader.GetOracleDecimal(1)
returnComm = reader.GetOracleDecimal(2)
End While
End With
'Close the command
oracommand.Dispose()
Catch ex As OracleException
MsgBox(ex)
End Try
'Use the return data as needed
Me.txtSal.Text = returnSal
Me.txtJob.Text = returnJob
Me.txtComm.Text = returnComm
'Close the database connection
oraconnection.Close()
End Sub
The code contains comments where appropriate, so it is fairly self-explanitory.
Note that there is no data in the BONUS table by default, so you would have to insert some rows to see this working.
Now, calling a database function requires you to know that the first Parameter is always going to be the return parameter.
For example, say we had the following function in the database:
CREATE OR REPLACE FUNCTION getEmpCount(p_deptno IN NUMBER)
RETURN NUMBER
IS
lvn_count NUMBER := 0;
BEGIN
SELECT count(*)
INTO lvn_count
FROM emp
WHERE deptno = p_deptno;
RETURN lvn_count;
END getEmpCount;
Calling the Stored Procedure from VB would look like:
Private Function getEmpCount(ByVal deptno As Integer) As Decimal
' Define variables
Dim oraconnection As OracleConnection
Dim oracommand As OracleCommand
Dim retVal As OracleDecimal = New OracleDecimal
' Function return parameter has arbitrary name
Dim returnParam As OracleParameter = _
New OracleParameter("myReturnValue", OracleDbType.Decimal, 10)
returnParam.Direction = ParameterDirection.ReturnValue
'Define Oracle database connection
oraconnection = New OracleConnection(My.Settings.STConnectionString)
'Open the connection
oraconnection.Open()
Try
'Create a new Oracle command
oracommand = New OracleCommand()
With oracommand
'Associate the command to the Connection opened previously
.Connection = oraconnection
'Define the type of command
.CommandType = CommandType.StoredProcedure
'Create the command text
.CommandText = "getEmpCount"
'return parameter must be added first
.Parameters.Add(returnParam)
.Parameters.Add(New OracleParameter("P_DEPTNO", _
OracleDbType.Decimal)).Value = deptno
.ExecuteNonQuery()
End With
retVal = returnParam.Value
oracommand.Dispose()
Catch ex As OracleException
MsgBox(ex.Message())
End Try
oraconnection.Close()
Return retVal
End Function
Ok, on to the interesting part - User Defined Types.
This method is new to the latest version of ODP.NET (11.1.0.6.21). Previously, User Defined Types could not be handled by VB.
Let's look at a scenario. Say, for whatever reason, we have the requirement to show a big dump of data combining data from more than one table. It would probably be easier to create a view and access it that way, but where would the fun be in that? Lets create a new User Defined Type!
CREATE TYPE comboRecord IS OBJECT(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
MGR NUMBER(4),
MNAME VARCHAR2(10),
HIREDATE DATE,
SAL NUMBER(7,2),
DEPTNO NUMBER(2),
DNAME VARCHAR2(14)
TOTALBONUS NUMBER(8,2));
/
CREATE TYPE comboRecordTable is table of comboRecord;
/
And, a function to return a table of records:
CREATE OR REPLACE FUNCTION getFullEmpDetails(p_ename IN VARCHAR2)
RETURN comboRecordTable
IS
CURSOR comboCsr
IS
SELECT e.empno,
e.ename,
e.mgr,
e2.ename as mname,
e.hiredate,
e.sal,
e.deptno,
d.dname,
tb.totalbonus
FROM emp e,
emp e2,
dept d,
(SELECT ename, job, sum(sal) + sum(comm) as totalbonus
FROM bonus b
GROUP BY ename, job) tb
WHERE e.mgr = e2.empno(+)
AND e.deptno = d.deptno
AND tb.ename(+) = e.ename
AND tb.job(+) = e.job
AND e.ename LIKE '%'||UPPER(p_ename)||'%';
l_combotab comboRecordTable := comboRecordTable();
l_comborec comboRecord;
l_count NUMBER := 0;
BEGIN
l_comborec := comboRecord(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
FOR l_rec IN comboCsr
LOOP
l_comborec.EMPNO := l_rec.empno;
l_comborec.ENAME := l_rec.ename;
l_comborec.MGR := l_rec.mgr;
l_comborec.MNAME := l_rec.mname;
l_comborec.HIREDATE := l_rec.hiredate;
l_comborec.SAL := l_rec.sal;
l_comborec.DEPTNO := l_rec.deptno;
l_comborec.DNAME := l_rec.dname;
l_comborec.TOTALBONUS := l_rec.totalbonus;
l_combotab.EXTEND(1);
l_count := l_count + 1;
l_combotab(l_count) := l_comborec;
END LOOP;
RETURN l_combotab;
END getFullEmpDetails;
Now, because this new function returns a complex User Defined Type (UDT),
ODAC/ODP used to have problems with it. Now, with the latest version, you can create VB Classes and map them to the UDT. First, create new file to handle the classes.
UPDATE NOTE: This code can be automatically generated! Connect to the database from within Visual Studio, navigate to the UDT; right-click and choose Generate Custom Class...
Imports Oracle.DataAccess.Types.OracleUdt
Public Class ComboRec
Implements INullable, IOracleCustomType
Private m_bIsNull As Boolean
Private m_empNo As Decimal
Private m_eName As String
Private m_mgr As Decimal
Private m_mName As String
Private m_hireDate As Date
Private m_sal As Decimal
Private m_deptNo As Decimal
Private m_dName As String
Private m_totalBonus As Decimal
<OracleObjectMapping("EMPNO")> _
Public Property empNo() As Decimal
Get
Return m_empNo
End Get
Set(ByVal value As Decimal)
m_empNo = value
End Set
End Property
<OracleObjectMapping("ENAME")> _
Public Property eName() As String
Get
Return m_eName
End Get
Set(ByVal value As String)
m_eName = value
End Set
End Property
<OracleObjectMapping("MGR")> _
Public Property mgr() As Decimal
Get
Return m_mgr
End Get
Set(ByVal value As Decimal)
m_mgr = value
End Set
End Property
<OracleObjectMapping("MNAME")> _
Public Property mName() As String
Get
Return m_mName
End Get
Set(ByVal value As String)
m_mName = value
End Set
End Property
<OracleObjectMapping("HIREDATE")> _
Public Property hireDate() As Date
Get
Return m_hireDate
End Get
Set(ByVal value As Date)
m_hireDate = value
End Set
End Property
<OracleObjectMapping("SAL")> _
Public Property sal() As Decimal
Get
Return m_sal
End Get
Set(ByVal value As Decimal)
m_sal = value
End Set
End Property
<OracleObjectMapping("DEPTNO")> _
Public Property deptNo() As Decimal
Get
Return m_deptNo
End Get
Set(ByVal value As Decimal)
m_deptNo = value
End Set
End Property
<OracleObjectMapping("DNAME")> _
Public Property dName() As String
Get
Return m_dName
End Get
Set(ByVal value As String)
m_dName = value
End Set
End Property
<OracleObjectMapping("TOTALBONUS")> _
Public Property totalBonus() As Decimal
Get
Return m_totalBonus
End Get
Set(ByVal value As Decimal)
m_totalBonus = value
End Set
End Property
Public ReadOnly Property IsNull() As Boolean _
Implements Oracle.DataAccess.Types.INullable.IsNull
Get
Return m_bIsNull
End Get
End Property
Public Sub FromCustomObject( _
ByVal con As Oracle.DataAccess.Client.OracleConnection, _
ByVal pUdt As System.IntPtr) _
Implements Oracle.DataAccess.Types.IOracleCustomType.FromCustomObject
SetValue(con, pUdt, "EMPNO", empNo)
SetValue(con, pUdt, "ENAME", eName)
SetValue(con, pUdt, "MGR", mgr)
SetValue(con, pUdt, "MNAME", mName)
SetValue(con, pUdt, "HIREDATE", hireDate)
SetValue(con, pUdt, "SAL", sal)
SetValue(con, pUdt, "DEPTNO", deptNo)
SetValue(con, pUdt, "DNAME", dName)
SetValue(con, pUdt, "TOTALBONUS", totalBonus)
End Sub
Public Sub ToCustomObject( _
ByVal con As Oracle.DataAccess.Client.OracleConnection, _
ByVal pUdt As System.IntPtr) _
Implements Oracle.DataAccess.Types.IOracleCustomType.ToCustomObject
empNo = GetValue(con, pUdt, "EMPNO")
eName = GetValue(con, pUdt, "ENAME")
If Not IsDBNull(con, pUdt, "MGR") Then
mgr = GetValue(con, pUdt, "MGR")
mName = GetValue(con, pUdt, "MNAME")
End If
hireDate = GetValue(con, pUdt, "HIREDATE")
sal = GetValue(con, pUdt, "SAL")
deptNo = GetValue(con, pUdt, "DEPTNO")
dName = GetValue(con, pUdt, "DNAME")
If Not IsDBNull(con, pUdt, "TOTALBONUS") Then
totalBonus = GetValue(con, pUdt, "TOTALBONUS")
End If
End Sub
End Class
<OracleCustomTypeMapping("SCOTT.COMBORECORD")> _
Public Class ComboRecordFactory
Implements IOracleCustomTypeFactory
Public Function CreateObject() _
As Oracle.DataAccess.Types.IOracleCustomType _
Implements Oracle.DataAccess.Types.IOracleCustomTypeFactory.CreateObject
Return New ComboRec()
End Function
End Class
<Oracle.DataAccess.Types.OracleCustomTypeMapping("SCOTT.COMBORECORDTABLE")> _
Public Class ComboRecordTable
Implements IOracleArrayTypeFactory
Public Function CreateArray(ByVal numElems As Integer) As System.Array _
Implements Oracle.DataAccess.Types.IOracleArrayTypeFactory.CreateArray
Dim s(numElems) As ComboRec
Return s
End Function
Public Function CreateStatusArray(ByVal numElems As Integer) As System.Array _
Implements Oracle.DataAccess.Types.IOracleArrayTypeFactory.CreateStatusArray
Return Nothing
End Function
End Class
Now, whenever your VB code attempts to pull in data from your UDT structures, it has something to translate them to.
For instance, we can capture the incoming data as an Array and use it to populate a DataGrid dynamically.
Public Sub populateDynamicFullDetails(ByVal eName As String)
Dim oraconnection As OracleConnection = _
New OracleConnection(My.Settings.STConnectionString)
Dim oracommand As OracleCommand = New OracleCommand()
Dim empArr As System.Array = Nothing
Try
oraconnection.Open()
With oracommand
.Connection = oraconnection
.CommandType = CommandType.Text
.CommandText = "select getFullEmpDetails(:1) from dual"
.Parameters.Add(New OracleParameter(":1", _
OracleDbType.Varchar2)).Value = eName
Dim subReader As OracleDataReader = .ExecuteReader
While (subReader.Read())
If Not subReader.IsDBNull(0) Then
empArr = DirectCast(subReader.GetValue(0), System.Array)
End If
End While
End With
If Not oracommand Is Nothing Then
oracommand.Dispose()
End If
Catch ex As Exception
If Not oracommand Is Nothing Then
oracommand.Dispose()
End If
Finally
If Not oraconnection Is Nothing Then
oraconnection.Dispose()
End If
End Try
Me.dgrdDyno.DataSource = empArr
End Sub
During the retrieval of the data into the array,
ODP will detect the data type being returned from the Oracle function (comboRecordTable) and attempt to find a mapped VB class that matches. Now that we have told it how to handle it, everything should work fine.
