oracle-databasevb.net

Problem in connecting Oracle Database 21c Express Edition for Windows x64 with VB.Net


Background Installed Oracle Database 21c Express Edition in WINDOWS 11 machine. Using Oracle SQL Plus created necessary tablespace and user with DBA privilege. Also configured tnsnames.ora. These are the command in SQL Plus.

/* Creating Tablespace*/
create tablespace ts_Trading DATAFILE 'D:\Database\OracleTest\ORATest.dbf'
size 10m
autoextend on maxsize 1024m
extent management local uniform size 64k

/* Creating User */
create user ####### identified by #######
default tablespace ts_Trading
temporary tablespace temp
quota unlimited on ts_Trading
quota 0 on system
/
grant DBA to #######

Successfully test the newly created username from SQL Plus using the following style (user name / password @ instance name) #######/*****@XE and it works.

Also successfully tested tnsping utility to test the connectivity of the Oracle client.

But failed to connect with Oracle from VB.net Application (.Net 6 framework with OracleManagedDataAccess.Core) Write the following code in windows form

Imports System.Data.OracleClient
Imports Oracle.ManagedDataAccess.Client
Imports Oracle.ManagedDataAccess.Types

Public Class frmOracle
    Private Sub frmOracle_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim oradb As String = "Data Source=XE;User Id=#######;Password=*****;"
        Dim conn As New OracleConnection(oradb)
        conn.Open()
    End Sub
End Class

It is raising the following error message (Oracle.ManagedDataAccess.Client.OracleException: 'ORA-12154: Cannot connect to database. Cannot find alias XE in DataSources, , EZConnect)

Do I need to do something more with configuring oracle? or, Anything else?


Solution

  • Problem Resolved

    There something more to do for working with Oracle with .Net technologies. First of all, I installed Oracle Developer Tools for Visual Studio 2022. It adds an option to work with Run SQLPlus Script* from tool menu of VS2022 IDE and many other things. I implemented the solution in two different framework (.Net 8.0 and .Net Framework 4.7.2). For Different framework different versions package are needed to install from Nuget.

    For .Net 8.0 packages will be

    For.Net Framework 4.7.2 will be

    With the help of Run SQLPlus Script*, I check the problem regarding connection string as there is an easier way connection test. It made a minor change in my connection string like it

    "Data Source=DESKTOP-60AADF9;Persist Security Info=True;User ID=#####; Password=*******;"
    

    Finaly the actual code in the form is

    Imports Oracle.ManagedDataAccess.Client
    Imports Oracle.ManagedDataAccess.Types
    Public Class frmOracle
        Private Sub frmOracle_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim oradb As String
            oradb = "Data Source=DESKTOP-60AADF9;Persist Security Info=True;User ID=######; Password=*******;"
            Dim conn As New OracleConnection(oradb)
            conn.Open()
        End Sub
    End Class
    

    Successfully made operations like data manipulation, data definition and data retrieval using OracleDataReader, OracleCommand and ExecuteReader. Stack Overflow community members help me to find the solution, thanks to all. We are software developer, development never ends. Any further better solution will be highly appreciated.