ASP'NET and ADO'NET

1
ASP.NET and ADO.NET
2
ADO.NET Objects
.NET Applications
Data Set
Data Reader
Command Object
Connection Object
Managed Data Provider (OLEDB)
Database
3
Bind the DataReader to a Server Control

• DataGrid
• dim objDataReader as oledbDataReader
• objDataReaderobjComm.executeReader()
• dgCustomer.datasourceobjDataReader
• dgCustomer.DataBind()
• The Datagrid is defined as
• ltaspdatagrid id"dgCustomer" runat"server"
  /gtltbr /gt
• Demo CommandReadCust.ASPX

4
ListControl Base Class

• DropDownList, ListBox, CheckBoxList,
  RadioButtonList
• Properties
• AutoPostBack
• DataSource
• DataTextField The field in the datasource that
  provides the text to be used for the list items.
• DataValueField
• SelectedItem
• Event OnSelectedIndexChanged

5
Binding a DataReader to a DropDownList with
PostBack

• dim strSQL as string "select cname from
  customer"
• dim objComm as new OledbCommand(strSQL,objConn)
• objConn.open()
• dim objDataReader as oledbDataReader
• objDataReaderobjComm.executeReader()
• cnameList.datasourceobjDataReader
• cnameList.DataBind()
• The DropDownList is defined as
• ltaspdropdownlist id"CnameList" runat"server"
  DataTextField"cname" autopostback"true"
  OnSelectedIndexChanged"SHOW2" /gt
• Demo BindList.Aspx, ListPostBack.Aspx

6
Use List Items Add Method to Create a
DropDownList from a DataReader

• do while objDataReader.Read()true
• Cnamelist.items.add (objDataReader("Cname"))
• loop

7
DemoReaderUpdate.Aspx

• Use a DataReader to create a dropwdownList with
  customer names..
• Use a second DataReader to bind the selected
  customer data to a datagrid.
• Let user to choose a new customer rating from a
  listbox.
• Update customers rating using Command objects
  ExecuteNonQuery method.
• Create another reader to bind the selected
  customers new data in a second grid.

8
DataSet and Related Objects

• DataSet Can contain multiple tables and
  relationships.
• DataTable object Represents a table in the
  dataset.
• DataAdapter This the object used to pass data
  between the database and the dataset. The Fill
  method copies the data into the dataset, and the
  Update method copies the updates back into the
  database.
• DataView This represents a specific view of the
  DataTables held in the dataset.

9
Reading Data into a Table

• dim strConn as string "ProviderMicrosoft.Jet.OLE
  DB.4.0Data Source c\sales2k.mdb"
• dim objConn as new OledbConnection(strConn)
• dim strSQL as string "select from customer"
• dim objDataSet as new Dataset()
• dim objAdapter as new OledbDataAdapter(strSQL,
  objConn)
• objAdapter.Fill(objDataSet, "Cust")

10
Binding a Table to a Server Control

• Each Table object has a DefaultView property.
• Create a DataView object for the table, then use
  the DataView object to bind the control.
• objAdapter.Fill(objDataSet, "Cust")
• dim objDataView as new DataView(objDataSet.tables(
  "Cust"))
• dgCustomer.DatasourceobjDataView
• dgCustomer.DataBind()
• Demo AdapterReadCust.Aspx

11
DataView Object

• The DataView object exposes a complete table or a
  subset of the records from a table.
• Properties
• RowFilter
• objDataView.rowfilter"rating'"
  Ratinglist.SelectedItem.Text "'
• Demo AdapterReadcustView.aspx,
  AdapterReadcustView2.aspx
• AdapterReadcustView3.aspx

12
Creating Multiple Tables in A DataSet

• dim strSQLCust as string "select from
  customer"
• dim strSQLOrder as string "select from
  orders"
• dim objComm as new OledbCommand()
• dim objAdapter as new OledbDataAdapter()
• dim objDataSet as new Dataset()
• objComm.ConnectionobjConn
• objComm.CommandTypeCOmmandType.Text
• objComm.CommandTextstrSQLCust
• objAdapter.SelectCommandobjComm
• objConn.open()
• objAdapter.Fill(objDataSet, "Customer")
• objComm.COmmandTextstrSQLOrder
• objAdapter.Fill(objDataSet, "Orders")

13
Adding Relationship to a Dataset

• The Dataset object has a Relations property. It
  is a collection of DataRelations. We can use a
  relationship to enforce the referential
  integrity.
• To define a DataRelation
• DataRelObjDataRelation(RelationName, ParentTable
  Field, ChildTableField)
• objRel New DataRelation("custOrder",
  objDataset.tables("customer").columns("cid"),
  objDataset.tables("orders").columns("cid"))
• Demo AdapterCustOrderRel.aspx

14
DataTables Rows Property

• This is a collection of all the records in a
  table, a collection of DataRow objects.
• The DataRow object has a GetChildRows method that
  returns a collection of rows from another table
  that are related as child rows to this row.

15
Access Rows in a DataRow Collection

• dim objTable as DataTable objDataset.Tables("Cus
  tomer")
• dim objRow as DataRow
• For each objRow in objTable.Rows
• strResultstrResult"ltbgt" objRow("cid") " "
  objRow("cname") "lt/bgtltbr /gt"
• Next

16
Displaying Parent/Child Records in a Relation

• Define the relation.
• Specify the relation in the GetChildRows method
  of the DataRow object.
• Demo ParentChild.Aspx

17
Persistence of Data between Page Postback

• Each page is entirely new each time we submit
  information to the same page. There is no
  persistence of information between pages (other
  than the ViewState which remembers the data
  contained in controls). Variables and ADO
  objects are lost.
• We can store variables and ADO objects in Session
  or Application.
• Demo PersistDataSet.Aspx

18
sub Page_load() if not page.ispostback
then strConn "ProviderMicrosoft.Jet.OLEDB.4.0D
ata Source c\sales2k.mdb" objConn.connectionSt
ringstrConn strSQL "select from
customer" dim objAdapter as new
OledbDataAdapter(strSQL, objConn) objAdapter.Fill
(objDataSet, "Cust") objDataViewobjDataSet.Table
s("cust").DefaultView dgCustomer.DatasourceobjDa
taView dgCustomer.DataBind() response.write
("this is the first grid") Session("MyDataSet")o
bjDataSet end if end sub sub ClickHandler(Sender
As Object, E As EventArgs) objDataSetSession("MyD
ataSet") objDataViewobjDataSet.tables("Cust").def
aultView dgCustomer2.DatasourceobjDataView dgCust
omer2.DataBind() response.write("this is
ClcikHandler") end sub