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 StatementsThe SqlDataSource control can be used to represent four different types of SQL commands. The control supports the following four properties:
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
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 ProceduresThe 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:
You can create a new stored procedure in Visual Web Developer by opening the Database Explorer window, expanding a Data Connection, right-clicking Stored Procedures, and clicking Add New Stored Procedure (see Figure 9.4). Figure 9.4. Creating a new stored procedure in Visual Web Developer.The stored procedure in Listing 9.8 returns a count of the number of movies in each movie category. Listing 9.8. CountMoviesInCategory
The page in Listing 9.9 uses the CountMoviesInCategory stored procedure to display a report with a GridView control (see Figure 9.5). Figure 9.5. Showing count of movies in category.Listing 9.9. ShowMovieCount.aspx
Filtering Database RowsThe 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 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 ModeThe 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
Notice that the SqlDataSource control's DataSourceMode property is set to the value DataReader. Handling SQL Command Execution ErrorsWhenever 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:
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. 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
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 ExecutionYou 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:
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
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. |