Deborah's Developer MindScape

There are some scenarios that require building a connection string at runtime. For example, if you are building a database utility or if your application allows working with multiple databases. In these cases, you may want to ask the user for the basic parts of the connection and not require entry of a syntactically correct connection string.

Here is an example of a Windows Forms dialog for entry of connection information:

(Thanks to my friend Robin for creating this nice user interface.)

After the user makes the appropriate selections, you can use the entered values to create a connection string.

The xxxConnectionStringBuilder classes in System.Data can help you with this. Use the SqlConnectionStringBuilder class if you are accessing SQL Server or the OdbcConnectionStringBuilder class if you are referencing a Microsoft Access or other Odbc database.

This example uses the SqlConnectionStringBuilder. But you can readily replace it with OdbcConnectionStringBuilder when necessary.

The following example defines a shared/static function that builds a connection string from the controls on the form shown above.

Be sure to set a reference to System.Data.SqlClient.

In C#:

private static string CreateConnectionString()
{
    SqlConnectionStringBuilder sqlBuilder =
                          new SqlConnectionStringBuilder();

    if (!string.IsNullOrWhiteSpace(ServerNameTextBox.Text)) {
        sqlBuilder.DataSource = ServerNameTextBox.Text;

        if (!string.IsNullOrWhiteSpace(DatabaseNameComboBox.Text) {
            sqlBuilder.InitialCatalog = DatabaseNameComboBox.Text;
        }

        sqlBuilder.IntegratedSecurity = 
                      AuthenticationComboBox.SelectedValue ==
                      AuthenticationType.Windows ? true : false;

        // For SQL Server authentication, need a user Id and password
        if (sqlBuilder.IntegratedSecurity == false) {
            sqlBuilder.UserID = UserIdTextBox.Text;
            sqlBuilder.Password = PasswordTextBox.Text;
        }
    }
    return sqlBuilder.ConnectionString;
}

In VB:

Private Shared Function CreateConnectionString() As String

    Dim sqlBuilder As New SqlConnectionStringBuilder

    If Not String.IsNullOrWhiteSpace(ServerNameTextBox.Text) Then
        sqlBuilder.DataSource = ServerNameTextBox.Text

        If Not String.IsNullOrWhiteSpace(DatabaseNameComboBox.Text) Then
            sqlBuilder.InitialCatalog = DatabaseNameComboBox.Text
        End If

        sqlBuilder.IntegratedSecurity = If
                          (AuthenticationComboBox.SelectedValue =
                           AuthenticationType.Windows, True, False)

        ' For SQL Server authentication, need a user Id and password
        If sqlBuilder.IntegratedSecurity = False Then
            sqlBuilder.UserID = UserIdTextBox.Text
            sqlBuilder.Password = PasswordTextBox.Text
        End If
    End If

    Return sqlBuilder.ConnectionString
End Function

NOTE: The above code uses the implicit line continuation feature that is new in VB 10 (VS 2010). If you have an older version of VB, you will need to add the line continuation character ( _ ) where necessary.

The CreateConnectionString function shown above creates a new instance of the SqlConnectionStringBuilder. It then assigns the properties of the SqlConnectionStringBuilder from the data entry fields. The ConnectionString property then contains the appropriate connection string.

You can then use the returned connection string to connect to the selected database.

Enjoy!