The Problem
Converting an ADO recordset into a .NET DataSet is straightforward, the following code will achieve this
Dim da As New System.Data.OleDb.OleDbDataAdapter<br/> da.Fill(ds, rs, "ADODB.RecordSet")
where ds is a DataSet and rs is an ADO Recordset
So you would think that going from a DataSet to an ADO Recordset would be just as simple. Wrong ! This conversion is not built into .NET 1 or 2, the only information I could find on how to do this is article which utilises and XSL stylesheet to transform the data. So I modified the code from this article and have coded up an object which does the conversion.
In addition I can also access the ADO recordset XML as a string from this object. Why would I want to do this ? Well, if you want to pass back an ADO recordset as part of web service results you can’t, you get an error as the web service code can’t serialise the ADO recordset. The way round this is to pass the ADO recordset XML representation as a string over the web service and convert it back into and ADO recordset at the receiving end (this could be a legacy ASP app accessing the web-service via SOAP or XMLHTTP).
The Solution
'--------------------------------------------------------------------------
' File : DataConversion.vb
'
' Author : DJ
'
' Version : <VSS Version Stamp>
'
' Purpose : Routines to convert DataSet to ADO Recordset etc etc etc.
'
' This object uses and XSLT to transform dataset into ADO recordset. I have
' modified code from MS KB article to avoid using a temporary file for output.
' I build up a string of the XML representation of the ADO recordset which can
' then be sent back to the caller 'as xml' which can be stuffed into an ADO recordset
'
' It is a shame that MS did not include Dataset<->Recordset conversion as part
' of .NET n or .NET n+1
'
' Modification History:
' Taken from http://support.microsoft.com/kb/316337
'
'--------------------------------------------------------------------------
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Xml
Imports System.Xml.XPath
Imports System.Xml.Xsl
Imports System.IO
Public Class DataConversion
Private xmlstr As String
Private quote = "'"
Private error_message As String
Public Sub New()
Me.error_message = ""
Me.xmlstr = ""
End Sub
Public Property ErrorMessage()
Get
Return Me.error_message
End Get
Set(ByVal Value)
'NOP
End Set
End Property
'*************************************************************************
' Class Name : ConvertToRs
' Description : This class converts a DataSet to a ADODB Recordset.
'**************************************************************************
'**************************************************************************
' Method Name : GetADORS
' Description : Takes a DataSet and converts into a Recordset. The converted
' ADODB recordset is saved as an XML file. The data is saved
' to the file path passed as parameter.
' Output : The output of this method is long. Returns 1 if successfull.
' If not throws an exception.
' Input parameters:
' 1. DataSet object
' 2. Database Name
' 3. Output file - where the converted should be written.
'**************************************************************************
Public Function GetXML()
Return Me.xmlstr
End Function
'----------------------------------------------------------------
Public Function GetRS() As ADODB.Recordset
'----------------------------------------------------------------
' Purpose
' Turn XML string into ADO Recordset
'
' Input Params
' Returns
'
'----------------------------------------------------------------
Dim strm As New ADODB.Stream
Dim res_rs As New ADODB.Recordset
'-------------------------------------------
' Open a stream and write XML String to it |
'-------------------------------------------
strm.Open()
strm.WriteText(Me.xmlstr)
'----------------
' Reset positin |
'----------------
strm.Position = 0
'-------------------------
' Read it into recordset |
'-------------------------
res_rs.Open(strm)
GetRS = res_rs
End Function
Public Function ConvertDSToXML(ByVal ds As DataSet, ByVal xslfile As String) As Boolean
Me.xmlstr = ""
'Create an xmlwriter object, to write the ADO Recordset Format XML
Try
' Dim xwriter As New XmlTextWriter(outputfile, System.Text.Encoding.Default)
'call this Sub to write the ADONamespaces to the XMLTextWriter
WriteADONamespaces()
'call this Sub to write the ADO Recordset Schema
WriteSchemaElement(ds)
Dim TransformedDatastrm As New MemoryStream
TransformedDatastrm = TransformData(ds, xslfile)
'Pass the Transformed ADO REcordset XML to this Sub
'to write in correct format.
HackADOXML(TransformedDatastrm)
Return True
Catch ex As Exception
'Returns error message to the calling function.
Me.error_message = ex.Message & " - " & ex.ToString
Return False
End Try
End Function
Private Sub WriteADONamespaces()
'The following is to specify the encoding of the xml file
'WriteProcessingInstruction("xml", "version='1.0' encoding='ISO-8859-1'")
'The following is the ado recordset format
'<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
' xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
' xmlns:rs='urn:schemas-microsoft-com:rowset'
' xmlns:z='#RowsetSchema'>
' </xml>
'Write the root element
WriteStartElement("xml", "")
'Append the ADO Recordset namespaces
WriteAttributeString("xmlns", "s", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
WriteAttributeString("xmlns", "dt", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
WriteAttributeString("xmlns", "rs", "urn:schemas-microsoft-com:rowset")
WriteAttributeString("xmlns", "z", "#RowsetSchema")
WriteEndStartElement()
End Sub
Private Sub WriteSchemaElement(ByVal ds As DataSet)
'ADO Recordset format for defining the schema
' <s:Schema id='RowsetSchema'>
' <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
' </s:ElementType>
' </s:Schema>
'write element schema
WriteStartElement("s", "Schema")
WriteAttributeString("id", "RowsetSchema")
WriteEndStartElement()
'write element ElementTyoe
WriteStartElement("s", "ElementType")
'write the attributes for ElementType
WriteAttributeString("name", "row")
WriteAttributeString("content", "eltOnly")
WriteAttributeString("rs:updatable", "true")
WriteEndStartElement()
WriteSchema(ds)
'write the end element for ElementType
WriteFullEndElement("s", "ElementType")
'write the end element for Schema
WriteFullEndElement("s", "Schema")
End Sub
Private Sub WriteSchema(ByVal ds As DataSet)
Dim i As Int32 = 1
Dim dc As DataColumn
For Each dc In ds.Tables(0).Columns
dc.ColumnMapping = MappingType.Attribute
WriteStartElement("s", "AttributeType")
'write all the attributes
WriteAttributeString("name", dc.ToString)
WriteAttributeString("rs", "number", i.ToString)
WriteAttributeString("rs", "baseCatalog", "")
WriteAttributeString("rs", "baseTable", dc.Table.TableName.ToString)
WriteAttributeString("rs", "keycolumn", dc.Unique.ToString)
WriteAttributeString("rs", "autoincrement", dc.AutoIncrement.ToString)
WriteEndStartElement()
'write child element
WriteStartElement("s", "datatype")
'write attributes
WriteAttributeString("dt", "type", GetDatatype(dc.DataType.ToString))
WriteAttributeString("dt", "maxlength", dc.MaxLength.ToString)
WriteAttributeString("rs", "maybenull", dc.AllowDBNull.ToString)
WriteEndStartElement()
WriteFullEndElement("s", "datatype")
'write end element for datatype
'end element for AttributeType
WriteFullEndElement("s", "AttributeType")
i = i + 1
Next
dc = Nothing
End Sub
'Function to get the ADO compatible datatype
Private Function GetDatatype(ByVal dtype As String) As String
Select Case (dtype)
Case "System.Int32"
Return "int"
Case "System.DateTime"
Return "dateTime"
End Select
End Function
'Transform the data set format to ADO Recordset format
'This only transforms the data
Private Function TransformData(ByVal ds As DataSet, ByVal xslfile As String) As MemoryStream
Dim instream As New MemoryStream
Dim outstream As New MemoryStream
'write the xml into a memorystream
ds.WriteXml(instream, XmlWriteMode.IgnoreSchema)
instream.Position = 0
'load the xsl document
Dim xslt As New XslTransform
xslt.Load(xslfile)
'create the xmltextreader using the memory stream
Dim xmltr As New XmlTextReader(instream)
'create the xpathdoc
Dim xpathdoc As XPathDocument = New XPathDocument(xmltr)
'create XpathNavigator
Dim nav As XPathNavigator
nav = xpathdoc.CreateNavigator
'Create the XsltArgumentList.
Dim xslArg As XsltArgumentList = New XsltArgumentList
'Create a parameter that represents the current date and time.
xslArg.AddParam("tablename", "", ds.Tables(0).TableName)
'transform the xml to a memory stream
xslt.Transform(nav, xslArg, outstream)
instream = Nothing
xslt = Nothing
' xmltr = Nothing
xpathdoc = Nothing
nav = Nothing
Return outstream
End Function
''**************************************************************************
'' Method Name : ConvertToRs
'' Description : The XSLT does not tranform with fullendelements. For example,
'' <root attr=""/> intead of <root attr=""><root/>. ADO Recordset
'' cannot read this. This method is used to convert the
'' elements to have fullendelements.
''**************************************************************************
Private Sub HackADOXML(ByVal ADOXmlStream As System.IO.MemoryStream)
ADOXmlStream.Position = 0
Dim rdr As New XmlTextReader(ADOXmlStream)
Dim outStream As New MemoryStream
Dim wrt As New XmlTextWriter(outStream, System.Text.Encoding.Default)
rdr.MoveToContent()
'if the ReadState is not EndofFile, read the XmlTextReader for nodes.
Do While rdr.ReadState <> ReadState.EndOfFile
If rdr.Name = "s:Schema" Then
wrt.WriteNode(rdr, False)
wrt.Flush()
ElseIf rdr.Name = "z:row" And rdr.NodeType = XmlNodeType.Element Then
wrt.WriteStartElement("z", "row", "#RowsetSchema")
rdr.MoveToFirstAttribute()
wrt.WriteAttributes(rdr, False)
wrt.Flush()
ElseIf rdr.Name = "z:row" And rdr.NodeType = XmlNodeType.EndElement Then
'The following is the key statement that closes the z:row
'element without generating a full end element
wrt.WriteEndElement()
wrt.Flush()
ElseIf rdr.Name = "rs:data" And rdr.NodeType = XmlNodeType.Element Then
wrt.WriteStartElement("rs", "data", "urn:schemas-microsoft-com:rowset")
ElseIf rdr.Name = "rs:data" And rdr.NodeType = XmlNodeType.EndElement Then
wrt.WriteEndElement()
wrt.Flush()
End If
rdr.Read()
Loop
' wrt.WriteEndElement()
wrt.Flush()
'---------
' Kludge |
'---------
Dim byteArray = New Byte(CType(outStream.Length, Integer)) {}
Dim xstr As String
outStream.Position = 0
outStream.Read(byteArray, 0, outStream.Length)
xstr = System.Text.Encoding.ASCII.GetString(byteArray)
' remove trailing null
xstr = Left(xstr, Len(xstr) - 1)
Me.xmlstr &= xstr
WriteFullEndElement("xml", "")
End Sub
Private Sub WriteStartElement(ByVal tag, ByVal val)
Me.xmlstr &= "<" & tag
If (val <> "") Then Me.xmlstr &= ":" & val
End Sub
Private Sub WriteAttributeString(ByVal tag, ByVal val)
Me.xmlstr &= " " & tag & "=" & quote & val & quote
End Sub
Private Sub WriteAttributeString(ByVal tag1, ByVal tag2, ByVal val)
Me.xmlstr &= " " & tag1 & ":" & tag2 & "=" & quote & val & quote
End Sub
Private Sub WriteFullEndElement(ByVal tag, ByVal val)
Me.xmlstr &= "</" & tag
If (val <> "") Then Me.xmlstr &= ":" & val
Me.xmlstr &= ">"
End Sub
Private Sub WriteEndStartElement()
Me.xmlstr &= ">" & vbCrLf
End Sub
Public Function RsToXML(ByVal rs As ADODB._Recordset, ByRef outputXml As String) As Boolean
'----------------------------------------------------------------
' Purpose
' Convert and ADO Recordset into an XML String
' Input Params
' ADO Recordset
' Returns
' XML String
'----------------------------------------------------------------
Try
Dim streamObj As New ADODB.Stream
rs.Save(streamObj, ADODB.PersistFormatEnum.adPersistXML)
outputXml = streamObj.ReadText()
Return True
Catch ex As Exception
Me.error_message = ex.Message & " - " & ex.ToString
Return False
End Try
End Function
Public Function RStoDS(ByVal rs As ADODB._Recordset, ByRef ds As DataSet) As Boolean
Try
Dim da As New System.Data.OleDb.OleDbDataAdapter
da.Fill(ds, rs, "ADODB.RecordSet")
Return True
Catch ex As Exception
Me.error_message = ex.Message & " - " & ex.ToString
Return False
End Try
End Function
End Class
<?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"> <xsl:output method="xml" indent="yes"/> <xsl:param name="tablename"/> <xsl:template match="NewDataSet"> <rs:data> <xsl:for-each select="./node()[local-name(.)=$tablename]"> <z:row> <xsl:for-each select="@*"> <xsl:copy-of select="."/> </xsl:for-each> </z:row> </xsl:for-each> </rs:data> </xsl:template> </xsl:stylesheet>
sddsad