Creating Database Connections


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 Server

By 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

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show Local Connection</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         Runat="server" />     <asp:SqlDataSource                  SelectCommand="SELECT * FROM Movies"         ConnectionString="Data Source=.\SQLEXPRESS;             AttachDbFilename=|DataDirectory|MyDatabase.mdf;             Integrated Security=True;User Instance=True"         Runat="server" />     </div>     </form> </body> </html>

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

<%@ Page Language="VB" %> <%@ Import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">     Protected Sub btnConvert_Click(ByVal sender As Object, ByVal e As EventArgs)         Dim builder As New SqlConnectionStringBuilder(txtConnectionString.Text)         lblResult.Text = builder.ConnectionString     End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>SQL Connection String Builder</title> </head> <body>     <form  runat="server">     <div>     <asp:TextBox                  Columns="60"         Runat="Server" />     <asp:Button                  Text="Convert"         OnClick="btnConvert_Click"         Runat="Server" />     <hr />     <asp:Label                  Runat="server" />     </div>     </form> </body> </html>

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 Databases

If 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:

  • System.Data.OracleClient Use the ADO.NET provider for Oracle when connecting to an Oracle database.

  • System.Data.OleDb Use the OLE DB provider when connecting to a data source that supports an OLE DB provider.

  • System.Data.Odbc Use the ODBC provider when connecting to a data source with an ODBC driver.

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

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Connect Oracle</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         Runat="server" />     <asp:SqlDataSource                  ProviderName="System.Data.OracleClient"         SelectCommand="SELECT * FROM Orders"         ConnectionString="Data Source=OracleDB;Integrated Security=yes"         Runat="server" />     </div>     </form> </body> </html>

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 File

Storing 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

<?xml version="1.0"?> <configuration>   <connectionStrings>     <add name="Movies" connectionString="Data Source=.\SQLEXPRESS;       AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True; User Instance=True" />   </connectionStrings> </configuration>

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

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show Movies</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         Runat="server" />     <asp:SqlDataSource                  SelectCommand="SELECT * FROM Movies"         ConnectionString="<%$ ConnectionStrings:Movies %>"         Runat="server" />     </div>     </form> </body> </html>

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 Strings

You 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

<?xml version="1.0"?> <configuration>   <protectedData>     <protectedDataSections>       <add name="connectionStrings" provider="RsaProtectedConfigurationProvider"         inheritedByChildren="false" />     </protectedDataSections>   </protectedData>   <connectionStrings>     <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"       xmlns="http://www.w3.org/2001/04/xmlenc#">       <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc #tripledes-cbc" />       <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">         <EncryptedKey Recipient="" xmlns="http://www.w3.org/2001/04/xmlenc#">           <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />           <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">             <KeyName>Rsa Key</KeyName>           </KeyInfo>           <CipherData> <CipherValue>MPLyXy7PoZ8E5VPk6K/azkGumO5tpeuWRzxx4PfgKeFwFccKx/8Zc7app++0 4c/dX7jA3uvNniFHTW6eKvrkLOsW2m6MxaeeLEfR9ME51Gy5jLa1KIXfTXKuJbXeZdiwrjCRdIqQpEj4fGZvr 3KkwI5HbGAqgK4Uu7IfBajdTJM=</CipherValue>           </CipherData>         </EncryptedKey>       </KeyInfo>       <CipherData>         <CipherValue>CgnD74xMkcr7N4fgaHZNMps+e+if7dnEZ8xFw07kOBexaX+KyJvqtPuZiD2hW Dpqt5EOw6YM0Fs2uI5ocetbb74+d4kfHorC0bEjLEV+zcsJVGi2dZ80ll6sW+Y99osupaxOfrL3ld3mphM Yrpcf+xafAs05s2x7H77TY01Y1goRaQ77tnkEIrQNQsHk/5eeptcE+A8scZSlaolFRNSSCdyO1TiKjPHF+ MtI/8qzr2T6yjYM5Z+ZQ5TeiVvpg/6VD7K7dArIDmkFMTuQgdQBSJUQ23dZ5V9Ja9HxqMGCea9NomBdhGC 0sabDLxyPdOzGEAqOyxWKxqQM6Y0JyZKtPDg==</CipherValue>       </CipherData>     </EncryptedData>  </connectionStrings> </configuration>

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."





ASP. NET 2.0 Unleashed
ASP.NET 2.0 Unleashed
ISBN: 0672328232
EAN: 2147483647
Year: 2006
Pages: 276

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net