How to Connect VB.NET With Oracle

Query an Oracle database in Visual Basic.

Connecting to an Oracle database using Visual Basic.NET is not as complicated as you may think. Making an Oracle connection is almost the same as making a connection to an SQL Server database. One difference is that for Oracle you need to use the Oracle.DataAccess.Client namespace. Once you make the connection, then you can query the database by using the OracleCommand class. The OracleDataReader is used to read the results from the command and display them in your application.

Instructions

1. Launch Microsoft Visual Studio, click “File > New Project” and click expand “Visual Basic.” Click “Windows” and double-click “Windows Forms Application” to create a new project.

2. Right-click the project name on the Solution Explorer pane and click “Add Reference.” Click “Oracle.DataAccess” located in the .NET tab and click “OK.”

3. Double-click “Button” from the Toolbox pane to add a new button. Double-click “Button1” to create a click event for the button. Add a label using the same technique.

4. Add the following line of code at the very top of your code module to use the Oracle namespace:
   Imports Oracle.DataAccess.Client

5. Copy and paste the following code to define the connection to your Oracle server:

   Dim oracleConn As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
   + "(ADDRESS=(PROTOCOL=TCP)(HOST=<server name>)(PORT=<port number>)))" _
   + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<service name>)));" _
   + "User Id=<user ID>;Password=<password>;"

6. Add the following code to open the connection defined in the previous step:

   Dim myConn As New OracleConnection(oracleConn)
   myConn.Open()

7. Copy and paste the following code to query a field of a table of your choice, adding a where condition, and display the results through the label control:

   Dim myOrcleCmd As New OracleCommand
   myOrcleCmd.Connection = myConn
   myOrcleCmd.CommandText = "select <fieldname> from <tablename> where <fieldname> = <value>"
   myOrcleCmd.CommandType = CommandType.Text
   Dim dataRdr As OracleDataReader = myOrcleCmd.ExecuteReader()
   dataRdr.Read()
   Label1.Text = dataRdr.Item("<fieldname>")

8. Add the following code to release objects from memory:

   dataRdr.Dispose()
   myOrcleCmd.Dispose()
   myConn.Dispose()

9. Press “F5” to run the program and click “Button1” to query your Oracle database.

References

• Oracle; Build a .NET Application With Visual Studio; John Paul Cook; September 2008