In the last few weeks, I had to build a database connection dialog (a UI letting users select the SQL database server, the authentication method, and the database of their choice) for a special administration tool.

This article shows you how to inspect your system for database servers and databases on the selected server. It also shows how to create a SQL connection string using the SqlConnectionStringBuilder class.

Demo application

This month demo application is provided in both VB and C#. It is created using Visual Studio 2013 targeting the .Net Framework 4.0 but I am pretty sure most of the code works even if you are targeting the .Net Framework 2.0.

Building the UI

The UI created for this demo application is not offering all the extended properties available for a connection to a SQL server. As you can see in figure 1, only the main ones are available here. Feel free to add your own if need be.

Figure 1: The demo application in action

Filling the servers combo

When you start the demo application, you will find the server combo pre-filled with the value “(local)”. This value was convenient for me doing tests and chances are that you will also use that database.

When you dropdown that combo, the EnumerateServers method is called. This method calls the Sql.SqlDataSourceEnumerator.Instance.GetDataSources method which is not very reliable. When you search the Internet on this method, you find many posts saying that the results returned are not very accurate. On requirement is that the SQL Server Browser service runs which is not always the case. Firewalls are another frequent reason for not seeing the servers.

After the GetDataSources returned its data (if you are lucky enough), the datatable is transformed into a list of string (properly handling named instances if you have any) for later usage using this code:

_servers = New List(Of String)()
For Each row As DataRow In dt.Rows
    Dim strName As String = row("ServerName").ToString()
    Dim strInstance As String = row("InstanceName").ToString()
    If String.IsNullOrWhiteSpace(strInstance) Then
        _servers.Add(strName)
    Else
        _servers.Add(strName + "\" + strInstance)
    End If
Next

Finally, the PopulateServerComboBox method is called to add the found servers to the combo. If none are found, (local) is added for your convenience.

Filling the databases combo

The databases combo is 100% reliable if you have valid connection. After setting the name of the server and the proper authentication method (Windows or SQL), a connection to the server can be establish, and when successful, the list of tables will be returned by querying the sysdatabases system table.

The demo also detect if you are trying to connect to an Azure databases because the SQL Azure database does not have the Has_DBAccess function and we will use it in the next command.

Using the datatable filled by querying sysdatabases, a list of string is filled with the name of tables. That list is used by the PopulateDatabaseComboBox to fill the combo.

Building the connection string

Instead of concatenating strings together to create a connection string, I have decided to use the SqlConnectionStringBuilder class.

The name of the server is used to fill the Data Source property. The name of database (if any selected) sets the Initial Catalog property. The authentication properties (Integrated Security, User ID, and Password) are also filled according to the authentication method you selected.

The full code of the BuildConnectionString method reads like this:

If String.IsNullOrWhiteSpace(cboServer.Text) Then
    Return String.Empty
End If

Dim builder As New SqlConnectionStringBuilder()
If Not String.IsNullOrWhiteSpace(cboDatabases.Text) Then
    builder("Initial Catalog") = cboDatabases.Text
End If
builder("Data Source") = cboServer.Text
builder("Application Name") = Application.ProductName
If optAuthenticationWindows.Checked Then
    builder("integrated Security") = True
Else
    builder.IntegratedSecurity = False
    builder("User ID") = txtUserName.Text
    builder("Password") = txtPassword.Text
End If
Return builder.ConnectionString

Clicking the Get Connection String button shows something like figure 2. This is a typical connection string that any ADO.Net connection can use.

Figure 2: Showing the connection string

Some people might have discovered that the figure 3 is showing a property of the connection that I haven’t talked yet. This new property is Application Name. I like to set this property just in case I need to monitor my database to see which applications are accessing my databases. If you don’t set this property and you run a command on your SQL server (like sp_who2), you will find the famous “.Net SqlClient Data Provider” as the program name of your connection. If you have multiple .Net application, this is not helping at all.

Validating the connection

Every dialog letting you input connection properties offers you a test button. This button just test if the connection is valid by opening it.

But in the steps here above, we have already kind of validated if the connection is correct. By querying the server to return the list of databases, if the combo is filled and you pick one of the values, your job is done!

This button is only really useful if you type everything (including the database name) yourself.

Clicking this button will show a dialog like the one in figure 3.

Figure 3: Connection validation

Conclusion

I concur that not all applications need to offer a dialog like this one. You don’t want to open your database to all the users. Or at least, you will secure your database so they are not doing anything harmful to your precious data.