第 118 页

interface, so it is important for it not to freeze when retrieving

data. In the past this could only be achieved by doing the data

processing in a separate thread from the user interface. The data

access methods, such as ExecuteNonQuery, now support calls

using the Begin and End asynchronous pattern. For example,

BeginExecuteNonQuery will return immediately so the user

interface does not block while the data access is performed.

AttachDBFilename Introduced in SQL Server 2005, this property means you can

work with databases that aren’t permanently attached to a

SQL Server instance. This property is a path reference to the

primary database file that contains the database. Specifying

AttachDBFilename effectively attaches and detaches the

database when required.

Connect Timeout Determines the maximum length of time that the Open method

will block when attempting to connect to the database. This

should not be confused with the Timeout property on the

SQLCommand class, which determines the timeout for a given

command to execute.

Data Source The host name or IP address of the instance of SQL Server

that the connection will be accessing. In cases where multiple

instances exist on a given machine, or where SQL Server has

been assigned an instance name other than the default instance,

this needs to be specified as part of the Data Source field. For

example, 192.168.205.223\InstanceName.

Initial Catalog Specifies the name of the database to connect to.

Integrated Security If IntegratedSecurity is used, the Windows credentials of

the current user will be used to connect to the database server.

To provide user ID and password, this property must be set

to false. Also be aware that when working with ASP.NET

using Windows authentication without impersonation, if

IntegratedSecurity is enabled, the authenticated web user’s

credentials will be used to access the database server.

Table 37-1: Some Common Connection Properties

Each connection string property must be specified as it appears in the preceding table, but they can

be in any order in the connection string. A semicolon is used to separate each property. An example

connection string might be as follows:

Data Source=.;Initial Catalog=AdventureWorksLT;Integrated Security=True;

MultipleActiveResultSets=True

In-Code Construction

Although the connection string wizard in Visual Studio 2010 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, string builder classes also exist 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, and 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:

C#

private void LoadDataClick(object sender, EventArgs e){

//Update the connection string based on user settings

var sqlbuilder = new System.Data.SqlClient.SqlConnectionStringBuilder

(Properties.Settings.Default.AdventureWorksLTConnectionString);

if (!string.IsNullOrEmpty(this.TxtUserId.Text)){

Connection Property Description

MultipleActiveResultSets Allows multiple result sets to be returned across a

given connection. For example, a single database

command might contain two SELECT statements. If the

MultipleActiveResultSets property is enabled, the results

of both SELECT statements will be returned and can be used to

populate a DataSet. This property is compatible only with SQL

Server 2005 and above.

Password Used for the SQL Server user account used to access the

database server.

User ID Specifies the SQL Server account used to access the database

server. Mixed-mode authentication for the SQL Server must be

enabled, and the IntegratedSecurity property must be set to

false.

In-Code Construction . 801

802 .

chaPter 37 connecTion STringS

sqlbuilder.IntegratedSecurity = false;

sqlbuilder.UserID = this.TxtUserId.Text;

sqlbuilder.Password = this.TxtPassword.Text;

}

sqlbuilder.MultipleActiveResultSets = true;

//Create the connection based on the updated connection string

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

sqlCon.ConnectionString = sqlbuilder.ConnectionString;

//Set the command and create the dataset to load the data into

var sqlcmd = new System.Data.SqlClient.SqlCommand(

"SELECT * FROM Person.Contact;" +

"SELECT * FROM Person.ContactType", sqlCon);

var ds = new DataSet();

var rds = new System.Data.SqlClient.SqlDataAdapter(sqlcmd);

//Open connection, retrieve data, and close connection

sqlCon.Open();

rds.Fill(ds);

sqlCon.Close();

}

Vb

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.AdventureWorksLTConnectionString)

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

encrypting Connection strings .

803

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.

encryPtinG connection strinGs

Although best practices state that you should use Windows authentication and integrated security

wherever possible, this is not always the case; sometimes you have to resort to specifying a user

ID and password in a connection string. It is recommended that this information not be hard-

coded into your application, because it can easily be extracted from the assembly. As such, this

information needs to be either specified by the users each time they use the system, or added to

the connection string in the configuration file. The upshot of this is that you need a mechanism

for encrypting configuration sections. This walk-through shows you how to encrypt a section of a

configuration file for a web application, StagingWebsite, which has a web.config file as follows:

<?xml version=“1.0”?>

<configuration>

<connectionStrings>

<add name=“AdventureWorksLTConnectionString” connectionString=“Data Source=

.\SQLEXPRESS;AttachDbFilename=C:\Users\MainUser\Downloads\SQL2008.Adventure

WorksLT2008_Only_Database\AdventureWorksLT2008_Data.mdf;Integrated Security

=True;Connect Timeout=30;User Instance=True”

providerName=“System.Data.SqlClient” />

</connectionStrings>

<!—

.

—>

</configuration>

Using the command prompt, execute the following commands in sequence, replacing UserName with

the name of the account that the web application will run as (for example, the AspNet account):

1 cd\WINDOWS\Microsoft.NET\Framework\v2.0.50739

2 aspnet_regiis -pa “NetFrameworkConfigurationKey” “UserName”

3 aspnet_regiis -pe “connectionStrings” -app “/StagingWebsite”

