Working with Data Source Controls and Data-Bound Controls in ASP.NET

12/10/2010

Contents

Before You Begin
Lesson 1: Connecting to Data with Data Source Controls
Lesson 2: Working with Data-Bound Web Server Controls
Lesson 3: Working with ASP.NET Dynamic Data
Case Scenarios
Suggested Practices
Take a Practice Test

Lesson 1: Connecting to Data with Data Source Controls

Data source controls are server controls that you can drag onto your page at design time. A data source control does not have a direct visual component (you use data-bound controls for actual data display, as discussed in the next lesson). Instead, they allow you to declaratively define access to data found in objects, XML, and databases. This lesson examines how you can use data source controls to make connecting to and working with data in an ASP.NET webpage a fast and straightforward development process.

Understanding the Data Source Controls

The data source controls in ASP.NET manage the tasks of selecting, updating, inserting, and deleting data on a webpage. They do so in combination with data-bound controls. The data-bound controls provide the user interface (UI) elements that allow a user to interact with the data by triggering events that call the data source controls.

There are multiple data source controls in ASP.NET. Each is meant to provide specialized access to a certain type of data, such as direct access to a database, objects, XML, or LINQ-based queries. These controls can be found in the System.Web.UI.WebControls namespace. Figure 12-1 shows an overview of the data source controls in ASP.NET.

Figure 12-1 The DataSource web control classes.

Each data source control is used in a similar manner. You can drag the control onto your webpage from the Toolbox in Visual Studio. You can then use the Configure Data Source Wizard to connect to your data and generate markup for the data source control. Connecting to data with markup (instead of code) is referred to as declarative data binding, because you are declaring your data access rather than writing ADO.NET code. Figure 12-2 shows the step for selecting data in this wizard.

This wizard creates the declarative markup used to define the data source connection information. This markup can contain connection information, data manipulation statements (such as SQL), and more. The following shows an example of the SqlDataSource control’s markup for connecting to the Products table in the Northwind database.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindCnnString %>"
    SelectCommand="SELECT * FROM [Alphabetical list of products]">
</asp:SqlDataSource>

Figure 12-2 The Configure Data Source Wizard in Visual Studio allows you to select the data to be exposed by your data source control.

Each data source control is specialized for the type of data with which it is meant to work. The following sections provide an overview of what makes each of these controls unique. The discussion includes some of the common uses of data source controls, such as binding, filtering, sorting, and modifying data.

Using Objects as Data Sources with ObjectDataSource

Many web applications work with a middle tier, or business layer, for retrieving and working with application data. This middle tier encapsulates database code inside classes. Web developers can then call methods on these classes to select, insert, modify, and delete data. With this structure, developers do not have to write direct ADO.NET code, because the code is written by whoever wrote the middle tier. In addition, this middle tier is often reusable across different applications.

You can use the ObjectDataSource control in ASP.NET to connect to and work with middle-tier objects in much the same way that you would work with the other data source objects. This control can be added to a page and configured to create an instance of a middle-tier object and call its methods to retrieve, insert, update, and delete data. The ObjectDataSource control is responsible for the execution lifetime of the object. It creates it and disposes of it. Therefore, the business layer code should be written in a stateless manner. Alternatively, if the business layer uses static methods (or shared methods, in Visual Basic), the ObjectDataSource can use these methods without creating an instance of the actual business object. In this case, however, keep in mind that you could end up with performance issues related to thread contention as multiple requests try to access the same static method.

You configure an ObjectDataSource to connect to a class by setting its TypeName attribute to a string that represents a valid type to which the web application has access. This class might be inside your App_Code directory or inside a DLL file to which the website has a reference (it should not be in your webpage’s code-behind file). You then set the SelectMethod attribute to a valid method name on the class. The ObjectDataSource control will then call this method when the data is requested.

As an example, imagine that you need to write an interface to allow a user to manage the shipper table inside the Northwind database. You might have a business object that can return all the shippers in the database and that looks as follows.

Sample of Visual Basic Code

Public Class Shipper

    Private Shared _cnnString As String = _
            ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ToString

    Public Shared Function GetAllShippers() As DataTable
        Dim adp As New SqlDataAdapter( _
            "SELECT * FROM shippers", _cnnString)

        Dim ds As New DataSet("shippers")
        adp.Fill(ds, "shippers")

        Return ds.Tables("shippers")
    End Function

End Class

Sample of C# Code

public class Shipper
{
    private static string _cnnString =
        ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();

    public static DataTable GetAllShippers()
    {
        SqlDataAdapter adp = new SqlDataAdapter(
            "SELECT * FROM shippers", _cnnString);

        DataSet ds = new DataSet("shippers");
        adp.Fill(ds, "shippers");

        return ds.Tables["shippers"];
    }
}

The Shipper class just listed returns a DataTable as a result of a call to GetAllShippers. You can configure an ObjectDataSource control to provide this data to a webpage by setting the TypeName and SelectMethod attributes as in the following code.

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

You can then use this data source control to bind to a web control (more on this in the next lesson). For example, the following markup binds the ObjectDataSource to a DetailsView control to display the information to the user.

<asp:DetailsView
  ID="DetailsView1"
  runat="server"
  DataSourceID="ObjectDataSource1"
  AllowPaging="true">
</asp:DetailsView>

Figure 12-3 shows an example of the output.

Figure 12-3 An ObjectDataSource bound to a DetailsView control.

Notice that the Shipper.GetAllShippers method returns a DataTable. An ObjectDataSource class can work with any data that implements any of the following interfaces: IEnumerable, IListSource, IDataSource, or IHierarchicalDatasource. This means that as long as your business object class returns data as a DataTable, a DataSet, or some form of a collection, you can be sure that this data can be used with an ObjectDataSource control.

Passing Parameters

The business objects with which you work will undoubtedly define methods that take parameter values. These parameters might define a filter on the data or indicate values to be used when inserting or updating data. Fortunately, you can use the ObjectDataSource to map various page-level elements to parameters to be passed to your objects.

There are multiple sets of parameters you can define for an ObjectDataSource. These sets include Select, Insert, Update, Delete, and Filter parameters. These parameters work in conjunction with the method of the same name. For example, the <SelectParameters> set works with the method defined by the SelectMethod attribute.

The source of a parameter’s value can come from multiple places in your page or site, including Cookie, Control, Session, QueryString, Form, or Profile objects. These options make defining and mapping a data source an easier task. You can also define the source value in code.

