Reader Level:
ARTICLE

In Depth ASP.NET using ADO.NET: Part III

Posted by John Hudai Godel Articles | C# Tutorials November 18, 2003
In this article we will discuss a number of ways to retrieve, show, and update data with ASP.NET forms using ADO.NET. Also, we will have a clear idea about the most common server controls in ASP.NET.
  • 0
  • 0
  • 19328

Displaying Database Using a Table Web Server Control

Using the Table Web server control it is possible to display database information. By using the Repeater, DataList, or DataGrid Web server controls, for many applications, it is easier to display data-bound information in a table. In the most common scenario to display data in a Table Web server control, each row of the table represents a data row from the data source, and each cell of the table displays a single field from a row. The Table control does not have a mechanism for automatically iterating through the rows in a data source unlike the list controls (Repeater, DataList, etc.). Therefore, we must do these ourselves.

To display database information in a Table Web server control:

  • Set the contents of a TableCell control to the data to be displayed. Most often we set the cell's Text property to an expression that extracts data from a data source. It is also possible to include controls in a TableCell control and bind the controls to a source of data.

The following example demonstrates one way to display data in a Table Web server control. The sample illustrates an event handler for a button. A DataSet (ADataSet1), DataCommand, and DataConnection have previously been created. The method loops through the DefaultView object of a dataset, creating a TableRow control for each row in the dataset. The code then creates a TableCell control in each table row and sets its Text property to the "author_id" field of the current data row. 

public void Button1_Click (object sender, System.EventArgs e)
{
// Set up a dataview on the dataset.
FillDataSet(aDataSet1);
DataBind();
DataView dv = aDataSet1.Tables[0].DefaultView;
// Create a row with one column for each row in the DataView
foreach (DataRowView dataRow in dv)
{
TableRow tRow =
new TableRow();
TableCell tCell =
new TableCell();
tCell.Text = dataRow.Row.ItemArray[0].ToString();
tRow.Cells.Add(tCell);
Table1.Rows.Add(tRow);
}
}

Controls that we insert dynamically to a Web Forms page do not automatically become part of the page's view state. Neither the controls nor their values are saved when a page performs a round trip to the server. We are therefore responsible for saving the state of any dynamically-generated controls whose values we want to preserve.

Binding a Button Server Control to a DataSource

Almost all user interface applications use a button, and its function is the same across every application. A button lets a user choose an option, tell the application that it is done with a task, or exit an application. The Button controls do not have a DataSource property, but we can certainly bind the attributes of the Button to fields from our data source. For example, as seen in following source code, we can bind the name of a product from our data source to the Text attribute of the Button. Here is a straightforward example of the binding a button server control to a DataSource:

<script runat="server" language="C#">
void Page_Load(Object sender, EventArgs e){
DataBind();
}
public string ButtonText() {
return "Click Here";
}
</script>
<
html>
<
body>
<
form runat="server" ID="Form2">
<
asp:label id="message" runat="server" Font-Size="10"/>
<
p>
<
asp:button id="Button1" runat="server" BackColor="maroon" ForeColor="white" BorderWidth="2" BorderStyle="Solid" BorderColor="Black" Font-Bold="true" Text='<%# ButtonText() %>' Width="200" />
</
p>
</
form>
</
body>
</
html>

Binding a TextBox Server Control to a DataSource

The TextBox server control is an input control which allows the user to enter text. By default, the TextMode property is set to SingleLine, which creates a text box with only one line. We also can set the property to MultiLine or Password. MultiLine produces a text box with more than one line. Password creates a single-line text box that masks the value entered by the user. The Columns property determines the display width of the text box. The display height is determined by the Rows property if the text box is a MultiLine text box.

We will find a sample declaration for a TextBox control in an .aspx file in the following example. The control is multiline and displays a maximum of 10 lines. If the browser supports fonts for controls, the text is formatted in Arial font. The method txtLogin_Changed is bound to the control's TextChanged method.

<asp:TextBox runat=server id="txtLogin" Text="" Font_Face="Arial" Font_Size="3" BackColor="lightblue"
TextMode="MultiLine" Height="10" OnTextChanged="txtLogin_Changed"/>

The TextBox control also lacks a DataSource property, but we can certainly bind the attributes of the TextBox to fields from our data source. For instance, we can use a TextBox to load the initial value and then save the edited value back to our database if our site has a membership and we allow the user to edit this information. To accomplish this we can use Control.DataBind method (This binds a data source to the invoked server control and to all of its child controls):

public virtual void DataBind();

This method can be used to bind data from a source to a server control, it is commonly used after retrieving data set through a database query. When called on a server control, this method resolves all data-binding expressions in the server control and in any of its child controls. When creating custom templated databound controls this method is commonly overridden.

Let's try to use a TextBox in an aspx file. Here is a small code piece for this example:

