Detect MySQL servers available on network

[brotherkennyh]

Hi,
Is it possible to detect the mySQL servers available on the network and display the list in a combo box?
Cheers
Kenny

 

[brotherkennyh]

Hi,
Just thought I would post back with my solution in case anyone finds it useful.
The sub 'ListServers' is called by the forms on load event. The sub calls a function 'ListDevices' which lists all devices on the network and puts them in a string array.
Once it has a list of devices it uses the function 'TestConnection' to see if it can get a connection to an SQL server. The connection string form this function is built using the server from the list already created and a partial connection string stored in my application settings.
The list of servers is looped through testing for a viable connection as it goes. Each time it finds a viable connection the server name is added to a list and to a combo box on my form.
When my application needs a database connection it builds the connection string from the option selected in the combo box.

[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][COLOR=#0000ff]Sub ListServers()
        Dim ServerList As New List(Of String)()
        cboServerList.Items.Clear()
        ServerList = ListDevices()

        Dim dbServerList As New List(Of String)()
        Dim strServer As String
        Dim CanConnect As Boolean
        Dim x As Integer
        'add loop for these
        x = 0
        For Each strServer In ServerList
            CanConnect = TestConnection(ServerList(x).ToString)
            If CanConnect = True Then
                dbServerList.Add(ServerList(x).ToString)
                cboServerList.Items.Add(ServerList(x).ToString)
            End If
            x += 1
        Next
    End Sub

[/COLOR][/SIZE][/FONT][/SIZE][/FONT][/SIZE][/FONT]

Function ListDevices()
        Dim ServerList As New List(Of String)()
        Dim childEntry As DirectoryEntry
        Dim ParentEntry As New DirectoryEntry
        Try
            ParentEntry.Path = "WinNT:"
            For Each childEntry In ParentEntry.Children
                Select Case childEntry.SchemaClassName
                    Case "Domain"

                        Dim SubChildEntry As DirectoryEntry
                        Dim SubParentEntry As New DirectoryEntry
                        SubParentEntry.Path = "WinNT://" & childEntry.Name
                        For Each SubChildEntry In SubParentEntry.Children
                            Select Case SubChildEntry.SchemaClassName
                                Case "Computer"
                                    ServerList.Add(SubChildEntry.Name)

                            End Select
                        Next
                End Select
            Next
            Return ServerList
            'Console.WriteLine(ServerList(1))
        Catch Excep As Exception
            MsgBox("Error While Reading Directories : " + Excep.Message.ToString)
            Return Nothing
        Finally
            ParentEntry = Nothing

        End Try
    End Function

Public Function TestConnection(strServer As String) As Boolean
        ' build a connection and trys to open a connection. If a connection can be established this returns TRUE.
        Dim strTempConnection As String = "server=" & strServer & ";" & My.Settings.RiaBConnectionString
        Dim con As MySqlConnection = New MySqlConnection(strTempConnection)
        Try
            con.Open()
            con.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function

 

[jmcilhinney]

We do what we can with what we have but I'm afraid that's not a good solution I'm afraid. For one thing, that's going to miss any named instances. This is the sort of thing that you should be doing:

Imports System.Data.Sql

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dataSources = SqlDataSourceEnumerator.Instance.GetDataSources()
        Dim instances As New List(Of String)

        For Each dataSource As DataRow In dataSources.Rows
            If dataSource.IsNull("InstanceName") Then
                instances.Add(CStr(dataSource("ServerName")))
            Else
                instances.Add(String.Format("{0}\{1}",
                                            dataSource("ServerName"),
                                            dataSource("InstanceName")))
            End If
        Next

        Me.ComboBox1.DataSource = instances
    End Sub

End Class

 

[jmcilhinney]

Ah b*gger! As soon as I submitted that post I realised that you'd asked about MySQL rather than SQL Server. My mistake. There may be a similar class to SqlDataSourceEnumerator for MySQL but I'm not aware of it.

 

[Herman]

Yeah MySQL has no Browser service equivalent. The only way to find MySQL instances is by enumerating processes. I guess you could also port scan a range of addresses for open port 3306, which is the default port for MySql. Named instances would normally follow up with 3307, 3308, etc... But there is no guarantee of that as each instance has to be manually set to a different port, so the user could set it to anything.