jtammyg
asked on
how can i list the sql server and corresponding database after user selection
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
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
EugeneZ,
I am currently using the devpinoy solution, but since we have problems with our sql server browser service, sometimes I get all the sql servers and sometimes they just don't show up.
The code I have copied here apparently doesn't use the sql server browser and I found it online but it was in C# and i used a translator to changed to VB. Unfortunately, my VB knowledge is minimal.
If I could get how and where to add the listboxes in this code that'll be great!
Thanks a lot for your help though!
Tammy
I am currently using the devpinoy solution, but since we have problems with our sql server browser service, sometimes I get all the sql servers and sometimes they just don't show up.
The code I have copied here apparently doesn't use the sql server browser and I found it online but it was in C# and i used a translator to changed to VB. Unfortunately, my VB knowledge is minimal.
If I could get how and where to add the listboxes in this code that'll be great!
Thanks a lot for your help though!
Tammy
check
see code below for - >lstServers.
http://devpinoy.org/blogs/keithrull/archive/2006/06/08/Programmatically-List-All-SQL-Servers-And-Their-Databases-Using-SMO-.aspx
also this example:
http://it.gps678.com/16/81f03e6c1864c2e5.html
see code below for - >lstServers.
http://devpinoy.org/blogs/keithrull/archive/2006/06/08/Programmatically-List-All-SQL-Servers-And-Their-Databases-Using-SMO-.aspx
also this example:
http://it.gps678.com/16/81f03e6c1864c2e5.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The problem we are having is that the sql browser is not working as it should therefore my application sometimes can list the servers and sometimes it can't maybe there is another way of list servers and databases for the user to choose from a list? Without using sql browser?
Thanks!
Tammy
Tammy