Using the SqlDataSource Control

The SqlDataSource control enables you to quickly and easily represent a SQL database in a web page. In many cases, you can take advantage of the SqlDataSource control to write a database-driven web page without writing a single line of code.
You use the SqlDataSource control to represent a connection and set of commands that can be executed against a SQL database. You can use the SqlDataSource control when working with Microsoft SQL Server, Microsoft SQL Server Express, Microsoft Access, Oracle, DB2, MySQL, or just about any other SQL relational database ever created by man.

NOTE
Although you can use the SqlDataSource control when working with Microsoft Access, the ASP.NET Framework does include the AccessDataSource control, which was designed specifically for Microsoft Access. Because using Microsoft Access for a website is not recommended, this book doesn’t discuss the AccessDataSource control.

The SqlDataSource control is built on top of ADO.NET. Under the covers, the SqlDataSource uses ADO.NET objects such as the DataSet, DataReader, and Command objects. Because the SqlDataSource control is a control, it enables you to use these ADO.NET objects declaratively rather than programmatically.
The SqlDataSource control is a non-visual control—it doesn’t render anything. You use the SqlDataSource control with other controls, such as the GridView or FormView controls, to display and edit database data. The SqlDataSource control can also be used to issue SQL commands against a database programmatically.

NOTE
The SqlDataSource control is not an appropriate control to use when building more complicated multi-tier applications. The SqlDataSource control forces you to mix your data access layer with your user interface layer. If you want to build a more cleanly architected multi-tier application, then you should use the ObjectDataSource control to represent your database data.

The ObjectDataSource is discussed in detail in Chapter 16, “Using the ObjectDataSource Control.”
In this chapter, you learn how to represent connections and commands with the SqlDataSource control. You also learn how to use different types of parameters when executing commands. Finally, you learn how to improve the performance of your database-driven applications by taking advantage of the SqlDataSource control’s support for caching database data.

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). 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 (and encrypt them).

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

FIGURE 9.2 Converting a connection string.

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 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. 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 its 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. 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. 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\v2.0.50727\CONFIG

Encrypting Connection Strings
You can encrypt the 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. Notice that the contents of the 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 section is to use the aspnet_regiis command-line tool. This tool is located in the following folder:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 \

Executing the following command encrypts the 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 28, “Configuring Applications.”

Executing Database Commands
In this section, you learn how to represent and execute SQL commands with the SqlDataSource control. In particular, you learn how to execute both inline SQL statements and external stored procedures. You also learn how to capture and gracefully handle errors that result from executing SQL commands.

Executing Inline SQL Statements
The SqlDataSource control can be used to represent four different types of SQL commands. The control supports the following four properties:
. SelectCommand
. InsertCommand
. UpdateCommand
. DeleteCommand

You can assign any SQL statement to any of these properties. For example, the page in Listing 9.7 uses all four properties to enable selecting, inserting, updating, and deleting records from the Movies database table (see Figure 9.3). FIGURE 9.3 Executing inline SQL commands.

The page in Listing 9.7 contains a DetailsView control bound to a SqlDataSource control. You can click the Edit link to update an existing record, the New link to insert a new record, or the Delete link to delete an existing record. The DataBound control takes advantage of all four SQL commands supported by the SqlDataSource control.

Executing Stored Procedures
The SqlDataSource control can represent SQL stored procedures just as easily as it can represent inline SQL commands. You can indicate that a command represents a stored procedure by assigning the value StoredProcedure to any of the following properties:
. SelectCommandType
. InsertCommandType
. UpdateCommandType
. DeleteCommandType

You can create a new stored procedure in Visual Web Developer by opening the Database Explorer window, expanding a Data Connection, right-clicking Stored Procedures, and clicking Add New Stored Procedure (see Figure 9.4). FIGURE 9.4 Creating a new stored procedure in Visual Web Developer.

The stored procedure in Listing 9.8 returns a count of the number of movies in each movie category. The page in Listing 9.9 uses the CountMoviesInCategory stored procedure to display a report with a GridView control (see Figure 9.5). FIGURE 9.5 Showing count of movies in category. Filtering Database Rows
The SqlDataSource control includes a FilterExpression property that enables you to filter the rows returned by the control. You can define complex Boolean filters that include parameters with this property.
For example, the page in Listing 9.10 retrieves all movies that have titles that match the string entered into the TextBox control (see Figure 9.6). FIGURE 9.6 Show matching movies. In Listing 9.10, the FilterExpression includes the LIKE operator and the ? wildcard character. The LIKE operator is used to perform partial matches on the movie titles.
Notice that the filter expression includes a {0} placeholder. The value of the txtTitle TextBox is plugged into this placeholder. You can use multiple parameters and multiple placeholders with the FilterExpression property.

NOTE
Behind the scenes, the SqlDataSource control uses the DataView.RowFilter property to filter database rows. You can find detailed documentation on proper filter syntax by looking up the DataColumn.Expression property in the .NET Framework SDK Documentation.

Using the FilterExpression property is especially useful when caching the data represented by a SqlDataSource. For example, you can cache the entire contents of the movies database table in memory and use the FilterExpression property to filter the movies displayed on a page. You can display different sets of movies depending on a user’s selection from a drop-down list of movie categories.

Changing the Data Source Mode
The SqlDataSource control can represent the data that it retrieves in two different ways. It can represent the data using either an ADO.NET DataSet or an ADO.NET DataReader.
By default, the SqlDataSource represents records using the ADO.NET DataSet object. The DataSet object provides a static, memory-resident representation of data.

NOTE
Technically, the SqlDataSource control returns a DataView and not a DataSet. Because, by default, the SqlDataSourceMode enumeration is set to the value DataSet, I’ll continue to refer to DataSets instead of DataViews.

Some features of the DataBound controls work only when the controls are bound to a DataSet. For example, the GridView control supports client-side sorting and filtering only when the control is bound to a DataSet.
The other option is to represent the data that a SqlDataSource control returns with a DataReader object. The advantage of using a DataReader is that it offers significantly better performance than the DataSet object. The DataReader represents a fast, forward-only representation of data. If you want to grab some database records and display the records in the fastest possible way, use the DataReader object.
For example, the page in Listing 9.11 retrieves the records from the Movies database by using a DataReader. Notice that the SqlDataSource control’s DataSourceMode property is set to the value DataReader.

Handling SQL Command Execution Errors
Whenever you build a software application you need to plan for failure. Databases go down, users enter unexpected values in form fields, and networks get clogged. It is miraculous that the Internet works at all.
You can handle errors thrown by the SqlDataSource control by handling any or all of the following four events:
. Deleted—Happens immediately after the SqlDataSource executes its delete command.
. Inserted—Happens immediately after the SqlDataSource executes its insert command.
. Selected—Happens immediately after the SqlDataSource executes its select command.
. Updated—Happens immediately after the SqlDataSource executes its delete command.

Each of these events is passed an EventArgs parameter that includes any exceptions raised when the command was executed. For example, in the SELECT command in Listing 9.12, movies are retrieved from the DontExist database table instead of the Movies database table. If the page in Listing 9.12 is opened in a web browser, an exception is raised when the SqlDataSource control attempts to retrieve the rows from the DontExist database table (because it doesn’t exist). In the srcMovies_Selected() method, the exception is detected and displayed in a Label control.
Notice that the ExceptionHandled property is used to suppress the exception. If you do not set ExceptionHandled to true, then the page will explode (see Figure 9.7). FIGURE 9.7 An unhandled exception.

As an alternative to handling exceptions at the level of the SqlDataSource control, you can handle the exception at the level of a DataBound control. The GridView, DetailsView, and FormView controls all include events that expose the Exception and ExceptionHandled properties.
For example, the page in Listing 9.13 includes a GridView that handles the exception raised when you attempt to edit the contents of the DontExist database table. After you open the page in Listing 9.13, you can click the Edit link next to any record to edit the record. If you click the Update link, an exception is raised because the update command attempts to update the DontExist database table. The exception is handled by the GridView control’s RowUpdated event handler.
You can handle an exception at both the level of the SqlDataSource control and the level of a DataBound control. The SqlDataSource control’s events are raised before the corresponding events are raised for the DataBound control. If you handle an exception by using the ExceptionHandled property in the SqlDataSource control’s event handler, then the exception is not promoted to the DataSource control’s event handler.

