39.1. Connection String Wizard
Connection strings are similar to XML in that, although they can be read, it is neither an enjoyable experience nor recommended to work with them directly. Because connection strings are strings, it is easy to introduce errors, misspell words, or even omit a parameter. Unlike XML, which can easily be validated against a schema, connection strings are harder to validate. The connection string wizard built into Visual Studio 2008 enables you to specify database connections without having to manually edit the connection string itself.
You can invoke the connection string wizard in a number of ways, as you will experience when you start working with any of the data controls in either the Windows Form or Web Form designers. For the purposes of illustrating the wizard, follow these steps to add a new data source to an existing Windows Forms application. You'll connect to the sample AdventureWorks database, which you will need to download from the Codeplex web site (www.codeplex.com and search for AdventureWorks).
Selecting Database enables you to determine the database connection to use. If a connection already exists, you can select it from the drop-down and the associated connection string will appear in the lower portion of the window, as shown in Figure 39-1.
Figure 39.1. Figure 39-1
The connection string will connect to the AdventureWorks database on the default database server on machine drnick with schema dbo. Later in this chapter you'll look at the properties of a SQL Server connection string in more detail.
To create a new connection, click the New Connection button to open the Add Connection dialog in which you can specify the properties of the connection string. Figure 39-2 shows the dialog as it would appear for a SQL Server database connection. This dialog is specific to the database source being configured.
Figure 39.2. Figure 39-2
Notice in Figure 39-2 that only the basic connection properties (such as server name, database name, and authentication information) are presented.
Click the Advanced button to open the Advanced Properties window, shown in Figure 39-3, where you can configure all properties for a SQL Server connection. At the bottom of this window is the connection string being constructed. The default values are omitted from the connection string. Once a value is set, it appears in the connection string and in bold in the Properties window. The list of available properties is again based on the data source being used.
Figure 39.3. Figure 39-3
Click OK to return to the Add Connection window, where you can change the type of data source by clicking the Change button. This opens the Change Data Source dialog, shown in Figure 39-4.
Figure 39.4. Figure 39-4
The list on the left contains all the data sources currently registered in the machine.config file. For a given data source, such as Microsoft SQL Server, there may be multiple data providers — in this case, the SQL Server and OLE DB providers.
Selecting an alternative data source-data provider combination will result in a different Add Connection dialog, displaying parameters that are relevant to that database connection. In most cases it is necessary to open the Advanced properties window to configure the connection itself.
After specifying the data source and connection settings using the Add Connection dialog, return to the Data Source Configuration Wizard. If you are creating a new connection, you will be given the option to save the connection string in the application configuration file, as shown in Figure 39-5. Unless you can guarantee that the location of the database, the authentication mode, or any other connection property will not change at a later stage, it is a good idea to store the connection string in the configuration file. Saving the connection string to the configuration file has the added benefit that the same configuration string can be reused throughout the application.
Figure 39.5. Figure 39-5
If you don't save the connection string to the configuration file, it is explicitly assigned to the connection object you are creating, which makes reuse difficult. Alternatively, saving the connection string in the configuration file means that other connection objects can access the same string. If the database connection changes at a later stage, you can easily update it in a single location.
The Data Source Configuration Wizard will continue to step you through selecting which database objects you want to be added to your data source. This is covered in more detail in Chapter 21 on working with DataSets.
When you save a connection string to an application configuration file, it is added to the connectionStrings configuration section, as illustrated in the following snippet from an app.config file (the same section can exist in a web.config file for a web application):
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings />
<connectionStrings>
<add
name="Connection_Strings.Properties.Settings.AdventureWorksConnectionString"
connectionString="Data Source=drnick;Initial
Catalog=AdventureWorks;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>The connectionStrings section of a configuration file uses the standard element collection pattern, which allows multiple connection strings to be specified and then referenced in code. For example, the preceding connection string can be accessed in code as follows (this assumes your project has a reference to the System.Configuration assembly):
Private Sub OpenConnectionClick(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnOpenConnection.Click
Dim sqlCon As New SqlClient.SqlConnection
sqlCon.ConnectionString = ConfigurationManager.ConnectionStrings _
("AdventureWorksConnectionString").ConnectionString
sqlCon.Open()
End SubA nice artifact of working with the connection string wizard is that it also adds strongly typed support for accessing the connection string from within your code. This means that you can access the connection string using the following strongly typed methods, rather than call them using a string constant:
C#
Properties.Settings.Default.AdventureWorksConnectionString;
VB.NET
My.Settings.AdventureWorksConnectionString
The other advantage of saving the connection string in the configuration file is that when you are editing the project settings, the connection strings are listed alongside other settings for the project as shown in Figure 39-6. Not only can you modify the connection string directly; you also have a shortcut to the connection string wizard, via the ellipsis button to the right of the connection string value, which enables you to adjust the connection properties without fear of corrupting the connection string.
Figure 39.6. Figure 39-6
You will notice in Figure 39-6 that the name of the connection string excludes the rather lengthy prefix, Connection_Strings.Properties.Settings, which is in the application configuration file. This prefix is used to determine which connection strings should be included in both the project properties designer and for providing strongly typed support.
Given the inherent danger of getting data source properties wrong when manually editing the connection strings in the configuration file versus the benefits of using either the add data source wizard or the project properties designer, it is highly recommended that you avoid the manual approach wherever possible.