Data Controls

I l @ ve RuBoard

We've seen that ADO.NET offers a lot of functionality, but ADO.NET becomes much more when combined with the data-aware server controls.

The DataGrid Control

The workhorse of these controls is the DataGrid . As we've seen in previous examples, this control makes it simple to display data from the database. The simplest way to add a DataGrid is as follows :

 <asp:DataGrid id=DataGrid1 runat="server" /> 

The ID property gives us something we can write code against. By saying runat=server , we can handle server-side events, and change the properties of this control with code.

DataGrid Appearance

Right out of the box, the DataGrid gives you a lot of control over how it looks (see Listing 4.17).

Listing 4.17 Controlling the appearance of the DataGrid .

 <asp:DataGrid id="DataGrid1" runat="server" BorderStyle="None"     GridLines="Vertical" BorderWidth="1px" BorderColor="#999999"     BackColor="White" CellPadding="3">     <FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>     <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084">     </HeaderStyle>     <PagerStyle HorizontalAlign="Center" ForeColor="Black" BackColor="#999999"         Mode="NumericPages"></PagerStyle>     <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C">     </SelectedItemStyle>     <AlternatingItemStyle BackColor="#DCDCDC"></AlternatingItemStyle>     <ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle> </asp:DataGrid> 

We can use the HeaderStyle property to indicate the color , font, and other appearance features of the header row on the resulting table. ItemStyle and AlternatingItemStyle control the appearance of the odd and even lines.

Custom Column Headings

So far, the DataGrid has simply output all the information from the DataSet . The column names from the database became the column headings in the grid. We can take more control over the output by specifying the columns that we want displayed (see Listing 4.18).

Listing 4.18 Custom column headings.

 <asp:DataGrid id="DataGrid1" runat="server" BorderStyle="None"     GridLines="Vertical" BorderWidth="1px" BorderColor="#999999"     BackColor="White" CellPadding="3" '  AutoGenerateColumns="False"  >     <FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>     <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084">     </HeaderStyle>     <PagerStyle HorizontalAlign="Center" ForeColor="Black" BackColor="#999999"         Mode="NumericPages"></PagerStyle>     <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C"></  SelectedItemStyle>     <AlternatingItemStyle BackColor="#DCDCDC"></AlternatingItemStyle>     <ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>  <Columns>   <asp:BoundColumn DataField="au_lname" HeaderText="Last Name">   </asp:BoundColumn>   <asp:BoundColumn DataField="au_fname" HeaderText="First Name">   </asp:BoundColumn>   <asp:BoundColumn DataField="state" HeaderText="State">   </asp:BoundColumn>   </Columns>  </asp:DataGrid> 

By setting the AutoGenerateColumns property of the DataGrid to false, we're saying that we are going to control which columns appear. We do this by accessing the Column property, and listing the data-bound columns. The DataField property specifies the column name from the database, and the Header Text defines the name of the column as it appears to the user .

This appears as shown in Figure 4.5.

Figure 4.5. Displaying data using the Repeater control.

Previously, we sorted the DataGrid by adding buttons to the page, but the DataGrid was built with sorting in mind. If you set the AllowSorting property to true, the column heading become hyperlinks . With a few lines of code, we can sort the column when the user clicks on the column heading. First, we set the AllowSorting property to true:

Figure 4.4. Displaying data using the DataGrid .

 <asp:DataGrid id=DataGrid1 runat="server" ForeColor="Black"     AutoGenerateColumns=False     AllowSorting=True> 

We also need to modify our custom columns to specify the sort field as shown in Listing 4.19:

Listing 4.19 Adding the SortExpression

 <Columns>     <asp:BoundColumn DataField="au_lname" HeaderText="Last Name"  SortExpression="au_lname"  ></asp:BoundColumn>     <asp:BoundColumn DataField="au_fname" HeaderText="First Name"  SortExpression="au_fname"  ></asp:BoundColumn>     <asp:BoundColumn DataField="state" HeaderText="State"  SortExpression="state"  ></asp:BoundColumn> </Columns> 

Then, we write code for the SortCommand event of the DataGrid as shown in Listing 4.20.

Listing 4.20 The SortCommand Event

 protected void DataGrid1_SortCommand (object source,     System.Web.UI.WebControls.DataGridSortCommandEventArgs e) {     DataSet ds = LoadAuthors();     DataView dv = ds.Tables["authors"].DefaultView;     dv.Sort = e.SortExpression.ToString();     DataGrid1.DataSource = dv;     DataGrid1.DataBind(); } 

When the user clicks on a column heading, the SortCommand event fires. This event gets a DataGridSortCommandEventArgs argument that contains the name of the sort field for the column that the user clicked on. With this, we can set the Sort property of the DefaultView . We rebind the DataGrid , and the results are now resorted.

To work with sorting yourself, create a new page called DataGrid.aspx. Place the code from Listing 4.21 in the DataGrid.aspx file. Place the code from Listing 4.22 in the DataGrid.aspx.cs file.

Listing 4.21 Custom columns with sorting ”DataGrid.aspx.

 <%@ Page language="c#" Codebehind="DataGrid.cs" AutoEventWireup="false"     Inherits="ado_net_by_example.DataGrid" %> <HTML>     <HEAD>         <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">         <meta name="CODE_LANGUAGE" Content="C#">     </HEAD>     <body>         <form method="post" runat="server">  <asp:DataGrid id="DataGrid1" runat="server" BorderStyle="None"   GridLines="Vertical" BorderWidth="1px" BorderColor="#999999"   BackColor="White" CellPadding="3" AutoGenerateColumns="False"   AllowSorting="true">   <FooterStyle ForeColor="Black" BackColor="#CCCCCC">   </FooterStyle>   <HeaderStyle Font-Bold="True" ForeColor="White"   BackColor="#000084"></HeaderStyle>   <PagerStyle HorizontalAlign="Center" ForeColor="Black"   BackColor="#999999" Mode="NumericPages"></PagerStyle>   <SelectedItemStyle Font-Bold="True" ForeColor="White"   BackColor="#008A8C"></SelectedItemStyle>   <AlternatingItemStyle BackColor="#DCDCDC">   </AlternatingItemStyle>   <ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>   <Columns>   <asp:BoundColumn DataField="au_lname"   HeaderText="Last Name" SortExpression="au_lname">   </asp:BoundColumn>   <asp:BoundColumn DataField="au_fname"   HeaderText="First Name" SortExpression="au_fname">   </asp:BoundColumn>   <asp:BoundColumn DataField="state" HeaderText="State"   SortExpression="state"></asp:BoundColumn>   </Columns>   </asp:DataGrid>  </form>     </body> </HTML> 