Canceling Command Execution
You can cancel SqlDataSource commands when some criterion is not met. For example, you might want to validate the parameters that you are using with the command before executing the command.
You can cancel a command by handling any of the following events exposed by the SqlDataSource control:
. Deleting—Happens immediately before the SqlDataSource executes its delete command.
. Filtering—Happens immediately before the SqlDataSource filters its data.
. Inserting—Happens immediately before the SqlDataSource executes its insert command.
. Selecting—Happens immediately before the SqlDataSource executes its select command.
. Updating—Happens immediately before the SqlDataSource executes its delete command.

For example, the page in Listing 9.14 contains a DetailsView control bound to a SqlDataSource control that represents the contents of the Movies database table. The DetailsView control enables you to update a particular movie record. However, if you leave one of the fields blank, then the update command is canceled (see Figure 9.8). FIGURE 9.8 Canceling a command when a field is blank. The page in Listing 9.14 includes a srcMovies_Updating() method. In this method, each parameter associated with the update command is compared against the value Nothing (null). If one of the parameters is null, an error message is displayed in a Label control.

Using ASP.NET Parameters with the SqlDataSource Control
You can use any of the following ASP.NET Parameter objects with the SqlDataSource control:
. Parameter—Represents an arbitrary static value.
. ControlParameter—Represents the value of a control or page property.
. CookieParameter—Represents the value of a browser cookie.
. FormParameter—Represents the value of an HTML form field.
. ProfileParameter—Represents the value of a Profile property.
. QueryStringParameter—Represents the value of a query string field.
. SessionParameter—Represents the value of an item stored in Session state.

The SqlDataSource control includes five collections of ASP.NET parameters: SelectParameters, InsertParameters, DeleteParameters, UpdateParameters, and FilterParameters. You can use these parameter collections to associate a particular ASP.NET parameter with a particular SqlDataSource command or filter.
In the following sections, you learn how to use each of these different types of parameter objects.

Using the ASP.NET Parameter Object
The ASP.NET parameter object has the following properties:
. ConvertEmptyStringToNull—When true, if a parameter represents an empty string then the empty string is converted to the value Nothing (null) before the associated command is executed.
. DefaultValue—When a parameter has the value Nothing (null), the DefaultValue is used for the value of the parameter.
. Direction—Indicates the direction of the parameter. Possible values are Input, InputOutput, Output, and ReturnValue.
. Name—Indicates the name of the parameter. Do not use the @ character when indicating the name of an ASP.NET parameter.
. Size—Indicates the data size of the parameter.
. Type—Indicates the .NET Framework type of the parameter. You can assign any value from the TypeCode enumeration to this property.

You can use the ASP.NET parameter object to indicate several parameter properties explicitly, such as a parameter’s type, size, and default value.
For example, the page in Listing 9.15 contains a DetailsView control bound to a SqlDataSource control. You can use the page to update records in the Movies database table (see Figure 9.9). FIGURE 9.9 Updating movie records. In Listing 9.15, no ASP.NET parameter objects are declared explicitly. The DetailsView control automatically creates and adds ADO.NET parameters to the SqlDataSource control’s update command before the command is executed.
If you want to be explicit about the data types and sizes of the parameters used by a SqlDataSource control, then you can declare the parameters. The page in Listing 9.16 declares each of the parameters used when executing the update command. In Listing 9.16, each of the parameters used by the update command is provided with an explicit data type. For example, the DateReleased parameter is declared to be a DateTime parameter (if you didn’t assign an explicit type to this parameter, it would default to a string).
Furthermore, the Title and Director parameters are provided with default values. If you edit a movie record and do not supply a title or director, the default values are used.

