troubleshooting Question

how can i list the sql server and corresponding database after user selection

Avatar of jtammyg
jtammyg asked on
.NET ProgrammingMicrosoft SQL ServerMicrosoft SQL Server 2005
5 Comments1 Solution238 ViewsLast Modified:
I am trying to populate the server list in a listbox as soon as the user opens the application and then depending which server he/she selected the databases within the server.

I am not sure how can I achieve that...I have the code posted below:

Thanks a lot for your help!!!

Tammy
Imports System.Net
Imports System.Net.Sockets
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Collections.Generic.List(Of WindowsApplication3.Form1)
Imports System.Collections.Specialized
Imports System.Text
 
 
Public Class Form1
 
    Private Shared Sub DecodeBuf(ByVal buf() As Byte, ByVal len As Integer, ByVal servers As StringCollection)
        Dim str As String = Nothing
        Dim str2 As String = Nothing
        Dim strArray() As String = Encoding.ASCII.GetString(buf, 3, len).Split(New Char() {Microsoft.VisualBasic.ChrW(59)})
        Dim i As Integer = 0
        Do While (i _
                    < (strArray.Length - 1))
            If (strArray(i).Length > 0) Then
                Dim str4 As String = strArray(i).ToLower
                If str4.ToLower.Equals("servername") Then
                    str = strArray((i + 1))
                ElseIf str4.Equals("instancename") Then
                    str2 = strArray((i + 1))
                    If str2.Equals("MSSQLSERVER") Then
                        str2 = Nothing
                    End If
                End If
                i = (i + 1)
            Else
                If (Not (str) Is Nothing) Then
                    If (str2 Is Nothing) Then
                        If Not servers.Contains(str) Then
                            servers.Add(str)
                        End If
                    Else
                        Dim builder As StringBuilder = New StringBuilder(str)
                        builder.Append("\")
                        builder.Append(str2)
                        If Not servers.Contains(builder.ToString) Then
                            servers.Add(builder.ToString)
                        End If
                    End If
                End If
                str = Nothing
                str2 = Nothing
            End If
            i = (i + 1)
        Loop
    End Sub
    Public Shared Function FindDatabases(ByVal server As String) As String()
        Dim connectionString As String = (TrustedConnection(server, "master") + ";Connection Timeout=3")
        Dim connection As SqlConnection = Nothing
        Dim command As SqlCommand = Nothing
        Dim reader As SqlDataReader = Nothing
        Dim list As New List(Of String) '= New List(32)
        Try
            connection = New SqlConnection(connectionString)
            command = New SqlCommand("DECLARE @t TABLE(db sysname NOT NULL PRIMARY KEY) INSERT @t(db) SELECT name FROM master.dbo.sysdataba" & _
                "ses WITH(NOLOCK) WHERE name NOT IN(N'SpecialData',N'master',N'tempdb',N'model',N'msdb') AND" & _
                " HAS_DBACCESS(name)=1 SELECT db FROM @t WHERE OBJECT_ID(N'[' + db + N'].dbo.table_version','R') I" & _
                "S NOT NULL ORDER BY db", connection)
            connection.Open()
            reader = command.ExecuteReader((CommandBehavior.CloseConnection Or CommandBehavior.SingleResult))
 
            While reader.Read
                list.Add(reader.GetString(0))
 
            End While
        Finally
            If ((Not (reader) Is Nothing) _
                        AndAlso Not reader.IsClosed) Then
                reader.Close()
            End If
            If ((Not (connection) Is Nothing) _
                        AndAlso (connection.State <> ConnectionState.Closed)) Then
                connection.Close()
            End If
        End Try
        Return list.ToArray
    End Function
 
    Public Shared Function FindSqlServers(ByVal broadcast As IPAddress) As String()
        Dim socket As Socket = New Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp)
        socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Broadcast, 1)
        socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 100)
        Dim buffer() As Byte = New Byte() {2}
        Dim remoteEP As IPEndPoint = New IPEndPoint(broadcast, 1434)
        socket.SendTo(buffer, 0, 1, SocketFlags.None, remoteEP)
        Dim strArray() As String = UdpListen(socket)
        socket.Close()
        Return strArray
    End Function
 
    Public Shared Function GetServers() As String()
        Dim strArray() As String = Nothing
        Try
            Dim dataSources As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
            Dim index As Integer = dataSources.Columns.IndexOf("ServerName")
            Dim columnIndex As Integer = dataSources.Columns.IndexOf("InstanceName")
            strArray = New String((dataSources.Rows.Count) - 1) {}
            Dim i As Integer = 0
            Do While (i < strArray.Length)
                Dim row As DataRow = dataSources.Rows(i)
                Dim str As String = row.IsNull(index)
                Dim str2 As String = row.IsNull(columnIndex)
                strArray(i) = String.IsNullOrEmpty(str2)
                i = (i + 1)
            Loop
        Catch  'As System.Exception
            strArray = New String((0) - 1) {}
        End Try
        Return strArray
    End Function
 
    Public Shared Function ServerDatabases(ByVal server As String) As String()
        Dim connection As SqlConnection = New SqlConnection((TrustedConnection(server, "master") + "; Connect Timeout=5"))
        Dim cmdText As String = "select name from sysdatabases WHERE dbid>4 ORDER BY name"
        Dim command As SqlCommand = New SqlCommand(cmdText, connection)
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader
        Dim list As ArrayList = New ArrayList
 
        While reader.Read
            list.Add(reader.GetString(0))
 
        End While
        reader.Close()
        connection.Close()
        Return CType(list.ToArray(GetType(System.String)), String())
    End Function
 
    Public Shared Function TrustedConnection(ByVal server As String, ByVal database As String) As String
        Return ("Data Source=" _
                    + (server + ("; Integrated Security=SSPI;Initial Catalog=" + database)))
    End Function
 
    Private Shared Function UdpListen(ByVal socket As Socket) As String()
        Dim servers As StringCollection = New StringCollection
        Dim point As IPEndPoint = New IPEndPoint(IPAddress.Any, 0)
        Dim remoteEP As EndPoint = point
        Dim buffer() As Byte = New Byte((8192) - 1) {}
 
        While True
            Try
                Dim len As Integer = socket.ReceiveFrom(buffer, remoteEP)
                DecodeBuf(buffer, len, servers)
            Catch  'As SocketException
 
            End Try
 
        End While
        Dim array() As String = New String((servers.Count) - 1) {}
        servers.CopyTo(array, 0)
        Return array
    End Function
 
 
End Class
screenshot-connection.doc
ASKER CERTIFIED SOLUTION
jtammyg

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros