Provides a simple way to create and manage the contents of connection strings used by the SqlConnection class.
Assembly: System.Data (in System.Data.dll)
The connection string builder lets developers programmatically create syntactically correct connection strings, and parse and rebuild existing connection strings, using properties and methods of the class. The connection string builder provides strongly typed properties corresponding to the known key/value pairs allowed by SQL Server. Developers needing to create connection strings as part of applications can use the class to build and modify connection strings. The class also makes it easy to manage connection strings stored in an application configuration file.
The performs checks for valid key/value pairs. Therefore, you cannot use this class to create invalid connection strings; trying to add invalid pairs will throw an exception. The class maintains a fixed collection of synonyms and can translate from a synonym to the corresponding well-known key name.
For example, when you use the Item property to retrieve a value, you can specify a string that contains any synonym for the key you need. For example, you can specify "Network Address", "addr", or any other acceptable synonym for this key within a connection string when you use any member that requires a string that contains the key name, such as the Item property or the Remove method. See the ConnectionString property for a full list of acceptable synonyms.
The Item property handles tries to insert malicious entries. For example, the following code, using the default Item property (the indexer, in C#) correctly escapes the nested key/value pair:
Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder builder("Data Source") = "(local)" builder("Integrated Security") = True builder("Initial Catalog") = "AdventureWorks;NewValue=Bad" Console.WriteLine(builder.ConnectionString)
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); builder["Data Source"] = "(local)"; builder["integrated Security"] = true; builder["Initial Catalog"] = "AdventureWorks;NewValue=Bad"; Console.WriteLine(builder.ConnectionString);
The result is the following connection string that handles the invalid value in a safe manner:
Source=(local);Initial Catalog="AdventureWorks;NewValue=Bad"; Integrated Security=True
The following console application builds connection strings for a SQL Server 2005 database. The code uses a class to create the connection string, and then passes the ConnectionString property of the instance to the constructor of the connection class. The example also parses an existing connection string and demonstrates various ways of manipulating the connection string's contents.
This example includes a password to demonstrate how works with connection strings. In your applications, we recommend that you use Windows Authentication. If you must use a password, do not include a hard-coded password in your application.
Imports System.Data.SqlClient Module Module1 Sub Main() ' Create a new SqlConnectionStringBuilder and ' initialize it with a few name/value pairs: Dim builder As New SqlConnectionStringBuilder(GetConnectionString()) ' The input connection string used the ' Server key, but the new connection string uses ' the well-known Data Source key instead. Console.WriteLine(builder.ConnectionString) ' Pass the SqlConnectionStringBuilder an existing ' connection string, and you can retrieve and ' modify any of the elements. builder.ConnectionString = _ "server=(local);user id=ab;" & _ "password=a!Pass113;initial catalog=AdventureWorks" ' Now that the connection string has been parsed, ' you can work with individual items. Console.WriteLine(builder.Password) builder.Password = "new@1Password" builder.AsynchronousProcessing = True ' You can refer to connection keys using strings, ' as well. When you use this technique (the default ' Item property in Visual Basic, or the indexer in C#) ' you can specify any synonym for the connection string key ' name. builder("Server") = "." builder("Connect Timeout") = 1000 ' The Item property is the default for the class, ' and setting the Item property adds the value to the ' dictionary, if necessary. builder.Item("Trusted_Connection") = True Console.WriteLine(builder.ConnectionString) Console.WriteLine("Press Enter to finish.") Console.ReadLine() End Sub Private Function GetConnectionString() As String ' To avoid storing the connection string in your code, ' you can retrieve it from a configuration file. Return "Server=(local);Integrated Security=SSPI;" & _ "Initial Catalog=AdventureWorks" End Function End Module
Windows 7, Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2008 R2, Windows Server 2008, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98
The .NET Framework and .NET Compact Framework do not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.