Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

May 28, 2010

Building a Connection String at Runtime

Filed under: C#,VB.NET @ 7:54 pm

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:

image

(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!

7 Comments

  1.   Luciano Evaristo Guerche (Gorše) — May 30, 2010 @ 10:03 am    Reply

    Deborah,

    Do you know whether there is a managed “Microsoft OLEDB Service Component 1.0 Type Library” so that one could code similar to “HOW TO: Build a Connection String Programmatically in ADO.NET by Using Visual C# .NET” http://support.microsoft.com/kb/310083 without having to resort to interoperability wrappers?

    Regards,

  2.   DeborahK — May 30, 2010 @ 1:22 pm    Reply

    Hi Luciano –

    Do you have to use OleDb? Most databases are accessible using Odbc.

  3.   Rostov — June 2, 2010 @ 8:25 am    Reply

    Yeah, I used to have a wrapper for connection strings on my own until I found the XXXConnectionBuilders one day and was really impressed with how easy they were to use.

    Thanks again for your articles Deborah!

  4.   glemer — February 8, 2012 @ 12:43 pm    Reply

    C# OLEDB connection strin sample

    http://csharp.net-informations.com/data-providers/csharp-oledb-connection.htm

    glemer

  5.   Ali — February 27, 2012 @ 5:36 am    Reply

    Thanks for this code,
    Can I download this form with soure code?

    Please..

    Best regards

  6.   Qayyum — April 27, 2012 @ 12:23 am    Reply

    thanks for share this wonderful article but where i cant download the source code of this connectionstring.

  7.   Ranga — February 2, 2017 @ 9:53 pm    Reply

    Dear Sir,

    Really really Thank you for this post. I was struggling since 3 days for creating a sqlconnection string from variables declared by another sql serever table. I managed to build the sqlconnection string by modifying your code as per my need.

    thanks a lot sir.

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2021 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs