• Creating Database Connections
• Using ASP.NET Parameters with the SqlDataSource
Control
• Programmatically Executing SqlDataSource
Commands
• Caching Database Data with the SqlDataSource
Control
• Summary
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.
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 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.
The SqlDataSource
control is not an appropriate control to use when building more complicated multitier 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, you should use the ObjectDataSource
control to represent your database data.
The ObjectDataSource is discussed in detail in Chapter 18, “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.
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.
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.
For security reasons, you should never include a connection string that contains security credentials in an ASP.NET page. Theoretically, no one should 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).
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
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.
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, 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.
To connect to an Oracle database, you need to install the Oracle client software on your web server.
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 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
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"
Web configuration encryption options are discussed in more detail in Chapter 34, “Configuring Applications.”
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.
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.
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
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.
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.
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.
Technically, the SqlDataSource
control returns a DataView
and not a DataSet
. Because, by default, the SqlDataSourceMode
enumeration is set to the value DataSet
, I 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 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
.
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.
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.
SqlDataSource
ControlYou 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.
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.
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.
ControlParameter
ObjectYou 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.
CookieParameter
ObjectThe 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.
FormParameter
ObjectThe 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.
ProfileParameter
ObjectThe 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 Profile
object automatically stores user-specific information across visits to a website. The Profile object is discussed in detail in Chapter 28, “Maintaining Application State.”
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.
QueryStringParameter
ObjectThe 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
.
SessionParameter
ObjectThe 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.
SqlDataSource
CommandsYou 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.
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.
Insert
, Update
, and Delete
CommandsThe 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
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.
SqlDataSource
ControlThe 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
.
You need 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.
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.”
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.