Executing these commands modifies the web.config file as follows (if you get an error saying

that the RSA key container was not found, you may need to execute aspnet_regiis -pc

“NetFrameworkConfigurationKey” –exp to create the key container):

<?xml version=“1.0”?>

<configuration>

<connectionStrings configProtectionProvider=“RsaProtectedConfigurationProvider”>

<EncryptedData Type=“http://www.w3.org/2001/04/xmlenc#Element”

xmlns=“http://www.w3.org/2001/04/xmlenc#”>

<EncryptionMethod Algorithm=“http://www.w3.org/2001/04/xmlenc#tripledes-cbc” />

<KeyInfo xmlns=“http://www.w3.org/2000/09/xmldsig#”>

<EncryptedKey xmlns=“http://www.w3.org/2001/04/xmlenc#”>

<EncryptionMethod Algorithm=“http://www.w3.org/2001/04/xmlenc#rsa-1_5” />

804 .

chaPter 37 connecTion STringS

<KeyInfo xmlns=“http://www.w3.org/2000/09/xmldsig#”>

<KeyName>Rsa Key</KeyName>

</KeyInfo>

<CipherData>

<CipherValue>Y4Be/ND8fXTKl3r0CASBK0oaOSvbyijYCVUudf1AuQl

pU2HRsTyEpR2sVpxrOukiBhvcGyWlv4EM0AB9p3Ms8FgIA3Ou6mGORhxfO9eIUGD+M5tJSe6wn/

9op8mFV4W7YQZ4WIqLaAAu7MKVI6KKK/ANIKpV8l2NdMBT3uPOPi8=</CipherValue>

</CipherData>

</EncryptedKey>

</KeyInfo>

<CipherData>

<CipherValue>BeKnN/kQIMw9rFbck6IwX9NZA6WyOCSQlziWzCLA8Ff/JdA0W/dWIidnjae1

vgpS8ghouYn7BQocjvc0uGsGgXlPfvsLq18//1ArZDgiHVLAXjW6b+eKbE5vaf5ss6psJdCRRB0ab5xao

NAPHH/Db9UKMycWVqP0badN+qCQzYyU2cQFvK1S7Rum8VwgZ85Qt+FGExYpG06YqVR9tfWwqZmYwtW8iz

r7fijvspm/oRK4Yd+DGBRKuXxD6EN4kFgJUil7ktzOJAwWly4bVpmwzwJT9N6yig54lobhOahZDP05gtk

Lor/HwD9IKmRvO1jv</

CipherValue>

</CipherData>

</EncryptedData>

</connectionStrings>

<!—

.

—>

</configuration>

As you can see from this example, the connection string is no longer readable in the configuration

file. The commands you executed did two things. Ignoring the first command (because it simply

changes the directory so you can access the asp_regiis executable), the second command permits

access to the key container NetFrameworkConfigurationKey for the user Nick. This key container

is the default container for the RSAProtectedConfigurationProvider, which is specified in the

machine.config file. For your application to be able to decrypt data from the configuration file, the

user that the application is running as must be able to access the key container. To determine

the identity of this user, execute the following command:

System.Security.Principal.WindowsIdentity.GetCurrent( ).Name

The third command encrypts the connectionStrings section of the configuration file for the web

application StagingWebsite. Other sections of the configuration file can also be encrypted using the

same command. If at some later stage you need to decrypt the configuration section, execute

the same command, but with –pd instead of –pe. For example:

aspnet_regiis -pd "connectionStrings" -app "/StagingWebsite"

suMMary

This chapter showed you how to use Visual Studio 2010 to take charge of your application and

configure it to connect to a database using a connection string. With the built-in support of the data

classes in the .NET Framework, connection strings can be dynamically created and modified so you

never have to handcraft a connection string again.

38 38

resource files

what’s in this chaPter?

.

Understanding what an application resource is

.

Defining and using resources within your application

.

Defining culture-specifi c resources

.

Extending the default resource types

Developers often overlook the humble XML resource file, because it is often hidden by Visual

Studio 2010 so as not to clutter the solution. Because its most common use is as a backing

file for forms or web pages, you can write large applications without interacting directly with

resource files. However, resource files are an important tool that you need to be able to use in

order to write applications that can be easily maintained and translated into other languages.

The first part of this chapter explains why resource files are important and describes the

features that enable developers to work with them. The remainder of the chapter explains how

you can use resource files to localize an application for different languages and cultures.

what are resources?

A resource is any data required by an application, whether it is a string, an icon, an image, or

even an audio clip. Resources are non-executable and support the running of the application

through the provision of data such as location, size, and other physical properties of controls.

Though most resources are strings, images, audio clips, or icons, there is no reason why a

resource could not be a more complex object that supports serialization.

Three types of resource files can be compiled into an application: text, resx (XML resource

file), and resources (binary resource file) file formats. Whole files can also be embedded as

806 .

chaPter 38 reSource FileS

application resources where needed. Most developers who use Visual Studio 2010 will use resx files

and embedded file resources.

text file resources

Text files are the most basic sort of resource because they are limited to providing string values. In

applications for which a large number of string literals need to be managed, using a simple text file

can be the easiest way to do it because that way they are not cluttered among the other resources of

the application.

The format of strings defined in a text resource file is a name-value pair, where the name is used to

reference the resource in code, as shown in the following example: