ASP.NET 4.0 : Data Source Components (part 2) - The SqlDataSource Control

12/30/2013 1:51:48 AM

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 GroupDescription
DeleteCommand, DeleteParameters, DeleteCommandTypeGets or sets the SQL statement, related parameters, and type (text or stored procedure) used to delete rows in the underlying data store.
FilterExpression, FilterParametersGets 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, InsertCommandTypeGets 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, SelectCommandTypeGets or sets the SQL statement, related parameters, and type (text or stored procedure) used to retrieve data from the underlying data store.
SortParameterNameGets 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, UpdateCommandTypeGets 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 -->

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 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
CancelSelectOnNullParameterIndicates whether a data-retrieval operation is canceled if a parameter evaluates to null. The default value is true.
ConflictDetectionDetermines how the control should handle data conflicts during a delete or update operation. By default, changes that occurred in the meantime are overwritten.
ConnectionStringThe connection string to connect to the database.
DataSourceModeIndicates how data should be returned—via a DataSet or data reader.
OldValuesParameterFormatStringGets or sets a format string to apply to the names of any parameters passed to the Delete or Update method.
ProviderNameIndicates 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
ControlParameterGets the parameter value from any public property of a server control
CookieParameterSets the parameter value based on the content of the specified HTTP cookie
FormParameterGets the parameter value from the specified input field in the HTTP request form
ParameterGets the parameter value assigned by the code
ProfileParameterGets the parameter value from the specified property name in the profile object created from the application’s personalization scheme
QueryStringParameterGets the parameter value from the specified variable in the request query string
SessionParameterSets 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">
<asp:ControlParameter Name="MinID" ControlId="EmpID"
PropertyName="Text" />

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
CacheDurationIndicates, in seconds, how long the data should be maintained in the cache.
CacheExpirationPolicyIndicates 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.
CacheKeyDependencyIndicates 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.
EnableCachingEnables or disables caching support.
SqlCacheDependencyGets 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"
ConnectionString="<%$ ConnectionStrings:LocalNWind %>"
SelectCommand="SELECT * FROM employees"
  •  ASP.NET 4.0 : Data-Binding Expressions (part 2) - Other Data-Binding Methods
  •  ASP.NET 4.0 : Data-Binding Expressions (part 1) - Simple Data Binding
  •  Sharepoint 2010 : Putting Your Site on the Web - Customizing the User Experience (part 2) - Working with Page Layouts
  •  Sharepoint 2010 : Putting Your Site on the Web - Customizing the User Experience (part 1) - Working with Master Pages
  •  SharePoint 2013 and Windows Azure (part 3) - Creating a Simple Autohosted SharePoint App - Building A Client App Web Part
  •  SharePoint 2013 and Windows Azure (part 2) - Creating a Simple Autohosted SharePoint App - Building Your First Autohosted App
  •  SharePoint 2013 and Windows Azure (part 1) - Understanding SharePoint Cloud-Hosted Apps and Windows Azure
  •  Sharepoint 2013 : SharePoint Installation and Configuration - Create a New Subsite
  •  Sharepoint 2013 : Join a Server to the SharePoint Farm, Create a New Web Application, Create a New Site Collection
  •  Sharepoint 2013 : Install Without a Product Key in the Configuration File, Configure a New SharePoint Farm
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone