Oracle – Enumerate tnsnames.ora Entries Without Parsing

Parsing tnsnames.ora using PowerShell

I published a post on how to enumerate tnsnames.ora entries using PowerShell. Although it may work, that is not the correct way to do this. This is a re-post of my tip on CodeProject. This should be easy enough to convert to PowerShell. However, if your tnsnames.ora is not the one that will be used by ODP.net (say you don’t have the Oracle client installed), you still have to resort to parsing it manually was was done using PowerShell.

Parsing tnsnames.ora using C#

There is an easier way to get the Oracle data source than custom-code a TNSNames.ora parser.

Enumerating and writing out the entries in the default TNSNames.ora pointed to in by your system path is simple:

using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
class DataSourceEnumSample
{
    static void Main()
    {
        string ProviderName = "Oracle.DataAccess.Client";
        DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
        if (factory.CanCreateDataSourceEnumerator)
        {
            DbDataSourceEnumerator dsenum = factory.CreateDataSourceEnumerator();
            DataTable dt = dsenum.GetDataSources();
            foreach (DataRow row in dt.Rows)
            {
                System.Diagnostics.Debug.Print(dt.Columns[0] + " : " + row[0]);
                System.Diagnostics.Debug.Print(dt.Columns[1] + " : " + row[1]);
                System.Diagnostics.Debug.Print(dt.Columns[2] + " : " + row[2]);
                System.Diagnostics.Debug.Print(dt.Columns[3] + " : " + row[3]);
                System.Diagnostics.Debug.Print(dt.Columns[4] + " : " + row[4]);
                System.Diagnostics.Debug.Print("--------------------");
            }
        }
        else
            Console.Write("Data source enumeration is not supported by provider");
    }
}

Here is part of the output (with names changed to protect the innocent)

InstanceName : Dev.WORLD
ServerName : Dev-instance.mycompany.com
ServiceName : DEV
Protocol : TCP
Port : 1521
--------------------
InstanceName : QA
ServerName : QA-instance.mycompany.com
ServiceName : QA
Protocol : TCP
Port : 1521
--------------------
InstanceName : Prod
ServerName : prod-instance.mycompany.com
ServiceName : Prod
Protocol : TCP
Port : 1521

I didn’t code this, it is part of Oracle documentation!

Please note that I did not code the above. It is in the Oracle documentation. Just search for “Oracle data provider – Developer’s Guide”. The documentation is so hidden that you got to my blog faster to see how it is done!

If you are interested in enumerating SQL Server’s, just change the provider string:

string ProviderName = "System.Data.SqlClient";

Have fun coding!

Advertisements

2 thoughts on “Oracle – Enumerate tnsnames.ora Entries Without Parsing

  1. In what case, would the following:
    DataTable dt = dsenum.GetDataSources();
    foreach (DataRow row in dt.Rows)

    i.e, the dt.Rows be empty?
    On different environments, I’m getting entries or not getting them. In both cases the tnsnames.ora file is present.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s