MIS 426

1
MIS 426

• Chapter 12, Working with DataSets

2
Objectives

• In this chapter you should gain an
• Understanding of DataSets
• Understanding of DataTables
• Understanding of DataViews
• Understanding Cached DataSets

3
Understanding DataSets

• In chapter 9 you learned how to use the
  DataReader and Command classes to work with data
• Now we will learn a new set of classes to
  manipulate database information
• The reason for the two classes is that they have
  different uses
• To quickly grab and simply display a READ ONLY
  copy of information use a DataReader created by a
  Command Object
• To perform complicated updates to data use a
  disconnected record set - a DataSet created
  by a DataAdapter

4
DataAdapter
Command
Connection
Connection
ConnectionString
Connection
DataSet
SelectCommand
CommandText
UpdateCommand
Control on a Page or Form
CommandType
InsertCommand
DeleteCommand
Data Store
DataReader
5
Understanding DataSets (Cont.)

• A DataReader must remain connected to the
  underlying DataSource (a connected record set)
• A DataSet copies the information from the
  DataSource (based on a DataAdapters SQL SELECT
  statement) and holds it in the IISs computer
  memory
• This is a Disconnected Record Set which means you
  can break the connection to the server without
  affecting the Record Set

6
Understanding DataSets (Cont.)

• When used wisely DataSets can improve performance
• It allows you represent the same data across
  multiple pages without requesting it from the
  server again, which is a resource intensive task
• Also updates can be made in batchmode rather than
  execute a command after each edit
• When used badly DataSets can hurt performance as
  they occupy a lot of memory
• If 100 different users request a DataSet of
  10,000 records then 1 million records will end up
  being held in server memory

7
Elements of a DataSet (Please read Glossary)

• DataAdapter represents the bridge between the
  DataSource and the DataSet. The Fill method
  copies data from the datasource to the in-memory
  dataset, the Update methods transfers any changes
  held in memory back to the datasource
• DataSet can hold more than one DataTable, and
  use Datarelations to enforce referential
  integrity
• DataTable the actual data is stored in rows of
  the table
• DataRelation the representation of
  relationships in the DataSource
• DataView A wrapper around a DataTable that can
  be used to represent a filtered or sorted view of
  a DataTable

8
Adding DataTables to a DataSet

• To add a table to a DataSet you must use a
  DataAdapter
• Create a DataSet (Dim dsSales As DataSet)
• Create a database connection (same as before)
• Create and construct a DataAdapter with a SQL
  Select statement and the database connection
• Dim daSales as SqlDataAdapter daSales New
  SqlDataAdapter(Select from Sales, conEmp)
• Call the Fill method of the DataAdapter passing
  the DataSet this also constructs a DataTable -
  this is similar to executing a command
  daSales.Fill( dsSales, dtSales )

9
Data Sets

• You can add multiple DataTables to the same
  DataSet, and relate them by adding DataRelations
  to the Dataset
• Typically you refer to the table by
  DatasetName.Tables(DataTableName)dsABCco.Table
  s(dtCustomers)
• This can be very useful for creating parent/child
  relationships (shown later)
• View an example
• If necessary you can add DataTables to a DataSet
  from different sources (XML, .XLS, .MDB,
  SQLServer, Oracle, MYSQL, etc.)

10
Binding DataSets to Controls

• You can use Tables of a DataSet as the datasource
  of a Repeater, DataList and DataGrid control in
  almost the same way you would with a DataRepeater
• View an example
• The DataGrid is bound to the default (only)
  database table (Products) with the following two
  lines of code
• dgrdProducts.DataSource dstProducts
• dgrdProdcuts.DataBind()
• If a DataSet contains multiple tables you can
  assign which DataTable to display with the
  .DataMember Property or set the datasource to
  ds.Tables(dt)

11
Understanding DataTables

• A DataTable can be a memory-resident
  representation of a dbtable
• A DataTable can also be created Programmatically
  - This could be used for example to create a
  memory resident shopping cart View an example
• You can also modify the properties of a DataTable
• CaseSensetive for searching, sorting or
  filtering
• MinimumCapacity specifies the initial number of
  rows the DataTable accepts
• PrimaryKey defines the primary key column
• TableName specifies the DataTable name
• Tables also have a collection of
  UniqueConstraints and ForeignKeyConstraints

12
Understanding DataTables (Cont)

• You can also set the properties of columns in a
  DataTable
• Refer to Page 593 for a listing of properties
• You can use the DefaultValue property to assign a
  default value to a DataColumn
• You can specify a unique column in a DataTable in
  two ways
• The Unique column property
• Creating a unique constraint

13
Understanding DataTables (Cont)

• You can create columns that will AutoIncrement
• In a DataTable you can also create Calculated and
  Aggregate Columns
• You can compute column values in a DataTable by
  using the Compute method
• You can define relationships between DataTables
  with the Relations propertyThis allows you to
  use the awesome ParentRow.GetChildRow(RelationName
  )

14
Understanding DataTables (Cont)

• By default when you use the Fill() Method to
  create a DataTable from an existing data table no
  key or schema information is retrieved. To
  retrieve this information about the keys
  collection of DataTables (or constaints
  collection) you can use the
• FillSchema() Method
• MissingScheamAction Property

15
Updating Records in a DataTable

• Because the DataAdapater acts a bridge between
  the DataSource and the DataSet it can be used to
  update the DataSource
• To do this we must use the Update() Method View
  an example
• The DataAdapter has 4 properties that represent
  four database commands
• SelectCommandInsertCommandUpdateCommandDeleteCo
  mmand
• If you use a CommandBuilder inconcert with a
  DataAdapter, VS will write the code for the
  Insert, Update, and Delete commands
• You have the option to assign your own SQL
  commands to these properties. You may wish to do
  this if you are hoping to use stored procedures

16
Understanding DataViews

• You can use a DataView to display filtered or
  sorted data
• You can also use a DataView to find rows that
  have certain column values
• You can create a new DataView by passing a
  DataTable to a DataViews Constructor
• dvSales New DataView ( dtSalesTable)
• You can also call the DefaultView() method of the
  DataTable to create a DataView
• dvSales dtSalesTable.DefaultView()

17
Filtering and Sorting Rows in a DataView

• You can filter rows of a data in a DataView by
  using the RowFilter Property
• Complex (nested) Row Filter
• You can sort the rows in a DataView by using the
  Sort Property
• Sorting by a single column myDataView.Sort
  Price
• Sorting using multiple columns myDataView.Sort
  au_lname, au_fname

18
Finding Rows in a DataView

• You can use a DataViews Find() Method to find
  the index of a row that has a certain value
• You can pass a single value to the Find method
• intItemIndex MyDataView.Find(Smith)
• or you can pass an array of values
• Dim aryValues(2) As Object
• aryValues(0) Smith
• aryValues(1) Robert
• intItemIndex dvDataView.Find(aryValues)

19
Using DataSets with ASP.Net Pages

• Caching DataSets can greatly improve the
  performance of ASP.Net web pages
• When you place an item in the applications cache
  it is preserved between page requests in the
  servers memory
• Cache(CachedDataSet) dsDataSet
• This adds an item to the Cache and assigns it the
  name CachedDataSet

20
Using DataSets with ASP.Net Pages (Cont)

• You can never assume that a Cached DataSet is
  still in memory. You should always check to be
  sure with some kind of code
• dsDataSet Cached(CachedDataSet)
• If myDataSet Is Nothing Then
• Recreate the Data Set
• Cache(CachedDataSet) dsDataSet
• End If
• DataViews can also be chached
• You can also Filter and Find from DataSets that
  are cached