Best Practices for connecting to two servers

[amber]

Hello,

I'm fairly new to VB.NET.
I have created a simple program and tested successfully on my laptop, connecting to a local version of SQL on my laptop.
Now I'd like to put the program on another PC, that's connected to another SQL Server.

What are the best practices for setting this up?
How do I easily change which server the program should be pointing to?

Thanks in advance!

Amber

 

[jmcilhinney]

You should generally store the connection string in the config file. Various tools will do that automatically but, if you're writing all your own ADO.NET code, you can do it yourself via the Settings page of the project properties. There is a setting type specifically for connection strings. It's then possible to edit the connection string by hand or programmatically. Here's an example I created of doing programmatically, which includes protecting the possibly sensitive information with encryption:

Protected Configuration

 

[Herman]

In addition, you can use the SQL Server provider to discover network or local instance like so:

Dim dtInstanceList As DataTable = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources

To list databases on the instance, use this query:

SELECT [name]
FROM sys.databases
WHERE NOT ([name] = 'master' OR [name] = 'model' OR [name] = 'msdb' OR [name] = 'tempdb')

You can list those on your form, along with a username textbox and a password box and let the user choose the server and database. From there follow the link above.

 

[jmcilhinney]

In addition, you can use the SQL Server provider to discover network or local instance like so:

Dim dtInstanceList As DataTable = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources

To list databases on the instance, use this query:

SELECT [name]
FROM sys.databases
WHERE NOT ([name] = 'master' OR [name] = 'model' OR [name] = 'msdb' OR [name] = 'tempdb')

You can list those on your form, along with a username textbox and a password box and let the user choose the server and database. From there follow the link above.

Ye of little faith Here's some code from the OptionsDialogue of my demo:

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

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

 

[amber]

Database connection...

Thanks for your help!
I have set up a similar connection method to the one in your link.

I can't seem to get the databases to populate though.
"Unable to Connect" pops up.

Any thoughts on what I might be doing wrong?

Also - the server droplist does populate - but it takes quite some time...

Cheers,
Amber

 

[jmcilhinney]

I can't seem to get the databases to populate though.
"Unable to Connect" pops up.

Any thoughts on what I might be doing wrong?

As I don't know what you're doing, I don't know what you might be doing wrong.

Also - the server droplist does populate - but it takes quite some time...

That's normal enough. The same thing happens when you search for servers in SQL Server Management Studio.

 

[Herman]

If the server is an instance you setup yourself, a firewall might be blocking you server side.

 

[amber]

As I don't know what you're doing, I don't know what you might be doing wrong.

I'm trying to replicate your code. Although I tested yours on my laptop, and have the same problem. When I select the server dropbox - it populates fine - but it can't find the databases to populate that combobox...

I'm not sure if this helps - since it's pretty much identical to yours - but the code that fails to populate the combobox is:
Private Sub cboDataBase_DropDown(sender As Object, e As EventArgs) Handles cboDatabase.DropDown
Using connection As New SqlConnection(Me.GetConnectionString(False))
Try
connection.Open()

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

 

[jmcilhinney]

The first thing to do would be to actually look at the exception that is being thrown. You know that one is being thrown if it's being caught so look at it to find out what actually happened.

 

[amber]

New question - My.Settings.PrimaryConnectionString

The first thing to do would be to actually look at the exception that is being thrown. You know that one is being thrown if it's being caught so look at it to find out what actually happened.

I managed to get the combobox populating with different code.
So I'm happy with that

But can you help me out with this:
Dim builder As New SqlConnectionStringBuilder(My.Settings.PrimaryConnectionString)

I can't figure out what I need to import, or what reference I need to add to make this work...

Thanks,
Amber

 

[jmcilhinney]

I managed to get the combobox populating with different code.
So I'm happy with that

But can you help me out with this:
Dim builder As New SqlConnectionStringBuilder(My.Settings.PrimaryConnectionString)

I can't figure out what I need to import, or what reference I need to add to make this work...

Thanks,
Amber

1. That works in my demo so if you don't know how to make it work then you haven't spent enough time examining my demo.

2. If it doesn't work then there's an error. If there's an error then there's an error message. Keeping that error message a secret from those whose help you want is not productive.