SQL INSTANCENAME DISCOVERY หา instance ของ sqlserver ของเครื่องคอมพิวเตอร์ แก้ปัญหา connect ไม่เจอ ย้ายเครื่อง ฯ

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System
Imports Microsoft.VisualBasic
Imports System.Management

Namespace DatabaseUtility
    ''' <summary>
    ''' SqlHelper >> Simple and clean module for associated ms sql server.
    ''' Inspired from Entityframework executecommand method. 
    ''' Write by S. Chursittisak
    ''' Rev 1.02 Date 2015/01/10
    ''' </summary>
    ''' <remarks></remarks>
    Public Module SqlHelper

        Public ConnectionString As String = String.Empty
        Public hasGenerateError As Boolean = False


        ''' <summary>
        ''' Run this method first such as global.asax or whatever before uses this module 
        ''' </summary>
        ''' <param name="ConnectionStringName"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function ReadConnectionStringFromWebConfig(ByVal ConnectionStringName As String) As String
            Dim rootWebConfig As System.Configuration.Configuration
            rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MyWebSiteRoot")
            Dim connString As System.Configuration.ConnectionStringSettings
            If (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0) Then
                connString = rootWebConfig.ConnectionStrings.ConnectionStrings(ConnectionStringName)
                If Not (connString.ConnectionString = Nothing) Then
                    Return connString.ConnectionString
                End If
            End If

            Return String.Empty
        End Function


        Public Sub GetConnectionStringFromWebConfig(ByVal ConnectionStringName As String)
            Dim rootWebConfig As System.Configuration.Configuration
            rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MyWebSiteRoot")
            Dim connString As System.Configuration.ConnectionStringSettings
            If (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0) Then
                connString = rootWebConfig.ConnectionStrings.ConnectionStrings(ConnectionStringName)
                If Not (connString.ConnectionString = Nothing) Then
                    SqlHelper.ConnectionString = connString.ConnectionString
                    Return
                End If
            End If
            SqlHelper.ConnectionString = String.Empty
        End Sub

        Private Function SqlCommandComposer _
            (ByVal command As SqlCommand, ByVal ParamArray Param() As Object) As SqlCommand
            Dim ParamName As String = String.Empty

            Dim CommandText As String = command.CommandText

            If (Param.Length > 0) Then
                For i As Integer = 0 To Param.Length - 1
                    ParamName = "arg" & i.ToString()
                    command.Parameters.AddWithValue(ParamName, Param(i))
                    CommandText = CommandText.Replace("{" & i.ToString() & "}", "@" & ParamName)
                Next
                command.CommandText = CommandText
            End If

            Return command

        End Function

        Public Function RunQuery(ByVal CommandText As String, ByVal ParamArray Param() As Object) As Integer

            Dim Result As Integer = 0

            Using mConnection As New SqlConnection(SqlHelper.ConnectionString)
                Dim mCommand As New SqlCommand(CommandText, mConnection)
                Try
                    mCommand = SqlHelper.SqlCommandComposer(mCommand, Param)
                    mConnection.Open()
                    Result = mCommand.ExecuteNonQuery()
                Catch ex As Exception
                    If hasGenerateError Then
                        Throw ex
                    End If
                    Result = -1
                End Try
            End Using
            Return Result
        End Function

        Public Function RunQueryEx(ByVal CommandText As String, ByVal ParamArray Param() As Object) As Object

            Dim retVal As Object = Nothing

            Using mConnection As New SqlConnection(SqlHelper.ConnectionString)
                Dim mCommand As New SqlCommand(CommandText, mConnection)
                Try
                    mCommand = SqlHelper.SqlCommandComposer(mCommand, Param)
                    mConnection.Open()
                    retVal = mCommand.ExecuteScalar()
                Catch ex As Exception
                    If hasGenerateError Then
                        Throw ex
                    End If
                End Try
            End Using
            Return retVal
        End Function

        Public Function GetDataSetFromQuery(ByVal CommandText As String, ByVal ParamArray Param() As Object) As DataSet
            Dim ResultDataSet As New DataSet

            Using mConnection As New SqlConnection(SqlHelper.ConnectionString)
                Dim mCommand As New SqlCommand(CommandText, mConnection)
                mCommand = SqlHelper.SqlCommandComposer(mCommand, Param)
                Dim mDataAdapter As New SqlDataAdapter(mCommand)
                Try
                    mDataAdapter.Fill(ResultDataSet)
                Catch ex As Exception
                    If hasGenerateError Then
                        Throw ex
                    End If
                End Try
            End Using
            Return ResultDataSet
        End Function

        Public Function GetDataTableFromQuery(ByVal CommandText As String, ByVal ParamArray Param() As Object) As DataTable
            Dim ResultDataTable As New DataTable

            Using mConnection As New SqlConnection(SqlHelper.ConnectionString)
                Dim mCommand As New SqlCommand(CommandText, mConnection)
                mCommand = SqlHelper.SqlCommandComposer(mCommand, Param)
                Dim mDataAdapter As New SqlDataAdapter(mCommand)
                Try
                    mDataAdapter.Fill(ResultDataTable)
                Catch ex As Exception
                    If hasGenerateError Then
                        Throw ex
                    End If
                End Try
            End Using
            Return ResultDataTable
        End Function


        Public Function TestConnection(ByRef ErrorMessage As String) As Boolean
            Dim bRet As Boolean = False
            Using mConnection As New SqlConnection(SqlHelper.ConnectionString)
                Try
                    mConnection.Open()
                    bRet = False
                    ErrorMessage = String.Empty
                Catch ex As Exception
                    ErrorMessage = ex.Message
                End Try
            End Using
            Return bRet
        End Function
#Region "SQL SERVER INSTANCE DISCOVERY"
        'origin source from http://social.technet.microsoft.com/wiki/contents/articles/940.how-to-embed-sql-server-express-in-an-application.aspx?PageIndex=2

        ''' <summary>
        ''' Main function for get all posible sqlserver instance on computer.
        ''' Usage 
        '''       Dim instanceList As List(Of SqlServerInstanceAlias) = New List(Of SqlServerInstanceAlias)
        '''       EnumerateSQLInstances(instanceList)
        ''' </summary>
        ''' <param name="instanceList"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function EnumerateSQLInstances(ByVal instanceList As List(Of SqlServerInstanceAlias)) As Boolean

            Dim correctNamespace As String = GetCorrectWmiNamespace()

            If String.Equals(correctNamespace, String.Empty) Then
                Return False
            End If

            If (instanceList Is Nothing) Then
                instanceList = New List(Of SqlServerInstanceAlias)
            End If


            Dim query As String = String.Format("select * from SqlServiceAdvancedProperty " &
                                                " where SQLServiceType = 1 and PropertyName = 'instanceID'")
            Dim getSqlEngine As New System.Management.ManagementObjectSearcher _
                With {.Scope = New ManagementScope(correctNamespace), .Query = New ObjectQuery(query)}

            If getSqlEngine.Get().Count = 0 Then
                Return False
            End If
            'Console.WriteLine("SQL Server database instances disovered :")

            Dim instanceName As String = String.Empty
            Dim serviceName As String = String.Empty
            Dim version As String = String.Empty
            Dim edition As String = String.Empty

            'Console.WriteLine("Instance name {0} ServiceName {0} Edition {0} Version {0}", vbTab)


            For Each sqlEngine As ManagementObject In getSqlEngine.Get()
                serviceName = sqlEngine("ServiceName").ToString()
                instanceName = GetInstanceNameFromServiceName(serviceName)
                version = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "Version")
                edition = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "SKUNAME")
                instanceList.Add(New DatabaseUtility.SqlServerInstanceAlias(serviceName, instanceName, version, edition))
            Next

            Return True


        End Function

        ''' <summary>
        ''' Method returns the correct SQL namespace to use to detect SQL Server instances.
        ''' </summary>
        ''' <returns>namespace to use to detect SQL Server instances</returns>
        Function GetCorrectWmiNamespace() As String
            Dim wmiNamspaceToUse As String = "root\Microsoft\SqlServer"
            Dim compareKey As String = "ComputerManagement"
            Dim namespaces As New List(Of String)
            Try
                'Enumerate all WMI instances of
                '__namespace WMI class.
                Dim nsClass As New ManagementClass With _
                    {.Scope = New ManagementScope(wmiNamspaceToUse), .Path = New ManagementPath("__namespace")}

                For Each ns As ManagementObject In nsClass.GetInstances()
                    namespaces.Add(ns("Name").ToString())
                Next
            Catch ex As ManagementException
                Throw New Exception(ex.Message)
                'Console.WriteLine("Exception = %1", ex.Message)
            End Try
            If namespaces.Count > 0 Then

                For i As Integer = 0 To namespaces.Count - 1

                    If (namespaces(i).IndexOf(compareKey) >= 0) Then
                        wmiNamspaceToUse = wmiNamspaceToUse + "\" + namespaces(i)
                        Exit For
                    End If
                Next
            Else
                wmiNamspaceToUse = String.Empty
            End If
            Return wmiNamspaceToUse


        End Function
        ''' <summary>
        ''' method extracts the instance name from the service name
        ''' </summary>
        ''' <param name="serviceName"></param>
        ''' <returns></returns>
        Function GetInstanceNameFromServiceName(ByVal serviceName As String) As String
            If Not String.IsNullOrEmpty(serviceName) Then
                If String.Equals(serviceName, "MSSQLSERVER", StringComparison.OrdinalIgnoreCase) Then
                    Return serviceName
                Else
                    Return serviceName.Substring(serviceName.IndexOf("$"c) + 1, serviceName.Length - serviceName.IndexOf("$"c) - 1)
                End If
            Else
                Return String.Empty
            End If
        End Function

        ''' <summary>
        ''' Returns the WMI property value for a given property name for a particular SQL Server service Name
        ''' </summary>
        ''' <param name="serviceName">The service name for the SQL Server engine service to query for</param>
        ''' <param name="wmiNamespace">The wmi namespace to connect to </param>
        ''' <param name="propertyName">The property name whose value is required</param>
        ''' <returns></returns>

        Function GetWmiPropertyValueForEngineService(ByVal serviceName As String, ByVal wmiNamespace As String, ByVal propertyName As String) As String
            Dim propertyValue As String = String.Empty
            Dim query As String = String.Format("select * from SqlServiceAdvancedProperty " &
                                                " where SQLServiceType = 1 and PropertyName = '{0}' and ServiceName = '{1}'" _
                                                , propertyName, serviceName)
            Dim propertySearcher As New ManagementObjectSearcher _
                With {.Scope = New ManagementScope(wmiNamespace), .Query = New ObjectQuery(query)}
            For Each sqlEdition As ManagementObject In propertySearcher.Get()
                propertyValue = sqlEdition("PropertyStrValue").ToString()
            Next
            Return propertyValue
        End Function

#End Region
    End Module

    Public Class SqlServerInstanceAlias
        Public Property ServiceName As String = String.Empty
        Public Property InstanceName As String = String.Empty
        Public Property Version As String = String.Empty
        Public Property Edition As String = String.Empty

        Public Sub New()
        End Sub
        Public Sub New(ByVal ServiceName As String, ByVal InstanceName As String, ByVal Version As String, ByVal Edition As String)
            Me.ServiceName = ServiceName
            Me.InstanceName = InstanceName
            Me.Version = Version
            Me.Edition = Edition
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("Servicename=({0});Instancename=({1});Version=({2});Edition=({3})", Me.ServiceName, Me.InstanceName, Me.Version, Me.Edition)
        End Function
        ''' <summary>
        ''' Yield string with limiter such as <br /> in asp.net or vbcrlf in console apps.
        ''' </summary>
        ''' <param name="Saparator"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Overloads Function ToString(ByVal Saparator As String) As String
            Return String.Format("Servicename=({0});{4}Instancename=({1});{4}Version=({2});{4}Edition=({3})" _
                                 , Me.ServiceName, Me.InstanceName, Me.Version, Me.Edition, Saparator)
        End Function

    End Class
End Namespace