Executing Database Commands


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

Executing Inline SQL Statements

The SqlDataSource control can be used to represent four different types of SQL commands. The control supports the following four properties:

  • SelectCommand

  • InsertCommand

  • UpdateCommand

  • DeleteCommand

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

Listing 9.7. ShowInlineCommands.aspx

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <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  runat="server">     <div>     <asp:DetailsView                  DataSource         DataKeyNames="Id"         AllowPaging="true"         AutoGenerateEditButton="true"         AutoGenerateInsertButton="true"         AutoGenerateDeleteButton="true"         AutoGenerateRows="false"         Css         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                  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>

Figure 9.3. Executing inline SQL commands.


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

Executing Stored Procedures

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

  • SelectCommandType

  • InsertCommandType

  • UpdateCommandType

  • DeleteCommandType

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

Figure 9.4. Creating a new stored procedure in Visual Web Developer.


The stored procedure in Listing 9.8 returns a count of the number of movies in each movie category.

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

Figure 9.5. Showing count of movies in category.


Listing 9.9. ShowMovieCount.aspx

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <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  runat="server">     <div>     <asp:GridView                  DataSource         Css         Runat="server" />     <asp:SqlDataSource                  SelectCommand="CountMoviesInCategory"         SelectCommandType="StoredProcedure"         ConnectionString="<%$ ConnectionStrings:Movies %>"         Runat="server" />     </div>     </form> </body> </html>

Filtering Database Rows

The SqlDataSource control includes a FilterExpression property that enables you to filter the rows returned by the control. You can define complex Boolean filters that include parameters with this property.

For example, the page in Listing 9.10 retrieves all movies that have titles that match the string entered into the TextBox control (see Figure 9.6).

Figure 9.6. Show matching movies.


Listing 9.10. ShowFilterExpression.aspx

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <style type="text/css">         td, th         {             padding:10px;         }     </style>     <title>Show Filter Expression</title> </head> <body>     <form  runat="server">     <div>     <asp:TextBox                  Runat="server" />     <asp:Button                  Text="Match"         Runat="server" />     <hr />     <asp:GridView                  DataSource         Runat="server" />     <asp:SqlDataSource                  SelectCommand="SELECT Id,Title,Director,DateReleased             FROM Movies"         FilterExpression="Title LIKE '{0}%'"         ConnectionString="<%$ ConnectionStrings:Movies %>"         Runat="server">         <FilterParameters>             <asp:ControlParameter Name="Title" Control />         </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.

Note

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


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

Changing the Data Source Mode

The SqlDataSource control can represent the data that it retrieves in two different ways. It can represent the data using either an ADO.NET DataSet or an ADO.NET DataReader.

By default, the SqlDataSource represents records using the ADO.NET DataSet object. The DataSet object provides a static, memory-resident representation of data.

Note

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


Several features of the DataBound controls work only when the controls are bound to a DataSet. For example, the GridView control supports paging and sorting data only when the control is bound to a DataSet. Furthermore, you can take advantage of the SqlDataSource control's support for caching and filtering records only when using 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.

Listing 9.11. ShowDataSourceMode.aspx

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show Data Source Mode</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         Runat="server" />     <asp:SqlDataSource                  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.

Handling SQL Command Execution Errors

Whenever you build a software application you need to plan for failure. Databases go down, users enter unexpected values in form fields, 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

[View full width]

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">     Protected Sub srcMovies_Selected(ByVal sender As Object, ByVal e As  SqlDataSourceStatusEventArgs)         If Not e.Exception Is Nothing Then             lblError.Text = e.Exception.Message             e.ExceptionHandled = True         End If     End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head  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  runat="server">     <div>     <asp:Label                  EnableViewState="false"         Css         Runat="server" />     <asp:GridView                  DataSource         Runat="server" />     <asp:SqlDataSource                  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).

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

[View full width]

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">     Protected Sub grdMovies_RowUpdated(ByVal sender As Object, ByVal e As  GridViewUpdatedEventArgs)         If Not e.Exception Is Nothing Then             lblError.Text = e.Exception.Message             e.ExceptionHandled = True         End If     End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head  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  runat="server">     <div>     <asp:Label                  EnableViewState="false"         Css         Runat="server" />     <asp:GridView                  DataKeyNames="Id"         AutoGenerateEditButton="true"         DataSource         OnRowUpdated="grdMovies_RowUpdated"         Runat="server" />     <asp:SqlDataSource                  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.

Canceling Command Execution

You can cancel SqlDataSource commands when some criterion is not met. For example, you might want to validate the parameters that you are using with the command before executing the command.

You can cancel a command by handling any of the following events exposed by the SqlDataSource control:

  • Deleting Happens immediately before the SqlDataSource executes its delete command.

  • Filtering Happens immediately before the SqlDataSource filters its data.

  • Inserting Happens immediately before the SqlDataSource executes its insert command.

  • Selecting Happens immediately before the SqlDataSource executes its select command.

  • Updating Happens immediately before the SqlDataSource executes its delete command.

For example, the page in Listing 9.14 contains a DetailsView control bound to a SqlDataSource control that represents the contents of the Movies database table. The DetailsView control enables you to update a particular movie record. However, if you leave one of the fields blank, then the update command is canceled (see Figure 9.8).

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


Listing 9.14. CancelCommand.aspx

[View full width]

<%@ Page Language="VB" %> <%@ Import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">     ''' <summary>     ''' Iterate through all parameters and check for Nothing     ''' </summary>     Protected Sub srcMovies_Updating(ByVal sender As Object, ByVal e As  SqlDataSourceCommandEventArgs)         Dim param As SqlParameter         For Each param In e.Command.Parameters             If param.Value Is Nothing Then                 e.Cancel = True                 lblError.Text = "All fields are required!"             End If         Next     End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head  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  runat="server">     <div>     <asp:Label                  EnableViewState="false"         Css         Runat="server" />     <asp:DetailsView                  DataSource         DataKeyNames="Id"         AllowPaging="true"         AutoGenerateEditButton="true"         Runat="server" />     <asp:SqlDataSource                  SelectCommand="SELECT * FROM Movies"         UpdateCommand="UPDATE Movies SET Title=@Title,             Director=@Director,DateReleased=@DateReleased             WHERE Id=@id"         ConnectionString="<%$ ConnectionStrings:Movies %>"         OnUpdating="srcMovies_Updating"         Runat="server" />     </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.




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

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