As an example, suppose that you have a business object name Customer. Assume that this object contains the GetCustomersByCity method, which takes a city value as string. The method then returns a list of customers for the specified city parameter. Now suppose that you need to create a data source control to map to this class. The ObjectDataSource should pass the value for the city to the method from the query string. In this case, you would create a SelectParameters set that includes a QueryStringParameter definition. The QueryStringParameter definition would map between the name of the query string parameter and the name of the method’s parameter. The following code shows an example.

<asp:ObjectDataSource
    ID="ObjectDataSource1"
    runat="server"
    TypeName="Customer"
    SelectMethod="GetCustomersByCity">
    <SelectParameters>
        <asp:QueryStringParameter
            Name="city"
            QueryStringField="city"
            Type="String" />
    </SelectParameters>
</asp:ObjectDataSource>

You might then attach this data source to a GridView or similar control. You can then call the page by passing in the appropriate query string value, as follows.

http://localhost:5652/DataSourceSamples/Customers.aspx?city=London

You can use this same technique to pass multiple parameters of various types and from various sources. You can also use this same technique to pass parameters meant to handle inserting, updating, and deleting, as discussed in the next section.

Inserting, Updating, and Deleting

You can also use an ObjectDataSource control to define how data should be inserted, updated, and deleted. The InsertMethod, UpdateMethod, and DeleteMethod attributes can be mapped directly to the methods of an object that are to be called when these activities are invoked. You then use parameter definitions to map values to these method calls.

As an example, recall the Shipper class discussed previously. Now suppose that additional methods have been added to this object. These method signatures might look as follows.

Sample of Visual Basic Code

Public Shared Function GetAllShippers() As DataTable
Public Shared Sub InsertShipper(ByVal companyName As String, ByVal phone As String)
Public Shared Sub UpdateShipper(ByVal shipperId As Integer, _
  ByVal companyName As String, ByVal phone As String)
Public Shared Sub DeleteShipper(ByVal shipperId As Integer)

Sample of C# Code

public static DataTable GetAllShippers()
public static void InsertShipper(string companyName, string phone)
public static void UpdateShipper(int shipperId, string companyName, string phone)
public static void DeleteShipper(int shipperId)

You can map an ObjectDataSource control to each of these methods. In doing so, you need to define the parameters each method expects. The following markup shows an example.

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Shipper"
    SelectMethod="GetAllShippers" InsertMethod="InsertShipper"
    UpdateMethod="UpdateShipper" DeleteMethod="DeleteShipper">
    <DeleteParameters>
        <asp:Parameter Name="ShipperId" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="shipperId" Type="Int32" />
        <asp:Parameter Name="companyName" Type="String" />
        <asp:Parameter Name="phone" Type="String" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="companyName" Type="String" />
        <asp:Parameter Name="phone" Type="String" />
    </InsertParameters>
</asp:ObjectDataSource>

You can then use this ObjectDataSource control with a data-bound control such as a DetailsView. The following markup is an example. In this case, the fields are bound individually. This allows granular control over the ShipperId field because it is an auto-generated primary key (identity) in a Microsoft SQL Server database. Therefore, you set the InsertVisible property to false, to make sure that the DetailsView does not try to pass a value for ShipperId to the InsertMethod of the ObjectDataSource. You also set the ReadOnly attribute of the same field to true, to indicate that the value should not be available to change during an edit operation.

<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True"
    DataSourceID="ObjectDataSource1" AutoGenerateRows="False" Width="450px"
    DataKeyNames="ShipperID">
    <Fields>
        <asp:BoundField DataField="ShipperID" HeaderText="ShipperId"
            ReadOnly="true" InsertVisible="false" />
        <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" />
        <asp:BoundField DataField="Phone" HeaderText="Phone" />
        <asp:CommandField ShowInsertButton="True" ShowDeleteButton="True"
            ShowEditButton="True" />
    </Fields>
</asp:DetailsView>

Defining a Filter

You can also apply filters to an ObjectDataSource. Filters apply to data returned from the object’s methods as a DataSet or DataTable. This is because the filter is a valid filter expression as defined by the ADO.NET DataColumn class.

To define a filter for an ObjectDataSource control, you set the FilterExpression attribute to a valid filter. This filter will be applied after the data is retrieved from the database. You can also use FilterParameters to map values from the page to the filter expression by defining a filter expression that contains parameter mappings as numbers enclosed in braces. You then add the appropriate filter parameters.

The following code shows an example. Here, the Customer.GetAllCustomers method is bound to an ObjectDataSource control. When the data is returned, the filter expression city=‘{0}’ is applied to the result. The query string value for city is then passed as the {0} parameter to the filter expression.

<asp:ObjectDataSource
    ID="ObjectDataSource1"
    runat="server"
    TypeName="Customer"
    SelectMethod="GetAllCustomers"
    FilterExpression="city='{0}'">
    <FilterParameters>
        <asp:QueryStringParameter
            Name="city"
            QueryStringField="city"
            Type="String" />
    </FilterParameters>
</asp:ObjectDataSource>

Sorting and Paging

The data-bound controls that work with an ObjectDataSource control can be configured to page and sort the data returned by the data source control. However, it is often better to sort and page this data when the data is requested from the database. Doing so can reduce the consumption of resources on your server.

The ObjectDataSource control defines specific attributes for managing sorting and paging. You set these attributes to parameters of your SelectMethod. The SelectMethod must also define these properties and use them for sorting and paging. In addition, by using these specific properties, data-bound controls such as GridView can automatically work with your data source to provide input for sorting and paging.

As an example, suppose you want to provide a business object method to control how customer data is sorted and paged as it is retrieved before it is shown to the user. You could define a business method as follows.

Sample of Visual Basic Code

Public Shared Function GetPagedCustomersSorted( _
    ByVal sortCol As String, ByVal pageStart As Integer, _
    ByVal numRecords As Integer) As DataTable

    If numRecords <= 0 Then numRecords = 10
    If sortCol = "" Then sortCol = "CompanyName"

    Dim cnn As New SqlConnection(_cnnString)

    Dim sql As String = "SELECT * FROM customers"

    Dim cmd As New SqlCommand(sql, cnn)

    Dim adp As New SqlDataAdapter(cmd)
    cnn.Open()

    Dim ds As New DataSet("customers")
    adp.Fill(ds, pageStart, numRecords, "customers")

    Dim dsSort = From cust In ds.Tables("customers").AsEnumerable()
                 Order By cust.Field(Of String)(sortCol)
                 Select cust

    return dsSort.CopyToDataTable()

End Function

Sample of C# Code

