Using a DataList Control to Access a Database

Using a DataList Control to Access a Database

In this section, you'll learn how to use a DataList control to access the rows in the Products table.

Tip 

A DataList offers you a lot more flexibility in the presentation of column values than that offered by a DataGrid.

Perform the following steps:

  1. To create the new project, select File New Project in VS .NET. Select Visual C# Projects from the Project Types area on the left of the New Project dialog box, and select ASP .NET Web Application from the Templates area on the right. Enter http://localhost/DataList-WebApplication in the Location field. Click OK to continue. Your new project will contain a blank form.

  2. Next, you'll add a SqlConnection object and a SqlDataAdapter object to your form. Select the Products table in Server Explorer and drag it to your form.

  3. After you drag the Products table to your form, VS .NET creates a SqlConnection object named sqlConnection1 and a SqlDataAdapter object named sqlDataAdapter1.

  4. Click your sqlConnection1 object to display the properties for this object in the Properties window. To enable sqlConnection1 to access the database, you need to set the password for the connection. Add pwd=sa; to the ConnectionString property.

  5. Next, you'll modify the SQL SELECT statement used to retrieve the rows from the Products table. Click your sqlDataAdapter1 object to display the properties for this object. Click the addition icon to the left of the SelectCommand property to display the dynamic properties; one of the dynamic properties is the CommandText property, which contains the SELECT statement. Next, click CommandText and then click the ellipsis button to display the Query Builder. You can type in the SQL statement, or you can build it up visually. Uncheck all the columns except ProductID, ProductName, QuantityPerUnit, and UnitPrice. This results in the SQL SELECT statement being set to the following:

     SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products 

  6. Click OK to continue.

  7. Next, you need to create a DataSet object. Click an area of your form. Next, click the Generate-Dataset link near the bottom of the Properties window. This displays the Generate Dataset dialog box. Select the New radio button and make sure the field to the right of this radio button contains DataSet1. Click the OK button to continue. This adds a new DataSet object named dataSet11 to your form.

  8. Next, you'll add a DataList control to your form. To do this, select the DataList from the Toolbox and drag it to your form. Figure 15.16 shows the form with the new DataList.

    click to expand
    Figure 15.16: Form with a DataList

  9. Next, you'll need to set the DataSource property of your DataList to your DataSet object created earlier. This sets the source of the data for your DataList and allows the rows from your DataSet to be displayed in your DataList. To set the DataSource property, click your DataList object and set its DataSource property to dataSet11. Also, set the DataMember property of your DataList to Products; this is the table with rows that are displayed by the DataList.

A DataList uses templates that define how its contents are laid out, and your next task is to set up those templates.

Tip 

It is the DataList templates that give you the flexibility for laying out controls that display column values.

You'll edit the template that defines the header and footer for the DataList, along with the template that defines the actual items displayed within your DataList:

  1. To edit the header and footer template, right-click your DataList and select Edit Template Header And Footer Templates.

  2. You can add controls to the areas within the HeaderTemplate and FooterTemplate areas. Any controls you add will be displayed at the start and end of the DataList, respectively. Add a label in HeaderTemplate; you do this by dragging a Label control from the Toolbox to the empty area below HeaderTemplate. Set the Text property for this Label to Products. Also, add a Label in the FooterTemplate area and set its Text property to End of list. Figure 15.17 shows the modified header and footer templates with the Label controls.

    click to expand
    Figure 15.17: The modified header and footer templates with Label controls

    Note 

    You can end editing a template at any time by right-clicking your DataList and selecting End Template Editing.

  3. Next, you'll edit the item template and add Label controls to display the ProductID, ProductName, QuantityPerUnit, and UnitPrice columns. Right-click your DataList and select Edit Template Item Templates. Figure 15.18 shows the Item Templates editor.

    click to expand
    Figure 15.18: The Item Templates editor

As you can see from Figure 15.18, the Item Templates editor is divided into the following four areas:

  • ItemTemplate Contains controls that you typically use to display column values.

  • AlternatingItemTemplate Contains controls that are shown after the controls in the ItemTemplate.

  • SelectedItemTemplate Contains controls that are shown when you select an item.

  • EditItemTemplate Contains controls that are shown when you edit an item.

