search sql server by udp and list servers and respective databasesin listboxes

  • Thread starter Thread starter TG
  • Start date Start date
T

TG

Hi!

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 that selected 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
 
Back
Top