NOTE
Another situation in which explicitly declaring Parameter objects is useful is when you need to explicitly order the parameters. For example, the order of parameters is important when you use the OLE DB provider with Microsoft Access.

Using the ASP.NET ControlParameter Object
You use the ControlParameter object to represent the value of a control property. You can use it to represent the value of any control contained in the same page as the SqlDataSource control.
The ControlParameter object includes all the properties of the Parameter object and these additional properties:
. ControlID—The ID of the control that the parameter represents.
. PropertyName—The name of the property that the parameter represents.

For example, the page in Listing 9.17 includes a DropDownList control and a DetailsView control. When you select a movie from the DropDownList, details for the movie are displayed in the DetailsView control (see Figure 9.10). Notice that the second SqlDataSource control in Listing 9.17 includes a ControlParameter object. The ControlParameter represents the ID of the selected movie in the DropDownList control.
When using a ControlParameter, you must always set the value of the ControlID property to point to a control on the page. On the other hand, you are not always required to set the PropertyName property. If you do not set PropertyName, the ControlParameter object automatically looks for a property that is decorated with the ControlValueProperty attribute. Because the SelectedValue property of the DropDownList control is decorated with this attribute, you do not really need to set this property in Listing 9.17. FIGURE 9.10 Show matching movies for each movie category.

Because the Page class derives from the control class, you can use the ControlParameter object to represent the value of a Page property.
For example, the page in Listing 9.18 contains a simple guestbook. When a user adds a new entry to the guestbook, the user’s remote IP address is saved automatically with the guestbook entry (see Figure 9.11). FIGURE 9.11 Saving an IP address in guest book entries.

Notice that the ControlID property is set to the value __page. This value is the automatically generated ID for the Page class. The PropertyName property has the value IPAddress. This property is defined in the page.

Using the ASP.NET CookieParameter Object
The CookieParameter object represents a browser-side cookie. The CookieParameter includes all the properties of the base Parameter class and the following additional property:
. CookieName—The name of the browser cookie.

The page in Listing 9.19 illustrates how you can use the CookieParameter object. The page contains a voting form that you can use to vote for your favorite color. A cookie is added to the user’s browser to identify the user and prevent someone from cheating by voting more than once (see Figure 9.12). FIGURE 9.12 Vote on your favorite color. The cookie is added in the Page_Load() method. A unique identifier (GUID) is generated to identify the user uniquely.

Using the ASP.NET FormParameter Object
The FormParameter object represents a form field submitted to the server. Typically, you never work directly with browser form fields because their functionality is encapsulated in the ASP.NET form controls.
The page in Listing 9.20 contains a client-side HTML form that enables you to enter a movie title and director. When the form is submitted to the server, the values of the form fields are saved to the Movies database table (see Figure 9.13). Notice that you check whether a form field named AddMovie exists in the Page_Load() method. This is the name of the submit button. If this field exists, then you know that the client-side form was submitted and the SqlDataSource control’s Insert() method can be called to add the form fields to the database. FIGURE 9.13 Using a client-side HTML form.

Using the ASP.NET ProfileParameter Object
The ProfileParameter object enables you to represent any of the properties of the Profile object. The ProfileParameter includes all the properties of the Parameter class and the following property:
. PropertyName—Indicates the name of the Profile property associated with this ProfileParameter.

For example, imagine that you are building a Guest Book application and you want to allow users to enter their display names when adding entries to a guest book. You can add a DisplayName property to the Profile object with the web configuration file in Listing 9.21. NOTE
The Profile object automatically stores user specific information across visits to a website. The Profile object is discussed in detail in Chapter 24, “Maintaining Application State.”

The web configuration file in Listing 9.21 includes the definition of a Profile property named DisplayName. Notice that the default value of this property is Anonymous.
The page in Listing 9.22 uses the ProfileParameter object to read the value of the DisplayName property automatically when new entries are added to a Guest Book. Notice that the SqlDataSource control in Listing 9.22 includes a ProfileParameter object. This object represents the DisplayName profile property.

