Click here to Skip to main content
Click here to Skip to main content

TNSNames Reader

, 13 Nov 2008
Rate this:
Please Sign up or sign in to vote.
An article explaining how to list oracle data sources from tnsnames.ora file

Introduction

I needed some way to let the user select a datasource to use in one application. We have multiple databases which are defined in the tnsnames.ora file. This article shows a class that helps you list the datasources defined in that file. I just translated the code from VB and found here with some fixes.

The Class

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Win32;
using System.Text.RegularExpressions;

namespace TNSNamesReader
{
    public class TNSNamesReader
    {
        public List<string> GetOracleHomes()
        {
            List<string> oracleHomes = new List<string>();
            RegistryKey rgkLM = Registry.LocalMachine;
            RegistryKey rgkAllHome = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE");
            if (rgkAllHome != null)
            {
                foreach (string subkey in rgkAllHome.GetSubKeyNames())
                {
                    if (subkey.StartsWith("KEY_"))
                        oracleHomes.Add(subkey);
                }
            }
            return oracleHomes;
        }

        private string GetOracleHomePath(String OracleHomeRegistryKey)
        {
            RegistryKey rgkLM = Registry.LocalMachine;
            RegistryKey rgkOracleHome = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE\" +
                OracleHomeRegistryKey);

                if (!rgkOracleHome.Equals(""))
                    return rgkOracleHome.GetValue("ORACLE_HOME").ToString();
            return "";
        }

        private string GetTNSNAMESORAFilePath(String OracleHomeRegistryKey)
        {
            string oracleHomePath = this.GetOracleHomePath(OracleHomeRegistryKey);
            string tnsNamesOraFilePath = "";
            if (!oracleHomePath.Equals(""))
            {
                tnsNamesOraFilePath = oracleHomePath + @"\NETWORK\ADMIN\TNSNAMES.ORA";
                if (!(System.IO.File.Exists(tnsNamesOraFilePath)))
                {
                    tnsNamesOraFilePath = oracleHomePath + @"\NET80\ADMIN\TNSNAMES.ORA";
                }
            }
            return tnsNamesOraFilePath;
        }

        public List<string> LoadTNSNames(string OracleHomeRegistryKey)
        {
            List<string> DBNamesCollection = new List<string>();
            string RegExPattern = @"[\n][\s]*[^\(][a-zA-Z0-9_.]+[\s]*=[\s]*\(";
            string strTNSNAMESORAFilePath = GetTNSNAMESORAFilePath(OracleHomeRegistryKey);

            if (!strTNSNAMESORAFilePath.Equals(""))
            {
                //check out that file does physically exists
                System.IO.FileInfo fiTNS = new System.IO.FileInfo(strTNSNAMESORAFilePath);
                if (fiTNS.Exists)
                {
                    if (fiTNS.Length > 0)
                    {
                        //read tnsnames.ora file
                        int iCount;
                        for (iCount = 0; iCount < Regex.Matches(
                            System.IO.File.ReadAllText(fiTNS.FullName),
                            RegExPattern).Count; iCount++)
                        {
                            DBNamesCollection.Add(Regex.Matches(
                                System.IO.File.ReadAllText(fiTNS.FullName),
                                RegExPattern)[iCount].Value.Trim().Substring(0,
                                Regex.Matches(System.IO.File.ReadAllText(fiTNS.FullName),
                                RegExPattern)[iCount].Value.Trim().IndexOf(" ")));
                        }
                    }
                }
            }
            return DBNamesCollection;
        }
    }
}

Using the Code

  1. Create an instance of the class in your form.
    private TNSNamesReader tnsNamesReader = new TNSNamesReader();
  2. Populate a list control (combo, listbox, etc.) to let your user select the Oracle Home where the tnsnames.ora file will be read.
    cmbOracleHome.DataSource = tnsNamesReader.GetOracleHomes();
  3. Populate another list control with the datasources found.
    this.cmbDataSource.DataSource = tnsNamesReader.LoadTNSNames(
        (string)this.cmbOracleHome.SelectedValue);

Points of Interest

In the article I pointed out, it's supposed to be a registry key "SOFTWARE\ORACLE\ALL_HOMES" and a subkey called "LAST_HOME." None of them existed on my registry and caused an exception. I went to the windows registry and saw that every oracle home registry key begins with the word "KEY_" so I took every key beggining with this and listed them as Oracle Homes. Once an oracle home is selected we can look for the tnsnames.ora file path and load its data sources. This class doesn't find the Current or Active Oracle Home, however, you must select one.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

cirovladimir
Web Developer
Mexico Mexico
No Biography provided

Comments and Discussions

 
GeneralOracle version specific... PinmemberWTF_BFS18-Dec-08 12:39 
GeneralRe: Oracle version specific... Pinmemberciro_vladimir19-Dec-08 3:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 13 Nov 2008
Article Copyright 2008 by cirovladimir
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid