Read tnsnames.ora in VB.NET

Tuesday, 2 April 2013

Read tnsnames.ora in VB.NET

Below are the code for read tnsname.ora file(tns name) in vb.net. Through below functions you can get the information of the tns.

//Declare namespace
Imports System
Imports System.Collections.Generic
Imports System.Text.RegularExpressions
Imports System.IO

Public Class ReadTNS
    //Function for Load TNS Name
    Public Function LoadTNSNames() As List(Of String)
        Dim DBNamesCollection As New List(Of String)()
        Dim regPattern As String = "[\n][\s]*[^\(][a-zA-Z0-9_.]+[\s]*"
        Dim tnsNamesOraFilePath As String = GetPathToTNSNamesFile()

        If Not tnsNamesOraFilePath.Equals("") Then
            ' Verify file exists
            Dim tnsNamesOraFile As New FileInfo(tnsNamesOraFilePath)
            If tnsNamesOraFile.Exists Then
                If tnsNamesOraFile.Length > 0 Then
                    'read tnsnames.ora file                        
                    Dim tnsNamesContents As String = File.ReadAllText(tnsNamesOraFile.FullName)
                    Dim numMatches As Integer = Regex.Matches(tnsNamesContents, regPattern).Count
                    Dim col As MatchCollection = Regex.Matches(tnsNamesContents, regPattern)
                    For Each match As Match In col
                        Dim m As String = match.ToString()
                        m = m.Trim()
                        DBNamesCollection.Add(m.ToUpper())
                    Next
                End If
            End If
        End If
        Return DBNamesCollection
    End Function

   //Function for Get path of TNS File

    Private Shared Function GetPathToTNSNamesFile() As String
        Dim systemPath As String = Environment.GetEnvironmentVariable("Path")
        Dim reg As New Regex("[a-zA-Z]:\\[a-zA-Z0-9\\]*(oracle|app)[a-zA-Z0-9_.\\]*(?=bin)")
        Dim col As MatchCollection = reg.Matches(systemPath)

        Dim subpath As String = "network\ADMIN\tnsnames.ora"
        For Each match As Match In col
            Dim path As String = match.ToString() & subpath
            If File.Exists(path) Then
                Return path
            End If
        Next
        Return String.Empty
    End Function
End Class

8 comments:

  1. You are the best. Thanks a lot.

    ReplyDelete