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 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.
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.
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.
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).
Example 9.1. ShowLocalConnection.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show Local Connection</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView id="grdMovies" DataSourceID="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT * FROM Movies" ConnectionString="Data Source=.SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf; Integrated Security=True;User Instance=True" Runat="server" /> </div> </form> </body> </html>
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.
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).
Example 9.2. SqlConnectionStringBuilder.aspx
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> protected void btnConvert_Click(object sender, EventArgs e) { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(txtConnectionString.Text); lblResult.Text = builder.ConnectionString; } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>SQL Connection String Builder</title> </head> <body> <form id="form1" runat="server"> <div> <asp:TextBox id="txtConnectionString" Columns="60" Runat="Server" /> <asp:Button id="btnConvert" Text="Convert" OnClick="btnConvert_Click" Runat="Server" /> <hr /> <asp:Label id="lblResult" Runat="server" /> </div> </form> </body> </html>
After opening the page in Listing 9.2, if you enter a connection string that looks like this:
Server=localhost;UID=webuser;pwd=secret;database=Northwind
the page converts the connection string to look like this:
Data Source=localhost;Initial Catalog=Northwind;User ID=webuser;Password=secret
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.
You can configure additional providers that you can use with the SqlDataSource
control by adding new entries to the <DbProviderFactories>
section of the Machine.config
file.
For performance reasons, you should always use the native ADO.NET provider for a database. However, if your database does not have an ADO.NET provider, then you need to use either OLE DB or ODBC to connect to the database. Almost every database under the sun has either an OLE DB provider or an ODBC driver.
For example, the page in Listing 9.3 uses the ADO.NET Oracle provider to connect to an Oracle database.
Example 9.3. ConnectOracle.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Connect Oracle</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView id="grdOrders" DataSourceID="srcOrders" Runat="server" /> <asp:SqlDataSource id="srcOrders" ProviderName="System.Data.OracleClient" SelectCommand="SELECT * FROM Orders" ConnectionString="Data Source=OracleDB;Integrated Security=yes" Runat="server" /> </div> </form> </body> </html>
In Listing 9.3, notice that the ProviderName
property is set to the value System.Data.OracleClient
. The connection uses the native ADO.NET Oracle provider instead of the default provider for Microsoft SQL Server.
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 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.
Example 9.4. Web.Config
<configuration> <connectionStrings> <add name="Movies" connectionString="Data Source=.SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True; User Instance=True" /> </connectionStrings> </configuration>
You can add as many connection strings to the connectionStrings
section as you want. The page in Listing 9.5 includes a SqlDataSource
that uses the Movies connection string.
Example 9.5. ShowMovies.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show Movies</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView id="grdMovies" DataSourceID="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT * FROM Movies" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> </div> </form> </body> </html>
The expression <%$ ConnectionStrings:Movies %>
is used to represent the connection string. This expression is not case sensitive.
Rather than add a connection string to your project’s web configuration file, you can add the connection string to a web configuration file higher in the folder hierarchy. For example, you can add the connection string to the root Web.Config
file and make it available to all applications running on your server. The root Web.Config
file is located at the following path:
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727CONFIG
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.
Example 9.6. Web.Config
<configuration> <protectedData> <protectedDataSections> <add name="connectionStrings" provider="RsaProtectedConfigurationProvider" inheritedByChildren="false" /> </protectedDataSections> </protectedData> <connectionStrings> <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element" xmlns="http://www.w3.org/2001/04/xmlenc#"> <EncryptionMethod Algorithm="http://www.w3.org/2001/04/ xmlenc#tripledes-cbc" /> <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#"> <EncryptedKey Recipient="" xmlns="http://www.w3.org/2001/04/xmlenc#"> <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" /> <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#"> <KeyName>Rsa Key</KeyName> </KeyInfo> <CipherData> <CipherValue>MPLyXy7PoZ8E5VPk6K/azkGumO5tpeuWRzxx4PfgKeFwFccKx/8Zc7app++0 4c/dX7jA3uvNniFHTW6eKvrkLOsW2m6MxaeeLEfR9ME51Gy5jLa1KIXfTXKuJbXeZdiwrjCRdIqQpEj4fGZvr 3KkwI5HbGAqgK4Uu7IfBajdTJM=</CipherValue> </CipherData> </EncryptedKey> </KeyInfo> <CipherData> <CipherValue>CgnD74xMkcr7N4fgaHZNMps+e+if7dnEZ8xFw07kOBexaX+KyJvqtPuZiD2hW Dpqt5EOw6YM0Fs2uI5ocetbb74+d4kfHorC0bEjLEV+zcsJVGi2dZ80ll6sW+Y99osupaxOfr L3ld3mphMYrpcf+xafAs05s2x7H77TY01Y1goRaQ77tnkEIrQNQsHk/5eeptcE+A8scZSlaol FRNSSCdyO1TiKjPHF+MtI/8qzr2T6yjYM5Z+ZQ5TeiVvpg/6VD7K7dArIDmkFMTuQgdQBSJ UQ23dZ5V9Ja9HxqMGCea9NomBdhGC0sabDLxyPdOzGEAqOyxWKxqQM6Y0JyZKtPDg ==</CipherValue> </CipherData> </EncryptedData> </connectionStrings> </configuration>
Notice that the contents of the <connectionStrings>
section are no longer visible. However, an ASP.NET page can continue to read the value of the Movie database connection string by using the <%$ ConnectionStrings:Movie %>
expression.
The easiest way to encrypt the <connectionStrings>
section is to use the aspnet_regiis
command-line tool. This tool is located in the following folder:
C: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 28, “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.
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).
Example 9.7. ShowInlineCommands.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <style type="text/css"> .detailsView { margin:0px auto; border:solid 4px black; background-color:white; } .detailsView td { padding:8px; } html { background-color:silver; font-family:Georgia, Serif; } a { color:blue; text-decoration:none; } </style> <title>Show Inline Commands</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DetailsView id="dtlMovies" DataSourceID="srcMovies" DataKeyNames="Id" AllowPaging="true" AutoGenerateEditButton="true" AutoGenerateInsertButton="true" AutoGenerateDeleteButton="true" AutoGenerateRows="false" CssClass="detailsView" PagerSettings-Mode="NumericFirstLast" Runat="server"> <Fields> <asp:BoundField DataField="Id" HeaderText="Movie Id:" ReadOnly="true" InsertVisible="false" /> <asp:BoundField DataField="Title" HeaderText="Movie Title:" /> <asp:BoundField DataField="Director" HeaderText="Movie Director:" /> </Fields> </asp:DetailsView> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT Id,Title,Director FROM Movies" InsertCommand="INSERT Movies (Title,Director,CategoryId,DateReleased) VALUES (@Title, @Director,0,'12/15/1966')" UpdateCommand="UPDATE Movies SET Title=@Title, Director=@Director WHERE Id=@Id" DeleteCommand="DELETE Movies WHERE Id=@Id" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> </div> </form> </body> </html>
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).
The stored procedure in Listing 9.8 returns a count of the number of movies in each movie category.
Example 9.8. CountMoviesInCategory
CREATE PROCEDURE CountMoviesInCategory AS SELECT Name As Category, Count(*) As Count FROM Movies INNER JOIN MovieCategories ON CategoryId = MovieCategories.Id GROUP BY Name
The page in Listing 9.9 uses the CountMoviesInCategory
stored procedure to display a report with a GridView
control (see Figure 9.5).
Example 9.9. ShowMovieCount.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <style type="text/css"> .gridView { margin:0px auto; border:solid 4px black; background-color:white; } .gridView td, .gridView th { padding:20px; } html { background-color:silver; font-family:Georgia, Serif; } </style> <title>Show Movie Count</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView id="grdMovies" DataSourceID="srcMovies" CssClass="gridView" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="CountMoviesInCategory" SelectCommandType="StoredProcedure" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> </div> </form> </body> </html>
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).
Example 9.10. ShowFilterExpression.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <style type="text/css"> td, th { padding:10px; } </style> <title>Show Filter Expression</title> </head> <body> <form id="form1" runat="server"> <div> <asp:TextBox id="txtTitle" Runat="server" /> <asp:Button id="btnMatch" Text="Match" Runat="server" /> <hr /> <asp:GridView id="grdMovies" DataSourceId="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT Id,Title,Director,DateReleased FROM Movies" FilterExpression="Title LIKE '{0}%'" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <FilterParameters> <asp:ControlParameter Name="Title" ControlID="txtTitle" /> </FilterParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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.
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’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
.
Example 9.11. ShowDataSourceMode.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show Data Source Mode</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView id="grdMovies" DataSourceID="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" DataSourceMode="DataReader" SelectCommand="SELECT * FROM Movies" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> </div> </form> </body> </html>
Notice that 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.
Example 9.12. HandleError.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> protected void srcMovies_Selected(object sender, SqlDataSourceStatusEventArgs e) { if (e.Exception != null) { lblError.Text = e.Exception.Message; e.ExceptionHandled = true; } } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <style type="text/css"> .error { display:block; color:red; font:bold 16px Arial; margin:10px; } </style> <title>Handle Error</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" /> <asp:GridView id="grdMovies" DataSourceID="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT * FROM DontExist" ConnectionString="<%$ ConnectionStrings:Movies %>" OnSelected="srcMovies_Selected" Runat="server" /> </div> </form> </body> </html>
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).
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.
Example 9.13. GridViewHandleError.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> protected void grdMovies_RowUpdated(object sender, GridViewUpdatedEventArgs e) { if (e.Exception != null) { lblError.Text = e.Exception.Message; e.ExceptionHandled = true; } } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <style type="text/css"> .error { display:block; color:red; font:bold 16px Arial; margin:10px; } </style> <title>GridView Handle Error</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" /> <asp:GridView id="grdMovies" DataKeyNames="Id" AutoGenerateEditButton="true" DataSourceID="srcMovies" OnRowUpdated="grdMovies_RowUpdated" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT Id,Title FROM Movies" UpdateCommand="UPDATE DontExist SET Title=@Title WHERE Id=@ID" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> </div> </form> </body> </html>
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.
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).
Example 9.14. CancelCommand.aspx
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> /// <summary> /// Iterate through all parameters and check for null /// </summary> protected void srcMovies_Updating(object sender, SqlDataSourceCommandEventArgs e) { foreach (SqlParameter param in e.Command.Parameters) if (param.Value == null) { e.Cancel = true; lblError.Text = "All fields are required!"; } } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <style type="text/css"> .error { display:block; color:red; font:bold 16px Arial; margin:10px; } td,th { padding:10px; } </style> <title>Cancel Command</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" /> <asp:DetailsView id="dtlMovie" DataSourceID="srcMovies" DataKeyNames="Id" AllowPaging="true" AutoGenerateEditButton="true" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT * FROM Movies" UpdateCommand="UPDATE Movies SET Title=@Title, Director=@Director,DateReleased=@DateReleased WHERE Id=@id" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" OnUpdating="srcMovies_Updating" /> </div> </form> </body> </html>
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.
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.
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).
Example 9.15. ShowDetailsView.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show DetailsView</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DetailsView id="dtlMovie" DataKeyNames="Id" DataSourceID="srcMovies" AutoGenerateEditButton="true" DefaultMode="Edit" AllowPaging="true" runat="server" /> <asp:SqlDataSource id="srcMovies" ConnectionString="<%$ ConnectionStrings:Movies %>" SelectCommand="Select * FROM Movies" UpdateCommand="UPDATE Movies SET Title=@Title,Director=@Director, DateReleased=@DateReleased WHERE Id=@id" Runat="server" /> </div> </form> </body> </html>
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.
Example 9.16. ShowDetailsViewExplicit.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show DetailsView Explicit</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DetailsView id="dtlMovie" DataKeyNames="Id" DataSourceID="srcMovies" AutoGenerateEditButton="true" DefaultMode="Edit" AllowPaging="true" runat="server" /> <asp:SqlDataSource id="srcMovies" ConnectionString="<%$ ConnectionStrings:Movies %>" SelectCommand="Select * FROM Movies" UpdateCommand="UPDATE Movies SET Title=@Title,Director=@Director, DateReleased=@DateReleased WHERE Id=@id" Runat="server"> <UpdateParameters> <asp:Parameter Name="Title" Type="String" Size="100" DefaultValue="Untitled" /> <asp:Parameter Name="Director" Type="String" Size="100" DefaultValue="Alan Smithee" /> <asp:Parameter Name="DateReleased" Type="DateTime" /> <asp:Parameter Name="id" Type="int32" /> </UpdateParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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.
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).
Example 9.17. ShowControlParameter.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show Control Parameter</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DropDownList id="ddlMovies" DataSourceID="srcMovies" DataTextField="Title" DataValueField="Id" Runat="server" /> <asp:Button id="btnSelect" Text="Select" Runat="server" /> <hr /> <asp:DetailsView id="dtlMovie" DataSourceID="srcMovieDetails" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT Id,Title FROM Movies" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> <asp:SqlDataSource id="srcMovieDetails" SelectCommand="SELECT * FROM Movies WHERE Id=@Id" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <SelectParameters> <asp:ControlParameter Name="Id" ControlID="ddlMovies" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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.
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).
Example 9.18. ShowPageControlParameter.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> public string IPAddress { get { return Request.UserHostAddress; } } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show Page Control Parameter</title> </head> <body> <form id="form1" runat="server"> <div> <asp:FormView id="frmGuestBook" DataSourceID="srcGuestBook" DefaultMode="Insert" runat="server"> <InsertItemTemplate> <asp:Label id="lblName" Text="Your Name:" AssociatedControlID="txtName" Runat="server" /> <asp:TextBox id="txtName" Text='<%# Bind("Name") %>' Runat="server" /> <br /><br /> <asp:Label id="Label1" Text="Your Comments:" AssociatedControlID="txtComments" Runat="server" /> <br /> <asp:TextBox id="txtComments" Text='<%# Bind("Comments") %>' TextMode="MultiLine" Columns="60" Rows="4" Runat="server" /> <br /><br /> <asp:Button id="btnSubmit" Text="Submit" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <hr /> <asp:GridView id="grdGuestBook" DataSourceID="srcGuestBook" Runat="server" /> <asp:SqlDataSource id="srcGuestBook" SelectCommand="SELECT * FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (IPAddress,Name,Comments) VALUES (@IPAddress,@Name, ConnectionString="<%$ ConnectionStrings:GuestBook %>" Runat="server"> <InsertParameters> <asp:ControlParameter Name="IPAddress" ControlID="__page" PropertyName="IPAddress" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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.
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).
Example 9.19. Vote.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> void Page_Load() { if (Request.Cookies["VoterId"] == null) { string identifier = Guid.NewGuid().ToString(); HttpCookie voteCookie = new HttpCookie("VoterId", identifier); voteCookie.Expires = DateTime.MaxValue; Response.AppendCookie(voteCookie); } } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Vote</title> </head> <body> <form id="form1" runat="server"> <div> <asp:FormView id="frmVote" DataSourceID="srcVote" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label id="lblFavoriteColor" AssociatedControlID="rdlFavoriteColor" Runat="server" /> <asp:RadioButtonList id="rdlFavoriteColor" SelectedValue='<%#Bind("Color")%>' Runat="server"> <asp:ListItem Value="Red" Text="Red" Selected="True" /> <asp:ListItem Value="Blue" Text="Blue" /> <asp:ListItem Value="Green" Text="Green" /> </asp:RadioButtonList> <br /> <asp:Button id="btnSubmit" Text="Submit" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <hr /> <asp:GridView id="grdVote" DataSourceID="srcVote" Runat="server" /> <asp:SqlDataSource id="srcVote" SelectCommand="SELECT * FROM Vote ORDER BY Id DESC" InsertCommand="INSERT Vote (VoterId,Color) VALUES (@VoterId,@Color)" ConnectionString="<%$ ConnectionStrings:Vote %>" Runat="server"> <InsertParameters> <asp:CookieParameter Name="VoterId" CookieName="VoterId" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
The cookie is added in the Page_Load()
method. A unique identifier (GUID) is generated to identify the user uniquely.
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).
Example 9.20. ShowFormParameter.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> void Page_Load() { if (Request.Form["AddMovie"] != null) srcMovies.Insert(); } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show FormParameter</title> </head> <body> <form action="ShowFormParameter.aspx" method="post"> <label for="txtTitle">Movie Title:</label> <br /> <input name="txtTitle" /> <br /><br /> <label for="txtDirector">Movie Director:</label> <br /> <input name="txtDirector" /> <br /><br /> <input name="AddMovie" type="submit" value="Add Movie" /> </form> <form id="form1" runat="server"> <div> <asp:GridView id="grdMovies" DataSourceID="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT * FROM Movies" InsertCommand="INSERT Movies (Title,Director,CategoryId,DateReleased) VALUES (@Title,@Director,0,'12/25/1966')" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <InsertParameters> <asp:FormParameter Name="Title" FormField="txtTitle" DefaultValue="Untitled" /> <asp:FormParameter Name="Director" FormField="txtDirector" DefaultValue="Allen Smithee" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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.
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:
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.
Example 9.21. Web.config
<configuration> <connectionStrings> <add name="GuestBook" connectionString="Data Source=.SQLEXPRESS; AttachDbFilename=|DataDirectory|GuestBookDB.mdf; Integrated Security=True;User Instance=True" /> </connectionStrings> <system.web> <profile enabled="true"> <properties> <add name="DisplayName" defaultValue="Anonymous" /> </properties> </profile> </system.web> </configuration>
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.
Example 9.22. ShowProfileParameter.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show ProfileParameter</title> </head> <body> <form id="form1" runat="server"> <div> <asp:FormView id="frmGuestBook" DataSourceID="srcGuestBook" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label id="lblComments" Text="Enter Your Comments:" Runat="server" /> <br /> <asp:TextBox id="txtComments" Text='<%# Bind("Comments") %>' TextMode="MultiLine" Columns="50" Rows="4" Runat="server" /> <br /> <asp:Button id="btnInsert" Text="Add Comments" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <hr /> <asp:GridView id="grdGuestBook" DataSourceID="srcGuestBook" Runat="server" /> <asp:SqlDataSource id="srcGuestBook" SelectCommand="SELECT Name,Comments,EntryDate FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (Name,Comments) VALUES (@Name,@Comments)" ConnectionString="<%$ ConnectionStrings:GuestBook %>" Runat="server"> <InsertParameters> <asp:ProfileParameter Name="Name" PropertyName="DisplayName" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
Notice that the SqlDataSource
control in Listing 9.22 includes a ProfileParameter
object. This object represents the DisplayName
profile property.
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.
Example 9.23. ShowQueryStringParameterMaster.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show QueryStringParameter Master</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView id="grdMovies" DataSourceId="srcMovies" AutoGenerateColumns="false" ShowHeader="false" Runat="server"> <Columns> <asp:HyperLinkField DataTextField="Title" DataNavigateUrlFields="Id" DataNavigateUrlFormatString="ShowQueryStringParameterDetails.aspx?id={0}" /> </Columns> </asp:GridView> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT * FROM Movies" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> </div> </form> </body> </html>
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.
Example 9.24. ShowQueryStringParameterDetails.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show QueryStringParameter Details</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DetailsView id="dtlMovie" DataSourceID="srcMovie" Runat="server" /> <asp:SqlDataSource id="srcMovie" SelectCommand="SELECT * FROM Movies WHERE Id=@Id" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <SelectParameters> <asp:QueryStringParameter Name="Id" QueryStringField="Id" /> </SelectParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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
.
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.
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.
Example 9.25. ShowSessionParameter.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> void Page_Load() { Session["MovieCategoryName"] = "Animation"; } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show SessionParameter</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView id="grdMovies" DataSourceID="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" SelectCommand="SELECT Name As Category,Title,Director FROM Movies INNER JOIN MovieCategories ON CategoryId = MovieCategories.id WHERE Name=@Name" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <SelectParameters> <asp:SessionParameter Name="Name" SessionField="MovieCategoryName" /> </SelectParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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.
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.
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.
Example 9.26. AddParameter.aspx
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> protected void srcGuestBook_Inserting(object sender, SqlDataSourceCommandEventArgs e) { e.Command.Parameters.Add(new SqlParameter("@Name", User.Identity.Name)); } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Show ProfileParameter</title> </head> <body> <form id="form1" runat="server"> <div> <asp:FormView id="frmGuestBook" DataSourceID="srcGuestBook" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label id="lblComments" Text="Enter Your Comments:" Runat="server" /> <br /> <asp:TextBox id="txtComments" Text='<%# Bind("Comments") %>' TextMode="MultiLine" Columns="50" Rows="4" Runat="server" /> <br /> <asp:Button id="btnInsert" Text="Add Comments" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <hr /> <asp:GridView id="grdGuestBook" DataSourceID="srcGuestBook" Runat="server" /> <asp:SqlDataSource id="srcGuestBook" SelectCommand="SELECT Name,Comments,EntryDate FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (Name,Comments) VALUES (@Name,@Comments)" ConnectionString="<%$ ConnectionStrings:GuestBook %>" Runat="server" OnInserting="srcGuestBook_Inserting" /> </div> </form> </body> </html>
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.
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.
Example 9.27. ExecuteInsert.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> /// <summary> /// When button clicked, execute Insert command /// </summary> protected void btnAddEntry_Click(object sender, EventArgs e) { srcGuestBook.InsertParameters["Name"].DefaultValue = txtName.Text; srcGuestBook.InsertParameters["Comments"].DefaultValue = txtComments.Text; srcGuestBook.Insert(); } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Execute Insert</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label id="lblName" Text="Name:" AssociatedControlId="txtName" Runat="server" /> <br /> <asp:TextBox id="txtName" Runat="server" /> <br /><br /> <asp:Label id="lblComments" Text="Comments:" AssociatedControlId="txtComments" Runat="server" /> <br /> <asp:TextBox id="txtComments" TextMode="MultiLine" Columns="50" Rows="2" Runat="server" /> <br /><br /> <asp:Button id="btnAddEntry" Text="Add Entry" Runat="server" OnClick="btnAddEntry_Click" /> <hr /> <asp:GridView id="grdGuestBook" DataSourceId="srcGuestBook" Runat="server" /> <asp:SqlDataSource id="srcGuestBook" ConnectionString="<%$ ConnectionStrings:GuestBook %>" SelectCommand="SELECT Name,Comments FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (Name,Comments) VALUES (@Name,@Comments)" Runat="server"> <InsertParameters> <asp:Parameter Name="Name" /> <asp:Parameter Name="Comments" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
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.
Example 9.28. PhotoGallery.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Photo Gallery</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DataList id="dlstImages" DataSourceID="srcImages" RepeatColumns="3" Runat="server"> <ItemTemplate> <asp:Image ID="Image1" ImageUrl='<%# String.Format("DynamicImage.ashx?id={0}", Eval("Id")) %>' Width="250" Runat="server" /> <br /> <%# Eval("Description") %> </ItemTemplate> </asp:DataList> <hr /> <asp:FormView id="frmImage" DataSourceID="srcImages" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label id="lblImage" Text="Upload Image:" AssociatedControlId="upImage" Runat="server" /> <br /> <asp:FileUpload id="upImage" FileBytes='<%# Bind("Image") %>' Runat="server" /> <br /><br /> <asp:Label id="lblDescription" Text="Description:" AssociatedControlID="txtDescription" Runat="server" /> <br /> <asp:TextBox id="txtDescription" Text='<%# Bind("Description") %>' TextMode="MultiLine" Columns="50" Rows="2" Runat="server" /> <br /><br /> <asp:Button id="btnInsert" Text="Add Image" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <asp:SqlDataSource id="srcImages" SelectCommand="SELECT ID,Description FROM Images" InsertCommand="INSERT Images (Image,Description) VALUES (@Image,@Description)" ConnectionString="<%$ ConnectionStrings:Images %>" Runat="server" /> </div> </form> </body> </html>
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.
HTTP handlers are discussed in detail in Chapter 27, “Working with the HTTP Runtime.”
Example 9.29. DynamicImage.ashx
<%@ WebHandler Language="C#" Class="DynamicImage" %> using System.Data; using System.Web; using System.Web.Configuration; using System.Web.UI; using System.Web.UI.WebControls; /// <summary> /// Displays an image corresponding to the Id passed /// in a query string field /// </summary> public class DynamicImage : IHttpHandler { public void ProcessRequest (HttpContext context) { // Get the Id of the image to display string imageId = context.Request.QueryString["Id"]; // Use SqlDataSource to grab image bytes SqlDataSource src = new SqlDataSource(); src.ConnectionString = WebConfigurationManager.ConnectionStrings ["Images"].ConnectionString; src.SelectCommand = "SELECT Image FROM Images WHERE Id=" + imageId; // Return a DataView DataView view = (DataView)src.Select(DataSourceSelectArguments.Empty); context.Response.BinaryWrite( (byte[])view[0]["Image"]); // Return a DataReader //src.DataSourceMode = SqlDataSourceMode.DataReader; //IDataReader reader = (IDataReader)src.Select (DataSourceSelectArguments.Empty); //reader.Read(); //context.Response.BinaryWrite((byte[])reader["Image"]); //reader.Close(); } public bool IsReusable { get { return false; } } }
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.
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.
Example 9.30. CacheSqlDataSource.aspx
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> protected void srcMovies_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { lblMessage.Text = "Retrieving data from database"; } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Cache SqlDataSource</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label id="lblMessage" EnableViewState="false" Runat="server" /> <br /><br /> <asp:GridView id="grdMovies" DataSourceID="srcMovies" Runat="server" /> <asp:SqlDataSource id="srcMovies" EnableCaching="True" CacheDuration="3600" SelectCommand="SELECT * FROM Movies" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" OnSelecting="srcMovies_Selecting" /> </div> </form> </body> </html>
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
.
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.
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.