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