C # Database connection string

The connection string contains initialization information that is passed as a parameter to the data source. The connection string is parsed immediately after setting. A syntax error generates a Run-time exception, but you can discover additional errors only if the data source validates the information in the connection string. After validation, the data source settings enable the various options for the connection. Connection String Keywords

The connection string is formatted with a semicolon-delimited list of key/value parameter pairs:

Keyword1=value; Keyword2=value

Spaces are ignored and keywords are case-insensitive, although the values may be case-sensitive, depending on the case of the data source. To add a value that contains semicolons, single quotes, or double quotes, the value must be enclosed in double quotes.

The valid connection string syntax varies by provider, starting with the early APIs (such as ODBC), and has evolved over the years. The SQL Server. NET Framework Data Provider combines many elements from the earlier syntax and is generally more tolerant of common connection string syntax. For more information about the connection string keywords for the. NET data provider, see ConnectionString, ConnectionString, ConnectionString, and ConnectionString. Continuous Security Information

The default setting for the Persist security Info keyword in the connection string is false.

Persist Security Info=false;

Setting this keyword to TRUE or Yes will allow security-related information, including user identities and passwords, to be obtained from the connection after the connection is opened. If you must provide a user ID and password when establishing a connection, the safest method is to discard the information when you open the connection using the information, which occurs when Persist security Info is set to false or No. This is especially important when you provide an open connection to an untrusted source, or when you permanently save the connection information to disk. If you keep the Persist security info false, you can help ensure that untrusted sources cannot access the information that is involved in the connection, and help ensure that any information that involves security does not persist on disk with the connection string information. using Windows authentication

We recommend that you connect to the server database by using Windows authentication, commonly referred to as integrated security. To specify Windows authentication, you can use any of the following two key/value pairs for the SQL Server. NET Framework Data provider:

Integrated security=true; Integrated SECURITY=SSPI;

However, only the second one will apply to the OLE DB. NET Framework data Provider. Setting integrated security=true for ConnectionString throws an exception.

For an ODBC. NET Framework Data Provider, you must specify Windows authentication using the following key/value pairs.

Trusted_connection=yes;

Creating a connection string

Each. NET Framework Data Provider provides a strongly typed connection string builder class that inherits from Dbconnectionstringbuilder. The connection string Builder enables developers to programmatically create syntactically correct connection strings based on user input, and to parse and regenerate existing connection strings. For more information, see SqlConnectionStringBuilder, Oledbconnectionstringbuilder, Odbcconnectionstringbuilder, and Oracleconnectionstringbuilder. storing and retrieving connection strings

We recommend that you do not embed the connection string in your code. If the location of the server changes, your application will need to be recompiled. In addition, unencrypted connection strings compiled into application source code can be viewed using the MSIL disassembler (Ildasm.exe). to store a connection string in a configuration file

To avoid storing connection strings in code, you can store the code in the Web.config file of the ASP.net application and in the app.config file for the Windows application.

The connection string can be stored in the <connectionStrings> element of the configuration file. The connection string is stored as a key/value pair and can be used at run time to find the value stored in the ConnectionString property. The following configuration file example displays a connection string named DatabaseConnection that references a connection string that is connected to a local instance of SQL Server.

<connectionStrings> <add name= "databaseconnection" connectionstring= "Persist security info=          false;integrated security=sspi;database=northwind;server= (local); " Providername= "System.Data.SqlClient"/> </connectionStrings>

retrieving the connection string from the configuration file

The System.Configuration namespace provides a class that uses configuration information stored in a configuration file. The Connectionstringsettings class has two properties that are mapped to the name shown in the <connectionStrings> Example section shown above. ConnectionString

The connection string. Name

The name of the connection string for the <connectionStrings> section.

You can use a protected configuration to optionally encrypt the connection strings stored in the configuration file. See Encrypting configuration Information with protected configuration, encrypting and decrypting configuration sections, and Walkthrough: Encrypting Configuration Information with a protected configuration.

Sample

The following example retrieves the connection string from the configuration file by passing the name of the connection string to ConfigurationManager and then returning the Connectionstringsettings object. The ConnectionString property is used to display this value.