Using the QueryStringParameter Object
The QueryStringParameter object can represent any query string passed to a page. The QueryStringParameter class includes all the properties of the base Parameter class with the addition of the following property:
. QueryStringField—The name of the query string that the QueryStringParameter represents.

This type of parameter is particularly useful when you build Master/Detail pages. For example, the page in Listing 9.23 displays a list of movie titles. Each movie title links to a page that contains detailed information for the movie. Notice that the ID of the movie is passed to the ShowQueryStringParameterDetails.aspx page. The movie ID is passed in a query string field named id.
The page in Listing 9.24 displays detailed information for a particular movie. Notice that the SqlDataSource control in Listing 9.24 includes a QueryStringParameter. The QueryStringParameter is used to supply the movie ID in the SqlDataSource control’s SelectCommand.

Using the SessionParameter Object
The SessionParameter object enables you to represent any item stored in Session state. It includes all the properties of the base Parameter class and the following property:
. SessionField—The name of the item stored in Session state that the SessionParameter represents.

NOTE
Session state is discussed in detail in Chapter 24, “Maintaining Application State.”

The page in Listing 9.25 contains a GridView that displays a list of movies matching a movie category. The movie category is stored in Session state. Notice that the current movie category is added to the Session object in the Page_Load() method. The SqlDataSource reads the MovieCategoryName item from Session state when it retrieves the list of movies that the GridView displays.

Programmatically Executing SqlDataSource Commands
You aren’t required to use the SqlDataSource control only when working with DataBound controls. You can create parameters and execute the commands represented by a SqlDataSource control by working directly with the properties and methods of the SqlDataSource control in your code.
In this section, you learn how to add parameters programmatically to a SqlDataSource control. You also learn how to execute select, insert, update, and delete commands when using the SqlDataSource control.

Adding ADO.NET Parameters
Under the covers, the SqlDataSource control uses ADO.NET objects such as the ADO.NET DataSet, DataReader, Parameter, and Command objects to interact with a database. In particular, any ASP.NET Parameter objects that you declare when working with the SqlDataSource control get converted into ADO.NET Parameter objects.
In some cases, you will want to work directly with these ADO.NET Parameter objects when using the SqlDataSource control. For example, you might want to add additional ADO.NET parameters programmatically before executing a command.
The page in Listing 9.26 automatically adds an ADO.NET parameter that represents the current user’s username to the command that the SqlDataSource executes. Notice that the page in Listing 9.26 includes a srcGuestBook_Inserting() event handler. This event handler executes immediately before the SqlDataSource control executes its insert command. In the event handler, a new ADO.NET Parameter is added to the insert command, which represents the current user’s username.

NOTE
The names of ADO.NET parameters, unlike ASP.NET parameters, always start with the character @.

Executing Insert, Update, and Delete Commands
The SqlDataSource control has methods that correspond to each of the different types of commands that it represents:
. Delete—Enables you to execute a SQL delete command.
. Insert—Enables you to execute a SQL insert command.
. Select—Enables you to execute a SQL select command.
. Update—Enables you to execute a SQL update command.

For example, the page in Listing 9.27 contains a form for adding new entries to the GuestBook database table. This form is not contained in a DataBound control such as the FormView or DetailsView controls. The form is contained in the body of the page. When you click the Add Entry button, the SqlDataSource control’s Insert() method is executed. Executing Select Commands
The procedure for executing a select command is different from executing insert, update, and delete commands because a select command returns data. This section discusses how you can execute the SqlDataSource control’s Select() method programmatically and represent the data that the method returns.
Remember that a SqlDataSource control can return either a DataView or DataReader depending on the value of its DataSourceMode property. The SqlDataSource control’s Select() method returns an object of type IEnumerable. Both DataViews and DataReaders implement the IEnumerable interface.
To understand how you can call the Select() method programmatically, look at the following simple photo gallery application. This application enables you to upload images to a database table and display them in a page (see Figure 9.14).
First, you need to create the page that displays the images and contains the form for adding new images. The PhotoGallery.aspx page is contained in Listing 9.28. FIGURE 9.14 A photo gallery application. The page in Listing 9.28 has a FormView control that contains a FileUpload control. You can use the FileUpload control to upload images from your local hard drive to the application’s database table.
Also, the page contains a DataList control that is used to display the image. Notice that the Image control contained in the DataList control’s ItemTemplate points to a file named DynamicImage.ashx. The DynamicImage.ashx file represents an HTTP Handler that renders a particular image. The DynamicImage.ashx handler is contained in Listing 9.29.

