Practical Applications of the ADO.NET XML Integration Features Karthik Ravindran Karthikeyan Ponnusa

1
Practical Applications of the ADO.NET XML
Integration Features Karthik Ravindran
Karthikeyan Ponnusamy PSS XML Web Database
Microsoft Corporation
2
Session Objective

• Use a scenario-driven approach to examine how the
  Microsoft ADO.NET XML integration features can
  be used explicitly in Microsoft .NET applications
  to address real world application development
  challenges

3
Session Prerequisites

• Audience familiarity with the following will be
  assumed
• The ADO.NET Object Model
• Relational features in ADO.NET
• Implicit usage of XML in ADO.NET
  (marshalling/remoting format, typed datasets,
  etc.)
• Accessing/manipulating Microsoft SQL Server data
  using the SQL Managed Provider

4
Session Prerequisites (2)

• Audience familiarity with the following will be
  assumed
• Basic knowledge of XML and its related standards
  
• Writing well-formed XML
• XML schemas (XSD) and their role in XML data
  validation
• Concept and understanding of the usage of the XML
  DOM
• Concept and understanding of the usage of XSLT
  transformations
• Concept and understanding of the usage of XPath
  queries

5
Agenda

• Overview of ADO.NET a quick review
• Review implicit uses of XML in ADO.NET
• Identify the categories of the explicit ADO.NET
  XML integration API
• Briefly examine the XmlDataDocument class and its
  integration with the ADO.NET Dataset
• Examine scenarios to demonstrate practical
  applications of the ADO.NET XML integration
  features
• Q/A

6
ADO.NET Overview
Connected Layer
Disconnected Layer
Dataset
Connection
DataTableCollection
DataAdapter
DataTable
SelectCommand
Command
DataRowCollection
InsertCommand
DataReader
DataColumnCollection
UpdateCommand
ConstraintCollection
DeleteCommand
DataRelationCollection
DATABASE
7
Implicit Uses of XML in ADO.NET

• The XML integration features in ADO.NET are
  centered around the Dataset object
• XML is used implicitly in/by the following
  aspects of the Dataset object
• Underlying storage format
• Serialization
• Marshalling (Web Services, .NET Remoting)
• Schema definition
• Strongly typed Datasets
• XML is not the format used to represent data
  inside a memory resident Dataset object

8
Categories of the Explicit ADO.NET XML
Integration API

• The Dataset class implements the core ADO.NET XML
  integration API
• API categories
• Methods to fill a Dataset with data and schema
  information from an XML document/stream
• ReadXml
• ReadXmlSchema
• InferXmlSchema
• Methods to serialize a Dataset as XML
• WriteXml
• WriteXmlSchema
• GetXml
• GetXmlSchema

9
The XmlDataDocument Class

• Inherits from System.Xml.XmlDocument
• Implements the core W3C XML DOM level 1.0 API
• Designed specifically to work hand-in-hand with
  the ADO.NET Dataset
• Hierarchical view of relational data
• Relational data can be accessed/manipulated as
  hierarchical XML data (DOM API, XPath, XSLT)
• Relational view of hierarchical data
• Hierarchical XML data can be accessed/manipulated
  using the datasets relational API
• Automatic synchronization (integrate hierarchical
  and relational views of the data)

10
The XmlDataDocument and the DataSet
Controls, Designers etc.
XSLT, XPath, Validation etc.
XmlDataDocument
DataSet
SYNC
XmlReader, TextReader, Stream, File etc.
DataAdapter (SqlDataAdapter/ OleDbDataAdapter)
11
ADO.NET XML Integration Scenarios

• Application integration
• Transforming Dataset data using XSLT
• Executing XPath queries on Dataset data
• Relational views of hierarchical data
• Creating repositories of frequently used Dataset
  schemas

12
ADO.NET XML Integration Scenarios (2)

• Returning Datasets from ASP.NET Web Forms and Web
  Services
• Programmatically convert XDR schemas to XSD
  schemas
• Programmatically generate XSD schemas for XML
  documents
• Create and populate SQL Server database tables
  using a manually fabricated Dataset

13
Scenario 1 Application Integration
14
XML as a Medium for Application Integration

• XML is a universal standardsbased format for
  representing data
• Its specifications and development are not
  owned/controlled by any specific organization
• Its most significant application is its ability
  to serve as a universal platform/vendorindependen
  t data interchange format
• Platforms/applications capable of processing XML
  data can be integrated to share and exchange data

15
ADO.NET and Application Integration