<asp:textbox id=FirstName style="Z-INDEX: 108; LEFT: 190px; POSITION: absolute; TOP: 100px" runat="Server" width="100" height="25"></asp:textbox>
<
asp:textbox id=LastName style="Z-INDEX: 109; LEFT: 190px; POSITION: absolute; TOP: 130px" runat="Server" width="100" height="25"></asp:textbox>

Just as the Windows Forms designer produces some of the most terrible code known to programmer, so does the WebForms designer. In fact, we went through and trimmed and cleaned the code for this example. After we are done with this subject, we will see more source code for this pattern. The HTML (XHTML) code for this example will be in an aspx file.

During this process the CustomerDataForm class is used and contains the page logic for such action as databinding by the application. Here is TextBox in C# code:

public class EmployeeForm : System.Web.UI.Page
{
protected System.Web.UI.WebControls.TextBox LastName;
protected System.Web.UI.WebControls.TextBox FirstName;
}
addr.FirstName = FirstName.Text;
addr.LastName = LastName.Text;
((ArrayList)
this.Session["Customers"]).Add( addr );
CustomerRepeater.DataSource = (ArrayList)
this.Session["Customers"];
CustomerRepeater.DataBind( );

Binding a DropDownList Web Server Control

To create a single selection drop-down list control, use the DropDownList control. By setting the BorderColor, BorderStyle, and BorderWidth properties we can control the appearance of the DropDownList control. Place a ListItem element for each entry between the opening and closing tags of the DropDownList control to specify the items that we want to appear in the DropDownList control.

The DropDownList control also supports data binding. First create a data source, such as a System.Collections.ArrayList object, that contains the items to display in the control to bind the control to a data source. Next, use the Control.DataBind method to bind the data source to the DropDownList control. Now, the DropDownList control will display the information from the data source. To programmatically determine the index of the item selected by the user from the DropDownList control, use the SelectedIndex property.

DropDownList control enables users to select from a single-selection drop-down list. The drop-down portion can contain any number of list items.

<!-- Data Fields -->
<
asp:DropDownList id="DropDownList1" runat="server" DataSource="<% databindingexpression %>"
DataTextField="DataSourceField" DataValueField="DataSourceField" AutoPostBack="True|False"
OnSelectedIndexChanged="OnSelectedIndexChangedMethod">
<
asp:ListItem value="value" selected="True|False">
Text
</asp:ListItem>
</
asp:DropDownList>
The following example demonstrates how to use the DropDownList control.
<html>
<
head>
<
script language="C#" runat="server">
void Button_Click(Object sender, EventArgs e)
{
Label1.Text = "You selected: " +
dropdownlist1.SelectedItem.Text + ".";
}
</script>
</
head>
<
body>
<
form runat="server" ID="Form2">
<
h3><font face="Verdana">DropDownList Example</font></h3>

Select an item from the list and click the submit button.

<p>
<
asp:DropDownList id="Dropdownlist2" runat="server">
<
asp:ListItem>Item 1</asp:ListItem>
<
asp:ListItem>Item 2</asp:ListItem>
<
asp:ListItem>Item 3</asp:ListItem>
<
asp:ListItem>Item 4</asp:ListItem>
</
asp:DropDownList>
<
br><br>
<
asp:Button id="Button1"
Text="Submit"
OnClick="Button_Click"
runat="server"/>
<
br><br>
<
asp:Label id="Label1" runat="server"/>
</
form>
</
body>
</
html

In the last article we learned to use the data binding method with DropDownList server control. Now we will use another approach to this issue; Data Binding Expression. Data binding expressions create bindings between any property on an ASP.NET page, including server control properties, and a data source when the DataBind method is called on the page. We can include data binding expressions on the value side of an attribute/value pair in the opening tag of a server control or anywhere in the page.

<tagprefix:tagname property="<%# databinding expression %> runat="server" />

Or

literal text

<%# databinding expression %>

Regardless of where we place them, all data binding expressions, must be contained in <%# and %> characters. ASP.NET supports a hierarchical data binding model that supports associative bindings between server control properties and parent data sources. Any server control property can be data bound against any public field or property on the containing page or on the server control's immediate naming container.

The ASP.NET supplies a static method, called DataBinder.Eval that evaluates late-bound data binding expressions and optionally formats the result as a string. This method eliminates much of the explicit casting we must do to coerce values to the data type we desire. For example; in the following code piece, an integer is displayed as a currency string. We must first cast the type of the data row in order to retrieve the data field, IntegerValue, with the standard ASP.NET data binding syntax. Next, this is passed as an argument to the String.Format method:

<%# String.Format("{0:c}", ((DataRowView)Container.DataItem)["IntegerValue"]) %>  

