CODEWITHSUNDEEP
sqlvb.net
George Vaisey
George Vaisey

Reputation: 149

List of available SQL Databases w/ VB>NET

Looking to get a list of available databases from a user defined sql server. The code below properly queries available servers but now I'm trying to find the available databases on the selected server.

Thoughts?

Dim dt As Data.DataTable = Nothing, dr As Data.DataRow = Nothing
dt = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()

For Each dr In dt.Rows
    cbAvailableSQLServers.Items.Add(dr.Item(0).ToString)
Next

Upvotes: 0

Views: 4338

Answers (2)

jmcilhinney
jmcilhinney

Reputation: 54427

Firstly, your code is going to get default instances OK but will not show the names of named instances. Here's how I've populated a ComboBox with SQL Server instances in the past:

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

Here's how I populated the database list for a server in the same application:

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

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

Upvotes: 2

Harsh
Harsh

Reputation: 1319

You can either query sys.databases to get list of databases for a particular server instance or you need to use Microsoft.SqlServer.Management.Smo namespace to programmatically query them

Upvotes: 0

Related Questions