Cannot connect to an Oracle XE database from C#

Question

The connection keeps timing out in this C# code:

private const string _connectionString = "User Id=c##BookingApp;Password=ftn;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))";

public static void Test()
{
    using (OracleConnection con = new OracleConnection(_connectionString))
    {
        try
        {
            con.Open();

            OracleCommand com = con.CreateCommand();
            com.CommandText = "SELECT * FROM user_tb;";
            com.ExecuteToStream(System.IO.Stream.Null);

            con.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }
    }

And this is the output from lsnrctl status:

LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 25-MAR-2024 10:42:12

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-08F6LQM)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date                15-MAR-2024 15:56:49
Uptime                    9 days 18 hr. 45 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\app\PC\product\21c\homes\OraDB21Home1\network\admin\listener.ora
Listener Log File         C:\app\PC\product\21c\diag\tnslsnr\DESKTOP-08F6LQM\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-08F6LQM)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-08F6LQM)(PORT=5500))(Security=(my_wallet_directory=C:\APP\PC\PRODUCT\21C\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "7c74ac5f71294090a35b32d2635fdfc9" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
The command completed successfully

I have tried every variation of the connection string and I can't figure out why it's timing out. The connection works just fine from SQL Developer.

I'm using Oracle.ManagedDataAccess.Client and I've seen people mentioning Oracle.DataAccess.Client but I couldn't find the right package for it so I'm assuming that's just an old version of the ManagedDataAccess package.

Answer 1

You are connecting to:

(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe))

but your listener is listening on:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-08F6LQM)(PORT=1521)))

I'm assuming DESKTOP-08F6LQM is not mapped to 127.0.0.1. Also, when connecting to Express Edition, you'll usually be doing all of your work in the pluggable database XEPDB1 so you would typically connect to that, not to XE

Answer 2

I uninstalled the framework package from NuGet and installed just the core which solved the problem.

This is the string I ended up using

User Id=c##BookingApp;Password=ftn;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(SID=XE))

Which is different from all the listeners listed in the command but it's the same options I use in SQL Developer. (it took me a while to find out that you can define SID instead of SERVICE_NAME)