Data Adapter and Dataset

1
Data Adapter and Dataset

• ASP.NET
• http//sdetu/courses/sdetc101/
• Lecture 17
• Saturday, 03th April 2008

2
Agenda

• Recall
• Data Adapter
• Data Sets
• Construct a Data Set with/without a DA
• Navigate the Data Sets object model
• How to connect MS Access.

3
Recall

• Stored Procedures
• The command object
• Data Reader Object

4
Dataset

• Provides access to multiple tables, rows, and
  columns.

5
The Data Adapter

• DataSet knows nothing about interacting with the
  database
• DataAdapter fills DataSet with data from database
  with Fill(), and updates database with changes
  made to DataSet with Update()
• DataAdapter is provider-specific
• OleDbDataAdapter, SqlDataAdapter

6
Constructing a Data Adapter Object

• The constructors are as follows
• oDA New OleDbDataAdapter() //default
• oDA New OleDbDataAdpater(oCmd)
• oDA New OleDbDataAdapter(sSQL, oConn)

7
Example

• lt_at_ Page LanguageVB gt
• lt_at_ Import NamespaceSystem.Data gt
• lt_at_ Import NamespaceSystem.Data.OleDb gt
• ltSCRIPT LANGUAGEVB RUNATservergt
• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDbConnection
• Dim oDA As OleDbDataAdapter
• oConn New OleDbConnection(ProviderSQLOLEDBDat
  a
• Source(local)Initial CatalogMusicUser
  IDmusicPasswordmusic)
• oConn.Open
• oDA New OleDbDataAdapter(SELECT FROM
  t_bands, oConn)
• oConn.Close()
• End Sub
• lt/SCRIPTgt

8
Properties and Methods

• SelectCommand property
• Gets or sets a Command object used to select
  records in a DataSet.
• Example-a
• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDbConnection
• Dim oCmd As OleDbCommand
• Dim oDA As OleDbDataAdapter
• oConn New OleDbConnection(ProviderSQLOLEDBDat
  a
• Source(local)Initial CatalogMusicUser
  IDmusicPasswordmusic)
• oConn.Open()
• oCmd New OleDbCommand(SELECT FROM t_bands,
  oConn)
• oDA New OleDbDataAdapter()
• oDA.SelectCommand oCmd
• End Sub

9
SelectCommand property

• Example-b
• Dim oConn As New OleDbConnection
• Dim oDA As New OleDbDataAdapter
• With oConn
• .ConnectionString ProviderSQLOLEDBData
  Source(local)Initial
• CatalogMusicUser IDmusicPasswordmusic
• .Open
• End With
• oDA.SelectCommand New OleDbCommand
• With oDA.SelectCommand
• .CommandType CommandType.Text
• .CommandText SELECT FROM t_bands
• .Connection oConn
• End With
• oConn.Close
• End Sub

10
SelectCommand property

• In example-a that we
• (1) create an OleDbConnection object,
• (2) create an OleDbCommand object,
• (3) construct an OleDbDataAdapter object, and
• (4) set theOleDbDataAdapter objects
  SelectCommand equal to the previously created
  OleDbCommandobject.
• When the SelectCommand property is set to a
  previously created Command object, (i.e. as in
  example-a), the Command object is not cloned. The
  SelectCommand merely maintains a reference to the
  Command object
• We set the SelectCommand property without
  explicitly creating a Command object (as in
  example-b).Here were creating the SelectCommand
  explicitly.

11
Update, Delete and Insert Command properties

• The UpdateCommand property is used to get or set
  the command used to update records in the data
  source.
• The UpdateCommand is effectively the Command
  object used to update
• records in the data source for modified rows
  in the DataSet.
• Similarly the DeleteCommand and InsertCommand
  properties are used to get or set the command
  used to delete or insert, respectively, records
  in the data source when the Update method is
  called.

12
Fill method

• Adds data from your data source to a dataset.
• The Fill method accepts a variety of parameters
  including
• The DataSet object to fill, a string representing
  the alias for the newly created DataSet object,
• An integer representing the lower bound of
  records to retrieve, and
• an integer representing the upper bound of
  records to retrieve from our data source.
• Examples
• oDSCmd.Fill(oDS)
• oDSCmd.Fill(oDS, Band Information)
• Example
• //Creates a DataSet called Band Information and
  bind it to a DataGrid called dgBands.

13
Example(1/2)

• lt_at_ Page LanguageVB gt
• lt_at_ Import NamespaceSystem.Data gt
• lt_at_ Import NamespaceSystem.Data.OleDb gt
• ltSCRIPT LANGUAGEVB RUNATservergt
• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDbConnection
• Dim oDA As OleDbDataAdapter
• Dim oDS As New DataSet
• oConn New OleDbConnection(ProviderSQLOLEDBDat
  a
• Source(local)Initial CatalogMusicUser
  IDmusicPasswordmusic)
• oConn.Open
• oDA New OleDbDataAdapter(SELECT FROM
  t_bands, oConn)
• oDA.Fill(oDS, Band Information)
• dgBands.DataSource oDS
• dgBands.DataBind()