• The ADO.NET XML integration features facilitate
  true application integration
• The ability to serialize a Datasets data and
  schema to XML makes it possible for other
  XML-enabled applications/platforms to
• Access the data in a .NET applications database
• Interpret the structure of the data in a .NET
  applications database
• The abilities to merge ADO.NET Datasets and use a
  source XML document/stream to fill a Dataset make
  it possible to
• Insert/update/delete data in a .NET applications
  database using XML generated by an external
  application/platform

16
Sample Scenario www.buy-book.net
www.buy-book.net
www.store1.com (Unix/DB2)
www.buy-book.net
www.Publisher1.com (Linux/Oracle)
www.store1.com (Windows/SQL 2000)
An ASP.NET Web site to browse titles, compare
prices offered by participating vendors, and
purchase books
(Unix/Sybase)
Current catalog
(Windows/.NET/ SQL 2000)
Updated catalog (price, days to deliver)
Titles, stores, price, and days to deliver info
Orders
Schema S\service
New titles
Store catalog update service
New titles schema
Catalog schema
Order info
Publisher catalog update service
Buy-book.net DB (SQL Server 2000)
Order dispatch service
17
Buy-book.net Database Schema

• Schema structures of the Titles and TitleInfo
  tables in the Buy-book.net database
• These tables will be referenced in the subsequent
  slides of this scenario

ISBN VarChar(15)
Titles
PKey
TitleInfo
Title VarChar(50)
ISBN VarChar(15)
PKey
StoreID Int
Price money
DaysToDeliver Int
18
Sample Scenario The Publisher Catalog Update
Service

• Accesses a Web Service exposed by a publisher to
  obtain information about new titles and add new
  records to the Titles table in the Buy-book.net
  database.
• The Titles table contains two columns (ISBN and
  Title). The ISBN column is the Primary key.
• The publishers Web Service returns the list of
  new titles as well-formed XML that is compliant
  with the XSD schema definition of the structure
  of the Titles table.
• The XML returned by the publishers Web Service
  is used to add new records to the Titles table.

19
Sample Scenario The Publisher Catalog Update
Service (2)

• Steps executed by the Buy-book.net publisher
  catalog update service to add new records to the
  Titles table
• Instantiates a SqlDataAdapter by specifying a
  non-row returning query to select all columns in
  the Titles table
• Uses the SqlDataAdapter to fill an empty Dataset
  with schema information that reflects the
  structure of the Titles table
• Syncs the Dataset with an XmlDataDocument
• Loads the XML Data returned by the publishers
  Web Service into the XmlDataDocument (this will
  add the data to the Titles DataTable in the
  Dataset)
• Uses the Dataset and the SqlDataAdapter to update
  the Titles table in the database

20
Sample Code The Publisher Catalog Update Service

