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

    'Set the command and create the dataset to load the data into
    Dim sqlcmd As New SqlClient.SqlCommand("SELECT * FROM Person.Contact;" & _
                                           "SELECT * FROM Person.ContactType", _
                                            sqlCon)

    Dim ds As New DataSet
    Dim rds As New SqlClient.SqlDataAdapter(sqlcmd)

    'Open connection, retrieve data, and close connection
    sqlCon.Open()
    rds.Fill(ds)
    sqlCon.Close()
End Sub

The important thing to note about this code sample is that the MultipleActiveResultSets property is enabled, which means that multiple SELECT statements can be specified in the SqlCommand object. The SqlCommand object is then used by the SqlDataAdapter object to fill the DataSet. The DataSet object will contain two data tables, each populated by one of the SELECT statements.