Question Get SQL Server Instances and Databases For A Selected Instance

[VSCurtis]

Hi Guys,

I'm working Media Database application for my music collection. I currently have multiple versions of the database created with different versions of SQL Server Express. My reasons for this are NOT relevant to the problem I'm trying to solve so please do NOT ask. I have a very simple app that I use to import album cover images into the database and export them back out again as files to ensure that the files were correctly stored. Currently this app is targeted at only one of my databases. I would like to add code to the app that allows me to display a list of all my server instances in a combo box. Once I select an instance I would like to populate a second combo box with a list of databases that are present for that instance, select my database and connect to it so I can perform my import/exports on any one of the databases I choose. I have found some code that is supposed to accomplish this. I am able to retrieve a list of servers but I cannot seem to retrieve the databases for a given instance. I am not sure where the problem and I would appreciate any help you can provide. Here's my code

    Sub GetServers()

        Dim serverTable As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources()
        Dim upperBound As Integer = serverTable.Rows.Count - 1
        Dim serverNames(upperBound) As String

        For index As Integer = 0 To upperBound
            If serverTable.Rows(index).IsNull("InstanceName") Then
                serverNames(index) = CStr(serverTable.Rows(index)("ServerName"))
            Else
                serverNames(index) = String.Format("{0}\{1}", _
                                                   serverTable.Rows(index)("ServerName"), _
                                                   serverTable.Rows(index)("InstanceName"))
            End If
        Next

        With cmbServers
            .BeginUpdate()
            .Items.Clear()
            .Items.AddRange(serverNames)
            .SelectedItem = currentServerName
            .Text = currentServerName
            .EndUpdate()
        End With

    End Sub

    Sub GetServerDatabases()

        Dim builder As New SqlConnectionStringBuilder()

        Using connection As New SqlConnection(GetConnectionString(False))
            Try
                connection.Open()

                cmbDatabases.DataSource = connection.GetSchema("Databases")
                builder.IntegratedSecurity = chkIntegratedSecurity.Checked
                builder.UserID = txtUserID.Text
                builder.Password = txtPassword.Text

                If GetConnectionString(True) Then
                    builder.InitialCatalog = cmbDatabases.Text
                End If
            Catch
                MessageBox.Show("Unable to connect.", _
                                "Connection Error", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error)
            End Try
        End Using

    End Sub

    Private Function GetConnectionString(ByVal includeDatabase As Boolean) As String

        Dim csbbuilder As New SqlConnectionStringBuilder()

        csbbuilder.DataSource = cmbServers.Text

        If includeDatabase Then
            csbbuilder.InitialCatalog = cmbDatabases.Text
        End If

        Return csbbuilder.ConnectionString

    End Function

 

[jmcilhinney]

That looks rather like my code, which I posted elsewhere in 2008. Here's my original code to get the servers/instances:

Private Sub serverCombo_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles serverCombo.DropDown
    If Me.populateServerList Then
        'Enumerate available SQL Server instances.
        Dim serverTable As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources()
        Dim upperBound As Integer = serverTable.Rows.Count - 1
        Dim serverNames(upperBound) As String

        For index As Integer = 0 To upperBound
            If serverTable.Rows(index).IsNull("InstanceName") Then
                serverNames(index) = CStr(serverTable.Rows(index)("ServerName"))
            Else
                serverNames(index) = String.Format("{0}\{1}", _
                                                   serverTable.Rows(index)("ServerName"), _
                                                   serverTable.Rows(index)("InstanceName"))
            End If
        Next

        Dim currentServerName As String = Me.serverCombo.Text

        With Me.serverCombo
            .BeginUpdate()
            .Items.Clear()
            .Items.AddRange(serverNames)
            .SelectedItem = currentServerName
            .Text = currentServerName
            .EndUpdate()
        End With

        Me.populateServerList = False
    End If
End Sub

which is pretty much exactly the same as yours. Here's my original code to get the databases for the selected server/instance:

Private Sub databaseCombo_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles databaseCombo.DropDown
    Using connection As New SqlConnection(Me.GetConnectionString(False))
        Try
            connection.Open()

            'Enumerate available databases.
            Me.databaseCombo.DataSource = connection.GetSchema("Databases")
        Catch
            MessageBox.Show("Unable to connect.", _
                            "Connection Error", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Error)
        End Try
    End Using
End Sub

You've made some changes to that and I don't understand why. Why do you have a connection string builder mixed up with that code? There doesn't seem to be any reason for that. Here's my original code to get the connection string:

Private Function GetConnectionString(ByVal includeDatabase As Boolean) As String
    Dim builder As New SqlConnectionStringBuilder()

    'Build a connection string from the user input.
    builder.DataSource = Me.serverCombo.Text
    builder.IntegratedSecurity = Me.integratedSecurityOption.Checked
    builder.UserID = Me.userText.Text
    builder.Password = Me.passwordText.Text

    If includeDatabase Then
        builder.InitialCatalog = Me.databaseCombo.Text
    End If

    Return builder.ConnectionString
End Function

Notice that I am setting IntegratedSecurity based on a CheckBox and credentials based on TextBoxes. You are doing neither, so I wonder whether you are implicitly specifying that credentials are required but not providing any. It would have been helpful if you had actually explained what happened when you tried to get the databases. Does it succeed and return an empty list or is an exception thrown? If an exception is thrown, did you bother to look at that exception to see what it was telling you? I suspect that an exception is thrown and it will tell you that your connection string is lacking credentials.