Export (0) Print
Expand All

Storing and Retrieving Connection Strings 

Embedding connection strings in your application's code can lead to security vulnerabilities and maintenance problems. Unencrypted connection strings compiled into an application's source code can be viewed using the MSIL Disassembler (Ildasm.exe) tool. Moreover, if the connection string ever changes, your application must be recompiled. For these reasons, we recommend storing connection strings in an application configuration file.

Working with Application Configuration Files

Application configuration files contain settings that are specific to a particular application. For example, an ASP.NET application can have one or more web.config files, and a Windows application can have an optional app.config file. Configuration files share common elements, although the name and location of a configuration file vary depending on the application's host.

The connectionStrings Section

Connection strings can be stored as key/value pairs in the connectionStrings section of the configuration element of an application configuration file. Child elements include add, clear, and remove.

The following configuration file fragment demonstrates the schema and syntax for storing a connection string. The name attribute is a name that you provide to uniquely identify a connection string so that it can be retrieved at run time. The providerName is the invariant name of the .NET Framework data provider, which is registered in the machine.config file.

<?xml version='1.0' encoding='utf-8'?>
  <configuration>
    <connectionStrings>
      <clear />
      <add name="Name" 
       providerName="System.Data.ProviderName" 
       connectionString="Valid Connection String;" />
    </connectionStrings>
  </configuration>
NoteNote

You can save part of a connection string in a configuration file and use the DbConnectionStringBuilder class to complete it at run time. This is useful in scenarios where you do not know elements of the connection string ahead of time, or when you do not want to save sensitive information in a configuration file. For more information, see Building Connection Strings.

Using External Configuration Files

External configuration files are separate files that contain a fragment of a configuration file consisting of a single section. The external configuration file is then referenced by the main configuration file. Storing the connectionStrings section in a physically separate file is useful in situations where connection strings may be edited after the application is deployed. For example, the standard ASP.NET behavior is to restart an application domain when configuration files are modified, which results in state information being lost. However, modifying an external configuration file does not cause an application restart. External configuration files are not limited to ASP.NET; they can also be used by Windows applications. In addition, file access security and permissions can be used to restrict access to external configuration files. Working with external configuration files at run time is transparent, and requires no special coding.

To store connection strings in an external configuration file, create a separate file that contains only the connectionStrings section. Do not include any additional elements, sections, or attributes. This example shows the syntax for an external configuration file.

  <connectionStrings>
    <add name="Name" 
     providerName="System.Data.ProviderName" 
     connectionString="Valid Connection String;" />
  </connectionStrings>

In the main application configuration file, you use the configSource attribute to specify the fully qualified name and location of the external file. This example refers to an external configuration file named connections.config.

<?xml version='1.0' encoding='utf-8'?>
<configuration>
    <connectionStrings configSource="connections.config"/>
</configuration>

Retrieving Connection Strings at Run Time

New classes in the .NET Framework 2.0 System.Configuration namespace simplify retrieving connection strings from configuration files at run time. You can programmatically retrieve a connection string by name or by provider name.

NoteNote

The machine.config file also contains a connectionStrings section, which contains connection strings used by Visual Studio. When retrieving connection strings by provider name from the app.config file in a Windows application, the connection strings in machine.config get loaded first, and then the entries from app.config. Adding clear immediately after the connectionStrings element removes all inherited references from the data structure in memory, so that only the connection strings defined in the local app.config file are considered.

Working with the Configuration Classes

ConfigurationManager is used in the .NET Framework 2.0 when working with configuration files on the local computer, replacing the deprecated ConfigurationSettings. WebConfigurationManager is used to work with ASP.NET configuration files. It is designed to work with configuration files on a Web server, and allows programmatic access to configuration file sections such as system.web.

NoteNote

Accessing configuration files at run time requires granting permissions to the caller; the required permissions depend on the type of application, configuration file, and location. For more information, see Using the Configuration Classes and WebConfigurationManager for ASP.NET applications, and ConfigurationManager for Windows applications.

You can use the ConnectionStringSettingsCollection to retrieve connection strings from application configuration files. It contains a collection of ConnectionStringSettings objects, each of which represents a single entry in the connectionStrings section. Its properties map to connection string attributes, allowing you to retrieve a connection string by specifying the name or the provider name.

Property Description

Name

The name of the connection string. Maps to the name attribute.

ProviderName

The fully qualified provider name. Maps to the providerName attribute.

ConnectionString

The connection string. Maps to the connectionString attribute.

Example: Listing All Connection Strings

This example iterates through the ConnectionStringSettings collection and displays the Name, ProviderName, and ConnectionString properties in the console window.

NoteNote

System.Configuration.dll is not included in all project types, and you may need to set a reference to it in order to use the configuration classes. The name and location of a particular application configuration file varies by the type of application and the hosting process.

using System.Configuration;

class Program
{
    static void Main()
    {
        GetConnectionStrings();
        Console.ReadLine();
    }

    static void GetConnectionStrings()
    {
        ConnectionStringSettingsCollection settings =
            ConfigurationManager.ConnectionStrings;

        if (settings != null)
        {
            foreach(ConnectionStringSettings cs in settings)
            {
                Console.WriteLine(cs.Name);
                Console.WriteLine(cs.ProviderName);
                Console.WriteLine(cs.ConnectionString);
            }
        }
    }
}

Example: Retrieving a Connection String by Name

This example demonstrates how to retrieve a connection string from a configuration file by specifying its name. The code creates a ConnectionStringSettings object, matching the supplied input parameter to the ConnectionStrings name. If no matching name is found, the function returns null (Nothing in Visual Basic).

// Retrieves a connection string by name.
// Returns null if the name is not found.
static string GetConnectionStringByName(string name)
{
    // Assume failure.
    string returnValue = null;

    // Look for the name in the connectionStrings section.
    ConnectionStringSettings settings =
        ConfigurationManager.ConnectionStrings[name];

    // If found, return the connection string.
    if (settings != null)
        returnValue = settings.ConnectionString;

    return returnValue;
}

Example: Retrieving a Connection String by Provider Name

This example demonstrates how to retrieve a connection string by specifying the provider-invariant name in the format System.Data.ProviderName. The code iterates through the ConnectionStringSettingsCollection and returns the connection string for the first ProviderName found. If the provider name is not found, the function returns null (Nothing in Visual Basic).

// Retrieve a connection string by specifying the providerName.
// Assumes one connection string per provider in the config file.
static string GetConnectionStringByProvider(string providerName)
{
    // Return null on failure.
    string returnValue = null;

    // Get the collection of connection strings.
    ConnectionStringSettingsCollection settings =
        ConfigurationManager.ConnectionStrings;

    // Walk through the collection and return the first 
    // connection string matching the providerName.
    if (settings != null)
    {
        foreach (ConnectionStringSettings cs in settings)
        {
            if (cs.ProviderName == providerName)
                returnValue = cs.ConnectionString;
            break;
        }
    }
    return returnValue;
}

See Also

Community Additions

ADD
Show:
© 2014 Microsoft