Using Oracle in VB.NET

Using OleDb Provider

The Objects of the OleDb provider with which we work are:

The OleDbConnection Class
The OleDbConnection class represents a connection to OleDb data source. OleDb connections are used to connect to most databases.

The OleDbCommand Class
The OleDbCommand class represents a SQL statement or stored procedure that is executed in a database by an OLEDB provider.

The OleDbDataAdapter Class
The OleDbDataAdapter class acts as a middleman between the datasets and OleDb data source. We use the Select, Insert, Delete and Update commands for loading and updating the data.

The OleDbDataReader Class
The OleDbDataReader class creates a data reader for use with an OleDb data provider. It is used to read a row of data from the database. The data is read as forward-only stream which means that data is read sequentially, one row after another not allowing you to choose a row you want or going backwards.

Sample Code

We will work with the sample Emp table in Oracle.

Retrieving Records

Imports System.Data.OleDB
Public Class Form1 Inherits System.Windows.Forms.Form
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim dr As New OleDbDataReader()
'declaration
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
myConnection = New OleDbConnection_
("Provider=MSDAORA.1;UserID=scott;password=tiger; database=ora")
'MSDORA is the provider when working with Oracle
Try
myConnection.Open()
'opening the connection
myCommand = New OleDbCommand("Select * from emp", myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
While dr.Read()
'reading from the datareader
MessageBox.Show("EmpNo" & dr(0))
MessageBox.Show("EName" & dr(1))
MessageBox.Show("Job" & dr(2))
MessageBox.Show("Mgr" & dr(3))
MessageBox.Show("HireDate" & dr(4))
'displaying data from the table
End While
dr.Close()
myConnection.Close()
Catch e As Exception
End Try
End Sub
End Class

The above code displays first 5 columns from the Emp table in Oracle.

Inserting Records

Drag a Button from the toolbox onto the Form. When this Button is clicked the values specified in code will be inserted into the Emp table.

Imports System.Data.OleDb
Public Class Form2 Inherits System.Windows.Forms.Form
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim ra as Integer
'integer holds the number of records inserted
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As _
System.EventArgs) Handles Button1.Click
myConnection = New OleDbConnection(""Provider=MSDAORA.1;User_ ID=scott;password=tiger;database=ora")
Try
myConnection.Open()
myCommand = New OleDbCommand("Insert into emp values 12,'Ben','Salesman',300,_
12-10-2001,3000,500,10 ", myConnection)
'emp table has 8 columns. You can work only with the columns you want
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records Inserted" & ra)
myConnection.Close()
Catch
End Try
End Sub
End Class

Deleting Records

Drag a Button on a new form and paste the following code.

Imports System.Data.OleDb
Public Class Form3 Inherits System.Windows.Forms.Form
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim ra as Integer
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
Try
myConnection = New OleDbConnection(""Provider=MSDAORA.1;User_ ID=scott;password=tiger;database=ora")
myConnection.Open()
myCommand = New OleDbCommand("Delete from emp where DeptNo=790220",_
myConnection)
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records Deleted" & ra)
myConnection.Close()
Catch
End Try
End Sub
End Class

Updating Records

Drag a Button on a new form and paste the following code.

Imports System.Data.OleDb
Public Class Form4 Inherits System.Windows.Forms.Form
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim ra as Integer
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button1.Click
Try
myConnection = New OleDbConnection(""Provider=MSDAORA.1;User_ ID=scott;password=tiger;database=ora")
myConnection.Open()
myCommand = New OleDbCommand("Update emp Set DeptNo=65 where DeptNo=793410",_ myConnection)
ra=myCommand.ExecuteNonQuery()
MessageBox.Show("Records Updated" & ra)
myConnection.Close()
Catch
End Try
End Sub
End Class