Professional Visual Studio® 2008

39.3. In-Code Construction

Although the connection string wizard in Visual Studio 2008 provides a convenient tool for writing connection strings, it is often necessary to build one dynamically — a feat easily done with the SqlConnectionStringBuilder class. In fact, there are also string builder classes for Oracle, ODBC, and OLE DB, and they all derive from the generic DBConnectionStringBuilder class, which exposes the ConnectionString property.

This example demonstrates creating a connection builder object, based on an existing connection string, changing the authentication mode to use the user ID and password provided by the user before assigning the new connection string to the connection object. In addition, the example demonstrates the use of the MultipleActiveResultSets property to retrieve multiple tables from the database using a single command object:

Private Sub LoadDataClick(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click 'Update the connection string based on user settings Dim sqlbuilder As New SqlClient.SqlConnectionStringBuilder _ (My.Settings.AdventureWorksConnectionString) If Not Me.TxtUserId.Text = "" Then sqlbuilder.IntegratedSecurity = False sqlbuilder.UserID = Me.TxtUserId.Text sqlbuilder.Password = Me.TxtPassword.Text End If sqlbuilder.MultipleActiveResultSets = True 'Create the connection based on the updated connection string Dim sqlCon As New SqlClient.SqlConnection sqlCon.ConnectionString = sqlbuilder.ConnectionString ...