C#
Using System; Using System.Configuration; Class program { static void Main () { connectionstringsettings settings; Settings = configurationmanager.connectionstrings["DatabaseConnection"]; if (settings!= null) { Console.WriteLine (settings).         ConnectionString); } } }

SqlClient Connection String

The SqlConnection ConnectionString property can get or set the connection string for SQL Server 7.0 or later databases. If you need to connect to an earlier version of SQL Server, you must use an OLE DB. NET data provider. SqlClient Connection String Syntax

The syntax for connecting to a SQL Server database is flexible. Each of the following syntax forms will connect to the AdventureWorks database on the local server using integrated security. Servers are always specified by name or by keyword (local).

"Persist security info=false;integrated security=true;initial Catalog=adventureworks; SERVER=MSSQL1 " " Persist security info=false;integrated security=sspi;database=adventureworks;server= (local) "Persist security info=false; trusted_connection=true;database=adventureworks;server= (local) "

To enforce the use of a protocol, add one of the following prefixes:

NP: (local), TCP: (local), LPC: (local)

For SQL Server authentication, use this syntax to specify a user name and password, where the asterisk represents a valid username and password.

"Persist security info=false; User id=*****; Password=*****;initial Catalog=adventureworks; Server=mysqlserver "

For a complete list of valid connection string keywords, see ConnectionString. Connect to a named instance

To connect to a named instance of SQL Server 2000 or later, use the Server name/instance name syntax.

SERVER=MYSQLSERVER/MSSQL1; "

set up a network library

Use this syntax to connect through IP addresses, where the network library is Win32 Winsock tcp/ip,1433 is the port being used (default).

Network Library=dbmssocn;data source=000.000.000.000,1433;

SQL Server allows the following network libraries to be used when establishing a connection. Dbnmpntw

Win32 Named pipe DBMSSOCN

Win32 Winsock TCP/IP DBMSSPXN

Win32 spx/ipx Dbmsvinn

Win32 Banyan Vines DBMSRPCN

Win32 Multiple protocol (Windows RPC) OLE DB connection string

The OleDbConnection ConnectionString property enables you to get or set a connection string for an OLE DB data source, such as Microsoft Access or SQL Server 6.5 or later. Use SqlConnection for SQL Server 7.0 or later. OLE DB connection string Syntax

The provider name must be specified for the OleDbConnection connection string. The following connection string connects to a Microsoft Access database using the Jet provider. Note that if the database is not protected (the default setting), the UserID and Password keywords are optional.

If the database is protected, you must provide a location for the workgroup information file.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/northwind.mdb; Jet Oledb:system database=d:/northwindsystem.mdw; User id=*****; password=*****;

For SQL Server 6.5 or later, use the SQLOLEDB keyword.

Provider=sqloledb;data source=mysqlserver;initial catalog=pubs; User id=*****; password=*****;

do not use generic data link files

You can provide OleDbConnection connection information in a Universal Data Link (UDL) file, but you should avoid doing so. The UDL file is not encrypted, and the connection string information is exposed as clear text. Because a UDL file is a resource based on an external file for an application, the. NET Framework cannot be used to protect its security. Connect to Excel

The Microsoft Jet provider is used to connect to an Excel workbook. In the following connection string, the Extended properties keyword sets Excel-specific attributes. "Hdr=yes;" Indicates that the first row contains column names, not data, "imex=1;" Notifies the driver that the "mixed" data column is always read as text. Attention

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/myexcel.xls; Extended properties= "" Excel 8.0; Hdr=yes;imex=1 ""

Note that the double quotes required for Extended Properties must also be enclosed in double quotes. Data Shape Provider connection string Syntax

When you use the Microsoft Data Shape provider, you should use both the Provider and the data Provider keywords. The following example uses the Shape provider to connect to a local instance of SQL Server.

ODBC Connection String

The OdbcConnection ConnectionString property can get or set the connection string for the OLE DB data source. The following connection string uses the Microsoft text driver.

Driver={microsoft Text Driver (*.txt *.csv)};D Bq=d:/bin

For more information about the ODBC connection string syntax, see ConnectionString. Oracle Connection String

The OracleConnection ConnectionString property can get or set the connection string for the OLE DB data source.

Data source=oracle9i; User id=*****; password=*****;

For more information about the ODBC connection string syntax, see ConnectionString.