When creating a connection, you must
specify several pieces of required information. Typically, this
includes the type of authentication or user to authenticate, the
location of the database server, and the name of the database. In
addition, OLE DB connection strings specify an OLE DB provider, and
ODBC connection strings specify an ODBC driver. To specify this
information, use the ConnectionString property.
The ConnectionString contains a series of
name/value settings delimited by
semicolons (;). The
order of these settings is unimportant, as is the
capitalization. Taken together,
they specify the information needed to create a connection. Table 3-3 describes some settings you can use.
Parameters that are used for connection pooling are omitted; they are
discussed later in this chapter.
Connection strings are data source-specific, although they tend to have broad similarities. Most parameters in Table 3-2 are supported by the SQL Server, OLE DB, and Oracle providers, although some exceptions apply. Consult the documentation for your particular database product or your OLE DB or ODBC driver for more information.
Table 3-4 lists some connection string settings that are specific to SQL Server.
The following code snippet shows how you might set the
ConnectionString property on a
SqlConnection object. The actual connection string
details are omitted.
SqlConnection con = new SqlConnection(); con.ConnectionString = "...";
All standard ADO.NET Connection objects also
provide a constructor that accepts a value for the
ConnectionString property. For example, the
following code statement creates a SqlConnection
object and sets the ConnectionString property in
one statement. It’s equivalent to the previous
example.
SqlConnection con = new SqlConnection("...");The next few sections present some sample connection strings with commonly used settings. Because the connection string varies depending on the provider, these examples are separated into provider-specific sections.
When using a SQL Server database, you need to specify
the server name using the Data Source parameter
(use localhost for the current computer), the
Initial
Catalog parameter (the
database name), and the authentication information.
You have two options for supplying the authentication information. If your database uses SQL Server authentication, you can pass a user ID and password defined in SQL Server. This account should have permissions for the tables you want to access:
SqlConnection con = new SqlConnection("Data Source=localhost;" +
"Initial Catalog=Northwind;user id=
userid;password=
password
");If your database allows integrated Windows authentication, you can
signal this fact with the Integrated Security=SSPI
connection string parameter. The Windows operating system then
supplies the user account token for the currently logged-in user.
This is more secure because the login information
doesn’t need to be visible in the code (or
transmitted over the network):
SqlConnection con = new SqlConnection("Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI");Keep in mind that integrated security won’t always execute in the security context of the application user. For example, consider a distributed application that performs a database query through a web service. If the web service connects using integrated authentication, it uses the login account of the ASP.NET worker process, not the account of the client making the request. The story is similar with a component exposed through .NET remoting, which uses the account that loaded the remote component host.
MSDE is a scaled-down, freely
distributable version of SQL Server you can use to develop very small
systems with less than five users (see Appendix C
for a brief overview). MSDE uses the same connection string format as
SQL Server (in fact, MSDE uses the SQL Server engine under the hood).
Like SQL Server, MSDE supports integrated authentication and SQL
authentication. The only difference is found in the Data Source parameter, which consists of two parts: the computer
name and the data source name, separated by a backslash character.
The data source name will be NetSDK if MSDE was
installed from the .NET framework SDK, or VSdotNET
if installed as part of Visual Studio .NET. If you are using MSDE on
the local machine, the server name should be set to
localhost.
Here’s an example that connects to an MSDE instance on the local computer that was installed from the .NET framework SDK:
SqlConnection con = new SqlConnection("Data Source=localhost\\NetSDK;" +
"Initial Catalog=Northwind;Integrated Security=SSPI");The OLE DB connection string resembles the SQL
Server connection string. However, the support for some parameters
depends on the OLE DB provider you use. Typically, an OLE DB
connection string requires a Data Source parameter
(use localhost for the current computer), the
Initial Catalog parameter (the database name), and
the user
id and
password parameters. It also requires a
Provider setting that indicates which OLE DB
provider to use.
The following code snippet shows a sample connection string that connects to a SQL Server database through the OLE DB provider. This is the only way to connect to a version of SQL Server earlier than 7.0:
OleDbConnection con = new OleDbConnection("Data Source=localhost;" +
"Initial Catalog=Northwind;user id=sa;password=secret;" +
"Provider=SQLOLEDB");Here’s an example that connects to an Access database file through the Jet provider:
OleDbConnection con = new OleDbConnection("Data Source=localhost;" +
"Initial Catalog=c:\Nortwdind.mdb;" +
"Provider=Microsoft.Jet.OLEDB.4.0");The ODBC connection string resembles the SQL
Server and OLE DB connection strings. However, the support for some
parameters depends on the ODBC driver used. Typically, an ODBC
connection string requires a Data Source parameter
(use localhost for the current computer), the
Initial Catalog parameter (the database name), and
the user
id and
password parameters. It also requires a
Driver setting that indicates the ODBC driver to
use, or its data source name (DSN), which associates a symbolic name
with a group of database settings that otherwise goes into the
connection string. The DSN must be enclosed in curly braces and match
exactly.
Here is an example that accesses an Excel file:
OdbcConnection con = new OdbcConnection(
"Driver={Microsoft Excel Driver (*.xls)};" +
"DBQ=c:\book1.xls");Here’s an example that uses the ODBC driver for
MySQL (available from http://www.mysql.com). It adds a new connection string setting, Option, which configures certain
low-level behaviors to support specific clients. For more
information, refer to the MySQL documentation.
OdbcConnection con = new OdbcConnection(
"Driver={MySQL ODBC 3.51 Driver};" +
"Database=test;UID=root;PWD=secret;Option=3");Use the Data Sources icon (in the Administrative Tools portion of the Control Panel) to configure ODBC DSN settings or add new drivers.
The Microsoft Oracle provider supports a smaller subset of connection-string options, as shown in Table 3-3. The Oracle provider also includes connection string settings that allow you to configure connection pooling. These are described in Chapter 5.
Here’s how you can create an
OracleConnection with a connection string:
OracleConnection con = new OracleConnection(
"Data Source=Oracle8i;Integrated Security=true");Be careful if you are constructing a connection string dynamically based on user input. For example, make sure you check that the user has not inserted any extra semicolons (or that all semicolons are contained inside apostrophes). Otherwise, the user can add additional connection string parameters, possibly tricking your code into connecting to the wrong database.
For example, you might request a password and place it in a connection string as follows:
connectionString = "Data Source=localhost;" + "Initial Catalog=Northwind;user id=" + txtUser.Text + ";password=" + txtPassword.Text;
In this case, a problem occurs if the user submits a password in the
form ValidPassword;Initial
Catalog=ValidDatabase. The connection string will
now have two Initial Catalog parameters, and it
will use the second one, which the user appended to the end of the
password!
To overcome this sort of problem, you should never allow a user to specify connection string parameters directly. Consider storing this information in a configuration file.