2

I have two questions regarding connecting to an Oracle database using C#.NET.

My program references Oracle.DataAccess.dll (version 2.122.19.1) in my Oracle 19c client 64-bit folder.

I have a using statement:

using Oracle.DataAccess.Client;

and this code to connect:

string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.sys.mycompany.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mydb.mycompany.com)));User Id=MyLanId;Password=MyPasswors;Validate Connection=True;"

using (OracleConnection connection = new OracleConnection(connectionString))
{
    connection.Open();
}

Question #1:

How do I create an OID connection string that does not specify the DB name but instead queries the LDAP for the detail server/port info that is hardcoded in my TNS-less connection string above?

For example, in a Java program, we connect like this.

base.oracle.db.url=jdbc:oracle:thin:@ldap://oid.gtm.internal.mycompany.com:3060/dbname,cn=OracleContext,dc=mycompany,dc=com

What would the .NET equivalent be?

Question Number #2:

When I compile my C# program I selected "Any CPU" yet my program only works when I reference the 64-bit Oracle.DataAccess.dll. Is this because my PATh variable mentions my 64-bit oracle client folder before my 32-bit folder (I have both)? Right now, my PATH variable is way over the max length of 2048 and I cannot change it without making it shorter and I'm not sure what to delete.

2
  • Question 1 : A connection string is a string. So your connection string has three parts : "jdbc:oracle:thin" + ldap + ",cn=OracleContext,dc=mycompany,dc=com". You need to do a ldap query to get the second part. Question 2 : You need to map a network drive to reduce the path length. Are you using python? I had similar issue when using python with c#. Python was adding the network namespace to the path and making a very long pathname. Commented Jun 9, 2021 at 2:29
  • @jdwend. Thanks for the reply. The Java jdbc connection string was an example. I was looking for a connection string pattern that I could use in my OID connection string using C#. Commented Jun 9, 2021 at 12:38

1 Answer 1

1

I found this SO post,

How do I query LDAP from C# to resolve Oracle TNS hostname while using managed ODP.NET?

Instead of just passing in a connection string that points to an LDAP server, I can do an LDAP query to get the db descriptor to build the connection string.

It's a 2 step process, so I probably want to cache the connection string once I build it. I normally count on connection pooling and "close" connections when I am done with them but this two step process seems like I have to manually add some caching of the connection string to avoid the overhead of hitting LDAP multiple times.

Is there a better way?

    string directoryServer = "oid.gtm.internal.mycompany.com:3060";
    string defaultAdminContext = "cn=OracleContext,dc=mycompany,dc=com";
    string serviceName = "mydb";
    string userId = "mylanid";
    string password = "mypwd";

    using (IDbConnection connection = GetConnection(directoryServer, defaultAdminContext, serviceName, userId, password))
    {
        connection.Open();

        connection.Close();
    }
    
private static IDbConnection GetConnection(string directoryServer, string defaultAdminContext,
                                           string serviceName, string userId, string password)
{
    string descriptor = ConnectionDescriptor(directoryServer, defaultAdminContext, serviceName);
    // Connect to Oracle
    string connectionString = $"user id={userId};password={password};data source={descriptor}";
    OracleConnection con = new OracleConnection(connectionString);
    return con;
}

private static string ConnectionDescriptor(string directoryServer, string defaultAdminContext,
    string serviceName)
{
    string ldapAdress = $"LDAP://{directoryServer}/{defaultAdminContext}";
    string query = $"(&(objectclass=orclNetService)(cn={serviceName}))";
    string orclnetdescstring = "orclnetdescstring";

    DirectoryEntry directoryEntry = new DirectoryEntry(ldapAdress, null, null, AuthenticationTypes.Anonymous);
    DirectorySearcher directorySearcher = new DirectorySearcher(directoryEntry, query, new[] { orclnetdescstring },
        SearchScope.Subtree);

    SearchResult searchResult = directorySearcher.FindOne();
    byte[] value = searchResult.Properties[orclnetdescstring][0] as byte[];

    if (value != null)
    {
        string descriptor = Encoding.Default.GetString(value);
        return descriptor;
    }

    throw new Exception("Error querying LDAP");
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.