Connect to Oracle using Visual Basic

You can connect to Oracle from Visual Basic using Devart ODBC Driver for Oracle.

Add the Odbc package reference

If you’re using .Net Core, add the System.Data.Odbc NuGet package reference from your IDE, or run the following command to add it from the .NET CLI.

dotnet add package System.Data.Odbc

Define a connection string

An ODBC connection requires a connection string, which can either use a predefined DSN or be specified through connection string parameters (a DSN-less connection).

For information about available parameters, see Connection string parameters.

DSN connection string

You can use a connection string with a predefined DSN.

Dim connString As String = "DSN=your_dsn"

If you need credentials other than those used in the DSN, specify them in the connection string to override the DSN values.

Dim connString As String = "DSN=your_dsn;User ID=your_username;Password=your_password"

For information on configuring a DSN on specific operating systems, see instructions for Windows, macOS, or Linux.

DSN-less connection string

You can establish a connection without a DSN by specifying all necessary parameters directly in the connection string.

The following example uses Direct connection.

Dim connString As String = "DRIVER=Devart ODBC Driver for Oracle;Direct=True;Host=your_server;Service Name=your_service_name;User ID=your_username;Password=your_password"

For more information, see DSN-less connections.

Connect to Oracle

The following Visual Basic code demonstrates how to connect to Oracle and fetch data using the ODBC driver.

Imports System.Data
Imports System.Data.Odbc

Module Module1

    Sub Main()
        ' // Define an ODBC connection string
        Dim connString As String = "your_connection_string"

        ' Create an object that represents a connection to a data source
        Dim conn As New OdbcConnection(connString)

        Try
            ' Open the connection to the data source using the connection string
            conn.Open()
            Console.WriteLine("Connection to Oracle successful!")

            ' Define a SQL query to retrieve data from the data source
            Dim query As String = "SELECT DEPTNO, DNAME FROM DEPT"            

            ' Create an OdbcCommand to execute the SQL query
            Dim cmd As New OdbcCommand(query, conn)

            ' Execute the query and retrieve the results into a data reader
            Dim reader As OdbcDataReader = cmd.ExecuteReader()
            Throw New Exception

            ' Loop through the results and display each record
            Console.WriteLine("Query results:")
            While reader.Read()
                ' Access and print the fields for each record
                Console.WriteLine($"DEPTNO: {reader("DEPTNO")}, DNAME: {reader("DNAME")}")                
            End While

            ' Close the data reader after reading all records
            reader.Close()
        Catch ex As Exception
            ' Handle any errors that occur during connection or query execution
            ' Print the error message to the console
            Console.WriteLine($"Error: {ex.Message}")
        Finally
            ' Ensure the connection is closed properly to release resources
            If conn.State = ConnectionState.Open Then
                conn.Close()
                Console.WriteLine("Connection closed.")
            End If
        End Try

    End Sub

End Module