Listing 4.22 Custom columns with sorting ”DataGrid.aspx.cs.

 namespace ado_net_by_example {     using System;     using System.Collections;     using System.ComponentModel;     using System.Data;     using System.Data.SqlClient;     using System.Drawing;     using System.Web;     using System.Web.SessionState;     using System.Web.UI;     using System.Web.UI.WebControls;     using System.Web.UI.HtmlControls;     /// <summary>     ///    Summary description for DataGrid.     /// </summary>     public class DataGrid : System.Web.UI.Page     {         protected System.Web.UI.WebControls.DataGrid DataGrid1;     public DataGrid()     {         Page.Init += new System.EventHandler(Page_Init);         }         protected void Page_Load(object sender, EventArgs e)         {             if (!IsPostBack)             {  DataSet ds = LoadAuthors();   DataGrid1.DataSource = ds.Tables["authors"].DefaultView;   DataGrid1.DataBind();  }         }         protected DataSet LoadAuthors()         {             DataSet ds;             if (Cache["authors"] == null)             {                 SqlConnection cn = new SqlConnection(                     "server=localhost;" +                     "database=pubs;" +                     "uid=sa;" +                     "password=");                 SqlDataAdapter cmd = new SqlDataAdapter(                     "select * from authors",cn);                 ds = new DataSet();                 cmd.Fill(ds,"authors");                 Cache.Insert("authors",ds,                     null,                     System.Web.Caching.Cache.NoAbsoluteExpiration,                     TimeSpan.FromMinutes(5));             }             else             {                 ds = (DataSet)Cache["authors"];             }             return ds;         }         protected void Page_Init(object sender, EventArgs e)         {             //             // CODEGEN: This call is required by the ASP+ Windows Form             // Designer.             //             InitializeComponent();         }         /// <summary>         ///    Required method for Designer support - do not modify         ///    the contents of this method with the code editor.         /// </summary>         private void InitializeComponent()         {             this.DataGrid1.SortCommand += new                 System.Web.UI.WebControls.DataGridSortCommandEventHandler(                     this.DataGrid1_SortCommand);             this.Load += new System.EventHandler(this.Page_Load);         }  protected void DataGrid1_SortCommand (object source,   System.Web.UI.WebControls.DataGridSortCommandEventArgs e)   {   DataSet ds = LoadAuthors();   DataView dv = ds.Tables["authors"].DefaultView;   dv.Sort = e.SortExpression.ToString();   DataGrid1.DataSource = dv;   DataGrid1.DataBind();   }  } } 

The Repeater Control

The DataGrid is great for rendering data as an HTML table, but sometimes, that's not the look we're going for. To have even more control over the output, we can use the Repeater control. This is known as a "look-less" control, meaning, not only can we take control over the display, we have to take control because this particular object has no default appearance at all. Instead, we're going to specify the look of the header, footer, and each item with a series of templates. To begin, add a new Web Form to the project named "Repeater". Switch to HTML view, and add the code in Listing 4.23 to the Form tag.

Listing 4.23 The Repeater control.

 <asp:Repeater id=Repeater1 runat="server">     <HeaderTemplate>         <h1>Authors</h1>     </HeaderTemplate>     <ItemTemplate>         <table>             <tr>                 <td>First Name : </td>                 <td><%# DataBinder.Eval(Container.DataItem, "au_fname") %></td>             </tr>             <tr>                 <td>Last Name : </td>                 <td><%# DataBinder.Eval(Container.DataItem, "au_lname") %></td>             </tr>             <tr>                 <td>State : </td>                 <td><%# DataBinder.Eval(Container.DataItem, "state") %></td>             </tr>         </table>     </ItemTemplate>     <SeparatorTemplate>         <hr>     </SeparatorTemplate> </asp:Repeater> 

The contents of the HeaderTemplate will get output first. Then, the ItemTemplate gets displayed for each row from the data source. To output the value of a particular column, use the following syntax:

 <%# DataBinder.Eval(Container.DataItem, "au_lname") %> 

The " <%# ... %> " is the data-binding syntax for ASP.NET. Between these brackets, you can call a function, output the value of a variable, or get your data from any other source. Each time the ItemTemplate gets hit, the Container.DataItem will contain the next row of data from the data source. To simplify getting a value from this row, we use the Eval method of the DataBinder class, passing Container.DataItem , and the name of the column whose value we want. Finally, the contents of the separator template will be output between each DataItem .

Check out Listings 4.24 and 4.25, and Figure 4.5 for the complete listing and output.

Listing 4.24 Using the DataRepeater ”RepeaterControl.aspx.

 <%@ Page language="c#" Codebehind="RepeaterControl.cs" AutoEventWireup="false"     Inherits="ado_net_by_example.RepeaterControl" %> <html> <head>     <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">     <meta name="CODE_LANGUAGE" Content="C#"> </head> <body> <form method="post" runat="server">  <asp:Repeater id=Repeater1 runat="server">   <HeaderTemplate>   <h1>Authors</h1>   </HeaderTemplate>   <ItemTemplate>   <table>   <tr>   <td>First Name : </td>   <td><%# DataBinder.Eval(Container.DataItem, "au_fname") %>   </td>   </tr>   <tr>   <td>Last Name : </td>   <td><%# DataBinder.Eval(Container.DataItem, "au_lname") %>   </td>   </tr>   <tr>   <td>State : </td>   <td><%# DataBinder.Eval(Container.DataItem, "state") %>   </td>   </tr>   </table>   </ItemTemplate>   <SeparatorTemplate>   <hr>   </SeparatorTemplate>   </asp:Repeater>  </form> </body> </html> 

Listing 4.25 Using the DataRepeater ” RepeaterControl .cs.

 namespace ado_net_by_example {     using System;     using System.Collections;     using System.ComponentModel;     using System.Data;     using System.Data.SqlClient;     using System.Drawing;     using System.Web;     using System.Web.SessionState;     using System.Web.UI;     using System.Web.UI.WebControls;     using System.Web.UI.HtmlControls;     /// <summary>     ///    Summary description for RepeaterControl.     /// </summary>     public class RepeaterControl : System.Web.UI.Page     {         protected System.Web.UI.WebControls.Repeater Repeater1;     public RepeaterControl()     {         Page.Init += new System.EventHandler(Page_Init);         }         protected void Page_Load(object sender, EventArgs e)         {             if (!IsPostBack)             {  DataSet ds = LoadAuthors();   Repeater1.DataSource = ds.Tables["authors"];   Repeater1.DataBind();  }         }         protected DataSet LoadAuthors()         {             DataSet ds;             if (Cache["authors"] == null)             {                 SqlConnection cn = new SqlConnection(                     "server=localhost;" +                     "database=pubs;" +                     "uid=sa;" +                     "password=");                 SqlDataAdapter cmd = new SqlDataAdapter(                     "select * from authors",cn);                 ds = new DataSet();                 cmd.Fill(ds,"authors");                 Cache.Insert("authors",ds,                     null,                     System.Web.Caching.Cache.NoAbsoluteExpiration,                     TimeSpan.FromMinutes(5));             }             else             {                 ds = (DataSet)Cache["authors"];             }             return ds;         }         protected void Page_Init(object sender, EventArgs e)         {             //             // CODEGEN: This call is required by the ASP+ Windows Form             // Designer.             //             InitializeComponent();         }         /// <summary>         ///    Required method for Designer support - do not modify         ///    the contents of this method with the code editor.         /// </summary>         private void InitializeComponent()         {             this.Load += new System.EventHandler (this.Page_Load);         }     } } 

The DataList Control

The last data control that we're going to look at is the DataList control. This control offers "directional rendering," which means that you can output each item in rows and columns. We'll simply replace the Repeater with the DataList , and set its RepeatDirection to Horizontal or Vertical . We then specify the number of columns in the output by specifying a value for RepeatColumns :

 <asp:datalist id=DataList1 runat="server"     repeatdirection="Vertical"     repeatcolumns="2"> 

Here's the complete output in Listings 4.26 and 4.27. You can see the output in Figure 4.6.

Figure 4.6. Displaying data using the DataList .

Listing 4.26 Using the DataList ”DataListControl.aspx.

 <%@ Page language="c#" Codebehind="DataListControl.cs" AutoEventWireup="false"     Inherits="ado_net_by_example.DataListControl" %> <html>     <head>         <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">         <meta name="CODE_LANGUAGE" Content="C#">     </head>     <body>         <form method="post" runat="server">  <asp:datalist id="DataList1" runat="server"   repeatdirection="Vertical" repeatcolumns="2">   <HeaderTemplate>   <h1>   Authors   </h1>   </HeaderTemplate>   <ItemTemplate>   <table>   <tr>   <td>   First Name :   </td>   <td>   <%# DataBinder.Eval(   Container.DataItem, "au_fname") %>   </td>   </tr>   <tr>   <td>   Last Name :   </td>   <td>   <%# DataBinder.Eval(   Container.DataItem, "au_lname") %>   </td>   </tr>   <tr>   <td>   State :   </td>   <td>   <%# DataBinder.Eval(   Container.DataItem, "state") %>   </td>   </tr>   </table>   </ItemTemplate>   </asp:datalist>  </form>     </body> </html> 

Listing 4.27 Using the DataList ”DataListControl.cs.

 namespace ado_net_by_example {     using System;     using System.Collections;     using System.ComponentModel;     using System.Data;  using System.Data.SqlClient;  using System.Drawing;     using System.Web;     using System.Web.SessionState;     using System.Web.UI;     using System.Web.UI.WebControls;     using System.Web.UI.HtmlControls;     /// <summary>     ///    Summary description for DataListControl.     /// </summary>     public class DataListControl : System.Web.UI.Page     {         protected System.Web.UI.WebControls.DataList DataList1;     public DataListControl()     {         Page.Init += new System.EventHandler(Page_Init);         }         protected void Page_Load(object sender, EventArgs e)         {             if (!IsPostBack)             {  DataSet ds = LoadAuthors();   DataList1.DataSource = ds.Tables["authors"].DefaultView;   DataList1.DataBind();  }         }         protected DataSet LoadAuthors()         {             DataSet ds;             if (Cache["authors"] == null)             {                 SqlConnection cn = new SqlConnection(                     "server=localhost;" +                     "database=pubs;" +                     "uid=sa;" +                     "password=");                 SqlDataAdapter cmd = new SqlDataAdapter(                     "select * from authors order by au_lname",cn);                 ds = new DataSet();                 cmd.Fill(ds,"authors");                 Cache.Insert("authors",ds,                     null,                     System.Web.Caching.Cache.NoAbsoluteExpiration,                     TimeSpan.FromMinutes(5));             }             else             {                 ds = (DataSet)Cache["authors"];             }             return ds;         }         protected void Page_Init(object sender, EventArgs e)         {             //             // CODEGEN: This call is required by the ASP+ Windows Form             // Designer.             //             InitializeComponent();         }         /// <summary>         ///    Required method for Designer support - do not modify         ///    the contents of this method with the code editor.         /// </summary>         private void InitializeComponent()         {             this.Load += new System.EventHandler (this.Page_Load);         }     } } 

As you can see, directional rendering is very simple with the DataList . For those of you who've had to hand-code these kinds of pages in the past with ASP, I'm sure you can appreciate the elegance of this control.

I l @ ve RuBoard