You'll add a table to the ItemTemplate area, and then you'll add four Label controls in the cells of your table. The four Label controls will display the values for the ProductID, ProductName, QuantityPerUnit, and UnitPrice columns. To add a table:

  1. Click anywhere in the ItemTemplate area and select Table Insert Table. Set the properties for the table as shown in Figure 15.19.

    click to expand
    Figure 15.19: Setting the properties of the table

  2. Next, drag a Label to the first cell in the table. You'll use this first Label to display the ProductID column. Set the ID property of your Label to ProductID, as shown in Figure 15.20.

    click to expand
    Figure 15.20: Adding the Label

  3. To get the Label to display the ProductID column, you'll need to bind it to that column. To do this, click the ellipsis button in the DataBindings property. You'll then see the DataBindings dialog box. Open the Container node by clicking the addition icon, and then open the DataItem node; finally, select the ProductID column, as shown in Figure 15.21.

    click to expand
    Figure 15.21: Binding the Label to the ProductID column

  4. Next, add three more Label controls in the remaining cells of your table. Set the ID property for your three Label controls to ProductName, QuantityPerUnit, and UnitPrice, respectively. Also, bind each of your Label controls to the ProductName, QuantityPerUnit, and UnitPrice columns, respectively.

    Warning 

    As you add your Label controls to the cells, you'll notice that the remaining cells shrink. Watch out for that as it can make adding the other Label controls a little tricky.

  5. Next, you'll modify the HTML for your form to make the table a little easier to read. You'll change the width and border attributes of the TABLE tag and setting the width attribute of the TD tags.

    Note 

    The TABLE tag defines a table, and the TD tag defines an element in a row.

  6. To view the HTML code for your form, click the HTML link under the form designer to view the code for your form. Set the width and border attributes of your TABLE tag to 320 and 1, respectively, and set the width attributes of the four TD tags to 20, 100, 100, and 100, respectively. The following HTML shows these changes:

     <TABLE  cellSpacing="1" cellPadding="1" width="320" border="1">   <TR>     <TD width="20">       <asp:Label id=ProductID runat="server" Text='        <%# DataBinder.Eval(Container, "DataItem.ProductID") %>'>       </asp:Label></TD>     <TD width="100">       <asp:Label id=ProductName runat="server" Text='        <%# DataBinder.Eval(Container, "DataItem.ProductName") %>'>       </asp:Label></TD>     <TD width="100">       <asp:Label id=QuantityPerUnit runat="server" Text='        <%# DataBinder.Eval(Container, "DataItem.QuantityPerUnit") %>'>       </asp:Label></TD>     <TD width="100">       <asp:Label id=UnitPrice runat="server" Text='        <%# DataBinder.Eval(Container, "DataItem.UnitPrice") %>'>       </asp:Label></TD>   </TR> </TABLE> 

    Note 

    The ID attribute of your TABLE tag might differ from that shown in the previous code. Don't worry about changing the ID attribute for your TABLE tag.

  7. Next, you'll need to add code to populate sqlDataAdapter1 with the rows retrieved by your SELECT statement. Typically, the best place to place this code is in the Page_Load() method of your form. The Page_Load() method is called when the Web page containing your form is initially loaded or refreshed. Open the code for your form by selecting View Code, or press F7 on your keyboard. Set your Page_Load() method to the following:

     private void Page_Load(object sender, System.EventArgs e) {   // Put user code to initialize the page here   if (!this.IsPostBack)   {     sqlDataAdapter1.Fill(dataSet11, "Products");     this.DataBind();   } } 

The Fill() method retrieves the rows from the Products table and populates the dataSet11 object with those rows. The DataBind() method then causes the rows to be displayed in the DataList of your form.

To run your form, select Debug Start Without Debugging, or press Ctrl+F5 on your keyboard. Figure 15.22 shows the running form.

click to expand
Figure 15.22: The running form

Once you've finished running your form, close it and return to the form designer. Click the HTML link to view the code for your form. Listing 15.5 shows the WebForm1.aspx file for the form. You'll notice that this file contains a DataList control with the appropriate columns.

Listing 15.5: THE WebForm1.aspx FILE

start example
 <%@ Page language="c#" Codebehind="WebForm1.aspx.cs"  AutoEventWireup="false"  Inherits="DataListWebApplication.WebForm1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML>   <HEAD>     <title>WebForm1</title>     <meta content="Microsoft Visual Studio 7.0" name="GENERATOR">     <meta content="C#" name="CODE_LANGUAGE">     <meta content="JavaScript" name="vs_defaultClientScript">     <meta content="http://schemas.microsoft.com/intellisense/ie5"       name="vs_targetSchema">   </HEAD>   <body MS_POSITIONING="GridLayout">     <form  method="post" runat="server">       <asp:datalist id=DataList1 style="Z-INDEX: 101; LEFT: 33px;        POSITION: absolute; TOP: 28px" runat="server"        DataMember="Products" Height="140" Width="297" DataSource="        <%# dataSet11 %>">         <HeaderTemplate>           <asp:Label  runat="server">Products</asp:Label>         </HeaderTemplate>         <FooterTemplate>           <asp:Label  runat="server">End of list</asp:Label>         </FooterTemplate>         <ItemTemplate>           <TABLE  cellSpacing="1" cellPadding="1" width="320"            border="1">             <TR>               <TD width="20">                 <asp:Label id=ProductID runat="server" Text='                  <%# DataBinder.Eval(Container, "DataItem.ProductID") %>'>                 </asp:Label></TD>               <TD width="100">                 <asp:Label id=ProductName runat="server" Text='                  <%# DataBinder.Eval(Container,                   "DataItem.ProductName") %>'>                 </asp:Label></TD>               <TD width="100">                 <asp:Label id=QuantityPerUnit runat="server" Text='                  <%# DataBinder.Eval(Container, "DataItem.QuantityPerUnit") %>'>                 </asp:Label></TD>               <TD width="100">                 <asp:Label id=UnitPrice runat="server" Text='                  <%# DataBinder.Eval(Container, "DataItem.UnitPrice") %>'>                 </asp:Label></TD>             </TR>           </TABLE>         </ItemTemplate>       </asp:datalist></form>   </body> </HTML> 
end example




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net