Creating and Opening Connections

The first step in accessing data from a database is, of course, creating the connection. ADO.NET provides two classes for creating connections to a database: SqlConnection and OleDbConnection. The class you use depends on the type of database you will be connecting to and the needs of your application.

SqlConnection

For applications that use SQL Server as the back-end database and are unlikely to change to a different database in the future, the SqlConnection class is the appropriate choice. This class is optimized for the best performance when connecting to a SQL Server database. The SqlConnection class will also provide superior performance when accessing data in an MSDE database, since MSDE uses the same database engine and protocols as SQL Server.

Creating a SqlConnection class is simple and can be done in one line of code:

SqlConnection mySqlConn=new SqlConnection(ConnectionString);

This creates a connection called mySqlConn to the SQL Server specified by the MyConnectionString parameter passed to the constructor of the SqlConnection class. Opening the connection is as simple as calling the following:

mySqlConn.Open();

Closing the connection is just as easy:

mySqlConn.Close();

Important

As with classic ADO, it is very important that you close any connection you open when you are finished with it. Connections are not closed automatically when they go out of scope. Open connections are not returned to the connection pool, where they would be available to other clients. Leaving connections open can prevent your application from effectively handling larger numbers of users without unacceptable performance degradation.

In addition to setting the connection string by passing it to the constructor of the SqlConnection instance, you can also create the SqlConnection instance with no constructor argument and set the connection string later through the ConnectionString property.

The connection string for SqlConnection consists of key/value pairs separated by semicolons. You can delimit values by using either single or double quotes, but you cannot use both for the same value. (For example, key=‘value’s’ should be key=“value’s”.) All spaces except those appearing in quotes are ignored.

Important

When you’re creating connection strings dynamically based on user input, make sure to validate user input so that additional keys are not intentionally added to the connection string by the user. For example, a malicious user could add the database key to his password in an attempt to connect to a different database. Since the last key with the same name will be used to set the value for a connection string, failure to validate input can result in inappropriate access.

Table 9-1 lists the valid keys for a SqlConnection connection string.

Table 9-1: *SqlConnection* Connection String Keys
Key	Description
Application Name	The name of the application from which the connection is being made.
AttachDBFilename	The filename and full path to the primary file of an attachable database. This key requires the Database key to specify the database name.
Connect Timeout or Connection Timeout	The number of seconds before an attempted connection is aborted and an error is raised. The default is 15.
Connection Lifetime	The time, in seconds, that a pooled connection should remain alive. When a connection is returned to the connection pool, it is destroyed if the current time is more than this many seconds past its creation time. The default is 0, meaning that the connection will not be destroyed.
Connection Reset	A Boolean value that determines whether the connection state is reset when a connection is retrieved from the pool. If this key is set to false, the connection state will not be reset, which saves a trip to the server. But the programmer must then manually take any steps necessary to ensure that the connection state is appropriate for the application’s use. The default is true.
Current Language	The SQL Server language name.
Data Source or Server or Address or Addr or Network Address	The server name or network address of the SQL Server instance to connect to.
Enlist	Determines whether the connection is automatically enlisted in the creator’s current transaction context. The default is true.
Initial Catalog or Database	The name of the database to connect to.
Integrated Security or Trusted Connection	Determines whether the connection uses the Windows authentication credentials of the caller to authenticate against SQL Server. Setting this to true alleviates the need for authenticating a user ID and password, which means that you don’t need to worry about storing these values. The default is false.
Max Pool Size	Determines the maximum number of connections to be pooled. The default is 100.
Min Pool Size	Determines the minimum number of connections that should be in the pool. The default is 0.
Network Library or Net	Specifies the network library to use when connecting to the specified SQL Server. The default is dbmssocn, which specifies the TCP/IP sockets library. Other valid values are dbnmpntw Named pipes dbmsrpcn Multiprotocol dbmsadsn Apple Talk dbmsgnet VIA dbmsipcn Shared memory dbmsspxn IPX/SPX. The server must have the appropriate DLL for the specified network library.
Packet Size	Specifies the number of bytes per network packet to use in communicating with SQL Server. The default is 8192.
Password or Pwd	Specifies the password to use to log into the SQL Server database.
Persist Security Info	If set to false, this key prevents sensitive information, such as passwords, from being returned as a part of the connection if the connection is open. The default is false.
Pooling	Determines whether pooling is enabled for this connection. When true, a requested connection will be pulled from the appropriate pool. If no connections are available from the pool, the requested connection is created and then returned to the pool when closed. The default is true.
User ID	The name of the SQL Server user account with which to log into the server.
Workstation ID	The name of the machine connecting to SQL Server. The default is the local machine name.

OleDbConnection

For applications that need to be able to query databases other than SQL Server, such as Microsoft Access or Oracle, the OleDbConnection class is the appropriate choice. This class uses a standard OLE DB Provider string to connect to any OLE DB datasource through the OLE DB provider for that datasource.

Creating an OleDbConnection class is simple and can be done in one line of code:

OleDbConnection myOleDbConn = new OleDbConnection (ConnectionString);

This creates a connection called myOleDbConn to the database specified by the MyConnectionString parameter passed to the constructor of the OleDbConnection class. The ConnectionString argument is a standard OLE DB provider string. The following, from the MSDN .NET Framework Class Library Reference, shows some examples of OLE DB Provider strings for connecting to Oracle, Access, and SQL Server datasources, respectively:

Provider=MSDAORA; Data Source=ORACLE8i7; User ID=OLEDB;Password=OLEDB Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\bin\LocalAccess40.mdb Provider=SQLOLEDB;Data Source=MySQLServer;Integrated Security=SSPI;

