Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Using OleDb Provider

Posted by Ravi Varma Thumati on October 9, 2009

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_  ;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_;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_;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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: