Data Source Controls


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:

  • ObjectDataSource, which interfaces to custom classes

  • SiteMapDataSource, which interfaces to site map data, for site navigation

  • SqlDataSource, which interfaces to SQL databases

  • XmlDataSource, which interfaces to XML files

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 Control

The 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 Control

At 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 Data

One 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 Parameters

There are seven sources of data that can be applied to selects and filters.

  • ControlParameter, which takes its data from an ASP.NET server control

  • CookieParameter, which takes its data from a Cookie

  • FormParameter, which takes its data from an HTML FORM control

  • Parameter, which is the base class for the other parameters, and has no default source of data

  • ProfileParameter, which takes its data from a Profile property

  • QueryStringParameter, which takes its data from a query string value

  • SessionParameter, which takes its data from a session value

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.

Table 3.1. Common Properties of All Parameter Types

Property

Description

ConvertEmptyStringToNull

Indicates whether or not an empty value should be converted to NULL before being passed to SQL Server. The default value is True.

DefaultValue

Indicates the default value of the parameter, if one is not supplied by an external source.

Direction

Indicates the direction of data flow, and can be one of the ParameterDirection enumerations:

  • Input, for data transferred to SQL Server

  • InputOutput, for data transferred to and from SQLServer

  • Output, for data transferred from SQL Server

  • ReturnValue, for data transferred from SQL Server as the return value of a query

Name

The name of the parameter.

Size

The size of the parameter. Not required for fixed size types.

Type

The data type of the parameter. Can be one of the TypeCode enumerations: Boolean, Byte, Char, DateTime, DBNull, Decimal, Double, Empty, Int16,Int32, Int64, Object, SByte, Single, String, UInt16,UInt32, UInt64.


In addition to these properties, each parameter type has its own specific properties, as shown in Table 3.2.

Table 3.2. Specific Parameter Type Properties

Parameter

Property

Description

ControlParameter

ControlID

The ID of the control supplying the parameter data.

 

PropertyName

The name of the property on the control. For example, the Text property.

CookieParameter

CookieName

The name of the cookie supplying the parameter data.

FormField

FormParameter

The name of the form field supplying the parameter data.

ProfileProperty

PropertyName

The name of the Profile property supplying the parameter data.

QueryString Parameter

QueryString Field

The name of the query string field supplying the parameter data.

Session Parameter

SessionField

The name of the session field supplying the parameter data.


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 Wizard

The 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 control


In Figure 3.4, you can see on the left side of the window that three pieces of key information are required:

  • The Column, which is the column in the set of data that you filtering on. This is the column name added to the WHERE clause in the SQL

  • The Operator, which gives different options for comparison, such as equals, greater than, and LIKE

  • The source, which is the source of the value, and matches the parameter types

Figure 3.4. Selecting the parameter type


Figure 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 data


The 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 Declaratively

To select data, you use the SelectParameters of the data source, as shown in Listing 3.1.

Listing 3.1. Using SelectParameters

<asp:SqlDataSource  runat="server"   ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"   SelectCommand="SELECT * FROM [Products] WHERE                  ([ProductName] LIKE '%' + @ProductName + '%')">   <SelectParameters>     <asp:ControlParameter Name="ProductName" Type="String"       Control PropertyName="Text" />   </SelectParameters>   </asp:SqlDataSource>

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

<asp:SqlDataSource  runat="server"   ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"   SelectCommand="SELECT * FROM [Products] WHERE                  ([ProductName] LIKE '%' + @ProductName + '%')                  AND UnitPrice < @UnitPrice                  AND Discontinued = @Discontinued">   <SelectParameters>     <asp:ControlParameter Name="ProductName" Type="String"       Control PropertyName="Text" />     <asp:ControlParameter Name="UnitPrice" Type="Decimal"       Control PropertyName="SelectedValue" />     <asp:QueryStringParameter Name="Discontinued" Type="Boolean"       QueryStringField="Discontinued" />   </SelectParameters>   </asp:SqlDataSource>

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

<asp:SqlDataSource  runat="server"   ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"   SelectCommand="SELECT * FROM [Products]"   FilterExpression="ProductName LIKE '{0}%'">   <FilterParameters>     <asp:ControlParameter Name="ProductName" Type="String"       Control PropertyName="Text" />   </FilterParameters> </asp:SqlDataSource>

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 Code

When 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 Data

For 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.

Table 3.3. Properties Used for Data Modification

Action

Property

Description

Adding new data

InsertCommand

The command to run to insert a single row into the database.

 

InsertCommandType

The type of command contained within the InsertCommand property. Can be one of the SqlDataSourceCommandType enumerations: StoredProcedure or Text.

 

InsertParameters

The collection of parameters containing data to insert.

Updating existing data

UpdateCommand

The command to run to update a single row in the database.

 

UpdateCommand Type

The type of command contained within the UpdateCommand property. Can be one of the SqlDataSourceCommandType enumerations: StoredProcedure or Text.

 

Update Parameters

The collection of parameters containing data to update.

Deleting data

DeleteCommand

The command to run to delete a single row in the database.

 

DeleteCommandType

The type of command contained within the DeleteCommand property. Can be one of the SqlDataSource-CommandType enumerations: StoredProcedure or Text.

 

DeleteParameters

The collection of parameters containing data to delete.


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

<asp:SqlDataSource  runat="server"   ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"   SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice]                  FROM [Products]                  WHERE ([ProductName] LIKE '%' + @ProductName + '%')"   DeleteCommand="DELETE FROM [Products]                  WHERE [ProductID] = @ProductID"   InsertCommand="INSERT INTO [Products] ([ProductName], [UnitPrice])                  VALUES (@ProductName, @UnitPrice)"   UpdateCommand="UPDATE [Products]                  SET [ProductName] = @ProductName,                      [UnitPrice] = @UnitPrice                  WHERE [ProductID] = @ProductID">   <SelectParameters>     <asp:ControlParameter Control       Name="ProductName" PropertyName="Text"       Type="String" />   </SelectParameters>   <DeleteParameters>     <asp:Parameter Name="ProductID" Type="Int32" />   </DeleteParameters>   <UpdateParameters>     <asp:Parameter Name="ProductName" Type="String" />     <asp:Parameter Name="UnitPrice" Type="Decimal" />     <asp:Parameter Name="ProductID" Type="Int32" />   </UpdateParameters>   <InsertParameters>     <asp:Parameter Name="ProductName" Type="String" />     <asp:Parameter Name="UnitPrice" Type="Decimal" />   </InsertParameters> </asp:SqlDataSource>

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 command


One 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.



ASP. NET 2.0 Illustrated
ASP.NET 2.0 Illustrated
ISBN: 0321418344
EAN: 2147483647
Year: 2006
Pages: 147

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