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

Code:

imports

Imports</FONT> 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