NOTE
HTTP handlers are discussed in detail in Chapter 27, “Working with the HTTP Runtime.” In the ProcessRequest() method, an instance of the SqlDataSource control is created. The SqlDataSource control’s ConnectionString and SelectCommand properties are initialized. Finally, the SqlDataSource control’s Select() command is executed and the results are rendered with the Response.BinaryWrite() method.
Notice that the return value from the Select() method is cast explicitly to a DataView object. You need to cast the return value to either a DataView or IDataReader for it to work with the results of the Select() method.
In Listing 9.29, the image bytes are returned in a DataView. To illustrate how you can use the Select() method to return a DataReader, I’ve also included the code for returning the image with a DataReader, but I’ve added comments to the code so that it won’t execute.

Caching Database Data with the SqlDataSource Control
The easiest way to dramatically improve the performance of a database-driven website is through caching. Retrieving data from a database is one of the slowest operations that you can perform in a web page. Retrieving data from memory, on the other hand, is lightning fast. The SqlDataSource control makes it easy to cache data in your server’s memory.
Caching is discussed in detail in Chapter 25, “Caching Application Pages and Data.” In that chapter, you learn about all the different caching options supported by the SqlDataSource control. However, because it is so easy to cache data with the SqlDataSource control and caching has such a dramatic impact on performance, I wanted to provide you with a quick sample of how you can use the SqlDataSource control to cache data in this chapter.
The page in Listing 9.30 displays a list of movies that are cached in memory. In Listing 9.30, two properties of the SqlDataSource control related to caching are set. First, the EnableCaching property is set to the value True. Next, the CacheDuration property is set to a value that represents 3,600 seconds (one hour). The movies are cached in memory for a maximum of one hour. If you don’t supply a value for the CacheDuration property, the default value is Infinite.

WARNING
It is important to understand that there is no guarantee that the SqlDataSource control will cache data for the amount of time specified by its CacheDuration property. Behind the scenes, the SqlDataSource control uses the Cache object for caching. This object supports scavenging. When memory resources become low, the Cache object automatically removes items from the cache.

Notice that the page in Listing 9.30 includes a srcMovies_Selecting() event handler. This handler is called only when the movies are retrieved from the database rather than from memory. In other words, you can use this event handler to detect when the movies are dropped from the cache (see Figure 9.15).
The page in Listing 9.30 illustrates only one type of caching that you can use with the SqlDataSource control. In Chapter 25, you learn about all the advanced caching options supported by the SqlDataSource control. For example, by taking advantage of SQL cache dependencies, you can reload the cached data represented by a SqlDataSource control automatically when data in a database is changed. For more information, see the final section of Chapter 25. FIGURE 9.15 Caching the data represented by a SqlDataSource control.

Summary
In this chapter, you learned how to use the SqlDataSource control to connect and execute commands against a SQL relational database. In the first section, you learned how to represent database connection strings with the SqlDataSource control. You learned how to store connection strings in the web configuration file and encrypt the connection strings.
Next, you learned how to execute both inline SQL commands and stored procedures. You also learned how to cancel commands and handle errors gracefully.
This chapter also discussed the different types of ASP.NET parameters that you can use with the SqlDataSource control. You learned how to use the Parameter, ControlParameter, CookieParameter, FormParameter, ProfileParameter, SessionParameter, and QueryStringParameter objects.
Finally, you learned how to improve the performance of your database-driven applications through caching. You learned how you can cache the data represented by a SqlDataSource control in server memory and avoid accessing the database with each page request.