Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
my first time asking a question so please be nice :)

I'm learning(as a personal interest) to use VB.net and MS SQL to create applications for things that I do.

what id like is to create a gui log in screen that lets me see any active MS SQL servers and the database(s) associated to that instance.

click it and then provide login details to create the connection to that database.

if any one knows of reading materials that would help with this then that would be greatly appreciated.

I have tried searching but my results deal with creating the connection with in the code.

thanks for your time

-Darren


ok from the extremely helpful post below I have the following code
for a simple form with a drop down list filled with any found servers
I want to select the server from the drop down list and press refresh to populate a listview.
only using list view because I haven't played with that one yet.

VB
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
        For Each dr As DataRow In dt.Rows
            ddlInstances.Items.Add(String.Concat(dr("ServerName"), "\\", dr("InstanceName")))
        Next
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        'press this button to populate LVDBs with list of databases on selected server instance
    End Sub

    Private Sub LVDBs_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles LVDBs.SelectedIndexChanged

    End Sub
End Class
Posted
Updated 12-Jul-14 0:09am
v2

List SQL server instances:
VB
Dim instance As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
Dim dt As DataTable = instance.GetDataSources()
For Each row As System.Data.DataRow In dt.Rows
    Console.WriteLine("ServerName = {0}", row("ServerName"))
Next

List SQL databases on a particular server:
VB
Using con As New SqlConnection(strConnect)
	con.Open()
	Using cmd As New SqlCommand("sp_databases", con)
		cmd.CommandType = CommandType.StoredProcedure
		Dim read As SqlDataReader = cmd.ExecuteReader()
		While read.Read()
			Console.WriteLine(DirectCast(read("DATABASE_NAME"), String))
		End While
	End Using
End Using
Obviously, the connection string needs login details, which you cannot get automatically.
 
Share this answer
 
Comments
darrenfalconer312 12-Jul-14 7:35am    
OriginalGriff, I appreciate your response and it has given me some direction to what I am trying to achieve. iv modified my question so if you have another moment could you take a look.
many thanks
I need to make a few assumptions here based on my understanding / interpretation of your question.
1. You want to know what computer systems in your local network runs a MS SQL Server instance,
2. You wish to see a list of databases for each computer system running an instance of MS SQL Server.

To accomplish (1) you will need to retrieve a list of all computer systems on your local network. Once you have a list of all computer systems on the network you will need to check to see if an instance of MS SQL is running on each specific computer system. Unfortunately you will not be allowed to interrogate a MS Server without a valid username and password for that server because of its high level security.

The code example is for the local machine and there are code examples out there to retrieve the process list of a remote computer.
VB
'Check to see if MySQL is running on this system
    Private Function IsMySQLRunning() As Boolean
        Dim proc() As System.Diagnostics.Process
        Try
            'Check all processes to see if MySQL is running
            proc = System.Diagnostics.Process.GetProcessesByName("mysqld")
            '<0 then mysqld was found, =0 then mysqld is not running
            If proc.Length > 0 Then
                'Found
                Return True
            Else
                'Not found
                MsgBox("MySQL Server was not found to be running on this system." & vbCrLf & _
                       "Please verify that this is the MySQL database server and that MySQL is running", _
                        MsgBoxStyle.Information)
                Return False
            End If
        Catch ex As Exception
            'Damit, something went wrong.
            MsgBox("Unable to check if MySQL Server is running on this system." & vbCrLf & _
                   ex.ToString, MsgBoxStyle.Information)
            Return False
        End Try
    End Function


To achieve (2) you can follow Solution 1 as it is above my immediate scope and I don't work with MS SQL.
 
Share this answer
 
v2
Comments
darrenfalconer312 12-Jul-14 19:06pm    
thank you Tino for yout time but the previous post has already helped with seeing available MS SQL servers
my next task is to select that server and then see the available databases on that server. originalgriff has already provided code but im not sure how to use it to achieve what I want.

many thanks.
Tino Fourie 13-Jul-14 8:19am    
No worries mate, as long as you made some progress that what's it about. OG has solid general to advance knowledge on the various topics, so it is always good to consider his advice and go on from there.
ok well I switched to treeview to display associated databases and all has worked very well.

thank you for helps guys.

this is form1 for selecting server and database and please if you see some thing that could be done better that I can learn from then I would love to hear from you.

VB
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1
    Private myConn As SqlConnection
    Private myCmd As SqlCommand

    Public instance As String
    Public database As String
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
        For Each dr As DataRow In dt.Rows
            ddlInstances.Items.Add(String.Concat(dr("ServerName"), "\", dr("InstanceName")))
        Next
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        TVDBs.Nodes.Clear()
        If ddlInstances.SelectedIndex = 0 Then
            instance = ddlInstances.SelectedItem.ToString
            myConn = New SqlConnection("Initial Catalog=Master;" & _
                "Data Source=" & ddlInstances.SelectedItem.ToString & ";Integrated Security=SSPI;")
            myConn.Open()
            Using cmd As New SqlCommand("sp_databases", myConn)
                cmd.CommandType = CommandType.StoredProcedure
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                While reader.Read()
                    TVDBs.Nodes.Add(reader("DATABASE_NAME"), reader("DATABASE_NAME"), 0, 1)
                End While
            End Using
            myConn.Close()
        Else
            MessageBox.Show("Please Select Server")

        End If

    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        If TVDBs.SelectedNode Is Nothing Then
            MessageBox.Show("Please select Database From List")
        Else
            ' MessageBox.Show(TVDBs.SelectedNode.Name.ToString)
            database = TVDBs.SelectedNode.Name.ToString
            Form2.Show()
        End If
    End Sub
End Class
 
Share this answer
 
Comments
Tino Fourie 13-Jul-14 9:20am    
Darren, could you marked any of the solutions to stop your question from coming up as Unanswered.

Thanks mate and happy coding.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900