• Dim cn As SqlConnection
• cn New SqlConnection("ltConnection Stringgt")
• Dim dsTitles As New DataSet()
• Dim daTitles As New SqlDataAdapter("Select from
  Titles where 12", cn)
• Dim cmdBuilderTitles As New SqlCommandBuilder(daTi
  tles)
• cn.Open()
• dsTitles.DataSetName "NewTitles
• daTitles.FillSchema(dsTitles, SchemaType.Source,
  "Titles")
• Dim NewTitlesDoc As New XmlDataDocument(dsTitles)
• NewTitlesDoc.Load("c\buybookspublishers\publisher
  1\newtitles\newtitles.xml")
• daTitles.Update(dsTitles, "Titles")
• cn.Close()

21
Sample Scenario The Store Catalog Update Service

• Uses XML to provide stores with a listing of
  their current Buy-book.net catalog
• The stores return their updated Buy-book.net
  catalog as well-formed XML that is compliant
  with the XSD schema definition of the structure
  of the TitleInfo table
• Columns ISBN, StoreID, Price, DaysToDeliver
• Primary Key ISBN StoreID
• Uses the XML data returned by the stores to
  update their catalog in the Buy-book.net database
  (modifies existing store TitleInfo records adds
  new store TitleInfo records)

22
Sample Scenario The Store Catalog Update Service
(2)

• Steps executed by the Buy-book.net store catalog
  update service to update the TitleInfo table
• Instantiates a SqlDataAdapter by specifying a
  query to return all TitleInfo records for the
  store whose catalog has to be updated
• Fills an empty Dataset with schema information
  that reflects the structure of the TitleInfo
  table
• Uses the SqlDataAdapter to fill the Dataset with
  data that reflects the current catalog of the
  store
• Instantiates a second Dataset and fills it with
  the schema of the TitleInfo table and the updated
  data contained in the XML returned by the store
• Merges the first Dataset with the second to
  reflect the store updates
• Uses the Dataset and the SqlDataAdapter to update
  the TitleInfo table in the database

23
Sample Code The Store Catalog Update Service

• Dim cn As SqlConnection
• cn New SqlConnection("ltConnectionStringgt")
• Dim dsTitleInfo As New DataSet()
• Dim daTitleInfo As New SqlDataAdapter("Select
  from TitleInfo where StoreID1", cn)
• Dim cmdBuilderTitleInfo As New SqlCommandBuilder(d
  aTitleInfo)
• cn.Open()
• dsTitleInfo.DataSetName "BookStoreCatalog"
• dsTitleInfo.ReadXmlSchema("c\buybooks\schemas\tit
  leinfo.xsd")
• daTitleInfo.Fill(dsTitleInfo, "TitleInfo")
• Dim dsUpdatedTitleInfo As New DataSet()
• dsUpdatedTitleInfo.ReadXmlSchema("c\buybooks\sche
  mas\titleinfo.xsd")
• dsUpdatedTitleInfo.ReadXml("c\buybooksbookstores\
  Store1\catalog\catalog.xml")
• dsTitleInfo.Merge(dsUpdatedTitleInfo, False,
  MissingSchemaAction.Ignore)
• daTitleInfo.Update(dsTitleInfo, "TitleInfo")

24
Scenario 2 Transforming Dataset Data Using XSLT
25
Applications of Using XSLT to Transform Dataset
Data

• Widely used for data presentation purposes in an
  ASP.NET application
• Typically used when
• Out-of-the box ASP.NET data bound server controls
  do not address presentation requirements (e.g.
  read-only hierarchical grid control)
• Desired presentation format can be easily
  implemented by transforming the data to custom
  HTML

26
Applications of Using XSLT to Transform Dataset
Data (2)

• Can also be used to
• Transform Dataset data to alternate presentation
  formats (CSV files, fixed length text files,
  etc.)
• Transform data to an alternate XML representation
  (interchange data with an external third-party
  application)
• Get a window (subset) of Dataset data

27
Programming Steps Transforming Dataset Data
Using XSLT

• Fill the Dataset using a DataAdapter
• Use the Datasets GetXml method to load the XML
  representation of the data into an XmlDocument
  object instance
• Instantiate an XslTransform object using the
  desired XSLT style sheet
• Call the Transform method of the XslTransform
  object to execute the XSLT transformation

28
Sample Code Transforming Dataset Data in an
ASP.NET Web Form
Dim ds as DataSet Dim cn as SqlConnection Dim da
as SqlDataAdapter 'Code to establish db
connection, instantiate the DataAdapter and 'fill
the DataSet Dim doc As New XmlDocument() doc.Load
Xml(ds.GetXml()) Dim xslTran As
XslTransform New XslTransform() xslTran.Load("ltP
ath to XSLT Style sheetgt") Response.ContentType
"text/html" xslTran.Transform(doc, Nothing,
Response.OutputStream)
29
Scenario 3 Executing XPath Queries Against
Dataset Data
30
Applications of Executing XPath Queries Against
DataSet Data

• Implicit application
• XPath queries are used in XSLT style sheets to
  specify location paths for XSLT templates
• Explicit application
• Query/filter data contained in a 3 level deep
  hierarchical Dataset using conditional query
  expressions that reference columns 2 levels
  (grand-parent/grand-children columns) up/down the
  Dataset hierarchy

31
Sample Scenario Using XPath to Query Dataset Data

• Sample Dataset

dsNorthwind
DataTableCollection
Customers
Orders
OrderDetails
DataRelationCollection
CustomerOrders
OrdersOrderDetails
32
Sample Scenario Using XPath to Query Dataset
Data (2)

• Sample query Find the number of times CustomerID
  VINET has placed an order for ProductID 70
• Requires access to data in the Orders and the
  OrderDetails tables (Parent Child)
• Relational solution
• Dim datview As New DataView(dsNorthwind.Tables("O
  rderDetails"))
• datview.RowFilter "ProductID 70 and
  Parent.CustomerID'VINET'"
• XPath solution
• //OrdersCustomerID'VINET'/OrderDetailsProductI
  D70

33
Sample Scenario Using XPath to Query Dataset
Data (3)

• Sample query Find the names of customers in the
  UK who placed orders for ProductID 70
• Requires access to data in the Customers and the
  OrderDetails tables (Grand-parent Grand-child)
• Relational solution
• No straight-forward relational solution (for
  instance, you cannot do the following)
• Dim datview As New DataView(dsNorthwind.Tables("Or
  derDetails"))
• datview.RowFilter "ProductID 70 and " _
• " Parent.Parent.Country 'UK' "
• XPath solution
• //OrderDetailsProductID70/parentnode()/parent
  node()/CompanyName../Country'UK'"

34
Programming Steps Executing XPath Queries on
Dataset Data

• Fill the Dataset using a DataAdapter
• Synchronize the Dataset with an XmlDataDocument
• Use one of the following methods to execute the
  XPath query
• Execute the SelectNodes or SelectSingleNode
  methods of the XmlDataDocument object by
  specifying the required XPath query as the query
  string parameter.
• Execute the CreateNavigator method of the
  XmlDataDocument to create an XPathNavigator
  object instance. Use the XPathNavigator to
  execute the XPath query (faster).

35
Sample Code Executing an XPath Query on Dataset
Data Using an XPathNavigator

• 'Code to establish db connection, instantiate the
  DataAdapter(s) and
• 'fill the DataSet
• 'Instantiate and synchronize an XmlDataDocument
  with the DataSet
• Dim dataDoc As New System.Xml.XmlDataDocument(dsNo
  rthwind)
• 'Use an XPathNavigator object to execute the
  XPath query
• Dim nav As System.Xml.XPath.XPathNavigator
  dataDoc.CreateNavigator()
• 'Use an XPathNodeIterator object to access the
  results
• Dim iter As System.Xml.XPath.XPathNodeIterator
• iter nav.Select("ltXPath querygt")
• Do While iter.MoveNext
• System.Diagnostics.Debug.WriteLine(iter.Curre
  nt.Value)
• Loop

36
Sample Code Executing an XPath Query on Dataset
Data Using the SelectNodes Method

• 'Code to establish db connection, instantiate the
  DataAdapter(s) and
• 'fill the DataSet
• 'Instantiate and synchronize an XmlDataDocument
  with the DataSet
• Dim dataDoc As New System.Xml.XmlDataDocument(dsNo
  rthwind)
• 'Use the SelectNodes method of the
  XmlDataDocument to execute
• 'the XPath query
• nlist dataDoc.SelectNodes(ltXPath querygt")
• For Each node In nlist
• System.Diagnostics.Debug.WriteLine(node.Inn
  erText)
• Next

37
Scenario 4 Relational Views of Hierarchical Data
38
Relational View of Hierarchical Data

• A relational view of hierarchical data can be
  obtained by
• Using the ReadXml method of the Dataset
• Synchronizing an empty Dataset with an
  XmlDataDocument and loading the XmlDataDocument
  with XML data
• Applications
• Data binding (enables visual editing of XML data
  using a custom UI)
• Relational data access to hierarchical data (use
  of the ADO.NET Dataset API to access/manipulate
  hierarchical data)
• Obtain a relational window to access a subset of
  the hierarchical data

39
Relational Window of Hierarchical Data

• Can be used to obtain a relational view of a
  subset of the hierarchical data
• The subset of data used to fill the Dataset is
  determined by its schema
• Use the ReadXml method of the Dataset if you do
  not need to
• Update the data
• or
• Preserve the original hierarchical structure of
  the source XML when persisting changes made using
  the Dataset API and/or data bound controls

40
Relational Window of Hierarchical Data (2)

• Use an XmlDataDocument if you need to preserve
  the original hierarchical structure of the source
  XML when persisting changes made using the
  Dataset API and/or data bound controls

41
Sample Code Relational Window of Hierarchical
Data

• Dim SuppliersDS as DataSet
• Dim dataDoc as XmlDataDocument
• Private Sub DisplaySupplierData()
• 'Suppliers.xsd contains the XSD schema
  definition
• 'for the structure of the Suppliers table
• SuppliersDS.ReadXmlSchema("Suppliers.xsd")
• dataDoc New XmlDataDocument(SuppliersDS)
• 'Northwind.xml is a persisted snapshot of the
• 'Customers, Orders, OrderDetails, Products and
  Suppliers tables
• 'in the Northwind database
• dataDoc.Load("Northwind.xml")
• SuppliersGrid.DataSource SuppliersDS
• End Sub
• Private Sub UpdateSupplierData()

42
Scenario 5Creating a Repository of Frequently
Used Dataset Schemas
43
Filling Dataset Schemas

• Filling a Dataset with schema information can be
  done by
• Manually fabricating the schema
• Using the FillSchema method of the DataAdapter
• Using the ReadXmlSchema method of the Dataset
  object
• The manual option is generally code intensive and
  susceptible to errors
• The FillSchema option is accurate, but requires
  additional roundtrips to the data source
• The ReadXmlSchema method of the Dataset can be
  used efficiently when a repository of frequently
  used Dataset schemas exists

44
Persisting Dataset Schemas

• The WriteXml method of the Dataset object can be
  used to persist a Datasets schema information in
  XSD format
• A schema repository (e.g. a folder on a server,
  ASP.NET cache engine, or a database) can be
  created to store frequently used (rarely
  changing) Dataset schemas
• A routine can be created to combine the accuracy
  of the DataAdapters FillSchema method and the
  persistence capability of the Datasets
  WriteXmlSchema method to create a repository of
  frequently used Dataset schemas
• The ReadXml method of the Dataset object can then
  be used to fill a Dataset with schema information
  using a schema persisted in the repository

45
Scenario 6 Returning Datasets from ASP.NET Web
Forms and Web Services
46
Returning Datasets from an ASP.NET Web Form

• Use the WriteXml method to return a Datasets
  data as XML
• Example ds.WriteXml(Response.OutputStream)
• Control the format of the returned XML using the
  XmlWriteMode parameter of the WriteXml method
• Use the WriteXmlSchema method to return a
  Datasets schema in XSD format
• Example ds.WriteXmlSchema(Response.OutputStream
  )

47
Returning DataSets from an ASP.NET Web Service

• The Microsoft Diffgram format is used by default
  when a Dataset is returned directly by a method
  of an ASP.NET Web Service
• To return a Datasets data as regular well-formed
  XML
• Use the GetXml method to return a Datasets data
  as a regular well-formed XML string
• Use the returned string to load an XmlDocument
  object instance (the LoadXml method)
• Return the XmlDocument

48
Returning Datasets from an ASP.NET Web Service (2)

• To return a Datasets schema as regular
  well-formed XML
• Use the GetXmlSchema method to return a Datasets
  schema as a well-formed XML string (XSD format)
• Use the returned string to load an XmlDocument
  object instance (the LoadXml method)
• Delete the XmlDocuments XML prolog node using
  RemoveChild DOM API
• Return the XmlDocument

49
Scenario 7 Programmatically Converting XDR
Schemas to XSD Schemas

• The ReadXmlSchema method of the Dataset object
  can be used to fill a Datasets schema using
  either an XDR or an XSD schema source
• The WriteXmlSchema method of the Dataset object
  serializes a Datasets schema only in the XSD
  format
• These two methods can be used to programmatically
  convert an XDR schema into an XSD schema
• Dim ds as New DataSet()
• ds.ReadXmlSchema("Titles.xdr")
• ds.WriteXmlSchema("Titles.xsd")

50
Scenario 8 Programmatically Generating XSD
Schemas from XML Instance Documents

• Can be used to generate basic XSD schemas for XML
  instance documents that do not have associated
  schemas
• Use the ReadXml and WriteXmlSchema methods if you
  wish to work with the data before generating the
  schema
• Use the InferXmlSchema and WriteXmlSchema methods
  if you only need to infer and generate an XSD
  schema for an XML document
• Dim ds as New DataSet()
• ds.InferXmlSchema("Titles.xml", Nothing)
• ds.WriteXmlSchema("Titles.xsd")

51
Scenario 9 Generating and Populating SQL 2000
Database Tables Using Manually Fabricated Datasets

• Manually fabricate a Dataset
• Serialize its schema using the WriteXmlSchema
  method
• Serialize its data using the WriteXml method
• Alter the generated Schema to add SQLXML mapping
  annotations
• The SQL 2000 XML Bulk Load component can use the
  generated XSD schema and XML data file to create
  and populate tables in a SQL 2000 database that
  map to the Datasets manually fabricated tables

52
Summary

• Took a quick overview of the core ADO.NET
  components
• Reviewed implicit uses of XML in ADO.NET
• Identified the categories of the explicit ADO.NET
  XML integration API
• Briefly examined the XmlDataDocument class and
  its integration with the ADO.NET Dataset
• Examined scenarios to demonstrate practical
  applications of the ADO.NET XML integration
  features

53

• Thank you for joining us for todays Microsoft
  Support
• WebCast.
• For information about 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 feedback_at_microsoft.com and include
• Support WebCasts in the subject line.