The Provider= clause is required. You can also use other clauses supported by the particular OLE DB provider to set other properties for the provider. For example, the Initial Catalog= clause is used by the SQL Server OLE DB Provider to indicate the database against which queries will be run.

Note

With the integration of data providers for Oracle and ODBC data sources into the .NET Framework, you should use those data providers, rather than the OLE DB data provider, when accessing Oracle or ODBC data, as these providers are optimized for this purpose, and will be more efficient than attempting to use the .NET Framework Data Provider for OLE DB along with the OLE DB provider for the specific database type.

You open the connection by calling the following:

myOleDbConn.Open();

And you close it by calling the following:

myOleDbConn.Close();

Later in this chapter, you’ll see examples of using connections to perform data access.

Note

Most of the discussion and examples in this chapter will use either the .NET Framework Data Provider for SQL Server, or the .NET Framework Data Provider for OLE DB. Using other data providers will be similar, although there may be minor differences in the SQL syntax used to query a given database. For more information on the specifics of a given data provider, refer to the MSDN documentation for Visual Studio .NET 2003, which contains detailed documentation and examples for all of the .NET Framework Data Providers.

Using Trusted Connections

As mentioned in Chapter 6, ASP.NET is configured by default to use the unprivileged ASPNET account to run ASP.NET worker processes. This means that unlike in beta versions of ASP.NET, you must now employ one of the following two techniques to use a trusted connection:

Use Windows authentication and impersonation to connect to the database using the credentials of the logged-in user.
Set up the ASPNET account as a login in SQL Server (or any other data source that supports trusted connections).

In this section you’ll learn how to use the latter technique to enable the use of trusted connections without the need for Windows authentication.

Important

Setting up the ASPNET account in SQL Server will allow any ASP.NET application on the Web server to access the database with whatever privileges have been granted to the ASPNET account. For this reason, you should use this technique only on development systems or production systems on which you are in control of all ASP.NET applications. This technique should generally not be considered for shared server applications.

There are a couple of ways to provide access to a database for the ASPNET account, depending on the software you have installed. If you have the full version of SQL Server installed, you can use the graphical tool SQL Enterprise Manager to add the ASPNET account as a SQL Server login. If you have only MSDE installed, you will need to use the oSQL command-line utility to perform these tasks. You’ll learn both techniques in the following two sections.

Use SQL Enterprise Manager to set up the ASPNET account

Open SQL Enterprise Manager by clicking Start, All Programs, Microsoft SQL Server, and then Enterprise Manager.
Expand the tree and locate the desired SQL Server or MSDE instance. Expand this instance and locate the Security node.
Expand the Security node, right-click the Logins node, and then select New Login.
In the General tab, in the Authentication section, select the desired domain (or local machine name) from the Domain drop-down list, and then click the ellipsis (…) button next to the Name text box.
Locate and select the account named ASPNET (aspnet_wp account), click Add, and then click OK.
Also in the General tab, change the Database default to pubs (or the desired database). Click the Database Access tab.
Scroll to the pubs database (or the desired database), check the Permit check box, and then add the db_datareader and db_datawriter roles to the ASPNET login account by checking the check boxes for these roles.
Click OK. You can now use a trusted connection to read from and write to the pubs sample database (or the database you selected in Steps 6 and 7).

If you do not have SQL Enterprise Manager available, you can still take advantage of the oSQL command-line utility to add the ASPNET account, as shown in the following exercise.

Note

You must be logged in as an administrator or use an account with appropriate rights to the MSDE database to successfully complete the steps in the following procedure.

Use the oSql command-line tool to set up the ASPNET account

Start the oSql command-line utility by entering the following code at a command prompt. (If you’re using a SQL Server database other than a local version of the VSdotNET MSDE instance, enter that server or instance name as the -S parameter.)
```
oSql -S(local)\VSdotNET -E
```
You should see a 1> prompt if the command completes successfully.
Call the sp_grantlogin system stored procedure to grant login access to the ASPNET account.

The syntax should look like the following, with <domain> replaced by your domain or local machine name. The go command entered at the 2> prompt tells oSql to execute the stored procedure.
```
1> sp_grantlogin '<domain>\ASPNET 2> go
```

Call the sp_defaultdb system stored procedure to change the default database for the ASPNET account to pubs.

Note

The VSdotNET MSDE instance that ships with Visual Studio .NET does not automatically install sample databases, including the Pubs database. See Appendix C for instructions on manually adding the Pubs database to the VSdotNET MSDE instance.

The syntax should look like the following, with <domain> replaced by your domain or local machine name.

1> sp_defaultdb '<domain>\ASPNET', 'pubs 2> go

Call the sp_adduser system stored procedure to add the ASPNET login account to the pubs database, passing the db_datareader argument to add the account to the db_datareader role.

The syntax should look like the following, with <domain> replaced by your domain or local machine name.
```
1> sp_adduser '<domain>\ASPNET', 'ASPNET', 'db_datareader 2> go
```
If desired, call the sp_addrolemember system stored procedure to add the ASPNET account to the db_datawriter role. In this step, ASPNET is the username added to the pubs database in the previous step. The syntax should look like the following:
```
1> sp_addrolemember 'db_datawriter', 'ASPNET 2> go
```
Type exit and press the Enter key to exit the oSql utility.

Once you’ve set up the ASPNET account as described in the preceding steps, you should be able to access the desired database using a trusted connection, as shown in the connection strings used in the samples later in this chapter.