How to retrieve, add, modify, and delete Oracle Data with a DataGrid

Najeeb Al-Majidy

3.13/5 (9 votes)

Jul 27, 2006

82028

1740

a few lines of code to do many tasks on the Oracle Data

Sample Image - DatagridImg.jpg

Introduction

We all know that a good application is that one which the user can use easily, simply and have his jobs done quickly, I thought it will be great to have all this done by one tool and small code using a DataGrid.

This sample application shows how to Retrieve, Add, Modify, Delete Data from an Oracle Data Base.

Requirments:

First:

You have to run this sql statement on the SQL plus to create the required table:

Create table testTbl (id number(3),name varchar2(10), job varchar2(10));

Second:

If you don't have oracle tools installed on your system then install it and then add “Oracle.DataAccess.DLL” from Project => Add Reference => Brows

Code:

imports

Imports Oracle.DataAccess.Client

Declarations

Dim conn As New OracleConnection("Data Source=ServiceName;User ID=UserName;Password=Password")

Dim da As OracleDataAdapter = New OracleDataAdapter

Dim ds As New DataSet

Retrieving Code

da.SelectCommand = New OracleCommand("select * from testTbl", conn)

Try

If conn.State = ConnectionState.Closed Then conn.Open()

da.Fill(ds, "test")

myDataGrid.DataSource = ds.Tables("test")

conn.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Insert, Update, Delete Code

Dim param As OracleParameter

If conn.State = ConnectionState.Closed Then conn.Open()

Dim command_builder As New OracleCommandBuilder(da) 'create new command builder and passing the Dataadapter to it

Try

da.UpdateCommand = New OracleCommand("UPDATE testTbl SET name=:name, job=:job where id=:OLDid", conn) 'building Update command

da.DeleteCommand = New OracleCommand("DELETE FROM testTbl where id=:OLDid", conn) ' building Delete command

da.UpdateCommand.Parameters.Add(":name", OracleDbType.Varchar2, 10, "name") 'Passing parameters to the command builder

da.UpdateCommand.Parameters.Add(":job", OracleDbType.Varchar2, 10, "job")

param = da.UpdateCommand.Parameters.Add(":OLDid", OracleDbType.Int32, 3, "id")

param = da.DeleteCommand.Parameters.Add(":OLDid", OracleDbType.Int32, 3, "id")

param.SourceVersion = DataRowVersion.Original

da.Update(ds, "test") 'Update Data in the Table

conn.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try