Microsoft ASP .NET Professional Projects - Premier Press
.pdft = dv.Table
Dim r As DataRow
Dim c As DataColumn
Dim _cell as TableCell
Dim _row as TableRow
table = New Table()
Controls.Add( table )
For Each r in t.Rows
For Each c in t.Columns
_row = new TableRow() '<tr> 'Label
_cell = new TableCell() '<td> _cell.Controls.Add(new LiteralControl(c.ToString)) _row.Cells.Add(_cell) '</td>
'Value
_cell = new TableCell() '<td> _cell.Controls.Add(new LiteralControl(r(c).ToString)) _row.Cells.Add(_cell) '</td>
Table.Rows.Add(_row) '</tr>
Next c
Next r
End Sub </script>
<form runat = "server">
<h2> DataTable Columns & Rows </h2>
<body style="background-color='beige'; font-family='verdana'; font-size='10pt'">
<asp:Table id="Table" Font -Name="Verdana" Font-Size="8pt" CellPadding=5 CellSpacing=0 BorderColor="black"
BorderWidth="1" Gridlines="Both" runat="server"/> </body>
</form>
</html>
The Groups table in the DataSet is populated with the SQL query "select * from groups where code_value = 700 ". A DataView is created on the Groups table as follows:
dv = new DataView(ds.Tables("groups"))
This DataView is assigned to a DataTable. I then have two loops. The outer loop iterates the row collection of the DataTable and the inner loop iterates the column collection. The basic loop is as follows (I have removed all formatting elements like Table, TableRow, and TableCell, so that I can explain better):
Dim t As DataTable
t = dv.Table
Dim r As DataRow
Dim c As DataColumn
Dim cell As TableCell
Dim row As DataRow
For Each r in t.Rows
For Each c in t.Columns
response.write(c.ToString)
response.write(r(c).ToString)
Next c
Next r
I am dynamically creating the Table (<table> tag), the TableRow (<tr>), and the TableCell (<td>). Thus an opening Table <table> tag is created as follows:
table = New Table() 'supplies <table> tag
Controls.Add( table )
I want to display the column name as a non-editable label. I use the following syntax:
_cell = new TableCell() 'Supplies <td> tag
_cell.Controls.Add(new LiteralControl(c.ToString))
_row.Cells.Add(_cell) 'supplies </td> tag
The TableCell() method takes care of supplying the <td> and </td> tags. c is the field name and it is converted to a string using the ToString method. LiteralControl adds a label. Thus the field name is displayed as a label.
I want to display the value of each field in an editable textbox. This is achieved by the following code:
_cell = new TableCell() '<td>
Dim Box As New TextBox
Box.Text = r(c).ToString
_cell.Controls.Add(box)
_row.Cells.Add(_cell) '</td>
The new TableCell() and the Add(_Cell) provide the opening and closing table data tags ( i.e. <td> and </td>). I assign the column value to a textbox and add it to the controls collection.
The DataReader
The DataSet provides a disconnect means of access to a datasource. At times we might want a "quick and dirty" means of accessing a datasource. In such cases, a DataReader can be used.
A DataReader supplies a read-only, forward-only data stream and like the legacy ADO recordset, stays connected to the datasource. It can be used to return a recordset or execute action queries (like update, insert, and delete) which do not return any data. It holds one row in memory at a time as opposed to a DataSet, which holds a complete table in memory. Using a DataSet can be an issue when large tables are loaded in memory. If there are multiple users accessing the same machine at the same time, this can lead to a serious memory drain. In such situations a DataReader should be used instead of the DataSet.
A DataReader provides a simple method of iterating through the query. In the following example (see Figure 3.5), I populate a DropDownList by iterating through the Groups table using the DataReader.
Figure 3.5: DataReader.
DataReader.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
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)
if NOT (isPostBack)
FillList
End if
End Sub
Sub FillList()
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User
ID=sa;"
myConnection = New OleDbConnection(ConnStr) Dim dbRead AS OleDbDataReader
Dim dbComm AS OleDbCommand
SQL = "Select * from groups ORDER BY code_value" dbComm = New OleDbCommand(SQL,myConnection) myconnection.Open() dbRead=dbComm.ExecuteReader()
While dbRead.Read()
ddList.items.add(New ListItem(dbRead.Item("code_display"))) End While
End Sub </script> <body>
<h3><font face="Verdana">DropDownList & Reader </font></h3> <form runat=server>
<asp:DropDownList id="ddlist" runat="server" DataTextField = "code_display"/>
</form>
</body>
</html>
The DataReader uses the OleDbCommand to populate the command object. After the execute method is run, we can iterate dbRead (the DataReader) to populate the DropDownList.
Data Relation
As mentioned earlier in this chapter, you can set up relationships between tables in the DataSet. These relationships are akin to the primary-foreign key relationships, which exist in a database. The advantage of defining a relationship is that you can now navigate the relationship instead of navigating in a sequential manner as done previously in ADO. In this style of navigation, a master record is first selected, and then based on the relation key the row in the secondary table is accessed. Processing in the secondary table continues until all secondary records with the same primary key are processed. After this, the control moves back to the master table and another row is processed in a similar manner. Let me explain this with an example.
DataRelation.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
Sub readDs(Sender As Object, E As EventArgs)
Dim dv As DataView
Dim i As integer
Dim myConnection As OleDbConnection
Dim myCommand As OleDbDataAdapter
Dim ds As New DataSet
Dim ConnStr As String
Dim SQL As String
ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=pubs;User
ID=sa;"
myConnection = New OleDbConnection(ConnStr)
'Populate authors table
SQL = "select * from authors "
myCommand = New OleDbDataAdapter(SQL, myConnection) myCommand.Fill(ds, "authors")
'Populate TitlesAuthor
SQL = "select * from titleAuthor"
myCommand = New OleDbDataAdapter(SQL, myConnection) myCommand.Fill(ds, "titleauthor")
'Define a relation based on the au_id
'dc1 is the primary, dc2 is the secondary table Dim dc1 As DataColumn
Dim dc2 As DataColumn
dc1 = ds.Tables("Authors").Columns("au_id") dc2 = ds.Tables("titleauthor").Columns("au_id") Dim dr As DataRelation
dr = New DataRelation("vRelation", dc1, dc2) ds.Relations.Add(dr)
'Loop thru the relation Dim child() As datarow Dim r As datarow
for each r in ds.Tables("Authors").Rows
child = r.GetChildRows(ds.Relations("vRelation")) for i = 0 to UBound(child)
Response.Write(child(i)("au_id").ToString ) next
next End Sub
</script>
<body>
<h3><font face="Verdana">DataView</font></h3>
<form runat=server>
<asp:button text="Read Relation" Onclick="readDs" runat=server/>
</form>
</body>
</html>
In this example, I populate the DataSet ds with two tables from the Pubs database. These are the author's table (the primary table) and the title Author table (the child table). I then define a relationship (vRelation) between the two tables, based on the au_id key in both tables. I then loop through all the records in the relationship. The external loop iterates through the author's table, one row at a time. It picks up a row and then loops through all child records in the titleauthor table, which have the same au_id. This is done by the internal loop.
For example, you can use the select method of the DataSet to filter records:
Dim child() As DataRow = workTable.Select("au_id like 'A%'").
Like a database, the DataSet supports unique and cascading constraints. You can enclose your code within a BeginEdit and EndEdit block. This in effect defers constraint validation until the EndEdit method is called. Three values are stored for each row. These are the original, current, and proposed values. The proposed value is the intermediate value between the BeginEdit and EndEdit block. The currentvalue becomes the originalvalue once the AcceptChanges method is called. Finally, the RejectChanges method drops changes to the DataSet.
Summary
This chapter looked at working with ADO.NET. This new data access technology involves working with DataSets and Managed Providers. I introduced data binding with ADO.NET, and showed how action queries could be performed using ADO.NET. DataViews and DataRelations were explained in detail. The next chapter extends this knowledge, and shows you how to bind controls using ADO.NET.
Chapter 4: Data Binding
Controls can be bound to a datasource, much like Visual Basic Bound controls. Thus controls like the DropDownList, CheckBoxList, and RadioButtonList can be bound to a DataSet. ASP.NET has certain controls that render HTML based on repetitive data. These controls are collectively referred to as "List Bound Controls." The controls included in this category are the DataRepeater, the DataList, and the DataGrid. Developers can apply various styles and set properties in an XSL template-like style. There is a header template, a footer template, and an item template. As the names imply, the header and footer templates control the header and footer sections respectively. The item template is used to control repetitive data. In a DataGrid, the item template is applied to columns. The item template can be further fine-tuned by alternating it with a separator template. You can use these templates to apply different colors to odd and even rows. The DataRepeater does not have editing capabilities, though both the DataList and the DataGrid do. In addition, the DataGrid has advanced paging and sorting capabilities.
Binding Controls
You can bind controls like the CheckBoxList, the RadioButtonList, the ListBox, and the DropDownList to DataSets. Binding is as simple as specifying the datasource and binding the control using the DataBind method. In the following example, I show you how to bind a ListBox, a DropDownList, and a set of RadioButtons (a RadioButtonList) to the data from the Groups table. The result appears in Figure 4.1.
Figure 4.1: Binding "selection" controls.
DataBind.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
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)
bind end if End Sub Sub Bind()
'DataSetCommand
SQL = "select * from Groups"
myCommand = New OleDbDataAdapter(SQL, myConnection) 'use Fill method of DataSetCommand to populate dataset myCommand.Fill(ds, "Groups") list1.DataSource=ds.Tables("Groups").DefaultView list1.DataBind() rb.DataSource=ds.Tables("Groups").DefaultView rb.DataBind() dl.DataSource=ds.Tables("Groups").DefaultView dl.DataBind()
End Sub
Sub SubmitBtn_Click(sender As Object, e As EventArgs)
Dim s As string
s = "—-Selected List Item :" + list1.SelectedItem.Text
s = s + "——Selected DropDownList : " + dl.SelectedItem.Text s = s + "——Selected RadioButton : " + rb.SelectedItem.Text Label1.Text = s
End Sub </script> <body>
<h3><font face="Verdana">Binding Controls </font></h3> <form runat=server>
<asp:ListBox id="List1" DataTextField = "code_display" DataValueField = "code_value" runat="server"/>
<hr>
<ASP:radiobuttonList
repeatcolumns="4" repeatdirection="horizontal" repeatlayout="table"
id="rb" datatextfield="code_display" DataValueField="code_value" runat="server"/>
<hr>
<asp:DropDownList id="dl" DataTextField = "code_display" DataValueField = "code_value" runat="server"/>
<hr>
<asp:button Text="Submit" OnClick="SubmitBtn_Click" runat=server/>
<asp:Label id=Label1 font-name="Verdana" font-size="10pt" runat="server" />
</form>
</body>
</html>
Binding involves specifying the DataSource and then using the DataBind method to perform the actual bind, as in the following example:
list1.DataSource=ds.Tables("Groups").DefaultView
list1.DataBind()
CheckBoxLists and RadioButtonLists have a RepeatColumns and a RepeatDirection property. RepeatColumns can be used to assign the number of columns repeated in the direction specified by the RepeatDirection property. In the preceding example I have set the RepeatColumns property to 4 and the
RepeatDirection property to be horizontal.
In the SubmitBtn_Click event, I use the SelectedItem.text property of each of these selection controls (the ListBox, the DropDownList, and the RadioButtonList) to display the item selected by the user.
Each of these controls has an AutoPostBack property, which can be set as true or false, such as in the following:
<asp:ListBox id="List1" DataTextField = "code_display" DataValueField = "code_value" runat="server" AutoPostBack = 'true" />
Setting this property to true will cause a post back to the server each time that a value in the control is changed. The "if NOT (isPostBack)" statement ensures that the controls are only bound once at the time of page_load.
The DataRepeater
The DataRepeater is used to render HTML for repeating data. It is completely template driven and the following templates can be set for it:
§ItemTemplate
§AlternatingItemTemplate
§SeparatorTemplate
§HeaderTemplate
§FooterTemplate