14
Example(2/2)

• oDS.Dispose()
• oDA.Dispose()
• oConn.Close()
• End Sub
• lt/SCRIPTgt
• ltHTMLgt
• ltBODYgt
• ltaspDataGrid iddgBands runatserver
• BorderColor000000
• BorderWidth2
• GridLinesBoth
• CellPadding5
• CellSpacing0
• Font-NameArial
• HeaderStyle-BackColorC0C0C0
• /gt
• lt/BODYgt
• lt/HTMLgt

15
Update method

• Calls the respective insert, update, or delete
  command for each inserted, updated, or deleted
  row in the DataSet.
• Example
• If you make changes to any of the tables in your
  DataSet, the following syntax is used to send the
  changes to the Music database
• oDA.Update(oDS, t_bands)
• Dispose method
• Disposes of the DataAdapter object.

16
Using DataSet Objects

• The DataSet object is a very complex object and
  has numerous properties, methods, and
  collections.
• DataSetName property
• Get or set the name of the DataSet object.
• oDS.DataSetName MyDataSet
• Dim sName As String
• sName oDS.DataSetName //Retrives the name
  of the dataset
• CaseSensitive property
• Gets or sets a value (as a Boolean) indicating
  whether string comparisons within DataTable
  objects are case sensitive.
• The CaseSenstive property affects operations such
  as sorting, filtering, and searching a DataTable.
  
• The default value is False.
• Dim bCase As Boolean
• bCase oDS.CaseSensitive

17
Constructing a DataSet

• //Creating a DS without DA
• Dim oDS As DataSet
• oDS New DataSet()
• Dim oDS As DataSet
• oDS New DataSet(MyDataSet)
• Dim oDS As DataSet
• oDS New DataSet()
• oDS.DataSetName MyDataSet

18
Tables property

• Example
• lt_at_ Page LanguageVB gt
• lt_at_ Import NamespaceSystem.Data gt
• lt_at_ Import NamespaceSystem.Data.OleDb gt
• ltSCRIPT LANGUAGEVB RUNATservergt
• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oDS As DataSet
• Dim oDTC As DataTableCollection
• oDS New DataSet(MyDataSet)
• oDTC oDS.Tables
• lblTableCount.Text oDTC.Count
• End Sub
• lt/SCRIPTgt
• ltHTMLgt
• ltBODYgt
• MyDataSet contains ltaspLabel IdlblTableCount
  Text Runatserver /gt
• tables.
• lt/BODYgt
• lt/HTMLgt

The Count property should return a value of zero
because you havent actually added any DataTable
objects to the DataTableCollection object.
19
Tables Collection Object

• Count property
• The number of tables in the DataTableCollection
  object using its Count property
• Dim oDS As DataSet
• oDS New DataSet(MyDataSet)
• lblTableCount.Text oDs.Tables.Count
• Item property
• Gets a specified DataTable from the
  DataTableCollection.
• In order to get the desired table you either pass
  an integer representing the tables index or a
  string representing the tables name to the Item
  property.

20
Example(1/2)

• Dim oConn As New OleDbConnection
• Dim oCmd As New OleDbCommand
• Dim oDA As New OleDbDataAdapter
• Dim oDS As DataSet
• Dim i As Integer
• Dim x As Integer
• With oConn
• .ConnectionString ProviderSQLOLEDB Data
  Source(local)
• Initial CatalogMusic User IDmusic
  Passwordmusic
• .Open
• End With
• With oCmd
• .Connection oConn
• .CommandType CommandType.Text
• .CommandText SELECT FROM t_bands
• End With
• oDS New DataSet(Music)
• oDA.SelectCommand oCmd
• oDA.Fill(oDS, t_bands)

21
Example(2/2)

• oDA.SelectCommand.CommandText SELECT FROM
  t_music_types
• oDA.Fill(oDS, t_music_types)
• oDA.SelectCommand.CommandText SELECT FROM
  t_record_companies
• oDA.Fill(oDS, t_record_companies)
• For i 0 To oDS.Tables.Count - 1
• Response.Write(oDS.Tables.Item(i).TableName
  ltBRgt chr(13))
• For x 0 To oDS.Tables.Item(i).Columns.Count - 1
• Response.Write(nbspnbspnbspnbsp
• oDS.Tables.Item(i).Columns.Item(x).ColumnName
  ltBRgt chr(13))
• Next
• Next
• oDS.Dispose()
• oDA.Dispose()
• oCmd.Dispose()
• oConn.Close()
• End Sub

22
Tables Collection Object

• Contains method
• Returns a Boolean value indicating whether the
  DataTableCollection
• and thereby the DataSet contain a specified
  table.
• Accepts a string representing the tables name as
  input.
• Example
• Dim bValid As Boolean
• bValid oDS.Tables.Contains(t_bands) //true
  (from previous example)
• The IndexOf method returns the index of the
  specified table. The method accepts either a
  string representing a DataTables name or a
  DataTable object as input parameters
