You can use the SqlDataSource control to connect to just about any SQL relational database server. In this section, you learn how to connect to Microsoft SQL Server and other databases such as Oracle. You also learn how you can store the database connection string used by the SqlDataSource securely in your web configuration files. Connecting to Microsoft SQL ServerBy default, the SqlDataSource control is configured to connect to Microsoft SQL Server version 7.0 or higher. The default provider used by the SqlDataSource control is the ADO.NET provider for Microsoft SQL Server. You represent a database connection string with the SqlDataSource control's ConnectionString property. For example, the page in Listing 9.1 includes a SqlDataSource control that connects to a local SQL Server 2005 database (see Figure 9.1). Listing 9.1. ShowLocalConnection.aspx
Figure 9.1. Displaying the Movies database table.In Listing 9.1, the SqlDataSource control uses the following connection string: Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf; Integrated Security=True;User Instance=True This connection string connects to an instance of SQL Server Express located on the local machine and a database file named MyDatabase.mdf. The connection string uses Integrated Security (a Trusted Connection) to connect to the local database. You can use the following connection string to connect to a database located on a remote server. Data Source=DataServer;Initial Catalog=Northwind; User ID=webuser;Password=secret This database connection string connects to a SQL Server database located on a remote machine named DataServer. The connection string connects to a database named Northwind. This second connection string uses SQL Standard Security instead of Integrated Security. It contains a user ID and password that are associated with a SQL Server login. Warning For security reasons, you should never include a connection string that contains security credentials in an ASP.NET page. Theoretically, no one should able to see the source of an ASP.NET page. However, Microsoft does not have a perfect track record. Later in this section, you learn how to store connection strings in the web configuration file. The .NET Framework includes a utility class, named the SqlConnectionBuilder class, that you can use when working with SQL connection strings. This class automatically converts any connection string into a canonical representation. It also exposes properties for extracting and modifying individual connection string parameters such as the Password parameters. For example, the page in Listing 9.2 automatically converts any connection string into its canonical representation (see Figure 9.2). Figure 9.2. Converting a connection string.Listing 9.2. SqlConnectionStringBuilder.aspx
After opening the page in Listing 9.2, if you enter a connection string that looks like this: Server=localhost;UID=webuser;pwd=secret;database=Northwind the page converts the connection string to look like this: Data Source=localhost;Initial Catalog=Northwind;User ID=webuser;Password=secret Connecting to Other DatabasesIf you need to connect to any database server other than Microsoft SQL Server, then you need to modify the SqlDataSource control's ProviderName property. The .NET Framework includes the following providers:
Note You can configure additional providers that you can use with the SqlDataSource control by adding new entries to the <DbProviderFactories> section of the Machine.config file. For performance reasons, you should always use the native ADO.NET provider for a database. However, if your database does not have an ADO.NET provider then you need to use either OLE DB or ODBC to connect to the database. Almost every database under the sun has either an OLE DB provider or an ODBC driver. For example, the page in Listing 9.3 uses the ADO.NET Oracle provider to connect to an Oracle database. Listing 9.3. ConnectOracle.aspx
In Listing 9.3, notice that the ProviderName property is set to the value System.Data.OracleClient. The connection uses the native ADO.NET Oracle provider instead of the default provider for Microsoft SQL Server. Note To connect to an Oracle database, you need to install the Oracle client software on your web server. Note Oracle has produced their own native ADO.NET provider. You can download the Oracle provider at http://www.oracle.com/technology/tech/windows/odpnet/index.html. Storing Connection Strings in the Web Configuration FileStoring connection strings in your pages is a bad idea for three reasons. First, it is not a good practice from the perspective of security. In theory, no one should ever be able to view the source code of your ASP.NET pages. In practice, however, hackers have discovered security flaws in the ASP.NET framework. To sleep better at night, you should store your connection strings in a separate file. Also, adding a connection string to every page makes it difficult to manage a website. If you ever need to change your password, then you need to change every page that contains it. If, on the other hand, you store the connection string in one file, you can update the password by modifying the single file. Finally, storing a connection string in a page can, potentially, hurt the performance of your application. The ADO.NET provider for SQL Server automatically uses connection pooling to improve your application's data access performance. Instead of being destroyed when they are closed, the connections are kept alive so that they can be put back into service quickly when the need arises. However, only connections that are created with the same connection strings are pooled together (an exact character-by-character match is made). Adding the same connection string to multiple pages is a recipe for defeating the benefits of connection pooling. For these reasons, you should always place your connection strings in the web configuration file. The Web.Config file in Listing 9.4 includes a connectionStrings section. Listing 9.4. Web.Config
You can add as many connection strings to the connectionStrings section as you want. The page in Listing 9.5 includes a SqlDataSource that uses the Movies connection string. Listing 9.5. ShowMovies.aspx
The expression <%$ ConnectionStrings:Movies %> is used to represent the connection string. This expression is not case sensitive. Rather than add a connection string to your project's web configuration file, you can add the connection string to a web configuration file higher in the folder hierarchy. For example, you can add the connection string to the root Web.Config file and make it available to all applications running on your server. The root Web.Config file is located at the following path: C:\WINDOWS\Microsoft.NET\Framework\[version]\CONFIG Encrypting Connection StringsYou can encrypt the <connectionStrings> section of a web configuration file. For example, Listing 9.6 contains an encrypted version of the Web.Config file that was created in Listing 9.4. Listing 9.6. Web.Config
Notice that the contents of the <connectionStrings> section are no longer visible. However, an ASP.NET page can continue to read the value of the Movie database connection string by using the <%$ ConnectionStrings:Movie %> expression. The easiest way to encrypt the <connectionStrings> section is to use the aspnet_regiis command-line tool. This tool is located in the following folder: C:\WINDOWS\Microsoft.NET\Framework\[version]\ Executing the following command encrypts the <connectionStrings> section of a Web.Config file located in a folder with the path c:\Websites\MyWebsite: aspnet_regiis -pef connectionStrings "c:\Websites\MyWebsite" The -pef option (Protect Encrypt Filepath) encrypts a particular configuration section located at a particular path. You can decrypt a section with the -pdf option like this: aspnet_regiis -pdf connectionStrings "c:\Websites\MyWebsite" Note Web configuration encryption options are discussed in more detail in Chapter 26, "Configuring Applications." |