public static DataTable GetPagedCustomersSorted(
    string sortCol, int pageStart, int numRecords)
{
    if (numRecords <= 0) numRecords = 10;
    if (sortCol == "") sortCol = "CompanyName";

    SqlConnection cnn = new SqlConnection(_cnnString);

    string sql = "SELECT * from customers";

    SqlCommand cmd = new SqlCommand(sql, cnn);

    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet("customers");
    cnn.Open();
    adp.Fill(ds, pageStart, numRecords, "customers");

    var dsSort = from cust in ds.Tables["customers"].AsEnumerable()
                    orderby cust.Field<string>(sortCol)
                    select cust;

    return dsSort.CopyToDataTable();
}

Notice that this business method defines three parameters: one for sorting the data, one for setting the starting record (or page), and one for setting the number of records in a page. You can then use these parameters when defining an ObjectDataSource. You set the control’s SortParameterName attribute to the parameter of your business object that is used for sorting data. You set the StartRowIndexParameterName to the parameter that defines the row number at which you want to start retrieving data. You then set the MaximumRowsParameterName to the parameter that is used to define the number of rows you want to include in a data page. The following markup shows an example.

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

You can then bind this data source to a control such as a GridView. The following markup shows an example.

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

When the page is run, the GridView control passes sorting and paging information to the data source. However, because the paging is happening before the GridView is bound, the GridView does not know the number of pages to display. Therefore, you need to implement your own custom paging in this scenario to advance the PageIndex property of the GridView control on the user’s request. After you reset this value, the GridView will pass the value on to the ObjectDataSource.

Caching Data

You can tell ASP.NET to cache your ObjectDataSource control. This will keep the data in memory between page calls and can increase the performance and scalability of your application if the data is to be shared and accessed often.

To indicate caching of an ObjectDataSource, you set the EnableCaching attribute to true. You then set the CacheDuration property to the number of seconds you want to have ASP.NET cache the data. The following shows an example of these settings.

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

The first call to this page will call the object and return its data. Subsequent calls within 30 seconds (such as moving through data pages) will use the cached data (and not call the underlying object).

Creating a DataObject Class

There are not many restrictions on which objects you can use as the source of ObjectDataSource controls. If you know that your business object will be used as an ObjectDataSource, you can define attributes on your class that make consuming your class inside an ObjectDataSource easier in the designer. These attributes are used to predefine which methods to use as Select, Insert, Update, and Delete methods.

To get started, you set the DataObject attribute at the top of your class. This simply indicates that your class is meant to be a DataObject. Again, this is not required for use with ObjectDataSource controls but simply makes things easier. The following shows an example of the class declaration and attribute.

Sample of Visual Basic Code

<System.ComponentModel.DataObject()> _
Public Class Shipper

Sample of C# Code

[DataObject()]
public class Shipper

You then add the DataObjectMethod attribute to the top of each method you intend to use as a data object method. You pass a DataObjectMethodType enum value to this attribute to indicate Delete, Insert, Update, or Select. The following code shows an example of the method signature and attribute.

Sample of Visual Basic Code

<System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select)> _
Public Shared Function GetAllShippers() As DataTable

Sample of C# Code

[DataObjectMethod(DataObjectMethodType.Select)]
public static DataTable GetAllShippers()

By defining these attributes, you make the designer aware of your business object’s intentions. This can ease the burden of configuring an ObjectDataSource control when large business objects with many methods are involved.

Connecting to Relational Databases by Using SqlDataSource

The SqlDataSource control is used to configure access to relational databases such as SQL Server and Oracle. It can also be configured to work with Open Database Connectivity (ODBC) and Object Linking and Embedding (OLE) Db data connections. You configure the control to connect to one of these database types. The code inside the control will then use the appropriate data provider based on your configuration settings, including ADO.NET provider classes for SqlClient, OracleClient, OleDb, and Odbc.

You configure the SqlDataSource control by first setting its ID property to a unique identifying string value. This property is similar to any other web control ID property. However, the value is used when referring to the data source during data binding (which will be discussed later in this section). You then set the ConnectionString property either to a valid connection string or to page script that reads the connection string from the Web.config file (as shown in the next code example).

You then set various command properties, including commands for selecting, inserting, updating, and deleting data. The command properties you set are based on how you intend to use the control. For example, you use the SelectCommand to define an SQL statement that can be used to retrieve data from a database. In this case, you would use the Text SelectCommandType (which is the default). You can also set the SelectCommandType to StoredProcedure and then provide a stored procedure name for the SelectCommand attribute.

The DataSourceMode attribute is used to define how the SqlDataSource control should retrieve your data. You have two options: DataSet and DataReader. The former connects to the database and returns all records as a DataSet instance. It then closes the database connection before continuing to process the page. The latter, DataReader, keeps an open connection to the database while it reads each row into the data source control.

The following markup shows an example of connecting to a Microsoft SQL Server Express Edition database by first reading the connection string from the Web.config file. It uses a text-based SQL statement and a DataReader. It then binds the data source to a GridView control for display.

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

<asp:GridView
    ID="GridView1"
    runat="server"
    DataSourceID="SqlDataSource1">
</asp:GridView>

You can also work with the data source controls from code. When doing so, you replace the markup attribute settings with object property settings. You first create the data source control inside the Page_Init method. You then add the data source control to the page to ensure that it is available to be bound to other controls. The following code shows the preceding markup example translated as code in a code-behind page.

Sample of Visual Basic Code

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Init

        Dim sqlDs As New SqlDataSource
        sqlDs.ConnectionString = _
            ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ToString
        sqlDs.ID = "SqlDataSource1"
        sqlDs.SelectCommandType = SqlDataSourceCommandType.Text
        sqlDs.SelectCommand = "SELECT * FROM [products]"
        sqlDs.DataSourceMode = SqlDataSourceMode.DataReader
        Me.Controls.Add(sqlDs)
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Load
        GridView1.DataSourceID = "SqlDataSource1"
    End Sub

End Class

Sample of C# Code

public partial class DefaultCs : System.Web.UI.Page
{
    protected void Page_Init(object sender, EventArgs e)
    {
        SqlDataSource sqlDs = new SqlDataSource();
        sqlDs.ConnectionString =
            ConfigurationManager.ConnectionStrings[
            "NorthwindConnectionString"].ToString();
        sqlDs.ID = "SqlDataSource1";
        sqlDs.SelectCommandType = SqlDataSourceCommandType.Text;
        sqlDs.SelectCommand = "SELECT * FROM [products]";
        sqlDs.DataSourceMode = SqlDataSourceMode.DataReader;
        this.Controls.Add(sqlDs);
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSourceID = "SqlDataSource1";
    }
}

Working with data source controls in code is less common than working with them in markup. Declaring your data source in markup is very straightforward;, the attributes you define in markup are the same as the properties you set in code. Therefore, the majority of this lesson assumes that you are working with markup only, and will not provide examples in code.

Using Parameters

The SqlDataSource control can also be configured to use parameters for Select, Insert, Update, Filter, and Delete commands. This is done by defining parameters inside your SQL statements by using the @ param syntax. You then map parameter values to these parameter definitions by using parameter declarations.

As an example, suppose that you are creating a SqlDataSource control to return products based on their category ID. The category ID will be passed to the page as a value from the query string. You can define this parameter inside the SelectParameters collection as a QueryStringParameter. The following shows the markup of this example.

<asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommandType="Text"
    SelectCommand="SELECT * FROM [products] WHERE CategoryID=@CategoryId"
    DataSourceMode="DataSet">
    <SelectParameters>
        <asp:QueryStringParameter
            Name="CategoryId"
            QueryStringField="catId"
            Type="Int16" />
    </SelectParameters>
</asp:SqlDataSource>

You can then bind this control to a GridView (or similar control). When the page is accessed, the data is filtered based on the query string parameter. The following shows an example Uniform Resource Locator (URL) for this call:

http://localhost:5652/DataSourceSamples/Products.aspx?catId=2

You use the same method for defining InsertParameters, UpdateParameters, and DeleteParameters. These parameters are mapped to the respective InsertCommand, UpdateCommand, and DeleteCommand commands. Controls such as GridView and DetailsView work to trigger update, insert, and delete actions and, in doing so, pass parameter values to the appropriate command. This is similar to what was demonstrated previously in the ObjectDataSource section.

Filtering Data with SqlDataSource

As with the ObjectDataSource control, you can also filter data inside a SqlDataSource control. Again, the data must be a DataSet because the filter is applied to the ADO.NET DataColumn or DataView.RowFilter property.

To define a filter, you set the FilterExpression attribute to a valid filter. This filter will be applied after the data is retrieved from the database. You can also use FilterParameters to map values from the page to the filter expression by defining a filter expression that contains parameter mappings as numbers enclosed in braces. You then add the appropriate filter parameters.

The following code shows an example of a SqlDataSource control that first selects all products from the database. It then applies a FilterExpression to show only those products that have been discontinued.

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

Caching SqlDataSource Data

As with ObjectDataSource, you can also configure a SqlDataSource control to be cached by the server. When doing so, however, you must set the DataSourceMode property to DataSet. DataReader sources cannot be cached, because they would hold open a connection to the server.

You indicate the caching of a SqlDataSource control the same way you would an ObjectDataSource control: by setting the EnableCaching and CacheDuration attributes. The following shows an example.

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

Working with Access Data Files and AccessDataSource Controls

The AccessDataSource control is meant to connect to and work with Microsoft Access file-based databases (.mdb files). This control is very similar to the SqlDataSource control. In fact, it derives from the SqlDataSource class. Therefore, you can expect to work with the AccessDataSource control in a very similar manner when passing parameters, caching, filtering data, and calling Access stored procedures.

One of the main differences between the AccessDataSource control and the SqlDataSource control is how they connect to the database. The AccessDataSource control replaces the SqlDataSource.ConnectionString property with the DataFile property. You pass a path to a database file to this property to define a connection to an Access database. The following markup shows how you configure the AccessDataSource control to connect to an .mdb file in the App_Data folder.

<asp:AccessDataSource
    ID="AccessDataSource1" runat="server"
    DataFile="~/App_Data/AccessNorthwind.mdb"
    SelectCommand="SELECT * FROM [Products]">
</asp:AccessDataSource>

The code inside this data source control uses the ADO.NET System.Data.OleDb provider for connecting to an Access data file. Of course, this code is abstracted for you by the control itself. You need only define the markup to begin accessing and working with data in the Access file.

Connecting to an Entity Model by Using EntityDataSource

The EntityDataSource control works much like the SqlDataSource control. However, the SqlDataSource control is tied to a specific database model, whereas the EntityDataSource control works with an Entity Data Model that gets mapped to an actual data store (see Chapter 11, “Connecting to and Querying Data with LINQ”, for more information on creating and working with Entity Framework’s Entity Data Model).

You configure the EntityDataSource control to provide a connection to the entity model’s underlying data store, which is done through the ConnectionString property. This value is set to a named entity model connection string that uses the System.Data.EntityClient provider. The connection string is generated and stored in the Web.config file when you set up your entity model (EDMX file).

You can then indicate an EntitySetName to point to the data you want to select and expose through the data source control. This entity set is a named collection of data in your model. Alternatively, you can use the CommandText attribute to define a custom LINQ query to define your selection (more on this later in this section).

The following markup shows an example. This assumes that an entity model named NorthwindEntities has been created. Notice the connection string reference. This string is stored in the Web.config file. Also, notice that the EntitySetName is set to Orders. This is used to establish a default entity set and will return all orders in the data store. Finally, the results are bound to a GridView control for display.

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

<asp:GridView ID="GridView1" runat="server"
    DataSourceID="EntityDataSource1">
</asp:GridView>

Selecting Data to Return

You saw in the previous example that you can return the entire collection for an entity by using the EntitySetName attribute. The EntityDataSource allows you to get more specific with your selection. You can do so through the Select attribute. You define a string in this attribute that will be passed to ObjectQuery<T>. In this way, you can write LINQ to Entities code against the data source for the Select, Where, and Order By attributes.

For example, suppose you want to return only specific fields from the OrderDetails collection in an entity model. You might also want to rename some of these fields and even generate a field based on a calculation. As with a LINQ to Entities query, you can specify a Select statement that will do just that. Recall from Chapter 11 that this is called a projection, because you are projecting the results into a collection of a new, anonymous type. The following markup shows an example.

<asp:EntityDataSource ID="EntityDataSource1"
    runat="server"
    ConnectionString="name=NorthwndEntities"
    DefaultContainerName="NorthwndEntities"
    EnableFlattening="False"
    EntitySetName="OrderDetails"
    Select="it.OrderId as Id, it.UnitPrice, it.Quantity, it.UnitPrice * it.Quantity as
  LineItemTotal">
</asp:EntityDataSource>

Specifying a Custom Query

