--------------------------------------------------------------------------------------

How to use SAX2 and ADO.NET XML Support to Programmatically Generate C++ Header Type Definitions from SQL Server Table Schemas

---------------------------------

Note: This outline assumes that on your machine you have installed Windows 2000, Visual Studio 6 (with Service Pack 4), Visual Studio .NET 7 Beta 1, SQL Server 2000, and the Microsoft XML Parser (MSXML) 3.0 (You may download the parser from http://msdn.microsoft.com/code/sample.asp?url=/msdn-files/027/000/541/msdncompositedoc.xml)

---------------------------------

Guide Overview

For demonstration a SQL Server 2000 table having variously typed columns is created.

Using Visual Studio 7 .NET Beta 1 (VS7) a Visual Basic Console Application is generated. ADO.NET XML (Extensible Markup Language) -aware classes SQLConnection, SQLDataSetCommand, and DataSet are used to generate a XSD (XML Schema Definition) Schema for the SQL Server table. SAX2 (Simple API for XML) is used to parse the XSD Schema file. A Visual Basic implementation of SAX2's event handling Interface IVBSAXContentHandler transforms the XSD Schema into a corresponding C++ type-definition that is written to a generated C++ header file.

1. In a SQL Server 2000 database create a table with a number of differently typed columns and having two dummy records.

1a. Left-click your system's 'Start' menu. Select 'Programs / Microsoft SQL Server / Enterprise Manager'.

1b. (Optional) Using 'Enterprise Manager' create a new Database 'ForXMLDataTypes'.

1c. Using 'Enterprise Manager' create a new table 'tableSQLServerSchemaToCppH' having the following Design (SQL Table Schema): (See Step 1d. below, for another way to create the table)

Column Name          Data Type     Length
SqlS2kTypeChar       char          10
SqlS2kTypeFloat      float         8
SqlS2kTypeInt        int           4
SqlS2kTypeReal       real          4
SqlS2kTypeSmallint   smallint      2
SqlS2kTypeTinyint    tinyint       1
SqlS2kTypeVarchar    varchar       50

Comment: The above choice of types is arbitrary. The procedures described here do not require any particular types. However, code additions will be required to support additional types (see Step 6, below).

1d. (Optional) As an alternative to Step 1c. the table may be created using SQL Server's 'Query Analyzer' ('Start / Programs / Microsoft SQL Server / Query Analyzer'). Paste the following code snippet into a text file having extension ".sql" (without quotes):

/* paste into tableSQLServerSchemaToCppH.sql begin */

/****** Object: Table [dbo].[tableSQLServerSchemaToCppH] ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tableSQLServerSchemaToCppH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tableSQLServerSchemaToCppH]

CREATE TABLE [dbo].[tableSQLServerSchemaToCppH] (

[SqlS2kTypeChar] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SqlS2kTypeFloat] [float] NULL ,

[SqlS2kTypeInt] [int] NULL ,

[SqlS2kTypeReal] [real] NULL ,

[SqlS2kTypeSmallint] [smallint] NULL ,

[SqlS2kTypeTinyint] [tinyint] NULL ,

[SqlS2kTypeVarchar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

/* paste into tableSQLServerSchemaToCppH.sql end */

Using 'Query Analyzer':
- Open the ".sql" file ('File / Open...').

- Connect to your local SQL Server ('File / Connect...').

- Select a Database ('Query / Change Database...').

- Execute the ".sql" file ('Query / Execute').

2. Using Visual Studio 7 (.NET Beta 1) generate a new Visual Basic Console Application.

2a. Somewhere on your hard drive create a new Folder (directory) that will contain your VS7 solution, project and source files. Name the folder ManagedCode.

2b. Create a new VS7 solution: 'ManagedCode\ConsoleApplication1\ConsoleApplication1.sln'.
In VS7 Left-click the 'File' menu item. Select 'New / Project', and complete steps that generate a new 'Visual Basic Projects / Console Application'. Your project will contain a single source file: 'Module1.vb'.

3. Add to your Console Project a (COM) Reference to MsXml3.dll

3a. In 'Solution Explorer' right-click project item 'ConsoleApplication1'. Select 'Add Reference...'. In the 'COM' Tab locate and select 'Microsoft XML, v3.0' (MsXml3.dll).

4. Add to your Console Project two Classes 'ClassContentHandlerImpl.vb' and 'ClassErrorHandlerImpl.vb'.

4a. In 'Solution Explorer' right-click project item 'ConsoleApplication1'. Select 'Add / Add Class...'. In the 'Name:' field enter "ClassContentHandlerImpl.vb" (without quotes) and click 'Open'.

4b. Repeat Step 4a. using 'Name:' "ClassErrorHandlerImpl.vb" (without quotes).

5. Replace the content of file 'ClassErrorHandlerImpl.vb' with the following code:

'----- VB Source Code for ClassErrorHandlerImpl.vb begin

Option Explicit On

Option Strict On

Imports MSXML2

Public Class ClassErrorHandlerImpl

Implements MSXML2.IVBSAXErrorHandler

Public Sub localError( _

ByVal oLocator As MSXML2.IVBSAXLocator, _

ByRef strErrorMessage As String, ByVal nErrorCode As Integer) _

Implements MSXML2.IVBSAXErrorHandler.error

End Sub

Public Sub fatalError( _

ByVal oLocator As MSXML2.IVBSAXLocator, _

ByRef strErrorMessage As String, _

ByVal nErrorCode As Integer) _

Implements MSXML2.IVBSAXErrorHandler.fatalError

GeneratedCHeaderFileContent = GeneratedCHeaderFileContent & _

"*** error *** " & strErrorMessage

End Sub

Public Sub ignorableWarning(ByVal oLocator As MSXML2.IVBSAXLocator, _

ByRef strErrorMessage As String, ByVal nErrorCode As Integer) _

Implements MSXML2.IVBSAXErrorHandler.ignorableWarning

End Sub

End Class

'----- VB Source Code for ClassErrorHandlerImpl.vb end

6. Replace the content of file 'ClassContentHandlerImpl.vb' with the following code:

'----- VB Source Code for ClassContentHandlerImpl.vb begin

Option Explicit On

Option Strict On

Imports Microsoft.VisualBasic.Compatibility.VB6

Imports MSXML2

Imports System.IO

Public Class ClassContentHandlerImpl

Implements MSXML2.IVBSAXContentHandler

Private m_strTableName As String

Private m_strOutFileName As String

Private m_strOutFileContent As String

'members involved in processing logic

Private m_nDidFind1st_strQName_equal_xsd_element As Integer

Private m_bAmWithin_tag_xsd_all As Boolean

Private m_strCurntMemberName_iii As String

Private m_strCurntMemberType_iii As String

Private m_strCType As String

'These member functions are here in call-order

Public WriteOnly Property documentLocator() As MSXML2.IVBSAXLocator _

Implements IVBSAXContentHandler.documentLocator

Set

End Set

End Property

Public Sub startDocument() _

Implements MSXML2.IVBSAXContentHandler.startDocument

m_strOutFileName = ""

m_strOutFileContent = ""

m_nDidFind1st_strQName_equal_xsd_element = 0

m_strTableName = ""

m_bAmWithin_tag_xsd_all = False

m_strCurntMemberName_iii = ""

m_strCurntMemberType_iii = ""

m_strCType = ""

End Sub

Public Sub startPrefixMapping(ByRef strPrefix As String, _

ByRef strURI As String) _

Implements MSXML2.IVBSAXContentHandler.startPrefixMapping

End Sub

Public Sub startElement(ByRef strNamespaceURI As String, _

ByRef strLocalName As String, ByRef strQName As String, _

ByVal oAttributes As MSXML2.IVBSAXAttributes) _

Implements MSXML2.IVBSAXContentHandler.startElement

If "xsd:element" = strQName Then

m_nDidFind1st_strQName_equal_xsd_element = _

m_nDidFind1st_strQName_equal_xsd_element + 1

End If

Dim iii As Integer

Dim localnameiii As String

Dim valueiii As String

For iii = 0 To (oattributes.length - 1)

localnameiii = oattributes.getLocalName(iii)

valueiii = oattributes.getValue(iii)

If 1 = m_nDidFind1st_strQName_equal_xsd_element Then

If iii = 0 Then

If localnameiii = "name" Then

If "" = m_strTableName Then

m_strTableName = valueiii

debug.Assert(Not "" = m_strTableName)

m_strOutFileName = m_strTableName & ".h"

m_strOutFileContent = m_strOutFileContent & _

"/* AutoGenerated " & Now() & " */" & chr(13) & chr(10) & _

chr(13) & chr(10) & _

"/* " & m_strTableName & ".h " & " */" & chr(13) & chr(10) & _

chr(13) & chr(10) & _

"#include <wtypes.h> /* for BSTR */ " & chr(13) & chr(10) & _

chr(13) & chr(10) & _

"typedef struct tag_" & m_strTableName & chr(13) & chr(10) & _

"{ " & chr(13) & chr(10) & _

chr(13) & chr(10)

End If

Else ' => not 1 = m_nDidFind1st_strQName_equal_xsd_element

If m_bAmWithin_tag_xsd_all Then

If "name" = localnameiii Then

m_strCurntMemberName_iii = valueiii

debug.Assert(Not "" = m_strCurntMemberName_iii)

End If

If "type" = localnameiii Then

m_strCurntMemberType_iii = valueiii

debug.Assert(Not "" = m_strCurntMemberType_iii)

Select Case m_strCurntMemberType_iii

Case "xsd:string"

m_strCType = "BSTR"

Case "xsd:double"

m_strCType = "double"

Case "xsd:int"

m_strCType = "LONG"

Case "xsd:float"

m_strCType = "float"

Case "xsd:short"

m_strCType = "SHORT"

Case "xsd:unsignedByte"

m_strCType = "BYTE"

'Here, add Case statements to handle additional types

Case Else

Err.Raise(vbObjectError + 1, "ContentHandler.startElement", _

"Received unknown xsd:type")

End Select

End If

If "" <> m_strCurntMemberName_iii And "" <> m_strCType Then

m_strOutFileContent = m_strOutFileContent & _

" " & m_strCType & " " & "m_" & m_strCurntMemberName_iii & _

" ;" & chr(13) & chr(10)

m_strCurntMemberName_iii = ""

m_strCType = ""

End If

End If ' If m_bAmWithin_tag_xsd_all

End If ' If 1 = m_nDidFind1st_strQName_equal_xsd_element

Next iii

If strQName = "xsd:all" Then

m_bAmWithin_tag_xsd_all = True

End If

If strLocalName = "qu" Then

Err.Raise(vbObjectError + 1, "ContentHandler.startElement", _

"Found element <qu>")

End If

End Sub

Public Sub characters(ByRef strChars As String) _

Implements MSXML2.IVBSAXContentHandler.characters

strChars = Replace(strChars, vbLf, vbCrLf)

GeneratedCHeaderFileContent = GeneratedCHeaderFileContent & strChars

End Sub

Public Sub endElement(ByRef strNamespaceURI As String, _

ByRef strLocalName As String, ByRef strQName As String) _

Implements MSXML2.IVBSAXContentHandler.endElement

If strQName = "xsd:all" Then

If m_bAmWithin_tag_xsd_all Then

m_bAmWithin_tag_xsd_all = False

End If

End Sub

Public Sub endDocument() _

Implements MSXML2.IVBSAXContentHandler.endDocument

m_strOutFileContent = m_strOutFileContent & _

chr(13) & chr(10) & _

"} " & m_strTableName & "_t ;" & _

chr(13) & chr(10)

debug.Assert(Not "" = m_strOutFileName)

Dim dout As StreamWriter

dout = File.CreateText(m_strOutFileName)

dout.Write(m_strOutFileContent)

dout.Close()

End Sub

Public Sub endPrefixMapping(ByRef strPrefix As String) _

Implements MSXML2.IVBSAXContentHandler.endPrefixMapping

End Sub

Public Sub ignorableWhitespace(ByRef strChars As String) _

Implements MSXML2.IVBSAXContentHandler.ignorableWhitespace

End Sub

Public Sub processingInstruction(ByRef strTarget As String, _

ByRef strData As String) _

Implements MSXML2.IVBSAXContentHandler.processingInstruction

End Sub

Public Sub skippedEntity(ByRef strName As String) _

Implements MSXML2.IVBSAXContentHandler.skippedEntity

End Sub

End Class

'----- VB Source Code for ClassContentHandlerImpl.vb end

7. Replace the content of file 'Module1.vb' with the following code:

'----- VB Source Code for Module1.vb begin

Option Explicit On

Option Strict On

Imports System.Data

Imports System.Data.SQL

Imports System.IO

Imports MSXML2

Module Module1

Private GeneratedXsdFileName As String

Public GeneratedCHeaderFileContent As String

Sub Main()

GeneratedXsdFileName = "MySchema.xsd"

Dim readstr As String

Dim refMyConnection As SQLConnection

Dim refMySQLDataSetCommand As SQLDataSetCommand

Dim refMyDataSet As DataSet

Dim refDataTableCollection As DataTable()

Dim dout As StreamWriter

Dim reader As New SAXXMLReader() 'Reads the XML document

Dim contentHandler As New ClassContentHandlerImpl() 'Receives parsing events

Dim errorHandler As New ClassErrorHandlerImpl() 'Receive error events

Try '-----------------------------------

'construct a new connection

refMyConnection = New SQLConnection( _

"server=localhost;uid=sa;pwd=;database=ForXmlDataTypes" _

)

debug.assert(Not refMyConnection Is Nothing)

Console.WriteLine("Before Open: refMyConnection.State= " & _

refMyConnection.State)

Console.WriteLine("Opening the connection....")

refMyConnection.Open()

debug.assert(refMyConnection.State = DBObjectState.Open)

Console.WriteLine("After Open(): refMyConnection.State=" & _

refMyConnection.State)

'construct a new SQLDataSetCommand

Console.WriteLine("Before 'New SQLDataSetCommand()'")

refMySQLDataSetCommand = New SQLDataSetCommand( _

"select * from tableSQLServerSchemaToCppH", refMyConnection)

debug.assert(Not refMySQLDataSetCommand Is Nothing)

'Close the connection explicitly: it is no longer needed

refMyConnection.Close()

Console.WriteLine("Closed the Connection.")

debug.assert(refMyConnection.State = DBObjectState.Closed)

Console.WriteLine("After Close(): refMyConnection.State=" & _

refMyConnection.State)

'construct a new DataSet

Console.WriteLine("Before 'New DataSet()'")

refMyDataSet = New DataSet()

debug.assert(Not refMyDataSet Is Nothing)

'fill the dataset with the table's schema

Console.WriteLine("Before '.FillDataSetSchema()'")

refDataTableCollection = refMySQLDataSetCommand.FillDataSetSchema( _

refMyDataSet, Data.SchemaType.Source, _

"tableSQLServerSchemaToCppH")

debug.assert(Not refDataTableCollection Is Nothing)

Console.WriteLine("")

Console.WriteLine(refMyDataSet.XmlSchema) '.XmlSchema is the Xsd Schema

Console.WriteLine("")

'create and write to a .xsd file

Console.WriteLine("Before 'dout.WriteLine()'")

dout = File.CreateText(GeneratedXsdFileName)

dout.WriteLine(refMyDataSet.XmlSchema)

dout.Close()

' begin SAX2 processing

Console.WriteLine("Before 'SAXXMLReader' stuff")

GeneratedCHeaderFileContent = ""

reader.contentHandler = contentHandler 'They work together

reader.errorHandler = errorHandler 'They also work together

reader.parseURL(GeneratedXsdFileName) 'Parse the document

Catch '-----------------------------------

debug.Assert(False)

Console.WriteLine("Couldn't Open Connection to " + _

refMyConnection.ConnectionString)

End Try

Console.WriteLine("Press <Enter> to cleanup and exit ....")

readstr = Console.ReadLine()

' cleanup '-----------------------------------

If Not reader Is Nothing Then

reader.contentHandler = Nothing

reader.errorHandler = Nothing

reader = Nothing

End If

If Not contentHandler Is Nothing Then

contentHandler = Nothing

End If

If Not errorHandler Is Nothing Then

errorHandler = Nothing

End If

If Not refDataTableCollection Is Nothing Then

refDataTableCollection = Nothing

End If

If Not refMyDataSet Is Nothing Then

refMyDataSet = Nothing

End If

If Not refMySQLDataSetCommand Is Nothing Then

refMySQLDataSetCommand = Nothing

End If

If Not refMyConnection Is Nothing Then

If refMyConnection.State = DBObjectState.Open Then

'Close the connection explicitly

refMyConnection.Close()

Console.WriteLine("Closed the Connection.")

debug.assert(refMyConnection.State = DBObjectState.Closed)

Console.WriteLine("After Close(): refMyConnection.State=" & _

refMyConnection.State)

End If

refMyConnection = Nothing

End If

End Sub

End Module

'----- VB Source Code for Module1.vb end

8. Rebuild and execute 'ConsoleApplication1' and verify generated '.xsd' (XSD Schema) and '.h' (C++ header) files.

8a. In the 'bin' directory under the 'ConsoleApplication1' folder you should find XML Schema 'MySchema.xsd':

<xsd:schema id="NewDataSet" targetNamespace="" xmlns="" xmlns:xsd="http://www.w3.org/1999/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

<xsd:element name="tableSQLServerSchemaToCppH">

<xsd:complexType content="elementOnly">

<xsd:all>

<xsd:element name="SqlS2kTypeChar" minOccurs="0" type="xsd:string"/>

<xsd:element name="SqlS2kTypeFloat" minOccurs="0" type="xsd:double"/>

<xsd:element name="SqlS2kTypeInt" minOccurs="0" type="xsd:int"/>

<xsd:element name="SqlS2kTypeReal" minOccurs="0" type="xsd:float"/>

<xsd:element name="SqlS2kTypeSmallint" minOccurs="0" type="xsd:short"/>

<xsd:element name="SqlS2kTypeTinyint" minOccurs="0" type="xsd:unsignedByte"/>

<xsd:element name="SqlS2kTypeVarchar" minOccurs="0" type="xsd:string"/>

</xsd:all>

</xsd:complexType>

</xsd:element>

<xsd:element name="NewDataSet" msdata:IsDataSet="True">

<xsd:complexType>

<xsd:choice maxOccurs="unbounded">

<xsd:element ref="tableSQLServerSchemaToCppH"/>

</xsd:choice>

</xsd:complexType>

</xsd:element>

</xsd:schema>

8b. In the 'bin' directory under the 'ConsoleApplication1' folder you should find C++ Header 'tableSQLServerSchemaToCppH.h':

/* AutoGenerated .... */

/* tableSQLServerSchemaToCppH.h */

#include <wtypes.h> /* for BSTR */

typedef struct tag_tableSQLServerSchemaToCppH

{

BSTR m_SqlS2kTypeChar ;

double m_SqlS2kTypeFloat ;

LONG m_SqlS2kTypeInt ;

float m_SqlS2kTypeReal ;

SHORT m_SqlS2kTypeSmallint ;

BYTE m_SqlS2kTypeTinyint ;

BSTR m_SqlS2kTypeVarchar ;

} tableSQLServerSchemaToCppH_t ;

9. Comments.

9a. It is interesting to contrast 'struct tag_tableSQLServerSchemaToCppH' with the corresponding record structure used by the OLE-DB Accessor to SQL table 'tableSQLServerSchemaToCppH'. That Accessor structure is:

{

TCHAR m_SqlS2kTypeChar[11];

double m_SqlS2kTypeFloat;

LONG m_SqlS2kTypeInt;

float m_SqlS2kTypeReal;

SHORT m_SqlS2kTypeSmallint;

BYTE m_SqlS2kTypeTinyint;

TCHAR m_SqlS2kTypeVarchar[51];

}

Use of 'struct tag_tableSQLServerSchemaToCppH' presumes a copy of SQL Server Table records formatted as XML streams. The data handling code (not shown here) will issue calls to ::SysAllocString() to allocate and initialize the BSTR's.

9b. Additional information on XML parsing and SAX2 may be found in the 'Microsoft MSXML SDK 3.0' which may be downloaded from http://msdn.microsoft.com/code/sample.asp?url=/msdn-files/027/000/542/msdncompositedoc.xml

Done.

Rafael Pena

rpenaphd@worldnet.att.net

3/14/2001

Keywords: HowTo