Building a generic dataaccess layer with SQLXML and .NET

1
Building ageneric data-access layerwith SQLXML
and .NET

• Alex Thissen
• Twice IT Training

2
Agenda

• Generic data-layers
• Introduction to SQLXML
• DataSets and XML
• Combining SQLXML and DataSets
• Coding of generic data-layer
• Using data-layer in applications

3
Generic data-layers

• Data traveling between layers has certain format
• XML
• DataSets or typed DataSets
• Objects
• Data-access layers often contain largely same
  code
• Tedious and repititous to write
• Essence is generalizing retrieval and storage
  code
• Write once, use many times
• Usually involves mapping data-container to
  relational model
• O/R Mappers perform these actions for objects
• Other data carriers possible
• Use of generalized constructions may mean
  specific details cannot be taken into account

4
What is SQLXML?

• SQLXML provides XML support to SQL Server
• Retrieve data from database as XML
• Store XML directly into tables
• Expose stored procedures as SOAP Web Services
• Included in initial release of SQL Server 2000
• Also available for SQL Server 7.0 as Technology
  Preview
• Loads of extra functionality from Web Releases
• Release 1.0 through 3.0 available
• Current release is 3.0 Service Pack 2
• This session assumes WR3.0 and up

5
Retrieving XML from SQL Server

• SQLXML offers many possibilities to retrieve data
  from SQL Server as XML
• Additions to SQL FOR XML AUTO, RAW, EXPLICIT
• Annotated XSD Schemas
• Regular XSD Schemas describe structure of XML
• Schemas contain extra attributes defining where
  data is to be retrieved from taken
• XML Templates are like stored procedures
• Define parameters to be used
• Combines XML from many sources and builds
  resultant XML defined in template

6
DEMO

• Retrieving data using Transact SQL

7
Storing XML in SQL Server

• XML is stored into one or more database tables,
  NOT as a complete string
• Bulkloading XML stores large data fragments based
  upon a XSD schema
• Useful for importing data from XML
• Not all mechanisms for retrieval can be used for
  saving
• Only annotated schemas can be used to store data
• DiffGrams allow updates, deletes and inserts in
  one XML document

8
Working with SQLXML data

• Retrieving and storing XML is possible through
• SQLXML Managed classes for .NET
• ActiveX Data Objects (ADO)
• Combination with Internet Information Server

9
SQLXML IIS Configuration

• Special MMC Snap-in for administering and
  configuring SQLXML for use with Internet
  Information Server
• Virtual directories map to SQLXML functionality
• Main items of configuration
• Define data source and security settings
• Which items are allowed to be used
• Virtual names appear as subdirectories in url
• Map to XSD schemas
• Map to template directory
• Map stored procedures to SOAP endpoints
• Direct access to BLOBs stored in database tables

10
DEMO

• Configuring IIS for SQLXML

11
Annotated XSD Schemas

• XSD schema defines structure and types of XML
  data
• Extra attributes from SQLXML namespace are added
  as annotations
• Provide link to relational data structure
• Describe what elements and attributes are to be
  retrieved from what tables
• Retrieve data based on schema with XPath
  expressions
• Limitations apply
• Not all XPath functions can be performed
• Set of allowed expressions is small

12
DEMO

• Annotated XML Schemas

13
XML Templates

• XML Templates are XML files that
• Act as template for layout of resultant XML
• Hold one or more ltsqlquerygt elements
• Queries are of type
• SELECT FOR XML statements
• EXEC stored procedures
• XPath expression for annotated schema
• Templates can be parametrized
• Parameters are declared with ltsqlparamgt and are
  untyped
• Define a string literal for NULL values
• Transform XML with XSLT documents
• Applied at server by specifying sqlxsl attribute
  at root element
• Applied at client with processing directive
  lt?xml-stylesheet ?gt

14
DEMO

• Using XML Templates

15
Access data using SOAP

• Team up SQLXML with Internet Information Server
• Use SOAP protocol to
• Execute stored procedures
• Retrieve data from templates
• Return data as
• Standard XML
• Single or multiple DataSets result includes
  schemas plus DiffGram for data

16
SQLXML Managed Classes

• SQLXML comes with managed classes
• SqlXmlCommand plus SqlXmlParameter
• SqlXmlAdapter
• SqlXmlException
• Allows execution of SQLXML statements, XML
  templates
• SqlXmlCommand object
• Has a extra properties compared to other Command
  classes
• Executes a command or returns a Stream or
  XmlReader

