Microsoft ADO.NET: Programming ADO.NET Datasets Vijaya Lakshmi Byri Support Engineer Manisha Gupta S

1
Microsoft ADO.NET Programming ADO.NET
DatasetsVijaya Lakshmi ByriSupport
EngineerManisha GuptaSupport Engineer(PSS)
Microsoft Corporation
2
Agenda

• Review of ADO.NET architecture in the Microsoft
  .NET Framework
• Working with datasets
• ADO.NET datasets
• XML and datasets
• Typed datasets

3
Microsoft .NET Framework
XML Web Services
Web Forms
Windows Forms
ASP.NET
Data and XML Classes
Base Framework Classes
Common Language Runtime
4
ADO.NET Architecture
5
ADO.NET Datasets

• Architecture of dataset
• Creating a dataset
• Navigating the dataset hierarchy
• Copying a dataset
• Merging a dataset

6
Dataset Architecture
Dataset
DataTableCollection
DataTable
DataRowCollection
DataColumnCollection
ConstraintCollection
DataRelationsCollection
7
Creating a Dataset

• Server Explorer
• Using DataAdapter
• Programmatically
• Using XML

8
Server Explorer
9
Generate a Dataset
10
Using DataAdapter

• Open connection
• Create a DataAdapter
• Fill the dataset

11
Sample Code

• Dim cnNwind As New SqlClient.SqlConnection("Data
  Source(localhost)
• Integrated SecuritySSPI
• Initial CatalogNorthwind")
• Dim daOrders As New SqlClient.SqlDataAdapter
  ("Select from Orders", cnNwind)
• Dim ds As New dataset()
• daOrders.Fill(ds, "Orders")

12
Create Dataset Programmatically
It is possible to fabricate a dataset without
loading in external data.

• Define some DataColumn objects.

DataColumn
DataColumn
13
Create Dataset Programmatically (2)

• Add the DataColumn objects to a DataTable object.

DataTable
DataColumn
DataColumn
14
Create Dataset Programmatically (3)

• Add the DataTable object to a Dataset object.

Dataset
DataTable
DataColumn
DataColumn
15
DataTable
16
Sample Code
Dim myCustomers As New Data.DataTable()
myCustomers.TableName "Customers"
With myCustomers .Columns.Add("Custome
rID", Type.GetType("System.String"))
.Columns.Add("CompanyName", Type.GetType("System.S
tring")) .Columns.Add("ContactName",
Type.GetType("System.String")) End With
Dim myDr As Data.DataRow myDr
myCustomers.NewRow() myDr("CustomerID")
"9876" myDr("CompanyName") Microsoft"
myDr("ContactName") Jeff Smith"
myCustomers.Rows.Add(myDr) Dim myDS As
New Data.dataset("CustomerDS")
myDS.Tables.Add(myCustomers)

17
Navigating Dataset
Home
18
Adding a DataRelation

• Use DataRelation objects to
• Relate one table to another
• Navigate through the tables
• Return child or parent rows from a related table

• custDS.Relations.Add("CustOrders", _
  custDS.Tables("Customers").Columns("CustID"
  ), custDS.Tables("Orders").Columns("C
  ustID"))

19
Navigating a Relationship

• Dim custOrderRel As DataRelation
  custDS.Relations.Add("CustOrders", _
  custDS.Tables("Customers").Columns("Custome
  rID"),
• custDS.Tables("Orders").Columns("CustomerID"))
• Dim custRow As DataRowDim orderRow As DataRow
• For Each custRow in custDS.Tables("Customers").Row
  s Console.WriteLine(custRow("CustomerID"))
• For Each orderRow in custRow.GetChildRows(custOrde
  rRel) Console.WriteLine(orderRow("OrderID"))
• Next
• Next

20
Copying a Dataset

• Create an exact copy of the dataset.
• Create a copy of modified rows.
• Create a copy of the schema.

21
Creating an Exact Copy of the Dataset

• To create an exact copy of the dataset that
  includes both schema and data, use the Copy
  method of the dataset.
• Dim copyDS As dataset custDS.Copy()

22
Creating a Copy of Modified Rows

• Use GetChanges to return only modified rows with
  a specified row state.
• Added
• Updated
• Deleted
• Dim changeDS As dataset custDS.GetChanges()
• Dim addedDS As dataset custDS.GetChanges(DataRow
  State.Added)

23
Creating a Copy of the Schema

• Use the Clone method to copy only the schema of
  the dataset.
• Use the ImportRow method of the DataTable to add
  the existing rows.
• Dim custGermanyDS As dataset custDS.Clone()Dim
  copyRows() As DataRow custDS.Tables("Customers")
  .Select("Country 'Germany'")
• Dim custTable As DataTable custGermanyDS.Tables(
  "Customers")
• Dim copyRow As DataRow
• For Each copyRow In copyRows custTable.ImportRow(
  copyRow)
• Next

24
Merging a Dataset

• Merge the contents of a dataset
• Merge contents of the DataTable
• Merge contents of the DataRow array
• MergeFailed event

25
Merging Factors and Options

• Primary keys
• PreserveChanges
• MissingSchemaAction
• Constraints

26
Sample Code

• Dim ds As New Dataset("myDataset")
• Dim t As New DataTable("Items")
• ' Create variable for temporary Dataset.
• Dim xSet As Dataset
• ds.Tables.Add(t)
• Add two columns
• Add Rows
• ds.AcceptChanges()
• Change row values
• Add one row.
• Insert code for error checking.
• If the table has changes or errors, create a
  subset Dataset.
• xSet ds.GetChanges(DataRowState.Modified Or
  DataRowState.Added)
• Insert code to reconcile errors.
• xSet.Tables("Items").Columns.Add(New
  DataColumn("newColumn"))
• Add values to the rows for each column.
• ds.Merge(xSet, False, System.Data.MissingSchemaAct
  ion.Add)

27
XML and Datasets

• Loading a dataset with XML data
• Writing a dataset as XML data
• Loading a dataset with XSD schema
• Writing a dataset as XSD schema
• Synchronizing dataset and XMLDataDocument

28
Loading a Dataset with XML Data

• The method used to load XML data into a dataset
• Public Function ReadXml(Stream) As XmlReadMode
• Public Function ReadXml(Stream, XmlReadMode) As
  XmlReadMode

29
Sample Code

• Dim ds As New Dataset()
• ds.ReadXml("c\books.xml",XmlReadMode.Auto)
• Dim dr As DataRow
• For Each dr In ds.Tables(0).Rows
• Console.WriteLine(dr.Item(0))
• Next

30
XmlReadMode

• Auto
• DiffGram
• Fragment
• IgnoreSchema
• InferSchema
• ReadSchema

31
Writing a Dataset as XML

• The method used to write XML data using a
  dataset
• Overloads Public Sub WriteXml(Stream)
• Overloads Public Sub WriteXml(Stream,XmlWriteMode)

32
Sample Code

• Dim cnNwind As New SqlConnection(
• "Data Source(localhost)integrated
  SecuritySSPIInitial catalogNorthwind")
• Dim daOrders As New SqlDataAdapter(
• "Select from customers", cnNwind)
• Dim ds As New Dataset()
• daOrders.Fill(ds, "Customers")
• ds.WriteXml("C\Customers.xml",XmlWriteMode.Ignore
  Schema)

33
Loading a Dataset with XSD Schema

• ReadXmlSchema
• Loads the XSD schema
• Does not load any data
• InferXmlSchema
• Infers the XSD schema from the XML data
• Loads the XSD schema
• Loads the XML data

34
Writing XML Schema

• GetXmlSchema
• Returns an XSD schema
• Return type is String
• Dim xsdDS As String DS.GetXmlSchema()
• WriteXmlSchema
• Writes XSD schema to a file or stream
• DS.WriteXmlSchema("Customers.xsd")

35
Synchronizing XmlDataDocument and Dataset

• Synchronize XMLDataDocument and a dataset
• Perform XPath queries on a dataset
• Perform XSLT transformations on a dataset

36
Synchronizing XmlDataDocument and Dataset (2)

• Create a dataset
• Create an XmlDataDocument using a dataset
• Dim cnNwind As New SqlConnection("Data
• Source(localhost)Integrated securitySSPIIniti
  al
• catalogNorthwind")
• Dim daOrders As New SqlDataAdapter("Select
  from
• Orders where 12", cnNwind)
• Dim ds As New Dataset()
• daOrders.FillSchema(ds, "Orders")
• Dim xmldoc As New Xml.XmlDataDocument(ds)
• xmldoc.Load("C\Orders.xml")

37
Performing XPath Queries on a Dataset

• Create an XmlDataDocument based on a dataset
• Perform XPath queries on the XmlDataDocument
• Dim nodes As Xml.XmlNodeList
• nodes xmldoc.SelectNodes("//OrderID")
• Dim node as Xml.XmlNode
• For each node in nodes
• Console.WriteLine(node.InnerText)
• Next

38
Performing XSL Transformations on Dataset

• Create an XMLDataDocument based on a dataset
• Using XslTransform object to transform the data
  in the dataset
• Dim xmldoc As New Xml.XmlDataDocument(ds)
• Dim xsldoc As New Xml.Xsl.XslTransform()
• xsldoc.Load("Orders.xsl")
• Dim xpathnav As Xml.XPath.XPathNavigator
• xpathnav xmldoc.CreateNavigator
• xsldoc.Transform(xpathnav, Nothing)

39
Typed Datasets

• How to generate a typed dataset
• Using MSDatasetGenerator
• Using .NET command-line tools
• Xsd.exe
• Language compiler
• Using annotations with a typed dataset

40
Using MSDatasetGenerator
41
IntelliSense for Typed Dataset
42
IntelliSense for Typed Dataset (2)
43
Using .NET Command-Line Tools

• Create a dataset using an XSD schema
• Creates a class file (depending upon the language
  specified)
• Example Class1.vb
• Syntax
• xsd.exe /d /lC XSDSchemaFileName.xsd
  /nXSDSchema.Namespace
• Using language compiler compile the library from
  the generator code
• Syntax
• csc.exe /tlibrary XSDSchemaFileName.cs
  /rSystem.dll /rSystem.Data.dll

44
Class1.vb

• Namespace
• Classes
• x(xsd schema name)
• Inherits dataset class
• xDataTable
• Inherits DataTable Class
• xRow
• Inherits DataRow Class
• xRowChangeEvent
• Inherits EventArgs Class
• Note x would be the XSD schema name.

45
Using Annotations with a Typed Dataset

• Modify the names of the elements in the typed
  dataset without modifying the underlying schema.
• How to annotate
• Add the Annotations namespace
• Namespace xmlnscodegen"urnschemas-microsoft-c
  omxml-msprop"
• Add the annotation
• ltxselement name"Customers" codegentypedName"C
  ustomer" typedPlural"Customers"gt

46
Annotations

• TypedName
• TypedPlural
• TypedParent
• TypedChildren
• NullValue

47

• Thank you for joining us for todays Microsoft
  Support
• WebCast.
• For information on all upcoming Support WebCasts
  and
• access to the archived content (streaming media
  files,
• PowerPoint slides, and transcripts), please
  visit
• http//support.microsoft.com/webcasts/
• We sincerely appreciate your feedback. Please
  send any
• comments or suggestions regarding the Support
• WebCasts to supweb_at_microsoft.com