Contrast this syntax with that of DataBinder.Eval, which has only three arguments: the naming container for the data item, the data field name, and a format string. In a templated list like DataList Class, DataGrid Class, or Repeater Class, the naming container is always Container.DataItem.

<%# DataBinder.Eval(Container.DataItem, "IntegerValue", "{0:c}") %>

The format string argument is optional. DataBinder.Eval returns a value of type object, if it is omitted, as in the following example:

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

When data binding controls within a templated list DataBinder.Eval is particularly useful, since often both the data row and the data field must be cast. The following source code demonstrates how we can data bind against properties in an ASP.NET server control. The Label Web server control data binds against the selected item in the list when a user selects a state from the DropDownList Web server control.

<html>
<
head>
<
script language="C#" runat="server">
void SubmitBtn_Click(Object sender, EventArgs e)
{
// Rather than explictly pull out the variable from the "StateList"
// and then manipulate a label control, just call "Page.DataBind".
/ This will evaluate any <%# %> expressions within the page.
Page.DataBind();
}
</script>
</
head>
<
body>
<
h3><font face="Verdana">Data binding to a property of another server control</font></h3>
<
form runat="server" ID="Form2">
<
asp:DropDownList id="StateList" runat="server">
<
asp:ListItem>CA</asp:ListItem>
<
asp:ListItem>IN</asp:ListItem>
<
asp:ListItem>KS</asp:ListItem>
<
asp:ListItem>MD</asp:ListItem>
<
asp:ListItem>MI</asp:ListItem>
<
asp:ListItem>OR</asp:ListItem>
<
asp:ListItem>TN</asp:ListItem>
<
asp:ListItem>UT</asp:ListItem>
</
asp:DropDownList>
<asp:button Text="Submit" OnClick="SubmitBtn_Click" runat="server" ID="Button1"/>
<p>
Selected State: <asp:label text='<%# StateList.SelectedItem.Text %>' runat="server" ID="Label1"/>
</form>
</
body>
</
html

Binding a RadioButtonList to a DataSource

The RadioButtonList control's programmatic functionality is almost identical to the CheckBoxList, but in fact it is different. When rendered, each item from the RadioButtonList.DataSource will be part of a group of RadioButton controls and we can only select one RadioButton out of the group at one time. The RadioButtonList is part of the List controls suite, therefore its behavior is the same as the proceeding List controls as far as how we enable automatic post back (AuoPostBack), how we tell if an item is checked (ListItem.Selected), and how to handle the SelectedIndexChanged on the post back. Therefore, we will be continuing with the code example now. This source code piece demonstrates how to use all the proceeding attributes and events to determine item selection.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server" language="C#" >
void
Page_Load(Object sender, EventArgs e)
{
if (! IsPostBack)
{
rbl_DataBind();
}
}
void rbl_DataBind()
{
SqlConnection SqlCon =
new SqlConnection("server=localhost;uid=sa;pwd=;database=northwind");
SqlCommand SqlCmd =
new SqlCommand("SELECT TOP 21 ProductName, ProductID FROM Products", SqlCon);
SqlCon.Open();
rbl.DataSource = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
rbl.DataTextField = "ProductName";
rbl.DataValueField = "ProductID";
rbl.DataBind();
}
void RadioButtonList_SelectedIndexChanged(Object sender, EventArgs e)
{
StringBuilder sb =
new StringBuilder("<b><u>Item Selected</u></b><p>");
int i;
for(i = 0; i < rbl.Items.Count; i++)
{
if(rbl.Items[i].Selected)
{
sb.Append(i);
sb.Append(" - ");
sb.Append(rbl.Items[i].Text);
sb.Append("<br>");
}
}
CheckBoxList.Text = sb.ToString();
}
<asp:Label width="100%" runat="server" text="<center>Pick Products</center>" BackColor="white"
ForeColor="Navy"  Font-Bold="true" Font-Size="13" ID="Label1"/>
<
asp:RadioButtonList  runat="server" id="rbl" CellPadding="4" CellSpacing="0" RepeatLayout="table"
RepeatColumns="3" RepeatDirection="Vertical" AutoPostBack="true" OnSelectedIndexChanged="RadioButtonList_SelectedIndexChanged" font-size="10" BackColor="white"
ForeColor="Navy" Font-Bold="true" width="100%" BorderWidth="1" BorderColor="Navy" />
<
p>
<
asp:label
runat="server" id="lCheckBoxList" Font-Bold="false" Font-Size="8" ForeColor="Navy"/>
</
form>
</
body>
</
html>

We will get a page with three columns of RadioButton controls and a product name next to each when we execute this source code. The page is posted back to the server and the name of the product we selected is printed out to the screen when we select one. We can only check one product at a time when using the RadioButtonList control, unlike the CheckBoxList (remember from last article).

continue article

COMMENT USING

Trending up