Resources

Data Source Controls in ASP.NET websites

Manage tasks of selecting, updating, inserting and deleting data on webpage in combination with data-bound controls (latter providing UI).

Each data source control used in similar manner.

Can use Configure Data Source Wizard to connect to data via markup (called declarative data binding).

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStirngs:NorthwindCmdString" %> SelectCommand="SELECT * FROM [alpha list]" />

Can also use in code. In this case the attributes used within the markup equate to properties of same name used within code.

Using ObjectDataSource

Typically used in n-tier models.

Provides access to objects in middle tier that in turn modify data.

Control added to page and configured to work with middle-tier object using its methods to retrieve, insert, update and delete data.

Responsible for middle-tire object lifetime - creates and disposes it.

Specify middle-tier class via TypeName attribute. This class may be in App_Code directory or within referenced DLL.

Set the SelectMethod attribute to appropriate method on class (which should return IEnumerable, IListSource, IDataSource or IHierarchicalDataSource as a result).

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Shipper" SelectMethod="GetAllShippers" />

Passing parameters

Can map page-level elements onto parameters passed to middle-tier methods.

Parameters exist for Select, Insert, Update, Delete and Filter, work with corresponding method - e.g. SelectParameters with SelectMethod.

Parameter source can be from multiple places; cookie, control, session, query string, form or profile.

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Customer" SelectMethod="GetCustomersByCity">

<SelectParameters>

<asp:QueryStringParameter Name="city" QueryStringField="city" Type="string" />

</SelectParameters>

</asp:ObjectDataSource>

Will pass the city entry from the query string to the GetCustomerByCity method of the Customer class.

Similarly can add DeleteParameter, UpdateParameter and InsertParameter elements to the ObjectDataSource.

Filters

Apply to data returned from ObjectDataSource as DataSet or DataTable.

To define set FilterExpression attribute to valid filter.

FilterParameters can be used to map page data to the inserts within the filter expression.

<asp:ObjectDataSouce ID="ObjectDataSource1" runat="server" TypeName="Customer" SelectMethod="GetAllCustomers" FilterExpression="city={0}'">

<FilterParameters>

<asp:QueryStringPArameter Name="city" QueryStringField="city" Type="city" />

</FilterParameters>

</asp:ObjectDataSouce>

Sorting and Paging

ObjectDataSource can be configured to sort and page data returned by the data source.

Often better to do this at the database itself.

For example, assume middle tier provides method like:

public static DataTable GetPagedCustomersSorted(string sortCol, int pageStart, int numRecords);

Can then setup ObjectDataSource to sort and page:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="customer" SelectMethod=" GetPagedCustomersSorted" SortParameterName="sortCol" EnablePaging="true" StartRowIndexParameterName="pageStart" MaximumRowsParameterName="numRecords"/>

And then bind to the ObjectDataSource:

<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1" AllowPaging="True" PageSize="10" AllowSorting="true" />

Caching Data

Can cache data in ObjectDataSource by setting EnableCaching property to be true. The CacheDuration property indicates how many seconds to cache data for.

Creating DataObject class

Not many restrictions on objects acting as source for ObjectDataSource.

Can define attributes on class to make its consumption easier - these attributes indicate which methods to use for select, update, etc.

Can also apply DataObject attribute to class, indicates class is meant to be consumed.

[DataObject()]
public class Shipper
{
[DataObjectMethod(DataObjectMethodType.Select)]

public static DataTable GetAllShippers()

Using SqlDataSource

Configures access to relation data sources, e.g. Oracle or SQL Server.

Can work with ODBC and OLE data connections.

Provide SqlDataSource with id unique to page.

Then set ConnectionString property to valid connection string or page script that reads connection string from web.config.

Set command properties to provide commands for selecting, inserting, updating and deleting data. Commands can be sql statements or stored procedure names.

DataSourceMode attribute can take two values - DataSet or DataReader. Former gets all data and keeps in memory, latter maintains connection to database.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommandType="Text" SelectCommand="SELECT * FROM [products]" DataSourceMode="DataReader" />

As with ObjectDataSource can pass parameters to commands, filter results and apply caching.

Using AccessDataSource

Derived from SqlDataSource

Only use to access Jet-based databases (.mdf extension).

Newer version of access (.accdb) should use SqlDataSource.

To connect to data source don't use connection string, instead use DataFile property to specify path to file.

Using EntityDataSource

Works like SqlDataSource.

Unlike SqlDataSource (which is tied to specific data model) the EntityDataSource works with an Entity Data Model that gets mapped to an actual data source.

Connect to Entity Model via ConnectionString property. Set this to name entity model connection string - as found in web.config after entity model created.

Indicate EntitySetName to point to data to select and expose through the control, alternatively use CommandText property to define LINQ query defining the selection.

<asp:EntityDataSource ID="EntityDataSet1" runat="server" ConnectionString="name=NorthwndEntities" DefaultContainerName="NorthwndEntities" EnableFiltering="False" EntitySetName="Orders" />

Selecting Data

Besides the EntitySetName can use Select attribute for more fine grained control by specifying LINQ statement to execute against results, e.g. renaming and combining fields:

<asp:EntityDataSource ID="EntityDataSet1" runat="server" ConnectionString="name=NorthwndEntities" DefaultContainerName="NorthwndEntities" EnableFiltering="False" EntitySetName="Orders" Select="it.OrderId as Id, it.UnitPrice * it.Quantity as Total" />

Can also specify query via CommandText:

<asp:EntityDataSource ID="EntityDataSet1" runat="server" ConnectionString="name=NorthwndEntities" DefaultContainerName="NorthwndEntities"CommandText="Select o.OrderId as Id, o.Quantity from OrderDetails as o where o.Discount > 0 order by o.ProductId" />

Can include additional objects to be included in result via Include attribute. Following returns all Orders, for each Order the results will include the OrderDetails collection;

<asp:EntityDataSource ID="EntityDataSet1" runat="server" ConnectionString="name=NorthwndEntities" DefaultContainerName="NorthwndEntities" EntitySetName="Orders" Include="OrderDetails" />

Ordering Data

By specifying the OrderBy attribute, e.g. OrderBy="it.ProductId".

Filtering Data

By specifying the Where attribute, e.g. Where="it.Discount > 0".

Parameters

Can specify parameters via CommandParameters, DeleteParameters, OrderByParameters, SelectParameters, UpdateParameters and WhereParameters.

Parameters can take values from ASP.NET parameter control, e.g. ControlParameter, FormParameter, CookieParameter, etc.

<asp:EntityDataSource ID="EntityDataSet1" runat="server" ConnectionString="name=NorthwndEntities" DefaultContainerName="NorthwndEntities" EntitySetName="OrderDetailss" OrderBy="it.ProductId" Where"it.Quantity > @OrderQty>
<WhereParameters>
<asp:ControlParamter ControlID="QtyValue" Name="OrderValue" DbType="Int32" PropertyName="Text" DefaultBalue="0"/>
</WhereParameters>
</asp:EntityDataSource>

Elsewhere in page a text box with an ID of QtyValue is used to read the quantity, which is then loaded by the WhereParameter into @OrderQty for use by the Where clause

Paging, Sorting, Editing and Updating

EntityDatsSource supports attributes for specific scenarios. Include AutoPage, AutoSort, EnableInsert, EnableUpdate, EnableDelete. When true these features enabled for EntityDataSource and exposed via controls such as GridView.

Using XmlDataSource

Binds controls to xml file.

Best used when xml data is hierarchical, e.g.

<Products>
<Product>
<Name>Chai</Name>
</Product>
<Product>
<Name>Ale</Name>
</Product>
</Products>

Outer xml elements represent data records, child elements are be sub-records related to outer one.

Child elements and attributes of outer elements are bound to as fields - typically represent columns of data on record.

XmlDataSource typically bound to controls that show data in hierarchical fashion, e.g. TreeView.

Configure XmlDataSource to point to XML file via DataFile attribute, typically stored in App_Data folder.

Can also bind to string containing XML via the Data property.

Typically used to read XML data, does not provide automatic attributes for inserting, deleting, updating and saving XML data - need to write custom code if require this behaviour.

Transforming XML

Can define XSL to change shape and content of XML.

Specify XSL using TransformFile attribute.

XL applied after data loaded but before it is bound for output.

Filtering XML

Use XPath attribute to define subset of XML

Attribute contains XPath expression to select a portion of XML document.

<asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="~/App_Data/products.xml" TransformFile="~/App_Data/ProductTransform.xsl" Xpath="/Products/Product[Category='Category: Beverages']" />

Using LinqDataSource

Connects to any data source that represents a collection of data, e.g. lists, arrays, etc.

Flexible and requires little support code.

ContentTypeName defines database context (typically a class representing it) of LINQ based data, e.g. a DBML DataContext file has been created via LINQ to SQL which would include the NorthwindDataContext class representing tables in the database.

Used in similar way to other controls.

Can define parameters to control sorting, paging, etc.

Can use LINQ constructs within markup of LinqDataSource control, for example by default Select will return all fields in collection, but can use LINQ expression to generate new anonymous type.

<asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="NorthwindDataContext" TableNAme="Suppliers" Where="Country == @Country" Select="new(SuppliedId as Id. CompanyName as Name)">
<WhereParameters>
<asp:QueryStringParameter DefaultVallue="USA" Name="Country" QueryStringField="country" Type="String" />
</WhereParameters>
</asp:LinqDataSource>

Using SiteMapDataSource

Connects to site navigation data for website.

Cannot configure to point to specific file - it automatically picks up web.sitemap defined at root of application.

<asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" />

<asp:Menu ID="Menu1" runat="server" DataSourceID="SiteMapDataSource1" />

Filtering

The StartingNodeUrl attribute indicates node (e.g. products.aspx") in sitemap to be used as root of data source.

The ShowStartingNode when set to false will hide the starting node.

The StartFromCurrentNode attribute will use the current page as the root of the data source.

The StartingNodeOffset attribute moves the starting node up (-ve number) / down (+ve number) the sitemap data tree

Downloads