• Example
• Dim iIndex As Integer
• iIndex oDS.Tables.IndexOf(t_bands)
• The Clear method, as the name implies, removes
  all tables from the DataTableCollection object.

23
Tables Collection Object

• Remove method
• Removes the specified table from the
  DataTableCollection object.
• Remove method can accept either a tables name or
  a DataTable object as its input parameter.
• Example
• oDS.Tables.Remove(t_bands)
• Add method
• Adds a table to the DataTableCollection. E.g.
  oDS.Tables.Add(Bands)
• You can call the Add method and not pass it any
  input parameters. In this case a DataTable is
  added to DataTableCollection and assigned a
  default name
• You can also pass only a string representing the
  tables name. In this case, a DataTable is added
  to the DataTableCollection and assigned the
  specified name.
• You can call the Add method and pass it a
  DataTable object. In this case, the specified
  DataTable is added to the DataTableCollection.

24
DataTable Object

• A central object in the ADO.NET library and
  effectively represents a data sources data.
• You can manually fabricate a DataTable using its
  properties and methods,or you can have it
  automatically filled using DataSet commands.
• To manually construct a DataTable
• Dim oDT As DataTable
• oDT New DataTable()
• You can pass a string representing the
  DataTables name to the constructor
• oDT New DataTable(MyTable)

25
DataTable Object

• CaseSensitive property
• A Boolean value that indicates whether string
  comparison within a table is case sensitive.
• The propertys default value is set to the parent
  DataSet objects CaseSensitive property.
• Affects string comparisons in sorting, searching,
  and filtering.
• Columns property
• Gets the collection of columns that belong to a
  DataTable in the form DataColumnCollection
  object.
• The relationship between the Columns property and
  a of a DataTable is very similar to the
  relationship between the Tables property and a
  DataSet.
• The Columns property exposes a DataColumnCollectio
  n through which you can access a DataTables
  columns.
• Dim oDCC As DataColumnCollection
• oDCC oDS.Tables(0).Columns

26
DataTable Object

• Rows property
• Provides access to the collection of rows that
  belong to the table
• It returns a collection of DataRow objects
• A DataTables DataColumn objects represent the
  DataTables structure whereas the DataRow objects
  represent the DataTables data.
• Example
• Demonstrates iterating through the
  DataRowCollection (returned by the Rows property)
  in a DataTable and writing its contents to a .NET
  Table control.

27
Example

• Dim dtblCart As DataTable
• Dim dcolColumn As DataColumn
• Dim drowItem As DataRow
• ' Create DataTable
• dtblCart New DataTable( "Cart" )
• ' Create Columns
• dcolColumn New DataColumn( "ProductID",
  GetType( Int32 ) )
• dtblCart.Columns.Add( dcolColumn )
• dcolColumn New DataColumn( "ProductName",
  GetType( String ) )
• dtblCart.Columns.Add( dcolColumn )
• dcolColumn New DataColumn( "ProductCategory",
  GetType( String ) )
• dtblCart.Columns.Add( dcolColumn )
• dcolColumn New DataColumn( "ProductPrice",
  GetType( Decimal ) )
• dtblCart.Columns.Add( dcolColumn )

28
Example.

• ' Add Rows
• drowItem dtblCart.NewRow()
• drowItem( "ProductID" ) 12
• drowItem( "ProductName" ) "Anchor Steam"
• drowItem( "ProductCategory" ) "Beer"
• drowItem( "ProductPrice" ) 7.90
• dtblCart.Rows.Add( drowItem )
• drowItem dtblCart.NewRow()
• drowItem( "ProductID" ) 13
• drowItem( "ProductName" ) "Samuel Adams"
• drowItem( "ProductCategory" ) "Beer"
• drowItem( "ProductPrice" ) 8.67
• dtblCart.Rows.Add( drowItem )

29
Example.

• ' Display DataTable
• For each drowItem in dtblCart.Rows
• For each dcolColumn in dtblCart.Columns
• Response.Write( drowItem( dcolColumn ) )
• Next
• Next

30
DataTable Object

• Dispose method
• It is good programming practice to dispose of all
  your objects after you are done using them in
  order to release valuable system resources.
• NewRow method
• Creates a new DataRow object with the same schema
  as the table through which it is being created.
• Once a row is created, you can add it to the
  tables DataRowCollection via the Rows property.
• Example
• Dim oDR As DataRow
• oDR oDS.Tables(0).NewRow
• oDR(band_title) Toad The Wet Sprocket
• ADD OTHER COLUMN INFORMATION HERE
• oDS.Tables(0).Rows.Add(oDR)
• //creates a new row in the first table (index of
  zero) in the DataSet, oDS.

31
Connecting MS Access

• Only change is with connection string.
• Dim conn as oledbconnetion
• conn New OleDbConnection("PROVIDERMicrosoft.Je
  t.OLEDB.4.0DATA SourceD\Documents and
  Settings\prashant\Desktop\test.mdb")

32
Summary

• Data Adapter
• Data Sets
• Construct a Data Set with/without a DA
• Navigate the Data Sets object model

33
Questions ?
34
(No Transcript)