chapter 10 - data binding with list controls

8/9/2019 Chapter 10 - Data Binding with List Controls

1/61

DataBinding with ListControls

Main Menu 1 of 61



2/61


Main Menu 2 of 61

Scope

Introduction to ADO.NET

Binding Data to Controls

List Controls

List Control Base Class

Look-Less Controls

DataList Control

Repeater Control

Table Control

Fetching Data from SQL Stored Procedures

Fetching XML-based Data


3/61


Main Menu 3 of 61

ADO.NET is designed to be data-centric, non-database-

centric, focused on handling disconnected data sets, and

tightly integrated with XML.

It enables better data sharing capabilities betweencomponents and tiers in an application.

A .NET data provider is used for connecting to a database,

executing commands, and retrieving results.

Those results are either processed directly, or placed in anADO.NET DataSet in order to be exposed to the user.



4/61


Main Menu 4 of 61

The ADO.NET objects are:

Connection Object

Command Object

DataReader Object

DataAdapter Object

DataSet Object



5/61


Main Menu 5 of 61

The .NET Data Provider Connection object is used

to set a connection to a data source.

Each .NET Provider has its own Connection object.

The SQL Server .NET Provider includes a

SqlConnection Object, while the OLE DB .NET

Provider includes an OleDbConnection object.

The ADO.NET Connection object accepts one stringparameter called ConnectionString for assigning the

data source connection details.

Connection Object


6/61


Main Menu 6 of 61

Ideally the string for ConnectionString should be

written as follows:

Provider=SQLOLEDB.1; Data Source=MySQLServer; Initial

Catalog=NORTHWIND; uid=sa; pwd=;

For the SqlConnection object this string is like a

typical OLE DB Provider string, except that you omit

the Provider parameter.

Imports System.Data.SqlClient

Dim SqlConn As SqlConnection = New SqlConnection ("

Data Source=tilesd; Initial Catalog=Northwind; uid=SA; pwd= ;")

Connection Object


7/61


Main Menu 7 of 61

The Command object allows executing a SQL

statement or stored procedure in a data source.

On executing the Command object, either a row set

containing data is returned that is passed to anotherobject such as DataReader/ DataAdapter or it returns

a count of number of records affected for queries

that do not return a row set.

There are two command objects, one for OLEDB

and other one for SQL as given in next slide:

Command Object


8/61


Main Menu 8 of 61

SqlCommand: Uses Tabular Data Services with MS SQLServer

OleDbCommand: Used with OLE-DB provider or OLE-

DB/ODBC driver

The syntax of creating an instance of Command

object and how to execute it is given below:

Dim myCommand As New SqlCommand (, )myCommand.Connection.Open ()

myCommand.ExecuteNonQuery ()

Command Object


9/61


Main Menu 9 of 61

The DataReader object is squarely aimed at web

developers. It provides a data, read-only, forward-

only data stream that is designed to provide quick

access to data. The DataReader object will retrieve multiple

resultset . It will also return schema information for

the underlying data source.

Each .NET Data Provider includes its own version

of the DataReader object.

DataReader Object


10/61


Main Menu 10 of 61

Syntax of making an instance of SqlDataReader

object:

SqlCommand sqlCmd = New SqlCommand (, )

Connection.Open ()

Dim sqlReader As SqlDataReader

SqlReader = sqlCmd.ExecuteReader ()

DataReader Object


11/61


Main Menu 11 of 61

The DataAdapter object is a very important member of the

Managed Data Provider because it provides the link for

exchanging the data between a data source and a data set.

ADO.NET Provides the DataSet object for caching andmanipulating data.

Data adapters can move any kind of data between a data

source and a DataSet object as long as it is supported by a

.NET Data Provider.

The DataAdapter object is used to retrieve data from a data

source and populate the DataTable objects in a DataSet.

DataAdapter Object


12/61


Main Menu 12 of 61

The main advantage of DataSet is that a DataSet object can

hold more than one table from the same source, as well as the

relationship between them.

Each table in a DataSet is a DataTable object within theDataTableCollection.

Each DataTable object contains a collection of DataRow

objects (within the DataRowCollection) and a collection of

DataColumn objects (within the DataColumnCollection).

DataSet Object


13/61


Main Menu 13 of 61

VB.NET web forms provides a variety of controls

which can be bound to the data so that data can be

fetched from the database, as every control is

actually a representation of a base class inASP.NET.

There is a hierarchy of each of the controls.

All the web controls inherit fromSystem.Web.UI.WebControls which is the base

class of all the data binding controls.

Binding data to Controls


14/61


Main Menu 14 of 61

List controls are used in conjunction with ADO.NET.

They provide data binding facilities, which allow us to

display data from a particular data store via a control.

We have the facility to bind to various sources such as

arrays and Datasets in ADO.NET.

We can also use templates to change the layout of the

control and display a fully featured list output withediting ability.

List Controls


15/61


Main Menu 15 of 61

Types of List Controls:

DropDownList Control

ListBox Control

CheckBoxList Control

RadioButtonList Control

DataGrid Control

DataList Control

Repeater Control

List Controls


16/61


Main Menu 16 of 61

The System.Web.UI.WebControls.ListControl class

serves as an abstract base class for all the control

mentioned above and defines properties, methods

and events common to these controls. The information in this section applies to the

following list controls i.e.

ListBox Control


CheckBoxList Control

RadioButtonList Control

List Control Base class


17/61


Main Menu 17 of 61

You can add items to a list Web server control in

following ways:

Add static items at design time.

Add items programmatically at run time.

Add items using data binding.

Adding items


18/61


Main Menu 18 of 61

Adding static items at design time

In Design view, select the list control.

In the Properties window, click the ellipsis button for the

Items property to open the Collection Editor dialog box. Click Add to add a new item.

To set the properties for the new item, select it, and then in

the ListItem Properties list, enter values for its Text, Value,

and Selected properties. Keep on repeating Steps 2 and 3 for each item to add, and

then click on OK to close the Collection Edit or Dialog box.

Adding items

d h C l


19/61


Main Menu 19 of 61

Adding items programmatically

Drag a list control and set its Text and Value properties.

Call the Add method of the control's Items collection and

pass it to the new object. The following example shows how to add ListItem objects to

a ListBox control on click of a button,

Public Sub Button1_Click(ByVal sender As Object, ByValue As

System.EventArgs) Handles Button1.ClickListBox1.Items.Add(New ListItem("Delhi", "DEL"))

ListBox1.Items.Add(New ListItem("Mumbai", "Mum"))

End Sub

Adding items

D Bi di i h Li C l


20/61


Main Menu 20 of 61

Populating from a database

You can use a list Web server control to display items that

are fetched from a data source.

Each item in the control corresponds to an item (a row) in thedata source.

The control can display one field from the source and you can

also optionally use a second field as the item value.

Adding items

D Bi di i h Li C l


21/61


Main Menu 21 of 61

Click on the Data tab in the Toolbox.

Double Click on the SQLDataAdapter Control.

This starts the SQLDataAdapter Configuration Wizard.

Follow the instructions to create a connection and specify aQuery.

Establish a connection. Select SQL Statement radio button.

Click on the Query builder and build a query.

Click on Next button. Click on the Finish Button. Now Right click on the SqlDataAdapter1 and select the

Generate DataSet option.

Connecting to the database

D Bi di i h Li C l


22/61


Main Menu 22 of 61

Give a name to the dataset say dataset1.Click on OKbutton.

In the Page_Load event write the following Code:SQLDataAdapter1.Fill(DataSet1)

DataboundControlname.DataBind()

The above steps will create the following items in the designview.

SQLDataAdapter

SQLConnection

DataSet

All the above steps will be repeated for all the controls,which have to be bound to the database. We will call these asDataConnection Steps.

Connecting to the database

D t Bi di ith Li tC t l


23/61


Main Menu 23 of 61

Single-selection list control:

One of the most common tasks in working with a list Web

server control is to determine what item or items user has

selected. The procedure and the steps vary depending on whether the

list control allows single or multiple selections.

Use the following procedure when working with the

DropDownList control, the RadioButtonList control, and asingle-selection ListBox control

Determining the Selection



24/61


Main Menu 24 of 61

Use one of the following methods:

To get the index value of the selected item, read the value of

the SelectedIndex property.

The index is zero-based. If nothing has been selected, thevalue of the property is -1.

To get the contents of the selected item, get the control's

SelectedItem property.

This property returns an object of type ListItem. You can get the contents of the selected item by getting the

Text or Value property of the object.




25/61


Main Menu 25 of 61

Multi-selection list control

If the list control supports multiple selections, you must loop

through the control and check for items selected one by one.

Loop through the control's Items collection and test theSelected property of every individual item.




26/61


Main Menu 26 of 61

Normally, user selects items in a list Web server

control to indicate his choice. However, a web

developer might want to preselect items or select

items at run time (programmatically) based on somecondition.

Setting the Selection



27/61


Main Menu 27 of 61

Design Time:

To set the selection in a list Web server control at design time

In Design view, select the control. In the Properties window,

click the ellipsis button for the Items property to open theCollection Editor Dialog box.

From the Members list, choose the member to be selected,

and then set its Selected property to true.

If the control is set to allow multiple selections, repeat Step 2for each item to select, and then choose OK to close the

Collection Editor Dialog box.




28/61


Main Menu 28 of 61

Programmatically:Single Selection

To set a single selection in a list Web server controlprogrammatically.

Set the control's SelectedIndex property to the index value ofthe item to select. The index is zero-based. To set noselection, set SelectedIndex to -1.

ListBox1.SelectedIndex = 2

This code snippet selects the third item in the list box.




29/61


Main Menu 29 of 61

Multiple Selections

To set multiple selections in a list control programmatically

Loop through the control's Items collection and set the

Selected property of every individual item. Please keep in mind that you can only select multiple items if

the control's SelectionMode property is set to Multiple.




30/61


Main Menu 30 of 61

SelectedIndexChanged:

A ListControl raises this event when user selects an item.

By default, this event does not cause the page to be posted tothe server, but you can cause the control to force animmediate post by setting the AutoPostBack property to true.

Please note that the ability of a DropDownList control to postto the server when it is checked requires that the browsersupport ECMAScript (JScript, JavaScript) and that scripting

be enabled on the user's browser.

ListControl Events



31/61


Main Menu 31 of 61

It represents a control that allows the user to select a single

item from a drop-down list.

The DropDownList control is very similar to the ListBox

Web server control. However it shows only the selected item

in a box, along with a drop-down button the list of items

remains hidden until users click the drop-down button.

You can control the appearance of the DropDownList control

by setting the BorderColor, BorderStyle, and BorderWidth

properties.




32/61


Main Menu 32 of 61

Binding Data to the Control

You can use a DropDownList Web server control to list

options that are read from a data source.

Each item in the DropDownList control corresponds to anitem i.e., a rowin the data source.

The control displays one field from the source.

Optionally, you can bind the control to a second field to set

the value of an item.




33/61


Main Menu 33 of 61

This control displays a list of items that allows single or

multiple item selection.

The ListBox Web server control allows users to select one or

more items from a predefined list of item displayed in the list

box.

It differs from a DropDownList control as it can display

multiple items at a time.

ListBox Control



34/61


Main Menu 34 of 61


You can use a ListBox Web server control to list options that

are read from a data source.

Each item in this control corresponds to an item i.e. a rowin the data source.

The control displays one field from the source.

Optionally, you can bind the control to a second field to set

the value of an item.

ListBox Control



35/61


Main Menu 35 of 61

Provides a way for users to switch between true/false, yes/no,or on/off options.

You add individual CheckBox controls to a page and workwith them singly.

Each type of control has advantages. By using individualCheckBox controls, you get more control over the layout ofthe check boxes on the page.

The CheckBoxList control is a better choice if you want to

create a series of check boxes out of data in a database.

CheckBox List Control



36/61


Main Menu 36 of 61

RadioButton Control and RadioButtonList Web server

control allow users to select one item from a short, predefined

list.

Both controls allow users to select from a small set of

mutually-exclusive, pre-defined choices. The controls allow

you to define any number of radio buttons with labels and to

arrange them horizontally or vertically.

If you want to present users with a longer list of options or a

list that might vary in length at run time, use a ListBox or

DropDownList Web server control.

RadioBox List Control



37/61


Main Menu 37 of 61

RadioButton Control versus RadioButtonList Control You add individual RadioButton controls to a page and work

with them singly.

Typically, you group two or more individual buttons

together. In contrast, the RadioButtonList control is a single control

that acts as a parent control for a collection of radio buttonlist items.

Individual RadioButton controls give you more control over

the layout of the radio button group. The RadioButtonList control does not permit you to insert

text between the buttons, but is far easier to use if you wantto bind the buttons to a data source.




38/61


Main Menu 38 of 61

It is also slightly easier to write code that checks which

button has been selected.


You can also bind a RadioButtonList control to a data source. In that case, the data source dynamically generates radio

buttons (list items) for each record in the data source.




39/61


Main Menu 39 of 61

DataGrid displays information stored in the database in a grid

form, such as table containing rows and columns.

This control is used for displaying Full-featured list output

with editing and Paged reporting.

Has a grid appearance by default.

Can customize look of grid extensively.

Has an auto-format option.

Can specify output using bound columns, columns of buttonsor hyperlinks, and custom columns created using templates.

DataGrid Control



40/61


Main Menu 40 of 61

Has no separator template. However, the grid renders in a

table, and you can specify table border size and color.

By default, the DataGrid displays data in read-only mode, but

the DataGrid is also capable of automatically displaying the

data in editable controls at run-time.

Supports WYSIWYG template editing.

Supports custom functionality that can be applied to items

(for example, specifying an "Add to shopping cart" button for

each item).

Supports single selection. Multiple selection requires custom

code.

DataGrid Control



41/61


Main Menu 41 of 61

DataSource Property:

The DataGrid Web server control is bound to a data source

via this property otherwise it is not rendered (displayed) on

the page. Typical data sources for the DataGrid are the DataSet and the

OleDbDataReader.

You can use data sources available from the Toolbox, such as

the DataSet or DataView class. You can also bind to data sources through code, such as

OleDbDataReader or an array.

Properties



42/61


Main Menu 42 of 61

Properties

DataKeyField Property:

As part of the data binding, you can specify a DataKeyFieldproperty.

This property allows you to specify information that uniquelyidentifies each item in the grid.

The information does not have to be part of the informationdisplayed in the grid.

It can consist of the name of a field in the data source (such

as a primary key). You can use this information to update a specific item in the

data source.



43/61

g w

Main Menu 43 of 61

DataBind (inherited from BaseDataList):

This method binds the control and all its child controls to the

data source specified in the DataSource property of the

control. When the Web page runs, the code must call the control's

DataBind method to load the grid with data (this is true for all

the data bound controls).

This method is called again if the data changes (for example,in an event handler) to refresh the grid.

Methods



44/61

g

Main Menu 44 of 61

If you right click on the DataGrid Control there is a Property

Builder option, you will find various tabs in it such as

General, Columns, Paging, Format and Borders.

There is a Format tab in the Property Builder dialog box

which controls the layout of the rows of the DataGrid.

Its settings specify the color, font, and alignment etc. of the

rows.

The actual control, text, and data contents of the row are

specified in the Columns tab of the Property Builder.

Property Builder



45/61

g

Main Menu 45 of 61

Sorting

The DataGrid Web server control provides a way for you to

add sorting to the grid.

The grid does not directly support sorting. It provides a way for you to add sort options to the grid, such

as hyperlinks in the column heads that users click to sort.

The grid then notifies you when a user has requested sorting.

The DataGrid control provides two levels of sorting support: Default sorting:

Custom sorting:

Property Builder



46/61

g

Main Menu 46 of 61

Let us understand this control further with help of an example.

Steps:

Follow the Database Connection steps.

In the query, write the following SQL statement Select Cityfrom Emp .

Drag and drop a DataGrid control on the web form and set the

following

properties:Property Value

ID MyDataGrid

DataSource DataSet11

DataMember Employee

DataKeyField Emp_id




47/61

g

Main Menu 47 of 61

Now right click on the DataGrid control and go to theProperty builder. Click on the Columns tab.

UnCheck the create columns at run time checkbox .

Now select the columns in the order you want and set the

header text to a descriptive name for the column. Click on the Button Column and select Delete.

Close the property builder.

Write the following code in the Page_Load event.SqlDataAdapter1.Fill(DataSet11)

MyDatagrid.DataBind() Go to the code view and create a procedure named

BindGrid.




48/61

g

Main Menu 48 of 61

Write the code in the DeleteCommand event of the DataGridcontrol.

Save and run the project o get the following output.




49/61

g

Main Menu 49 of 61

Steps:

Create a stored procedure in the SQL Server.

CREATE PROCEDURE dbo.Fetch_authors (@city varchar

(20) ='oakland')

AS select au_fname,au_lname from authors wherecity=@city

SET NOCOUNT ON

RETURN

In your web form drag and drop a SQLConnection objectand set the Connection String and select an existing

connection or build a new one to Pubs database.

Using stored procedure



50/61

Main Menu 50 of 61

Design the web form as shown below:




51/61

Main Menu 51 of 61

In the DropDownList Box set the ID as CityList.

Add the following list in the Items collection at design

time.

Ann Arbor

Berkeley

Corvallis

Covelo

Gary Lawrence

Menlo Park

Nashville

Oakland Palo Alto

Rockville

Salt Lake City

San Francisco San Jose

Vacaville

Walnut Creek




52/61

Main Menu 52 of 61

Write the following code in the click of the button.

Dim DS As DataSet

Dim SqlAdapter1 As SqlDataAdapter

SqlAdapter1 = New SqlDataAdapter("fetch_authors",

SqlConnection1)

SqlAdapter1.SelectCommand.CommandType =

CommandType.StoredProcedure

SqlAdapter1.SelectCommand.Parameters.Add(NewSqlParameter("@City",

SqlDbType.VarChar, 20))




53/61

Main Menu 53 of 61

SqlAdapter1.SelectCommand.Parameters("@city").Value

=

CityList.SelectedItem.Value

DS = New DataSet()

SqlAdapter1.Fill(DS, "fetch_authors")DataGrid1.DataSource =

DS.Tables("fetch_authors").DefaultView

DataGrid1.DataBind()

Save and run the project. Select a city to display the authors

names corresponding to that particular city in the Authors

table.




54/61

Main Menu 54 of 61

The power and utility of the DataSet object lies in its

ability to abstract data in a way that is independent

of the actual data source be it SQLServer or any

other RDBMS. The DataSet supports the following methods for

accessing and manipulating the database.

ReadXml

ReadXmlSchema

WriteXml

WriteXmlSchema




55/61

Main Menu 55 of 61

FileStream Class:

This class is used for reading and writing files on a

file system, as well as other file-related operating

system handles (including pipes, standard input,standard output, and so on).

FileStream buffers input and output for better

performance.




56/61

Main Menu 56 of 61

Opening a file:

Synchronously : Methods used :-

Read

write

Asynchronously: Methods used:-

BinaryRead

BinaryWrite

File Stream defaults to opening files synchronously,but provides a constructor to open files

asynchronously.




57/61

Main Menu 57 of 61

StreamReader class

StreamReader is designed for character input in a

particular encoding. Used for reading lines of

information from a standard text file. This defaultsto UTF-8 encoding unless specified otherwise,

instead of defaulting to the ANSI code page for the

current system. UTF-8 handles Unicode characters

correctly and provides consistent results on localizedversions of the operating system.




58/61

Main Menu 58 of 61

ListControl is the base class for most of the list controls.

Items can be added to the list at design time, run time or

through the database.

DropDownList Control is used to display one item at a timeout of the list which can is displayed on clicking the drop

button.

ListBox Control is used to display a number of items at a

time.

CheckBoxList Control is used for fetching the database

results and generates the checkboxes dynamically.

Summary



59/61

Main Menu 59 of 61

RadioButtonList Control is used for fetching the database

results and generates the RadioButtons dynamically.

DataGrid control is used to display the data fetched from the

database in table like format.

DataList Control provides a GUI interface, drag and drop

feature to edit templates.

Repeater control requires the programmer to switch to the

HTML view write all the coding for the templates.

Table control is used to fetch data from the database and

display in a table. Rows and columns in a table control can be

generated dynamically.

Summary



60/61

Main Menu 60 of 61

Fill in the blanks: The DataGrid displays ___________ data by default.

To capture the information from the database, ________object is used.

To populate the information from the database, __________method is used.

The ________ control is a basic container control thatallows creating custom lists out of any data available to thepage.

In Repeater control, _________ view is used to edit thetemplates.

In ___________ we specify the number of columns perrow.

The DataGrid can contain an _____________ that renders

links for firing three special events.

Self-Assessment



61/61

State True or False

By default, the DataGrid control generates a bound column

for each field in the data source.

DataBinder.Eval method has four parameters The DataGrid directly supports sorting.

Self-Assessment

chapter 10 - data binding with list controls

Documents