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

<A href="DataGridEditor/DataGridEditor_src.zip"></A><A href="DataGridEditor/DataGridEditor_src.zip">Download source files - 2 Kb</A>

Download demo project - 16 Kb

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here