Week 6 Chapter 7
Create, Add, Delete, and Edit Data in a Disconnected Environment
Objectives in chapter
Create a DataSet graphically. Create a DataSet programmatically. Add a DataTable to a DataSet. Add a relationship between tables within a DataSet. Navigate a relationship between tables. Merge DataSet contents. Copy DataSet contents. Create a typed DataSet. Create DataTables. Manage data within a DataTable. Create and use DataViews. Use the OleDbDataAdapter object to access an ADO Recordset or Record. Generate DataAdapter commands automatically by using the Command-Builder object.
Chapter content
Lesson 1:Creating DataSet Objects Lesson 2: Creating DataTable Objects. Lesson 3: Creating DataAdapter Objects Lesson 4: Working with Data in DataTable Objects Lesson 6: Creating and Using DataView Objects
Lesson 1: Creating DataSet Objects
DataSet Objects Creating DataSet Objects Programmatically
Lesson 1: Creating DataSet Objects
DataSet Objects
temporarily store the data Like array, in memory
Why DataSet?
Perform database modifications
always disconnected
To transfer data between tiers have .NET framework (Linux, Unix, Windows) To send between computers across network by HTTP protocol in XML format To manipulate the data without an open connection To relate data from multiple sources To bind data to a Windows form Power data structure
Event an application without database, we can using dataset instead of arraylist, array
Populating DataSets
Manual From existing database (using DataAdapter) Read from XML,XSD data files
But before filling dataset
we need to know dataset structure
DataSet Components
DataTable DataColumn DataRow Constraint DataRelation DataView
DataSet Object Model
DataTable Collection DataTable
DataRowCollection DataColumnCollection ParentRelations ChildRelations ConstraintCollection DataView
1.reside in the System.Data namespace 2.doesnt care where it came from
See next page Lesson 6
DataRelationCollection DataSet Lesson 4
DataSet class tree model
DataSet Tables Table Columns Constraints Rows Relations Relation Object Collection
Column Constraint Row
DataTable
Represents one table in DataSet Are accessed using the DataSet's Tables property
Collection of DataTables Using index : ds.tables(0) Using name: ds.tables(authors) A Columns property A Rows property
DataSet DataTable
DataRow DataColumn
DataTable
DataRow
DataColumn
Each DataTable has:
(Add,remove,removeat,Item)
Access to data-cell
(Add,remove,removeat,Item) Dim DT1 As New DataTable("TableName")
Table.Rows(index).Item(index)
Example
Dim Cid as DataColumn = authors.Columns.Add("ID",gettype(integer)) Cid.AutoIncrement = true
Dim Cname as DataColumn = authors.Columns.Add("Name", gettype(String)) authors.PrimaryKey = new DataColumn() {Cid}
DataRow Class
A DataRow object provides access to one row of data in a DataTable DataSet
A DataTable's DataRow objects contain all the data in the DataTable Table.NewRow method Column name Column number DataColumn object DataTable
DataRow DataColumn
Create
Can access DataRow values by providing:
DataTable
DataRow
DataColumn
Core Pro./methods
Item(index) : get cell value IsNull Delete
Lesson 1: Creating DataSet Objects
Two kinds : typed, and untyped.
Un-Typed Dataset: from generic type Typed Dataset:from schema (.xsd)
Two ways to create
Declare a new DataSet object programmatically for untype Dataset Use design-time tools: for typed-Dataset
Populating DataSet-Manual
1.
1. 2. 3. 4.
Construct your own DataSet (schema)
Create Dataset Add DataTable(s) Add DataColumn(s) to DataTable Define datatable schema
2.
1. 2.
Fill Data into DataTable(s) in DataSet
Add DataRow to DataTable Processing data in dataset
Lesson 1: Creating DataSet Objects
DataSet Objects:
Choosing typed or Un-typed Dataset?
Exercise 1: Creating a typed DataSet with the DataSet Designer Questions after finishing?
Lesson 1: Creating DataSet Objects
Exercise 2: Creating a Typed DataSet with the Data Source Configuration Wizard Questions?
Lesson 1: Creating DataSet Objects
Exercise 3: Configuring Untyped DataSet Objects Questions?
Lesson 2: Creating DataTable Objects.
Create a DataTable. Add a DataTable to a DataSet. Define the schema of a DataTable. Add columns to a table. Create expression columns. Create AutoIncrementing columns. Define a primary key for a table. Add constraints to a table.
Lesson 2: Creating DataTable Objects.
Create a DataTable. Add a DataTable to a DataSet
Dim NorthwindDataset As New DataSet("NorthwindData") ' Create a new DataTable. Dim CustomersTable As New DataTable("Customers") ' Add the Datatable to the Dataset's Tables collection. NorthwindDataset.Tables.Add(CustomersTable)
10
DataTable Core Pro./Methods
TableName property A PrimaryKey property array of datacolumn Select(filter): datarow() End of sql NewRow DefaultView
Example: Create table and dataset
Dim ds as new DataSet ds.DataSetName = "BookAuthors"; or Dim ds as new DataSet(BookAuthors) Dim authors as new DataTable("Author") Ds.Tables.add (Authors)
DataTable Accessing example
myDataTable.Rows(5) returns a DataRow object that is the 6th row of myDataTable myDataRow(2) returns an object representing the data stored in the 3rd column of myDataRow myDataTable.Rows(5)(2) returns an object representing the data stored in the 3rd column of the 6th row of the myDataTable
11
DataColumn Class
Describes one column of data in a DataTable
DataColumn objects describe table's schema DataAdapters can generate the required DataColumns Doesnt have Value property ColumnName DataType DataSet DataTable
DataRow DataColumn
Similar to column in datatable Core Pro. / methods
DataTable
DataRow
DataColumn
AllowDBNull AutoIncrement DefaultValue Unique
ReadOnly
Ex. Gettype(integer)
Lesson 2: Creating DataTable Objects.
Create Expression Columns in DataTable Objects
Dim TotalPriceColumn As New DataColumn("TotalPrice", GetType(System.Double)) TotalPriceColumn.Expression = ("UnitPrice * Quantity") NorthwindDataSet.Order_Details.Columns.Add(TotalPriceCol umn)
12
Lesson 2: Creating DataTable Objects.
Create Auto-Incrementing Columns in DataTable
SalesTable.Columns.Add("SalesOrderID", Type.GetType("System.Int32")) SalesTable.Columns("SalesOrderID").AutoIncrement = True ' Provide the starting value in the AutoIncrementSeed property. SalesTable.Columns("SalesOrderID").AutoIncrementSeed = 100 ' The amount added to the previous row's value is determined by the ' AutoIncrementStep value. SalesTable.Columns("SalesOrderID").AutoIncrementStep = 5
Lesson 2: Creating DataTable Objects.
Add Constraints to a DataTable
Foreign Constraint
Dim ForeignKey As New ForeignKeyConstraint("FK_Orders_OrderDetails", NorthwindDataset.Orders.Columns("OrderID"), _ NorthwindDataset.Order_Details.Columns("OrderID")) NorthwindDataset.Orders.Constraints.Add(ForeignKey)
13
Lesson 2: Creating DataTable Objects.
Add Constraints to a DataTable
Unique Constraint
Dim Unique As New UniqueConstraint(NorthwindDataSet.Orders.OrderIDColumn) NorthwindDataSet.Orders.Constraints.Add(Unique)
Lesson 2: Creating DataTable Objects.
Lab: create DataTable
Page 353, with notes:
Create Datatable in memory with column, constraints Display Datatable in DataGridview User edit data in Datatable through DataGridView ' Create a record (DataRow) to add to the table. Create new row for datatable
Dim CustRow As DataRow = CustomersTable.NewRow CustRow.Item("CustomerID") = "ALFKI" CustomersTable.Rows.Add(CustRow)
14
