Chapter 9. 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.

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

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 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 2008 database (see Figure 9.1).

Figure 9.1. Displaying the Movies database table.

Listing 9.1. ShowLocalConnection.aspx

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 associated with a SQL Server login.

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 Databases

If you need to connect to any database server other than Microsoft SQL Server, 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.

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

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 view the source code of your ASP.NET pages. In practice, however, hackers have discovered security flaws in 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, 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 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:WINDOWSMicrosoft.NETFramework[v4.0.30319]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

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:WINDOWSMicrosoft.NETFrameworkv2.0.50727

Executing the following command encrypts the <connectionStrings> section of a Web.Config file located in a folder with the path c:WebsitesMyWebsite:

aspnet_regiis -pef connectionStrings "c:WebsitesMyWebsite"

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:WebsitesMyWebsite"

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

You can use the SqlDataSource control 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.

Listing 9.7. ShowInlineCommands.aspx

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.

Listing 9.8. CountMoviesInCategory

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.

Listing 9.9. ShowMovieCount.aspx

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.

Listing 9.10. ShowFilterExpression.aspx

In Listing 9.10, the FilterExpression includes the LIKE operator and the % wildcard character. The LIKE operator performs partial matches on the movie titles.

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.

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.

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

Listing 9.11. ShowDataSourceMode.aspx

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.

Listing 9.12. HandleError.aspx

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.

The ExceptionHandled property suppresses the exception. If you do not set ExceptionHandled to true, the page explodes (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.

Listing 9.13. GridViewHandleError.aspx

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, 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 use 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, the update command is canceled (see Figure 9.8).

Figure 9.8. Canceling a command when a field is blank.

Listing 9.14. CancelCommand.aspx

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 displays 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, the empty string converts to the value Nothing (null) before the associated command executes.

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

Listing 9.15. ShowDetailsView.aspx

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, you can declare the parameters. The page in Listing 9.16 declares each of the parameters used when executing the update command.

Listing 9.16. ShowDetailsViewExplicit.aspx

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.

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 display in the DetailsView control (see Figure 9.10).

Figure 9.10. Show matching movies for each movie category.

Listing 9.17. ShowControlParameter.aspx

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 decorated with the ControlValueProperty attribute. Because the SelectedValue property of the DropDownList control is decorated with this attribute, you do not need to set this property in Listing 9.17.

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 that connects to a database called “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.

Listing 9.18. ShowPageControlParameter.aspx

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.

Listing 9.19. Vote.aspx

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

Figure 9.13. Using a client-side HTML form.

Listing 9.20. ShowFormParameter.aspx

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

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 namex of the Profile property associated with this ProfileParameter.

For example, imagine that you build 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.

Listing 9.21. Web.config

The web configuration file in Listing 9.21 includes the definition of a Profile property named DisplayName. 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.

Listing 9.22. ShowProfileParameter.aspx

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.

Listing 9.23. ShowQueryStringParameterMaster.aspx

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.

Listing 9.24. ShowQueryStringParameterDetails.aspx

The SqlDataSource control in Listing 9.24 includes a QueryStringParameter. The QueryStringParameter supplies 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. The SessionParameter object 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.

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.

Listing 9.25. ShowSessionParameter.aspx

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

Listing 9.26. AddParameter.aspx

The page in Listing 9.26 includes a srcGuestBook_Inserting() event handler, which 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.

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.

Listing 9.27. ExecuteInsert.aspx

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

Figure 9.14. A photo gallery application.

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.

Listing 9.28. PhotoGallery.aspx

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 displays the image. The Image control contained in the DataList control’s ItemTemplate points to a file named DynamicImage.ashx, which represents an HTTP Handler that renders a particular image. The DynamicImage.ashx handler is contained in Listing 9.29.

Listing 9.29. DynamicImage.ashx

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.

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 also included the code for returning the image with a DataReader, but I 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 29, “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 want to provide you with a quick sample of how you can use the SqlDataSource control to cache data.

The page in Listing 9.30 displays a list of movies cached in memory.

Listing 9.30. CacheSqlDataSource.aspx

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 (1 hour). The movies are cached in memory for a maximum of 1 hour. If you don’t supply a value for the CacheDuration property, the default value is Infinite.

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

Figure 9.15. Caching the data represented by a SqlDataSource control.

The page in Listing 9.30 illustrates only one type of caching that you can use with the SqlDataSource control. In Chapter 29, 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, “Using the ASP.Net URL Routing Engine.”

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.