Microsoft ASP .NET Professional Projects - Premier Press
.pdfEnd Sub
Sub ReBind()
'DataSetCommand
SQL = "select m.*, g.code_display as category "
SQL = SQL + "from masters m, groups g "
SQL = SQL + " where m.code_category = g.code_value" myCommand = New OleDbDataAdapter(SQL, myConnection) 'use Fill method of DataSetCommand to populate dataset myCommand.Fill(ds, "masters")
'Sort accounding to sortField Dim dv2 As DataView
dv2 = ds.Tables("masters").DefaultView dv2.Sort = SortField
Grid1.DataSource= dv2
Grid1.DataBind()
End Sub
Sub MyDataGrid_Page(sender As Object, e As DataGridPageChangedEventArgs)
Grid1.CurrentPageIndex = e.NewPageIndex
ReBind
End Sub
Sub MyDataGrid_Sort(sender As Object, e As DataGridSortCommandEventArgs)
SortField = e.SortExpression
ReBind
End Sub
End Class
The DataList
The DataList is completely template-driven. The following templates can be applied to the DataList:
§ItemTemplate
§AlternatingItemTemplate
§SeparatorTemplate
§SelectedItemTemplate
§EditItemTemplate
§HeaderTemplate
§FooterTemplate
Using these templates you can apply various styles to the header, footer, items, and alternating items. The templates work just as they do in the Data Repeater. The difference is that you can define an "EditItemTemplate" at item level as in a DataGrid. This gives editing capabilities to the DataList. Where the DataList differs from the DataGrid is that you can specify the "RepeatDirection" and "RepeatColumns" properties. With the RepeatDirection property you can either render the datasource in a horizontal or vertical direction. With the RepeatColumns property you can control the number of columns that are rendered in a specified direction. In the example that follows, I build a web form, which derives its data from the Groups table. It renders columns, three across, and has editing functionality. Figure 4.6 shows what it looks like. Figure 4.7 shows what the DataList looks like in Edit mode.
Figure 4.6: The DataList.
Figure 4.7: DataList in Edit Mode.
GroupsDlist.aspx
<%@Page Language="VB" Inherits="BaseClass" Src="GroupsDlist.vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %> <html>
<script language="VB" runat="server">
Sub DataList_UpdateCommand(sender As Object, e As DataListCommandEventArgs) Dim sql As string
Dim code_display As String
Dim type As String
Dim myTextBox As TextBox
myTextBox = E.Item.FindControl("edit_display") code_display = mytextbox.text
myTextBox = E.Item.FindControl("edit_type") type = mytextbox.text
'Now execute stored procedure
response.write("Execute some procedure @name=" + code_display + "@type=" + type) End Sub
</script>
<body style="font: 10pt verdana"> <form runat="server">
<h3><font face="Verdana">Groups (DataList) </font></h3> <asp:Label id="Message" runat="server"/>
<asp:DataList id="Grid1" runat="server" BorderColor="black"
BorderWidth="1"
GridLines="Both"
CellPadding="3"
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Width="800px"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="Gainsboro"
EditItemStyle-BackColor="lightgreen"
OnEditCommand="DataList_EditCommand"
OnUpdateCommand="DataList_UpdateCommand"
OnCancelCommand="DataList_CancelCommand"
RepeatColumns="3" RepeatDirection="horizontal" RepeatMode="Table" >
<HeaderTemplate>Name</HeaderTemplate>
<ItemTemplate>
<asp:LinkButton id="button1" runat="server" Text="Edit" CommandName="edit" />
<%# Container.DataItem("code_display") %>
</ItemTemplate>
<EditItemTemplate>
Name:
<asp:Label id="Label1" runat="server" Text='<%# Container.DataItem("code_display") %>' />
<br>
Group:
<asp:TextBox id="edit_display" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"code_display") %>' />
<br>
Type:
<asp:TextBox id="edit_type" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "type") %>' />
<br>
<asp:LinkButton id="button2" runat="server" Text="Update" CommandName="update" />
<asp:LinkButton id="button3" runat="server" Text="Cancel" CommandName="cancel" />
</EditItemTemplate>
</asp:DataList>
</form>
</body>
</html>
GroupsDlist.vb is the Code Behind file for this form. It contains the following code:
GroupsDlist.vb
Option Strict Off
Imports System
Imports System.Collections
Imports System.Text
Imports System.Data
Imports System.Data.OleDb
Imports System.Web.UI
Imports System.Web.UI.WebControls
Public Class BaseClass
Inherits System.Web.UI.Page
Protected Grid1 As Datalist
Protected Message as label
Dim myConnection As OleDbConnection
Dim myCommand As OleDbDataAdapter
Dim ds As New DataSet
Dim ConnStr As String
Dim SQL As String
Sub Page_Load(Source As Object, E As EventArgs)
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User
ID=sa;"
myConnection = New OleDbConnection(ConnStr) if NOT (isPostBack)
rebind end if
End Sub
Sub ReBind()
'DataSetCommand
SQL = "select * from Groups"
myCommand = New OleDbDataAdapter(SQL, myConnection)
'use Fill method of DataSetCommand to populate dataset
myCommand.Fill(ds, "Groups")
'Binding a Grid
Grid1.DataSource=ds.Tables("Groups").DefaultView
Grid1.DataBind()
End Sub
Sub DataList_EditCommand(sender As Object, e As DataListCommandEventArgs)
Grid1.EditItemIndex = e.Item.ItemIndex
rebind
End Sub
Sub DataList_CancelCommand(sender As Object, e As DataListCommandEventArgs)
Grid1.EditItemIndex = -1
rebind
End Sub
End Class
The DataList derives its data from the DataSet ds. The function Rebind populates the DataList with data. In the ItemTemplate I define a template for LinkButton which displays a link called "edit". An "Edit" CommandName has been defined on the LinkButton. When clicked the CommandName tells the DataList that it is in Edit mode. Once in Edit mode, the DataList automatically generates the "update" and "cancel" links. Clicking on the "edit," "update," or "cancel" links fires the "DataList_EditCommand",
"DataList_UpdateCommand", or "DataList_CancelCommand" respectively. The
EditItemIndex property of the DataList is set to the index of the clicked button in the
DataList_EditCommand event. The EditItemIndex is set to -1 in the cancel event. In the update command (DataList_UpdateCommand) I build a SQL string in a manner similar to the one discussed in the DataGrid. This is a placeholder for an actual stored procedure call. I have specified the RepeatColumns="3" and RepeatDirection= "horizontal" properties in the DataList tag. This generates 3 columns across in the horizontal direction.
I use the DataBinder.Eval() method to bind the columns. This method takes three arguments: the naming container for the data item, the data field name, and a format string. The datacontainer for a DataList (and DataGrid) is always Container.DataItem. You can apply formatting to the bound column such as in the following:
<%# DataBinder.Eval(Container.DataItem, "IntegerValue", "{0:c}") %>
Binding to XML Data
XML is an integral part of ASP.NET so it is very simple to bind a list control to an XML datasource. Figure 4.8 shows an example.
Figure 4.8: Binding to XML Data. navXML.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<html>
<head> <B>Reading XML</B> </head>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
Dim ds As New DataSet
Dim fs As filestream
Dim xmLStream As StreamReader
fs = New filestream(Server.MapPath("more.xml"), FileMode.Open, FileAccess.Read)
xmlStream = new StreamReader(fs)
ds.ReadXML(XmlStream)
DataGrid1.DataSource=ds.Tables("article").DefaultView
DataGrid1.DataBind()
End Sub
</script>
<body>
<form runat=server>
<asp:DataGrid id="DataGrid1" runat="server" />
</form>
</body>
</html>
I use the filestream method to open the XML file (more.xml) in a "read" mode and populate the StreamReader with the contents. I then use the ReadXML method of the DataSet to read the XML data into the DataSet. Once in the DataSet, I can manipulate the data in a manner similar to database tables. Finally, I bind the "article" element of the XML file to a DataGrid.
Implementing a Master-Detail Relationship
A master-detail type of relationship is when one to many relationships exist between two tables. The master table rows are shown in one portion of the form. Clicking on a master row displays all detail rows in another location of the form. I will show you how to implement a master-child relationship using the authors, titles, and titleauthor tables of the Pubs database. The Authors details are displayed with a "Select" link. Clicking on this hyperlink displays details about the books written by the selected author. This relationship is implemented using a DataGrid. Figure 4.9 illustrates what it looks like.
Figure 4.9: Master Child Relationship.
MasterChild.aspx
<%@Page Language="VB" Inherits="BaseClass" Src="MasterChild.vb" %> <html>
<head>
<title>DataGrid Samples - Step 3</title> </head>
<body>
<h1> Master Child Example </h1> <form runat=server>
<table width="95%"> <tr>
<td valign="top"> <b>Authors:</b><br>
<asp:DataGrid id="authorsGrid" runat="server" AutoGenerateColumns="false" BackColor="White"
BorderWidth="1px" BorderStyle="Solid" BorderColor="Tan"
CellPadding="2" CellSpacing="0"
Font-Name="Verdana" Font-Size="8pt"
DataKeyField="au_id"
OnSelectedIndexChanged="Grid_Select"
>
<Columns>
<asp:ButtonColumn Text="Select" CommandName="Select"/> <asp:BoundColumn HeaderText="ID" DataField="au_id">
<HeaderStyle Width="100px"> </HeaderStyle> </asp:BoundColumn>
<asp:BoundColumn HeaderText="Name" DataField="au_name"> <HeaderStyle Width="150px"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="State" DataField="state">
<HeaderStyle Width="50px"></HeaderStyle> </asp:BoundColumn>
</Columns>
<HeaderStyle BackColor="DarkRed" ForeColor="White" Font-Bold="true"> </HeaderStyle>
<ItemStyle ForeColor="DarkSlateBlue"> </ItemStyle>
<AlternatingItemStyle BackColor="Beige"> </AlternatingItemStyle>
<SelectedItemStyle BackColor="PaleGoldenRod" Font-Bold="true"> </SelectedItemStyle>
</asp:DataGrid>
</td>
<td valign="top">
<asp:Panel id="detailsPanel" runat="server" Visible="false">
<table border="0" cellspacing="0" cellpadding="2" width="100% style="font-family: verdana; font-size: 8pt">
<b>Titles:</b><br> <td colspan="2">
<asp:DataGrid id="titlesGrid" runat="server" AutoGenerateColumns="false" ShowFooter="true"
BackColor="White"
BorderWidth="1px" BorderStyle="Solid" BorderColor="Tan" CellPadding="2"
CellSpacing="0"
Font-Name="Verdana" Font-Size="8pt"
>
<Columns>
<asp:BoundColumn HeaderText="ID" DataField="title_id"> <HeaderStyle Width="100px">
</HeaderStyle>