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
Popular Posts
-
Arguments passed by value and by reference. Until now, in all the functions we have seen, the arguments passed to the functions have been pa...
-
SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications....
-
The SQL ORDER BY clause comes in handy when you want to sort your SQL result sets by some column(s). For example if you want to select all t...
-
Java Server Pages JavaServer Pages (JSP) technology is the Java platform technology for delivering dynamic content to web clients in a por...
-
DIFFERENCE BETWEEN STRUCT AND UNION: In structures,each member has its own storage location,whereas all memebers of a union use the same loc...
-
SQL is short for Structured Query Language and is a widely used database language, providing means of data manipulation (store, retrieve, up...
-
Inheritance A key feature of OOP is reusability. It's always time saving and useful if we can reuse something that already exists rather...
-
Declaration of variables In order to use a variable in C++, we must first declare it specifying which data type we want it to be. The syntax...
-
Syntax of JSP Scriptles are: <% //java codes %> JSP Scriptlets begins with <% and ends %> .We can embed any a...
-
In this section we are going to implement insert data, delete data, and update data using with JDBC database and also using of JavaScript. ...
No comments:
Post a Comment