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 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 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).
From the Data menu within Visual Studio 2010, select Add New Data Source, which opens the Data Source Configuration Wizard.
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.
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.
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 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.
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.configfile. 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.Note
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.
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 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 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
MainUserDownloadsSQL2008.AdventureWorksLT2008_Only_DatabaseAdventureWorks
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 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.
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.
Note
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.
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.
Table 37.1. Some Common Connection Properties
CONNECTION PROPERTY | DESCRIPTION |
|---|---|
| Determines whether the connection will support asynchronous database calls. Most applications try to deliver a responsive user 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 |
| 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 |
| Determines the maximum length of time that the |
| 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, |
| Specifies the name of the database to connect to. |
| If |
Allows multiple result sets to be returned across a given connection. For example, a single database command might contain two | |
| Used for the SQL Server user account used to access the database server. |
| Specifies the SQL Server account used to access the database server. Mixed-mode authentication for the SQL Server must be enabled, and the |
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
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)){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
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.
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:UsersMainUserDownloadsSQL2008.Adventure
WorksLT2008_Only_DatabaseAdventureWorksLT2008_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):
cdWINDOWSMicrosoft.NETFrameworkv2.0.50739aspnet_regiis -pa "NetFrameworkConfigurationKey" "UserName"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" />
<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"
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.