Developer’s Introduction to Data Source controls in ASP.NET 2.0
Intended Readers: persons having basic knowledge of asp.net 1.1, data binding, .net 2.0
Platform: ASP.NET 2.0, Visual Studio 2005
Level: Intermediate
Introduction
Normally web applications are tends to be data centric and most of the coding efforts consumes in writing queries, getting results and display them to user etc. Although ASP.NET 1.1 provides powerful data binding architecture in conjunction with server controls, but still some development effort was need for complete web application, the situation become worse in large enterprise applications consist of multiple tiers.
Asp.net 2.0 simplifies the web development dramatically and the primary reason for this is introduction of new family of controls called data source controls. Data source controls doesn’t have any UI and their sole purpose is to provide the generic and consistent interface to other server controls such as grid view, details view etc. Data source control allows declarative data binding without any single line of code
Class hierarchy of Data source controls
Data source control supports data access to various types of data source such as SqlDatabase, XmlDocument, AccessDataSource, and SiteMapProvider etc for that framework provides different controls. The complete hierarchy of data source controls is
System.Web.UI.Control
System.Web.UI.DataSourceControl
System.Web.UI.SqlDataSource
System.Web.UI.AccessDataSource
System.Web.UI.ObjectDataSource
System.Web.UI.HierarchicalDataSourceControl
System.Web.UI.WebControls.XmlDataSource
System.Web.UI.WebControls.SiteMapDataSource
Since it’s impossible to discuss all of these server controls in one article so for the being being let’s focus on SqlDataSourceControl which is most frequently used of all.
SqlDataSource
SqlDataSource Control provides the interface to retrieve the relational data from OLE-DB or ODBC data source, main property of this control is connection string which specifies the actual source of data, the real beauty of this control is that it automatically uses the best data provider based on connection string but by default it is SqlServer. Let’s look at the schema of the SqlDataSource Control
<asp:SqlDataSource id="String" runat="server"
ConnectionString="String"
ProviderName="String"
DataSourceMode="[DataSet|DataReader]"
SelectCommand="String"
InsertCommand="String"
UpdateCommand="String"
DeleteCommand="String"
FilterExpression="String"
EnableCaching="[True|False]"
CacheDuration="Integer"
SqlCacheDependency="String"
CacheExpirationPolicy="[Absolute|SlidingWindow]"
OnSelecting="SqlDataSourceCommandEventHandler"
OnSelected="SqlDataSourceStatusEventHandler"
OnUpdating="SqlDataSourceCommandEventHandler"
OnUpdated="SqlDataSourceStatusEventHandler"
OnInserting="SqlDataSourceCommandEventHandler"
OnInserted="SqlDataSourceStatusEventHandler"
OnDeleting="SqlDataSourceCommandEventHandler"
OnDeleted="SqlDataSourceStatusEventHandler"
OnDataSourceChanged="EventHandler" >
<SelectParameters>
[<System.Web.UI.WebControls.Parameter ...>]
</SelectParameters>
<UpdateParameters>
[<System.Web.UI.WebControls.Parameter ...>]
</UpdateParameters>
<InsertParameters>
[<System.Web.UI.WebControls.Parameter ...>]
</InsertParameters>
<DeleteParameters>
[<System.Web.UI.WebControls.Parameter ...>]
</DeleteParameters>
<FilterParameters>
[<System.Web.UI.WebControls.Parameter ...>]
</FilterParameters>
</asp:SqlDataSource>
Before looking more into the details of the SqlDataSourceControl, let’s take an example of ASP.NET 2.0 code free data binding.
<asp:SqlDataSource id="ds1" runat="server" ConnectionString="[connection string]" SelectCommand="SELECT * FROM Products" />
<asp:GridView id="grid1" DataSourceID="ds1" runat="server" />
Simple is that! No explicit data binding code all is done declaratively by assigning properties. When Page Loads the data source control loads its configured data and makes it available to other data bound controls. After this it’s responsibility of GridView control to display the data and provides sorting and paging functionality if configured so.
Now its time to look into the details of SqlDataSourceControl along with the examples where appropriate
Select, Insert, Delete and Update Operations
SqlDataSource allows us to perform the above operations on data source just like ADO.NET does; it contains set of three properties for every operation i.e. for select
1. SelectCommand, Specifies the command text, either SQL Query of Stored Procedure Name.
2. SelectCommandType, Enumeration which can be Text or StoredProcedure.
3. SelectParameters, ParameterCollection which contains one or more instances of parameter collection (and can be of different types).
Keep in mind that all these objects are separate from ADO.NET object and they are defined in System.Web.UI.WebControls namespace and deals specifically with data source objects.
One of the amazing features of ASP.NET 2.0 is the parameters of Data sources, in ASP.NET 1.1 we have to write our own logic for parsing the parameters either from query string, session variable and then pass to the real command object, but now command object along with parameter object is capable of letting data from different sources, some of common parameter sources are
- ControlParameter: takes the parameter value from the control either in the page or current http context. ControlID specifies the control from which let the value and PropertyName is the name of property of the control. ControlID must of the server control.
- QueryStringParameter: takes input from the query string of page, if parameter is missing then default value specified will used.
- FormParameter: let’s value from any control within the current context, this can be either server control or simple HTML control.
- SessionParameter: lets the value from ASP.NET session object by specifying the key to use.
- CookieParameter: lets value from the cookie specified in the CookieName property.
Wow! All happens declaratively without writing any single line of code, isn’t it amazing…
Example, Lets create a simple page which contains ID’s of all employees in dropdown and on selecting particular id its details will shown in the details view control (new in ASP.NET 2.0).
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [EID], [ENAME] FROM [Employee] ORDER BY [EID]"></asp:SqlDataSource>
Connection string property contains the special syntax which is used to let the value from web.config.
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="ENAME" DataValueField="EID" />
Now we have list of employees in drop down so next task is create another data source and set its select parameter the value of dropdown.
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Employee] WHERE ([EID] = @EID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" DefaultValue="101" Name="EID" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Now it’s a matter of showing data in grid, this can be done through a new control in ASP.NET 2.0 called Grid View
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2" />
All is done without writing any single line of code, Visual studio does all this automatically.
This was the quite the basic example of selecting data from the data source, I am sure you will be able to perform the insert, delete and update operations. So let’s focus on some other properties and options of the SqlDatasource control.
Connection Management
SqlDataSource contains two properties specifically for connection management, ConnectionString represents the connection string for the database and ProviderName set/get the name of provider to use by default it is System.Data.SqlClient.
Data Fetching Modes
DataSourceMode property specify the fetching mode of data from data source, there are two modes of data fetching DataSet and DataReader. Rightly guess the same as in ADO.NET. DataSet mode allows caching, sorting and filtering while DataReader provides read-only, fast and sequential data access.
Filtering Data
SqlDataSource supports filter when DataSourceMode property set to DataSet. FilterExpression is the expression used to filter the records its same as RowFilter property of DataView in ADO.NET, additionally we can also specify parameters for filter by using FilterParameters property.
Caching
SqlDataSource provides rich support of caching, but it’s only applicable when DataSourceMode property sets to DataSet. EnableCaching specifies whether caching will be applied on the datasource. CacheDuration specifies the number of second data is cached.
CacheExpirationPolicy determines the Cache behavior, it can have two values
- Absolute: Cached data expires when the amount of time specified by the CacheDuration property has passed since the data was first cached.
- Sliding: Cached data expires only when the cache entry has not been used for the amount of time specified by the CacheDuration property.
SqlCacheDependency sets or returns an optional cache dependency as a String. The syntax is of the form connection:table-name. The connection refers to a named entry within the <cache> section of machine.config or web.config. The tablename refers to the name of the table in the database. Multiple dependencies can be delimited with a semicolon, for example: Connection:table1;connection:table2.
Methods of SqlDataSource
SqlDataSource has four major methods, Select(), Insert(), Update(), Delete(). Select Returns all the rows specified by the SelectCommand and the values in the SelectParameters collection from the data source or from the cached DataSourceView that contains the rows. Returns a System.Data.DataView instance if the DataSourceMode property is set to DataSet, or a DataReader if the DataSourceMode property is set to DataReader. The DataReader must be explicitly closed after use. Other methods simply return the number of effected rows.
Events of SqlDataSource
SqlDataSource expose two events for every operation on datasource, one raised before performing operation and other after that. I.e. Selecting-Selected, Inserting-Inserted etc. DataSourceChanged is another event which is raised when the contents of the SqlDataSourceView for this control change, and causes any data-bound controls to rebind.
SqlDataSourceView
Most of data bound objects in .NET expose there data as views the same is true for SqlDataSource as well, SqlDataSourceView exposes the underlying data of the SqlDataSource. Apart from exposing SqlDataSource’s operational methods, SqlDataSourceView expose some methods which determine the ability of view object such as CanDelete, CanInsert etc. SortExpression is property which sets/gets the expression for sorting the data source (like DataView’s SortExpression).
Conclusion
Data Source controls are server side controls which provide exposes data to other data bound controls, ASP.NET 2.0 introduces various data source controls and in this article I discuss SqlDataSource which provides data access through OLE-DB/ODBC or directly with Sql Server.
DataSource controls in general exposes lots of possibilities and source for exposing data, Microsoft hinted some of data source controls for the future versions such as
- A WebServiceDataSource control to allow you to work with data exposed by Web Services
- An ExcelDataSource control to allow access to Excel worksheet files
- An OracleDataSource control to allow manipulation of data in an Oracle database without using OLE-DB or ODBC directly
- An IndexServiceDataSource control that will allow the Indexing Service catalog to be queried
- A SharePointDataSource control that will allow interaction with the database of resources maintained in Microsoft Share Point
References
· A First Look at ASP.NET v. 2.0, Addison Wesley, ISBN 0-321-22896-0
· Professional ASP.NET 2.0, Wrox Press, ISBN 10: 0-7645-7610-0