You can specify an entire query (and not just the Select) by using the CommandText property of the EntityDataSource. In this case, you need not set the EntitySetName. The query will indicate the data to select. This query is written as an Entity SQL expression, which uses different syntax than LINQ for Entities. The following markup shows an example (line breaks in the CommandText property are added for clarity.

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

Note that you can write the same query by using the independent Select, Where, and OrderBy properties of the EntityDataSource control. You will see more on this in upcoming sections.

Specifying Objects to Return

You can specify that additional object collections be returned in the result (in addition to the collection being queried). These additional objects are added to your collection by using the Include attribute. These objects should exist as a navigation property in the entity data model. For example, the following markup returns all Orders. For each order object, the results will also include the OrderDetails collection.

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

Ordering Results

You can use the OrderBy property of the EntityDataSource control to specify an order by statement that will be passed to ObjectQuery<T>. The following markup shows an example of ordering the OrderDetails entity by ProductId.

<asp:EntityDataSource ID="EntityDataSource1" runat="server"
    ConnectionString="name=NorthwndEntities"
    DefaultContainerName="NorthwndEntities"
    EntitySetName="OrderDetails"
    OrderBy="it.ProductId">
</asp:EntityDataSource>

Filtering Data

You can use the Where property of the EntityDataSource control to specify a Where statement that will be passed to ObjectQuery<T> without modification. The following markup shows an example of filtering orders for only those that have a discount applied.

<asp:EntityDataSource ID="EntityDataSource1" runat="server"
    ConnectionString="name=NorthwndEntities"
    DefaultContainerName="NorthwndEntities"
    EntitySetName="OrderDetails"
    OrderBy="it.ProductId"
    Where="it.Discount > 0">
</asp:EntityDataSource>

Defining Parameters

The EntityDataSource control allows you to define several parameters that can be used with the @ paramName syntax inside various properties, including Select, Where, and CommandText. These ParameterCollection objects include CommandParameters, DeleteParameters, OrderByParameters, SelectParameters, UpdateParameters, and WhereParameters.

Each of these parameter collections might contain an ASP.NET parameter control such as ControlParameter, FormParameter, QueryStringParameter, CookieParameter, and similar parameters. This allows you to pass values from your website dynamically to the EntityDataSource control.

As an example, suppose you want to filter orders based on their value. You might ask a user to enter a value into a TextBox control. You could then create a <ControlParameter/> in the WhereParameters collection of an EntityDataSource control to map the TextBox.Text field to the Where clause. The following is an example; the parameter is defined as @ OrderValue, which aligns with the parameter name, OrderValue.

<asp:EntityDataSource ID="EntityDataSource1" runat="server"
    ConnectionString="name=NorthwndEntities"
    DefaultContainerName="NorthwndEntities"
    EntitySetName="OrderDetails"
    OrderBy="it.ProductId"
    Where="(it.Quantity * it.UnitPrice) > @OrderValue">
    <WhereParameters>
    <asp:ControlParameter
        ControlID="TextBoxValue" Name="OrderValue"
        DbType="Int32" PropertyName="Text"
        DefaultValue="0" />
    </WhereParameters>
</asp:EntityDataSource>

Enter an order value on which to filter:<br />
<asp:TextBox ID="TextBoxValue" runat="server"></asp:TextBox>
<asp:Button ID="ButtonUpdate" runat="server" Text="Button" />

<asp:GridView ID="GridView1" runat="server"
    DataSourceID="EntityDataSource1">
</asp:GridView>

Note that you can set the AutoGenerateWhereClause or AutoGenerateOrderByClause attributes to true if you want to have the EntityDataSource control automatically map a parameter to a field in the results. The parameter and field must have the same name for this to work.

Paging, Sorting, Editing, and Updating Data

The EntityDataSource control supports a few additional attributes that you can use to enable specific scenarios. These attributes include AutoPage, AutoSort, EnableInsert, EnableUpdate, and EnableDelete. These are all Boolean properties that, if set to true, will enable these features for your EntityDataSource. Of course, the features are provided by the underlying entity model; they then need to be exposed via a visible control such as a GridView.

The following markup shows an example of both a fully enabled EntityDataSource control and a GridView control that leverages all of these features. A user can access this page and view, sort, edit, update, and delete data.

<asp:EntityDataSource ID="EntityDataSource1" runat="server"
    ConnectionString="name=NorthwndEntities"
    DefaultContainerName="NorthwndEntities"
    EntitySetName="OrderDetails"
    AutoPage="true"
    AutoSort="true"
    EnableDelete="true"
    EnableInsert="true"
    EnableUpdate="true">
</asp:EntityDataSource>

<asp:GridView ID="GridView1" runat="server"
    DataSourceID="EntityDataSource1"
    AllowPaging="True"
    AllowSorting="True">
    <Columns>
        <asp:CommandField
            ShowDeleteButton="True"
            ShowEditButton="True"
            ShowSelectButton="True" />
    </Columns>
</asp:GridView>

Connecting to XML Data by Using XmlDataSource

The XmlDataSource control provides a means to create a binding connection between controls on your page and an XML file. The XmlDataSource control is best used when you want to bind to XML data that is represented as hierarchical. In these cases, the outer elements of the XML represent data records. The child elements can themselves be subrecords related to the outer records. In addition, the child elements and attributes of these outer “record” elements are typically bound to as fields. You can think of these fields as columns of data on the record. Due to this hierarchical nature, the XmlDataSource control is typically bound to controls that show data in a hierarchical manner, such as the TreeView control. However, XmlDataSource controls can be used to display data in tabular formats, too.

You configure the XmlDataSource control at design time to point to an XML file. XML data in your project is typically stored in your project’s App_Data folder. To bind to a file, you set the DataFile attribute on the data source control to point to the path of the XML file. The following code shows an example of defining an XmlDataSource control that points to a file containing product data.

<asp:XmlDataSource
    ID="XmlDataSource1"
    runat="server"
    DataFile="~/App_Data/products.xml" >
</asp:XmlDataSource>

You can also bind directly to a string value that represents XML. The XmlDataSource class provides the Data property for connecting to a string value in your code-behind page.

Transforming XML with the XmlDataSource Control

You can use the XmlDataSource control to define an Extensible Stylesheet Language (XSL) transformation to change the shape and content of your XML data. You do so by setting the TransformFile attribute to a valid XSL file. The XSL file will be applied to your XML data after your XML is loaded into memory and before the XML is bound for output.

As an example, consider the following XML file that defines a set of products across varied categories.

<?xml version="1.0" standalone="yes"?>
<Products>
    <Product>
        <Category>Beverages</Category>
        <Name>Chai</Name>
        <QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>
        <UnitPrice>18.0000</UnitPrice>
    </Product>
    <Product>
        <Category>Condiments</Category>
        <Name>Aniseed Syrup</Name>
        <QuantityPerUnit>12 - 550 ml bottles</QuantityPerUnit>
        <UnitPrice>10.0000</UnitPrice>
    </Product>
    <Product>
        <Category>Condiments</Category>
        <Name>Chef Anton's Cajun Seasoning</Name>
        <QuantityPerUnit>48 - 6 oz jars</QuantityPerUnit>
        <UnitPrice>22.0000</UnitPrice>
    </Product>
    <Product>
        <Category>Produce</Category>
        <Name>Uncle Bob's Organic Dried Pears</Name>
        <QuantityPerUnit>12 - 1 lb pkgs.</QuantityPerUnit>
        <UnitPrice>30.0000</UnitPrice>
    </Product>
    <Product>
        <Category>Beverages</Category>
        <Name>Guaraná Fantástica</Name>
        <QuantityPerUnit>12 - 355 ml cans</QuantityPerUnit>
        <UnitPrice>4.5000</UnitPrice>
    </Product>
    <Product>
        <Category>Beverages</Category>
        <Name>Sasquatch Ale</Name>
        <QuantityPerUnit>24 - 12 oz bottles</QuantityPerUnit>
        <UnitPrice>14.0000</UnitPrice>
    </Product>
    <Product>
        <Category>Beverages</Category>
        <Name>Steeleye Stout</Name>
        <QuantityPerUnit>24 - 12 oz bottles</QuantityPerUnit>
        <UnitPrice>18.0000</UnitPrice>
    </Product>
</Products>

Suppose that you have to transform this data by first sorting it and then adding descriptive text to each field to help a user when viewing the data in a TreeView control. In this case, you can write an XSL transform file. The following code represents an example.

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="Products">
        <Products>
            <xsl:for-each select="Product">
                <xsl:sort select="Name" order="ascending" />
                <Product>
                    <Name>
                        <xsl:value-of select="Name"/>
                    </Name>
                    <Category>
                        <xsl:text>Category: </xsl:text>
                        <xsl:value-of select="Category"/>
                    </Category>
                    <QuantityPerUnit>
                        <xsl:text>Quantity: </xsl:text>
                        <xsl:value-of select="QuantityPerUnit"/>
                    </QuantityPerUnit>
                    <UnitPrice>
                        <xsl:text>Price: </xsl:text>
                        <xsl:value-of select="UnitPrice"/>
                    </UnitPrice>
                </Product>
            </xsl:for-each>
        </Products>
    </xsl:template>
</xsl:stylesheet>

Next, you set the TransformFile attribute of the XmlDataSource control to point to the XSL file. The following shows an example of how the configured data source control would look in your markup, followed by an example of how the XmlDataSource control is bound to a TreeView control in markup.

<asp:XmlDataSource
    ID="XmlDataSource1"
    runat="server"
    DataFile="~/App_Data/products.xml"
    TransformFile="~/App_Data/ProductTransform.xsl" >
</asp:XmlDataSource>

<asp:TreeView
    id="TreeView1"
    runat="server"
    DataSourceID="XmlDataSource1">
    <DataBindings>
        <asp:TreeNodeBinding DataMember="Name" TextField="#InnerText" />
        <asp:TreeNodeBinding DataMember="Category" TextField="#InnerText" />
        <asp:TreeNodeBinding DataMember="QuantityPerUnit" TextField="#InnerText" />
        <asp:TreeNodeBinding DataMember="UnitPrice" TextField="#InnerText" />
    </DataBindings>
</asp:TreeView>

When the page is rendered, ASP.NET loads the XML file into memory. It then applies the XSL file to the XML data. Finally, the result is bound to the TreeView and embedded in the HTTP response. Figure 12-4 shows this data as it would look in a browser window. Notice that the data is sorted and the additional descriptive text has been added to several nodes.

Figure 12-4 The transformed file displayed in a browser window.

Filtering XML with the XmlDataSource Control

The XmlDataSource control also allows you to set a data filter to define a subset of your XML. This is done via the XPath attribute. You set this attribute to a valid XPath expression that represents a filter expression. For example, to retrieve a subset of the product data in the XML file defined in the previous section, you could set the XPath attribute as in the following markup.

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

In this example, the data is filtered for only those products with a category value set to “Beverages.” Notice that the value is actually set to “Category: Beverages.” This is because the XPath expression is applied following any XSL transformations. Recall that in the previous example, the “Category: ” text was added to data. Therefore, you have to account for it in the XPath expression.

Connecting to LINQ-Based Data by Using LinqDataSource

You can use the LinqDataSource control to easily connect to data supplied by any data source that represents a collection of data. This includes lists, arrays, LINQ to SQL objects, and more. In this way, it is the most flexible data source control and typically requires the least amount of supporting code.

The LinqDataSource control uses the ContextTypeName attribute to define the database context of your LINQ-based data. This attribute can be set to point to the name of the class that represents your database context. As an example, suppose you have created a DBML DataContext file to represent the Northwind database by using LINQ to SQL (see Chapter 11). This file would include the NorthwindDataContext class, which represents the tables in your database. The following markup shows how you would connect to this class by using the LinqDataSource control.

<asp:LinqDataSource
    ID="LinqDataSource1"
    runat="server"
    ContextTypeName="NorthwindDataContext"
    EnableDelete="True"
    EnableInsert="True"
    EnableUpdate="True"
    OrderBy="CompanyName"
    TableName="Suppliers"
    Where="Country == @Country">
    <WhereParameters>
        <asp:QueryStringParameter
            DefaultValue="USA"
            Name="Country"
            QueryStringField="country"
            Type="String" />
    </WhereParameters>
</asp:LinqDataSource>

The LinqDataSource control is similar to other data source controls. It allows you to define parameters, indicate sorting, enable paging, and more. However, its LINQ-style declarative syntax makes it unique. Consider the preceding markup. Notice that the TableName attribute is set to Suppliers. You then use attributes to define a query that indicates both a Where clause and an OrderBy clause. The Where clause uses the WhereParameters parameter, which represents a query string that filters the data based on the value of country on the query string.

You can use LINQ language constructs in the markup of your LinqDataSource control. For example, by default, if no Select attribute is specified, the data source will return all fields in the collection. However, you can use a LINQ expression to indicate a new anonymous type. You can even create calculated fields as you would with any Select statement in a LINQ query. The following markup shows an example. Notice that the Location field is made up of three different fields from the data source.

<asp:LinqDataSource ID="LinqDataSource1" runat="server"
    ContextTypeName="NorthwindDataContext"
    EntityTypeName=""
    TableName="Suppliers"
    Where="Country == @Country"
    Select="new(SupplierId As Id,
                CompanyName As Name,
                Address + ' ' + City + ' ' + PostalCode As Location)">
    <WhereParameters>
        <asp:QueryStringParameter DefaultValue="USA" Name="Country"
            QueryStringField="country" Type="String" />
    </WhereParameters>
</asp:LinqDataSource>

You can also bind a LinqDataSource control to a data-bound control, as you would with any other data source. You can set values on the LinqDataSource to indicate whether to allow deleting, inserting, and updating of data. The data-bound control will then work with the LinqDataSource as appropriate.

Connecting to Site Navigation Data by Using SiteMapDataSource

The SiteMapDataSource control is used to connect to site navigation data for your website. The data for this control is defined in a special XML file called a web.sitemap. You can define one sitemap file in the root of your website. The file includes information about the pages in your site and their hierarchy. It also includes page names, navigational information, and a description of each page. It is meant as a central place for managing the navigational data of your site; it is used by controls such as Menu and TreeView to allow users to easily navigate your application.

As an example, suppose that the following web.sitemap file is defined at the root of your web application.

<?xml version="1.0" encoding="utf-8" ?>
<siteMap xmlns="http://schemas.microsoft.com/AspNet/SiteMap-File-1.0" >
    <siteMapNode url="" title="Home" description="">
        <siteMapNode url="products.aspx" title="Products" description="">
            <siteMapNode url="productDetails.aspx"
                title="Product Details" description="" />
        </siteMapNode>
        <siteMapNode url="services.aspx" title="Services" description="" />
        <siteMapNode url="locations.aspx" title="Locations" description="" />
        <siteMapNode url="about.aspx" title="About Us" description="" />
    </siteMapNode>
</siteMap>

You can connect to this data by using a SiteMapDataSource control. You simply add the control to your page. You cannot configure it to point to a specific file. Instead, it automatically picks up the web.sitemap file defined at the root of your web application. The following markup shows an example. You bind to this data the same way you bind to the other data source controls. The following code also demonstrates binding to a Menu control.

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

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

The result of this binding is shown in Figure 12-5.

Figure 12-5 The site map data bound to a Menu control and displayed in a browser.

Filtering the Data Shown in the SiteMapDataSource

Sometimes, you might want to display only a portion of the data in your sitemap data file. The SiteMapDataSource control provides a couple of attributes that you can use to control the data that is provided to a visual control for display. The first, StartingNodeUrl, is used to indicate the node in the sitemap file that should be used as the root of the data source.

As an example, consider the sitemap file discussed previously. Suppose you need to display only the Products node and its subnodes. You can do so by setting the SiteMapDataSource control’s StartingNodeUrl property to product.aspx, as shown in the following sample markup.

<asp:SiteMapDataSource
    ID="SiteMapDataSource1"
    runat="server"
    StartingNodeUrl="products.aspx" />

You can also use the ShowStartingNode attribute to indicate whether to display the node where the SiteMapDataSource control is set to start. You set this value to false if you want to hide the starting node. This property works with the other properties of the SiteMapDataSource control, such as StartingNodeUrl.

You might find that you want your navigation controls to display navigation data based on the current active page in the browser. You can do so by setting the StartFromCurrentNode attribute to true. This evaluates the name of the current page, finds it in the sitemap, and uses it as the start node for any bound controls on that page. This setting is especially useful if you embed your navigation and SiteMapDataSource controls inside a master page.

Finally, the StartingNodeOffset attribute is used to move the starting node up or down the sitemap data tree. You set the value to a negative number to move the start node up the tree from its current evaluated position. A positive number moves it deeper into the tree hierarchy.

Quick Check

Which attribute of the SqlDataSource control do you use to define a connection to the database?
How do you define an ObjectDataSource to connect to a business object?
Which attribute of the LinqDataSource control do you use to indicate the O/R class used for connecting to and working with LINQ-based data?
Which attribute of the EntityDataSource control do you set to indicate a default entity set for your control to expose?

Quick Check Answers

You use the ConnectionString attribute.
You use the TypeName attribute.
You use the ContextTypeName attribute.
You use the EntitySetName attribute.

Practice Using a Data Source Control on a Webpage

In this practice, you work with Visual Studio to create a website to work with an EntityDataSource control that exposes data from the Northwind database.

EXERCISE 1 Creating the Website and Defining the Entity Model

In this exercise, you create a new website and define the Entity Model.

Open Visual Studio and create a new website called DataSourceLab by using your preferred programming language.
Add the northwnd.mdf file to your App_Data directory. You can copy the file from the sample files installed from this book’s CD.
Add a new ADO.NET Entity Data Model to your website. Name this model Northwind.edmx. When prompted, allow Visual Studio to add the model to the App_Code directory.
Using the Entity Data Model Wizard, select Generate from database on the first step and click Next.
On the next page, select the northwnd.mdf data file. Make sure to select the check box to save the connection string in the Web.config file. Name this connection string NorthwndEntitiesCnn and click Next.
On the next page, select the Customers, Order Details, and Orders tables. Make sure that both check boxes are selected, and set the Model Namespace to NorthwndModel. Click Finish to complete the wizard and generate your database model.
Rename items in the model to better identify them. First, select the Order_Detail entity in the model, right-click it, and choose Rename. Rename the entity to OrderDetail. Then select the Order_Details navigation property from the Order entity and rename it to OrderDetails.
Save and close the model.

EXERCISE 2 Binding to the EntityDataSource Control

In this exercise, you create a webpage that defines an EntityDataSource that will be used to work with the entity model created in the previous exercise.

Continue editing the project you created in the previous exercise. Alternatively, you can open the completed Lesson 1, Exercise 1 project in the samples installed from the CD.
Open the Default.aspx page. Set the title over the grid (<h2>) to Customers, and delete the default markup content. In Design view, drag an EntityDataSource control onto the page from the Data tab of the Toolbox. This control will be used to expose a list of customers in the database.
Click the smart tag in the upper-right corner of the EntityDataSource control to open the EntityDataSource Tasks list. Select Configure Data Source to open the Configure Data Source Wizard.
1. On the first page of the wizard, from the Named Connection list, select NorthwndEntitiesCnn.
2. On the second page of the wizard, select the Customers EntitySetName. Set the Select fields to CustomerID, CompanyName, City, Region, Country, and Phone.
3. Finish the wizard, and switch to Source view for your page. Edit the EntityDataSource markup to include AutoPage and AutoSort. Also, include an OrderBy statement to order the results by CompanyName (you can do so inside markup or use the Properties window). Your markup should look similar to the following.
```
<asp:EntityDataSource ID="EntityDataSource1" runat="server"
    ConnectionString="name=NorthwndEntitiesCnn"
    DefaultContainerName="NorthwndEntitiesCnn"
    EnableFlattening="False"
    EntitySetName="Customers"
    AutoPage="true"
    AutoSort="true
    OrderBy="it.CompanyName"
    Select="it.[CustomerID], it.[CompanyName], it.[City], it.[Region],
  it.[Country], it.[Phone]">
</asp:EntityDataSource>
```
Add a GridView control to the page. Set the DataSourceID property to point to the EntityDataSource created previously.
Define bound columns and change the HeaderText for each field in the result set.
Enable AllowPaging and AllowSorting.

Add a HyperLinkField to the GridView to call orders.aspx and pass the customer ID as a query string parameter.

Your GridView markup should look as follows.

<asp:GridView ID="GridViewCustomers" runat="server" AllowPaging="True"
    AllowSorting="True" AutoGenerateColumns="False"
    DataSourceID="EntityDataSource1">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="ID"
            ReadOnly="True" SortExpression="CustomerID" />
        <asp:BoundField DataField="CompanyName" HeaderText="Company"
            ReadOnly="True" SortExpression="CompanyName" />
        <asp:BoundField DataField="City" HeaderText="City"
            ReadOnly="True" SortExpression="City" />
        <asp:BoundField DataField="Region" HeaderText="Region"
            ReadOnly="True" SortExpression="Region" />
        <asp:BoundField DataField="Country" HeaderText="Country"
            ReadOnly="True" SortExpression="Country" />
        <asp:BoundField DataField="Phone" HeaderText="Phone"
            ReadOnly="True" SortExpression="Phone" />
        <asp:HyperLinkField DataNavigateUrlFields="CustomerID"
            DataNavigateUrlFormatString="orders.aspx?custId={0}"
            HeaderText="Orders" Text="view orders" />
    </Columns>
</asp:GridView>

Run the page. Your page should look similar to that shown in Figure 12-6.

Figure 12-6 The EntityDataSource control bound to a GridView.
Add an Orders.aspx page to your site.

Add an EntityDataSource control to the page. Set attributes to connect to the Customers entity. Define a Where clause to select a customer by a specific ID. Set the ID as a QueryStringParameter. Finally, indicate that the results should include the customer’s Orders collection. The following markup shows an example.

<asp:EntityDataSource ID="EntityDataSourceCust" runat="server"
    ConnectionString="name=NorthwndEntitiesCnn"
    DefaultContainerName="NorthwndEntitiesCnn"
    EnableFlattening="False"
    EntitySetName="Customers"
    Where="it.CustomerID=@custId"
    Include="Orders">
    <WhereParameters>
    <asp:QueryStringParameter
        QueryStringField="custId"
        Name="custId"
        DbType="String"/>
    </WhereParameters>
</asp:EntityDataSource>

Next, add a DetailsView control to the page and name it DetailsViewCust. In Design view, use the smart tag to set the data source to the EntityDataSource set previously. Select Refresh Schema from the same smart tag task list to generate columns bound to the data source.
Add a GridView control under the DetailsView and name it GridViewOrders. This will be used to show custom orders. You cannot, at present, bind this to the included collections of the EntityDataSource by using markup. Instead, you must write some code.

Add an event handler for the DetailsViewCust DataBound event. This event fires when the DetailsView control has been bound to data. You can use it to pull the bound Customer from the control and use its Orders collection for the GridView control. Your code should read as follows.

Sample of Visual Basic Code

Protected Sub DetailsViewCust_DataBound( _
    ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles DetailsViewCust.DataBound

    Dim cust As NorthwndModel.Customer =
        CType(DetailsViewCust.DataItem, NorthwndModel.Customer)

    Me.GridViewOrders.DataSource = cust.Orders
    Me.GridViewOrders.DataBind()

End Sub

Sample of C# Code

protected void DetailsViewCust_DataBound(object sender, EventArgs e)
{
    NorthwndModel.Customer cust =
        (NorthwndModel.Customer)DetailsViewCust.DataItem;
    this.GridViewOrders.DataSource = cust.Orders;
    this.GridViewOrders.DataBind();
}

Run the application. Select different customers. Click the view orders link and view the results. You should see something similar to the screen shown in Figure 12-7.

Figure 12-7 The orders.aspx page has two controls that are bound to two different collections exposed by the same EntityDataSource control.

Lesson Summary

ASP.NET provides several data source controls (LinqDataSource, ObjectDataSource, SqlDataSource, AccessDataSource, EntityDataSource, XmlDataSource, and SiteMapDataSource) that allow you to easily work with various types of data. These controls allow you to bind to data by using data-bound web server controls.
You can pass parameters to most data source controls. A parameter can be bound to a value in a cookie, in the session, in a form field, in the query string, or in a similar object.
Many of the data source controls allow you to cache data. Those that do include the ObjectDataSource, SqlDataSource, and AccessDataSource controls.

Lesson Review

You can use the following questions to test your knowledge of the information in Lesson 1, “Connecting to Data with Data Source Controls”. The questions are also available on the companion CD in a practice test, if you prefer to review them in electronic form.

You have a data context map for your SQL Server database defined inside a class file. You need to connect to this data by using a data source control. Which data source control should you use?
1. ObjectDataSource
2. SqlDataSource
3. SiteMapDataSource
4. LinqDataSource
You are using an ObjectDataSource control to connect to a business object. Which attributes of the control must you set to return data for the data source? (Choose all that apply.)
1. TypeName
2. SelectMethod
3. DataSourceId
4. SelectParameters
You want to apply caching to your data source control to increase your scalability for frequently used data. You want to set the cache to expire every 60 seconds. Which attributes of your data source control should you set to do so? (Choose all that apply.)
1. CacheTimeout
2. CacheDuration
3. EnableCaching
4. DisableCaching
You are using an EntityDataSource control on your page. You need to write a custom query that uses a parameter in the Where clause. What actions should you take? (Choose all that apply.)
1. Set the command by using the EntitySetName property.
2. Set the command by using the CommandText property.
3. Add a WhereParameters section to the EntityDataSource control markup.
4. Add a CommandParameters section to the EntityDataSource control markup.