2. The SqlDataSource Control
The SqlDataSource
control is a data source control that represents a connection to a
relational data store such as SQL Server or Oracle or any data source
accessible through OLE DB and Open Database Connectivity (ODBC) bridges.
You set up the connection to the data store using two main properties, ConnectionString and ProviderName.
The former represents the connection string and contains enough
information to open a session with the underlying engine. The latter
specifies the namespace of the ADO.NET managed provider to use for the
operation. The ProviderName property defaults to System.Data.SqlClient, which means that the default data store is SQL Server. On the other hand, to target any OLE DB provider, use the System.Data.OleDb string instead.
The
control can retrieve data using either a data adapter or a command
object. Depending on your choice, fetched data will be packed in a DataSet
object or a data reader. The following code snippet shows the minimal
code necessary to activate a SQL data source control bound to a SQL
Server database:
<asp:SqlDataSource runat="server" ID="MySqlSource"
ProviderName='<%$ ConnectionStrings:LocalNWind.ProviderName %>'
ConnectionString='<%$ ConnectionStrings:LocalNWind %>'
SelectCommand="SELECT * FROM employees" />
<asp:DataGrid runat="server" ID="grid" DataSourceID="MySqlSource" />
Programming Interface of SqlDataSource
The data operations supported by the associated view class are provided by the related sets of properties listed in Table 5.
Table 5. Properties for Configuring Data Operations
| Property Group | Description |
|---|
| DeleteCommand, DeleteParameters, DeleteCommandType | Gets
or sets the SQL statement, related parameters, and type (text or stored
procedure) used to delete rows in the underlying data store. |
| FilterExpression, FilterParameters | Gets or sets the string (and related parameters) to create a filter on top of the data retrieved using the Select command. It works only if the control manages data through a DataSet. |
| InsertCommand, InsertParameters, InsertCommandType | Gets
or sets the SQL statement, related parameters, and type (text or stored
procedure) used to insert new rows in the underlying data store. |
| SelectCommand, SelectParameters, SelectCommandType | Gets
or sets the SQL statement, related parameters, and type (text or stored
procedure) used to retrieve data from the underlying data store. |
| SortParameterName | Gets
or sets the name of an input parameter that a command’s stored
procedure will use to sort data. (The command in this case must be a
stored procedure.) It raises an exception if the parameter is missing. |
| UpdateCommand, UpdateParameters, UpdateCommandType | Gets
or sets the SQL statement, related parameters, and type (text or stored
procedure) used to update rows in the underlying data store. |
Each
command property is a string that contains the SQL text to be used. The
command can optionally contain the parameters listed in the associated
parameter collection. The managed provider and its underlying
relational engine determine the exact syntax of the SQL to use and the
syntax of the embedded parameters. For example, if the data source
control points to SQL Server, command parameter names must be prefixed
with the @ symbol. If the target data source is an OLE DB provider, parameters are unnamed, identified with a ?
placeholder symbol, and located by position. The following code snippet
shows a more complex data source control in which parametric delete and
update commands have been enabled:
<asp:SqlDataSource runat="server" ID="MySqlSource"
ConnectionString='<%$ ConnectionStrings:LocalNWind %>'
SelectCommand="SELECT * FROM employees"
UpdateCommand="UPDATE employees SET lastname=@lname"
DeleteCommand="DELETE FROM employees WHERE employeeid=@TheEmp"
FilterExpression="employeeid > 3">
<!-- parameters go here -->
</asp:SqlDataSource>
The syntax used for the FilterExpression property is the same as the syntax used for the RowFilter property of the DataView class, which in turn is similar to that used with the SQL WHERE clause. If the FilterExpression property needs to be parametric, you can indicate parameters through the FilterParameters collection. Filtering is enabled only when DataSourceMode is set to DataSet.
Note
Note the difference between filter expressions and parameters on the Select
command. Parameters on the command influence the result set returned by
the data store; a filter expression restricts the display to the result
set returned through the Select command. |
Table 6 details other operational properties defined on the SqlDataSource class. The list doesn’t include cache-related properties, which we’ll cover in a moment.
Table 6. Other Properties on SqlDataSource
| Property | Description |
|---|
| CancelSelectOnNullParameter | Indicates whether a data-retrieval operation is canceled if a parameter evaluates to null. The default value is true. |
| ConflictDetection | Determines
how the control should handle data conflicts during a delete or update
operation. By default, changes that occurred in the meantime are
overwritten. |
| ConnectionString | The connection string to connect to the database. |
| DataSourceMode | Indicates how data should be returned—via a DataSet or data reader. |
| OldValuesParameterFormatString | Gets or sets a format string to apply to the names of any parameters passed to the Delete or Update method. |
| ProviderName | Indicates the namespace of the ADO.NET managed provider to use. |
It
is interesting to note that many of these properties mirror identical
properties defined on the actual view class, as illustrated earlier in Figure 1.
The SqlDataSource object features a few methods and events, which in most cases are common to all data source components. The methods are Delete, Insert, Select, and Update,
and they’re implemented as mere wrappers around the corresponding
methods of the underlying data source view class. Events exist in pairs—Deleting/Deleted, Inserting/Inserted, Selecting/Selected, and Updating/Updated—and fire before and after any of the methods just mentioned. The beginning of a filtering operation is signaled through the Filtering event.
As
mentioned, only specific ASP.NET controls can really take advantage of
the capabilities of data source controls. For this reason, in the
upcoming article devoted to GridView, DetailsView, FormView, and ListView controls we’ll see a lot of sample code showing how to use the SqlDataSource
control for selecting, updating, paging, and sorting.
Declarative Parameters
Each command property has its own collection of parameters—an instance of a collection class named ParameterCollection. ASP.NET supports quite a few parameter types, which are listed in Table 7.
Table 7. Parameter Types for Data Source Controls
| Parameter | Description |
|---|
| ControlParameter | Gets the parameter value from any public property of a server control |
| CookieParameter | Sets the parameter value based on the content of the specified HTTP cookie |
| FormParameter | Gets the parameter value from the specified input field in the HTTP request form |
| Parameter | Gets the parameter value assigned by the code |
| ProfileParameter | Gets
the parameter value from the specified property name in the profile
object created from the application’s personalization scheme |
| QueryStringParameter | Gets the parameter value from the specified variable in the request query string |
| SessionParameter | Sets the parameter value based on the content of the specified session state slot |
Each parameter class has a Name
property and a set of properties specific to its role and
implementation. To understand declarative parameters in data source
controls, take a look at the following code:
<asp:SqlDataSource runat="server" ID="MySource"
ConnectionString='<%$ ConnectionStrings:LocalNWind %>'
SelectCommand="SELECT * FROM employees WHERE employeeid > @MinID">
<SelectParameters>
<asp:ControlParameter Name="MinID" ControlId="EmpID"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
The query contains a placeholder named @MinID. The data source control automatically populates the placeholder with the information returned by the ControlParameter
object. The value of the parameter is determined by the value of a
given property on a given control. The name of the property is
specified by the PropertyName attribute. The ID of the control is in the ControlId
attribute. For the previous code to work, page developers must
guarantee that the page contains a control with a given ID and
property; otherwise, an exception is thrown. In the example, the value
of the property Text on the EmpID control is used as the value for the matching parameter.
The
binding between formal parameters (the placeholders in the command
text) and actual values depends on how the underlying managed provider
handles and recognizes parameters. If the provider type supports named
parameters—as is the case with SQL Server and Oracle—the binding
involves matching the names of placeholders with the names of the
parameters. Otherwise, the matching is based on the position. Hence,
the first placeholder is bound to the first parameter, and so on. This
is what happens if OLE DB is used to access the data.
Conflicts Detection
The SqlDataSource
control can optionally perform database-intrusive operations such as
deletions and updates. It is not a far-fetched idea to have the data
read, perhaps modified on the client, and then updated. In a situation
in which multiple users are using the same page, what should the
behavior of the update/delete methods be if the record they attempt to
work on has been modified in the meantime by another user?
The SqlDataSource control uses the ConflictDetection property to determine what to do when performing update and delete operations. The property is declared as type ConflictOptions—an enum type. The default value is OverwriteChanges,
which means that any intrusive operation happens regardless of whether
values in the row have changed since they were last read. The
alternative is the CompareAllValues value, which simply ensures that the SqlDataSource control passes the original data read from the database to the Delete or Update method of the underlying view class.
It is important to note that changing the value of ConflictDetection
doesn’t produce any significant effect unless you write your delete or
update statements in such a way that the command fails if the data in
the row doesn’t match the data that was initially read. To get this
behavior, you should define the command as follows:
UPDATE employees SET firstname=@firstname
WHERE employeeid=@employeeid AND firstname=@original_firstname
In other words, you must
explicitly add to the command an extra clause to check whether the
current value of the field being modified still matches the value that
was initially read. In this way, intermediate changes entered by
concurrent users make the WHERE clause fail and make the command fail. You are in charge of tweaking the command text yourself; setting ConflictDetection to CompareAllValues is not enough.
How would you format the name of the parameters that represent old values? The SqlDataSource control uses the OldValuesParameterFormatString property to format these parameter names. The default value is original_{0}.
When you use the CompareAllValues option, you can handle the Deleted or Updated
event on the data source control to check how many rows are affected.
If no rows are affected by the operation, a concurrency violation might
have occurred.
void OnUpdated(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows == 0) {
// Concurrency violation: notify the user ...
...
}
}
Caching Behavior
The
data binding between a data-bound control and its data source component
is automatic and takes place on each postback caused by the data-bound
control. Imagine a page with a grid, a data source control, and a
button. If you turn on the grid in edit mode, the Select command on the data source control is run; if you click the button, the UI of the grid is rebuilt from the view state and no Select statement is run.
To
save a query on postbacks, you can ask the data source control to cache
the result set for a given duration. While data is cached, the Select method retrieves data from the cache rather than from the underlying database. When the cache expires, the Select method retrieves data from the underlying database and stores the fresh data back to the cache. The caching behavior of the SqlDataSource control is governed by the properties in Table 8.
Table 8. Caching Properties on SqlDataSource
| Property | Description |
|---|
| CacheDuration | Indicates, in seconds, how long the data should be maintained in the cache. |
| CacheExpirationPolicy | Indicates
if the cache duration is absolute or sliding. If the duration is
absolute, data is invalidated after the specified number of seconds. If
the duration is sliding, data is invalidated if not used for the
specified duration. |
| CacheKeyDependency | Indicates
the name of a user-defined cache key that is linked to all cache
entries created by the data source control. By having the key expire,
you can clear the control’s cache. |
| EnableCaching | Enables or disables caching support. |
| SqlCacheDependency | Gets or sets a semicolon-delimited string that indicates which databases and tables to use for the SQL Server cache dependency. |
A single cache entry is created for each distinct combination of SelectCommand, ConnectionString, and SelectParameters. Multiple SqlDataSource
controls can share the same cache entries if they happen to load the
same data from the same database. You can take control of cache entries
managed by the data source control through the CacheKeyDependency property. If set to a non-null string, the property forces the SqlDataSource
control to create a dependency between that key and all cache entries
created by the control. At this point, to clear the control’s cache,
you only have to assign a new value to the dependency key:
// Give the entry a default value to be changed later
Cache["_ClearAll"] = anyInitializationValue;
SqlDataSource1.CacheKeyDependency = "_ClearAll";
...
// Clear the data source control's internal cache
Cache["_ClearAll"] = anyOtherValue;
The SqlDataSource control can cache data only when working in DataSet mode. You get an exception if DataSourceMode is set to DataReader and caching is enabled.
Finally, the SqlCacheDependency property links the SqlDataSource cached data with the contents of the specified database table (typically, the same table where the cached data comes from):
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
CacheDuration="1200"
ConnectionString="<%$ ConnectionStrings:LocalNWind %>"
EnableCaching="true"
SelectCommand="SELECT * FROM employees"
SqlCacheDependency="Northwind:Employees">
</asp:SqlDataSource>