17
Managed classes hints and tips

• Do not forget to include provider in connection
  string
• Will result in a multi-step OLE-DB error
• Extract error message from SqlXmlException

public static string ExtractSqlXmlException(SqlXml
Exception ex) string message MemoryStream
stream new MemoryStream() stream
(MemoryStream)ex.ErrorStream message
System.Text.ASCIIEncoding.ASCII.GetString(stream.T
oArray()) return message
18
DataSets and XML

• Two faces of same thing DataSet and XML
• Turn DataSet into XML with
• GetXml(), returns a string
• WriteXml(), will store XML as file at location
  specified
• From XML to DataSet with ReadXml()
• Reads from Stream, string, XmlReader or
  TextReader
• XML must be of correct form
• XmlReadMode specifies what XML contains
• DiffGrams store both data and changes

19
DEMO

• The XML side of DataSets

20
Combining SQLXML and DataSets

• Ingredient 1 Correctly formed XML
• Retrieved through annotated XML Schemas for
  optimal control over structure and mapping from
  XML to table/column names
• Ingredient 2 Typed DataSet
• Designer available in Visual Studio .NET
• Native support for reading XML into DataSet
• Typed DataSets already have a defining XSD Schema
• Ingredient 3 XML Templates
• Templates act as views for retrieval of XML data
• Parameters allow retrieval of subsets of data
• Some .NET parameter types need to be "adjusted",
  because XSD/SQLXML types are different (Guid and
  DateTime)

21
Combining (cont'd)

• Ingredient 4 SQLXML Managed classes
• Programmatic control over retrieval of XML with
  templates
• Storage of DataSets with SqlXmlAdapter takes a
  DataSet, extracts DiffGram and handles changes
• Ingredient 5 Mapping file
• Defines connections
• Combines DataSet schemas and DataSet types

22
Tweaking XSD schemas

• Schemas designed with DataSet designer do not
  work for SQLXML by default
• Make following adjustments
• Root element should have sqlis-constant"1"
  defined
• Specify datatypes for datetime and guids
• Complex type for rows should be xssequence (not
  xschoice)
• Specify table and column names if not same
• NULLable data returns no elements use
  nillable"true"
• For multiple tables in DataSet with one to many
  relationship
• Add ltappinfogt element defining parent-child
  relationship

23
Generic data-layer

• Class for storage and retrieval
  SqlXmlObjectProvider
• Configuration section handler
• Allows reading of settings from config file
• Stores connection strings and definition of
  dataset types
• Interfaces IRetrieve and IPersist
• Allows implementation of layer for special cases
• Combine with factory design pattern

24
SqlXmlObjectProvider class

• Two methods available
• GetObjectData(Type, string, Hashtable)
• Takes a DataSet type, view and parameters
• Returns instance of DataSet filled with data
• StoreObjectData(DataSet)
• Accepts any typed DataSet (polymorphically) and
  stores data
• Extra helper method to extract any
  SqlXmlException
• Stored as a stream inside exception object

25
Typical usage of data-layer

• public BlogsDataSet GetByID(Guid BlogId)
• SqlXmlObjectProvider obj new
  SqlXmlObjectProvider()
• Hashtable parameters new Hashtable()
• parameters.Add("_at_BlogID", BlogId)
• BlogsDataSet ds (BlogsDataSet)obj.GetObjectDat
  a( typeof(BlogsDataSet), "BlogByID",
  parameters)
• return ds
• public void Save(PostingsDataSet objectData)
• SqlXmlObjectProvider obj new
  SqlXmlObjectProvider()
• obj.StoreObjectData(objectData)

26
DEMO

• Walkthrough of generic data-layer code

27
Application using GDL

• Blogging application
• Three business entities Blogs, Postings and
  Comments
• Two main DataSets
• Blogs
• Postings and (nested) Comments
• Runtime components perform actions on
  SqlXmlObjectProvider objects

28
Known limitations

• Not suitable for every situation because of other
  limitations
• Might be used in prototyping scenarios
• Filtering based on complex expressions
• LIKE queries are hard to do
• Only simple comparison available
• No design-time support other than DataSet
  designer

29
Future enhancements

• Dynamic creation of XML templates
• Specify XPath expression (including parameters)
• Filter these parameters from string and build
  template
• Some workarounds for limitations of XPath
  functions
• Factory design pattern for implementing other
  classes that handle difficult cases
• Caching on configuration data

30
Questions and Answers

• ? !