第 117 页

As applications grow, it is necessary to break up the logic into assemblies that are referenced by the

main application. In the past, if these referenced assemblies wanted to use an application setting, there

were a number of gotchas that made it problematic. With Visual Studio 2010, it is now possible to

share application settings among assemblies using the project properties designer. Figure 36-6 shows

the Settings tab of the project properties designer for a reference assembly. In this case the Access

Modifier drop-down has been set to Public to allow access to these settings from the main application.

fiGure 36-6

To access this property from the main application, you can again use the generated strongly typed

access properties:

Vb

ReferencedAssembly.My.MySettings.Default.SecondaryServer

c#

ReferencedAssembly.Properties.Settings.Default.SecondaryServer

A word of caution about using the project properties designer and referenced application settings:

If you examine the code-behind file for the settings designer, you will note that for each of the

settings you have defined there is a strongly typed access property, as previously discussed. What

is important is the DefaultSettingValueAttribute that is applied. This is significant because

it determines the value that will be returned by this property if the configuration file does not

have any value specified. In the following snippet, the default value of www.peaksite.com will be

returned if there is no SecondaryServer element defined in the configuration file:

Vb

Namespace My

Partial Friend NotInheritable Class MySettings

Inherits Global.System.Configuration.ApplicationSettingsBase

.

<Global.System.Configuration.ApplicationScopedSettingAttribute(), _

Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _

Global.System.Configuration.DefaultSettingValueAttribute("www.peaksite.com")> _

Public ReadOnly Property SecondaryServer() As String

Get

Return CType(Me("SecondaryServer "),String)

End Get

End Property

End Class

End Namespace

summary .

793

Now, you might ask why this is important when you’re dealing with referenced application settings.

It is because although the project properties designer enables you to specify that you want to allow

access to settings from another assembly, it doesn’t enable you to indicate that an application does,

in fact, reference settings from another assembly. The upshot is that when it compiles the application

it takes only the app.config file in the application project folder, rather than combining the

elements from the app.config files in the referenced assembly folder.

Unfortunately, because of the default value attribute you are unlikely to notice this until the

application is deployed and you realize that some of the settings are missing from the app.config

file. Because of this, you should make sure you manually combine these files. In this case the result

would be this:

<configuration>

.

<applicationSettings>

<ConfigurationApplication.My.MySettings>

<setting name="PrimaryServer" serializeAs="String">

<value>www.softteq.com</value>

</setting>

</ConfigurationApplication.My.MySettings>

<ReferencedAssembly.My.MySettings>

<setting name="SecondaryServer" serializeAs="String">

<value>www.peaksite.com</value>

</setting>

</ReferencedAssembly.My.MySettings>

</applicationSettings>

</configuration>

suMMary

In this chapter you have learned how configuration files can be used not only to control how your

application runs, but also to store settings that may need to be adjusted at runtime. You should now

be able to store simple name-value information, as well as more structured information, within the

configuration file.

37 37

Connection strings

what’s in this chaPter?

.

Creating connection strings for use in your application

.

Working with the Visual Studio 2010 Connection dialogs to specify

how to connect to a data source

.

Accessing connection strings from within code

A large proportion of applications need to persist data, and the obvious candidate for

enterprise software is a relational database. The .NET Framework provides support for

working with SQL Server, SQL Server Compact Edition, Oracle, ODBC, and OLE DB

databases. Many other databases are also supported through third-party providers. To

connect to any of these databases, you need to specify a connection string that determines the

location, the database, authentication information, and other connection parameters. This

chapter explains how to create and store connection strings. In addition, you learn about

encrypting and working with connection strings in code.

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 2010 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

796 .

chaPter 37 connecTion STringS

data source to an existing Windows Forms

application. You’ll connect to the sample

AdventureWorksLT database, which you

will need to download from the Codeplex

web site (www.codeplex.com and search for

AdventureWorksLT).

1 .

From the Data menu within Visual

Studio 2010, select Add New Data

Source, which opens the Data Source

Configuration Wizard.

2 .

Selecting Database, followed by either

DataSet or Entity Data Model, prompts

you to specify a 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 37-1.

The connection string connects to the

AdventureWorksLT database using the SQL

Server Express capability of attaching a

database file. Later in this chapter you look

at the properties of a SQL Server connection

string in more detail.

3 .

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

37-2 shows the dialog as it would appear for a

SQL Server Database File connection. This

dialog is specific to the database source

being configured.

Notice in Figure 37-2 that only the

basic connection properties (such as the

database filename and authentication

information) are presented.

4 .

Click the Advanced button to open the

Advanced Properties window, shown in

Figure 37-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

fiGure 37-1

fiGure 37-2

fiGure 37-3

Connection string Wizard .

797

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.

5 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 37-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.

fiGure 37-4

Selecting an alternative data source-data provider combination results 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.

6 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 are given the option to save the connection string in the application configuration

file, as shown in Figure 37-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.

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

fiGure 37-5

798

.

chaPter 37 connecTion STringS

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.

7 .

The Data Source Configuration Wizard continues 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 27 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.

AdventureWorksLTConnectionString”

connectionString=“Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\

MainUser\Downloads\SQL2008.AdventureWorksLT2008_Only_Database\AdventureWorks

LT2008_Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=Tr

ue” 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):

c#

private void OpenConnectionClick(object sender, EventArgs e){

var sqlCon = new System.Data.SqlClient.SqlConnection();

sqlCon.ConnectionString = ConfigurationManager.

ConnectionStrings["AdventureWorksLTConnectionString"].ConnectionString;

sqlCon.Open();

}

Vb

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 _

(“AdventureWorksLTConnectionString”).ConnectionString

sqlCon.Open()

End Sub

A 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

Connection string Wizard .

799

the connection string using the following strongly typed methods, rather than call them using a

string constant:

c#

Properties.Settings.Default.AdventureWorksLTConnectionString;

Vb

My.Settings.AdventureWorksLTConnectionString

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 37-6. Not only can you modify the connection string directly, but 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. Note that the ellipsis button is not visible until you click into the cell containing

the connection string value.

fiGure 37-6

You will notice in Figure 37-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.

800 . Chapter 37 Connection Strings

SQL Server Format

Probably the most familiar data provider is the SQL Server database provider, so Table 37-1 details

some of the common connection properties you may need to specify to connect to your database server.

Connection Property Description

Asynchronous Processing Determines whether the connection will support asynchronous

database calls. Most applications try to deliver a responsive user