Configuring Provider-Specific Connection Strings
The ConnectionString property of a SqlConnection allows you to get or set a connection string for a SQL Server 7.0 or later database. If you need to connect to an earlier version of SQL Server, you must use the .NET Data Provider for OleDb.
SqlClient Connection String Syntax
Syntax for connecting to a SQL Server database is flexible. Each of the following forms of syntax will use integrated security to connect to the AdventureWorks database on a local server. Always specify the server by name or by the 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 force a protocol, add one of the following prefixes:
np:(local), tcp:(local), lpc:(local)
For SQL Server Authentication, use this syntax specify a user name and password, where asterisks represent a valid user name 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.
Connecting to Named Instances
To connect to a named instance of SQL Server 2000 or later, use the server name\instance name syntax.
Server=MySqlServer\MSSQL1;"
Connecting to User Instances
A user instance is an instance of SQL Server Express that is executing under the user's Windows credentials and is intended to give users running Windows under a least-access user account (LUA) the capability of installing and running SQL Server Express databases. The User Instance and AttachDBFilename ConnectionString keywords allow a SqlConnection to connect to a user instance. User instances are also supported by the SqlConnectionStringBuilder UserInstance and AttachDBFilename properties.
The following connection string specifies connecting to a user instance by setting User Instance set to true and attaches the InstanceDb database.
Data Source=.\\SQLExpress;Integrated Security=true;
User Instance=true;AttachDBFilename=|DataDirectory|\InstanceDB.mdf;
Initial Catalog=InstanceDB;
For more information, see Working with User Instances.
Setting the Network Library
Use this syntax to connect using an IP address, where the network library is Win32 Winsock TCP/IP and 1433 is the port being used (the default).
Network Library=dbmssocn;Data Source=000.000.000.000,1433;
SQL Server allows you to use the following network libraries when establishing a connection.
- dbnmpntw
Win32 Named Pipes
- dbmssocn
Win32 Winsock TCP/IP
- dbmsspxn
Win32 SPX/IPX
- dbmsvinn
Win32 Banyan Vines
- dbmsrpcn
Win32 Multi-Protocol (Windows RPC)
OleDb Connection Strings
The ConnectionString property of a OleDbConnection allows you to get or set a connection string for an OLE DB data source, such as Microsoft Access or SQL Server 6.5 or earlier. Use a SqlConnection for SQL Server 7.0 or later.
OleDb Connection String Syntax
You must specify a provider name for an OleDbConnection connection string. The following connection string connects to a Microsoft Access database using the Jet provider. Note that the UserID and Password keywords are optional if the database is unsecured (the default).
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\Northwind.mdb;User ID=Admin;Password=;
If the database is secured, you must provide the location of 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=*****;
Use the sqloledb keyword for SQL Server 6.5 or earlier.
Provider=sqloledb;Data Source=MySqlServer;Initial Catalog=pubs;User Id=*****;Password=*****;
.gif) Security Note |
|---|
It is possible to supply connection information for an OleDbConnection in a Universal Data Link (UDL) file; however you should avoid doing so. UDL files are not encrypted, and expose connection string information in clear text. Because a UDL file is an external file-based resource to your application, it cannot be secured using the .NET Framework. |
Connecting to Excel
The Microsoft Jet provider is used to connect to an Excel workbook. In the following connection string, the Extended Properties keyword sets properties that are specific to Excel. "HDR=Yes;" indicates that the first row contains column names, not data and "IMEX=1;" tells the driver to always read "intermixed" data columns as text. Note that
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""
Note that the double-quote character required for the Extended Properties must also be enclosed in double quotation marks.
Data Shape Provider Connection String Syntax
Use both the Provider and the Data Provider keywords when using the Microsoft Data Shape provider. The following example uses the Shape provider to connect to a local instance of SQL Server.
"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=(local);Initial Catalog=pubs;Integrated Security=SSPI;"
Odbc Connection Strings
The ConnectionString property of a OdbcConnection allows you to get or set a connection string for an OLE DB data source. The following connection string uses the Microsoft Text Driver.
Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=d:\bin
For more information on ODBC connection string syntax, see ConnectionString.
Oracle Connection Strings
The ConnectionString property of a OracleConnection allows you to get or set a connection string for an OLE DB data source.
Data Source=Oracle9i;User ID=*****;Password=*****;
For more information on ODBC connection string syntax, see ConnectionString.