Chapter 2 showed how easy data source controls are to use, but there is more to them than their ease of use. Data source controls provide a declarative way to define not only the connection to a data store, but also the commands used to fetch and update data. Some data source controls don't interact directly with their data source, but interact by way of a provider. A provider abstracts the functionality of dealing with a data source and provides a consistent API for applications and controls to use. The providers are also pluggable, allowing you to replace the supplied providers with ones of your own. The following four data source controls are supplied as standard with ASP.NET 2.0:
We're not going to cover all of these in this chapter, because the Object-DataSource control is covered in Chapter 4, and the SiteMapDataSource control is covered in Chapter 10. However, many of the techniques for the SqlDataSource and ObjectDataSource are similar. The SqlDataSource ControlThe SqlDataSource control provides a two-tier model for interacting with relational databases. It abstracts away from the developer the need to explicitly create connections and commands, leaving you free to concentrate on the data statements. When using the drag and drop functionality shown in the previous chapter, these statements default to explicit SQL, but you can also use stored procedures. There is no way to have stored procedures automatically created and used in the SqlDataSource, so you'll have to do this yourself. However, a quick solution is to use the drag-and-drop functionality, and then use the SQL statements as the SQL for your stored procedures. To configure a SqlDataSource control, you can manually edit the properties, or use the smart task Configure Data Source option. The latter just walks you through selecting the connection (either from web.config, or creating a new one), selecting the tables and columns from which to fetch data, and optionally adding filtering and concurrency to the command. We'll look at these topics in the Filtering Data and Updating Data sections. The SqlDataSource control has a number of properties that govern its behavior. The first of these is DataSourceMode, which can be set either to DataSet or DataReader, to identify how the data is to be fetched from the underlying data source. The default is DataSet, which provides two-way binding, while DataReader provides increased performance, but only allows read-only data. To control how often the data is fetched, there are a number of properties specific to caching, allowing the control to cache data and only re-fetch it if it has changed since the last access. These will be covered in more detail in Chapter 6. The properties that you will use most often relate to the commands used to fetch and modify data. For each type of data manipulation, there are two properties: a command to run, and a command type identifying if the command is inline SQL or a stored procedure. Displaying Data with a GridView ControlAt its simplest, the SqlDataSource control only requires that two properties be set to fetch data from a database: the ConnectionString and the SelectCommand. For example: <asp:SqlDataSource runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>" SelectCommand="SELECT * FROM Products" /> This simply fetches all rows from the Products table. To switch to stored procedures, you replace the inline SQL statement with the name of the stored procedure, and add the SelectCommandType attribute, setting its value to StoredProcedure. <asp:SqlDataSource runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>" SelectCommand="usp_Products" SelectCommandType="StoredProcedure" /> You can use this data source control by binding another control, such as a GridView, to it. For example: <asp:GridView runat="server" DataSource /> This uses the DataSourceID property to identify the ID property of the data source control supplying the data, and is a common feature across the data source controls. By default the GridView displays all columns from the underlying data source, but this can be turned off by setting the Auto-GenerateColumns property to False, and by explicitly defining the columns. This will be covered in detail in the Customizing the GridView Control section. Filtering and Selecting DataOne of the greatest features of the new data controls is the support for parameterized queries, which allows you to filter data dependent upon external criteria, such as another control, or a query string. You can filter data at the database by using SelectParameters, or in the Web page after the data has been fetched, by using FilterParameters. The difference is that Select-Parameters and WHERE clauses are applied before the data is fetched, while FilterParameters fetch the data and then filter it. These are not mutually exclusive actions, and both can be performed if necessary. Selection is useful if the set of data being searched is large, because only a subset of the data is returned from the database to the page. Filtering is useful when the data is cached, because it reduces the overhead on the database. Filter and Select ParametersThere are seven sources of data that can be applied to selects and filters.
These parameters provide a great deal of flexibility in filtering data in a declarative manner. All of these parameters support the properties shown in Table 3.1.
In addition to these properties, each parameter type has its own specific properties, as shown in Table 3.2.
In action, all of these parameters work in the same way. The data to be used in the select or filter is taken from the location specified by the parameter. So for a ControlParameter, the ID identifies the control, and PropertyName identifies the property storing the data. For example, if a TextBox was used as the source of the parameter data, the Text property would be used as the PropertyName. Simple controls, such as a TextBox, aren't the only source of parameter data; a GridView could use the SelectedValue as the PropertyName, which would use the key field (from the DataKeyNames property) for the parameter value. Configuring parameters can be done with the wizard, declaratively in source view (which is what the wizard generates), or in code. Selecting Data Using the Configuration WizardThe configuration wizard is extremely easy to use, but it's worth pointing out the key areas where query parameterization is affected by it. The first place (see Figure 3.3), shows the WHERE button, which will open a window allowing the parameters to be configured. Figure 3.3. Adding a WHERE clause to a SqlDataSource controlIn Figure 3.4, you can see on the left side of the window that three pieces of key information are required:
Figure 3.4. Selecting the parameter typeFigure 3.5 shows how to configure the source of the data for the filter. In this case, you can see that TextBox1 is selected, and you can see the SQL Expression as it will be added to the code. Clicking Add will create the WHERE clause. Figure 3.5. Selecting the source of the parameter dataThe wizard doesn't do anything more than provide a way to visually set the SelectParameters on the control, so you can also set these manually if desired. Selecting Data DeclarativelyTo select data, you use the SelectParameters of the data source, as shown in Listing 3.1. Listing 3.1. Using SelectParameters
Listing 3.1 shows the results of configuring a SqlDataSource control with the wizard. Here the select command has had the WHERE clauses added. Notice that the parameter in the WHERE clause is treated just like a standard SQL Server parameter, by preceding it with the @ character. Also notice that the Name property of the ControlParameter matches the parameter name in the query. You can use any value for the name of parameters, but it must match the actual parameter, and keeping it the same as the column name makes it easy to read. Additional parameters can be added if necessary, as shown in Listing 3.2. Here there are three parameters. This first is as shown earlier, while the second is matching only rows where the UnitPrice is below a certain value, and the third matches the discontinued column. The SelectCommand still follows the standard SQL format, but each parameter has an entry in the SelectParameters. The first two take their values from controls, the first from the Text property of a TextBox and the second from the SelectedValue of a list. This shows that you can take the value from any property of a control. The third parameter takes its value from the QueryString, showing that you can mix different types of parameters in the same query. Listing 3.2. Multiple SelectParameters
Filtering is slightly different, in that the SelectCommand is not modified, but a FilterExpression property is added, and FilterParameters is used instead of SelectParameters, as shown in Listing 3.3. Listing 3.3. Using FilterExpression and FilterParameters
Notice that parameters in the FilterExpression aren't explicitly namedthey are positional. So {0} refers to the first parameter in FilterParameters; subsequent parameters would be {1}, {2}, and so on. Selecting Data in CodeWhen used declaratively, filtering and selecting requires no code, and provides a simple way to link data-bound controls to other controls, or other sources of filter data. You cannot directly filter or select from within code, although there are events that allow you to modify parameter values before an action takes place. The SqlDataSource control does have a Select method, but this only performs sorting or paging. Updating DataFor data updates, the SqlDataSource control provides a similar model to that of selection, where there is a SelectCommand, a SelectCommandType, and SelectParameters. For modifying data, we have the properties shown in Table 3.3.
Like the select features, these can be configured manually or via the wizard. For the latter, you select the Advanced option when selecting your table or query, and the window that pops up (see Figure 3.6) allows you to have the data modification statements automatically added to the data source. You also have the option of adding optimistic concurrency, which adds a column check to each column being modified. This ensures that data is only updated if all of the columns are the same, thus preventing you from overwriting data that someone else has updated. This is covered in Chapter 5 in more detail. Figure 3.6. Adding data modification to the SqlDataSource control
Once configuration has finished, you'll find the commands and parameters set. For example, consider Listing 3.4. Here the data source has been configured with only three columns to make things easier to see. Each of the commands is a normal SQL statement, and you can see how the parameters match up appropriately. For the DeleteCommand only a single parameter is needed, ProductID, because this uniquely identifies the row. This information is automatically taken from the key value fields from the database, so if you have a table whose unique key is multiple columns, you will require multiple DeleteParameters. Listing 3.4. A Fully Configured SqlDataSourceControl
For the UpdateCommand there are three parameters: ProductName and UnitPrice, which are updated, and ProductID, which identifies the row to update. Notice that even though these take different actions in the command, the parameters within the UpdateParameters are defined in the same way. For the InsertCommand, and the associated parameters, only two are requiredthe two defining the data to be inserted. Figure 3.7 shows how the <asp: Parameter/> objects are mapped to the command parameters. Figure 3.7. Mapping the parameters into the commandOne thing to notice is that for data updates, the parameters created by the wizard are the base Parameter type, because the data comes from the underlying data source. When the control bound to the data source issues a command, the appropriate values are pushed from the control into the parameters and the command is executed. |