How to convert an ADO.NET DataSet object to an ADO Recordset object in Visual Basic .NET

Back to the top

Requirements

• Microsoft Windows XP, Windows 2000, or Windows NT 4.0 Service Pack 6a
• Microsoft Data Access Components (MDAC) 2.6 or later
• Microsoft Visual Studio .NET

• Microsoft Visual Basic .NET syntax
• ADO.NET and earlier versions of ADO
• DataSet and ADO Recordset XML formats

Back to the top

Create an XSLT File

1. In Notepad, copy and then paste the following XSLT code:

   <?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>
   					

2. Save the file as Test.xsl.

Back to the top

Create a Visual Basic .NET DLL

1. Start Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. Click Visual Basic Projects under Project Types, and then click Class Library under Templates.
4. Type ConvertDStoRS in the Name: box, and then click OK.
5. In Class1.vb, replace existing code with following code:

   Imports System.Data
   Imports System.Xml
   Imports System.Xml.XPath
   Imports System.Xml.Xsl
   Imports System.IO
   
   '**************************************************************************
   '   Class Name  : ConvertToRs
   '   Description : This class converts a DataSet to a ADODB Recordset.
   '**************************************************************************
   Public Class ConvertToRs
   
     '**************************************************************************
     '   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 GetADORS(ByVal ds As DataSet, ByVal dbname As String, ByVal xslfile As String, _
       ByVal outputfile As String) As Long
   
       '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(xwriter)
         'call this Sub to write the ADO Recordset Schema
         WriteSchemaElement(ds, dbname, xwriter)
   
         Dim TransformedDatastrm As New MemoryStream
         'Call this Function to transform the Dataset xml to ADO Recordset XML
         TransformedDatastrm = TransformData(ds, xslfile)
         'Pass the Transformed ADO REcordset XML to this Sub
         'to write in correct format.
         HackADOXML(xwriter, TransformedDatastrm)
   
         xwriter.Flush()
         xwriter.Close()
         'returns 1 if success
         Return 1
   
       Catch ex As Exception
         'Returns error message to the calling function.
         Err.Raise(100, ex.Source, ex.ToString)
       End Try
   
     End Function
   
   
     Private Sub WriteADONamespaces(ByRef writer As XmlTextWriter)
       'The following is to specify the encoding of the xml file
       'writer.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
       writer.WriteStartElement("", "xml", "")
   
       'Append the ADO Recordset namespaces
       writer.WriteAttributeString("xmlns", "s", Nothing, "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
       writer.WriteAttributeString("xmlns", "dt", Nothing, "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
       writer.WriteAttributeString("xmlns", "rs", Nothing, "urn:schemas-microsoft-com:rowset")
       writer.WriteAttributeString("xmlns", "z", Nothing, "#RowsetSchema")
       writer.Flush()
   
     End Sub
   
   
     Private Sub WriteSchemaElement(ByVal ds As DataSet, ByVal dbname As String, ByRef writer As XmlTextWriter)
       '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
       writer.WriteStartElement("s", "Schema", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
       writer.WriteAttributeString("id", "RowsetSchema")
   
       'write element ElementTyoe
       writer.WriteStartElement("s", "ElementType", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
   
       'write the attributes for ElementType
       writer.WriteAttributeString("name", "", "row")
       writer.WriteAttributeString("content", "", "eltOnly")
       writer.WriteAttributeString("rs", "updatable", "urn:schemas-microsoft-com:rowset", "true")
   
       WriteSchema(ds, dbname, writer)
       'write the end element for ElementType
       writer.WriteFullEndElement()
   
       'write the end element for Schema 
       writer.WriteFullEndElement()
       writer.Flush()
     End Sub
   
   
     Private Sub WriteSchema(ByVal ds As DataSet, ByVal dbname As String, ByRef writer As XmlTextWriter)
   
       Dim i As Int32 = 1
       Dim dc As DataColumn
   
       For Each dc In ds.Tables(0).Columns
   
         dc.ColumnMapping = MappingType.Attribute
   
         writer.WriteStartElement("s", "AttributeType", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
         'write all the attributes 
         writer.WriteAttributeString("name", "", dc.ToString)
         writer.WriteAttributeString("rs", "number", "urn:schemas-microsoft-com:rowset", i.ToString)
         writer.WriteAttributeString("rs", "baseCatalog", "urn:schemas-microsoft-com:rowset", dbname)
         writer.WriteAttributeString("rs", "baseTable", "urn:schemas-microsoft-com:rowset", _
               dc.Table.TableName.ToString)
         writer.WriteAttributeString("rs", "keycolumn", "urn:schemas-microsoft-com:rowset", _
               dc.Unique.ToString)
         writer.WriteAttributeString("rs", "autoincrement", "urn:schemas-microsoft-com:rowset", _
               dc.AutoIncrement.ToString)
         'write child element
         writer.WriteStartElement("s", "datatype", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
         'write attributes
         writer.WriteAttributeString("dt", "type", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882", _
               GetDatatype(dc.DataType.ToString))
         writer.WriteAttributeString("dt", "maxlength", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882", _
               dc.MaxLength.ToString)
         writer.WriteAttributeString("rs", "maybenull", "urn:schemas-microsoft-com:rowset", _
               dc.AllowDBNull.ToString)
         'write end element for datatype
         writer.WriteEndElement()
         'end element for AttributeType
         writer.WriteEndElement()
         writer.Flush()
         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.
       Dim tablename As String
       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(ByRef wrt As XmlTextWriter, 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()
     End Sub
   
   End Class
   
   

6. On the Build menu, click Build Solution. The ConvertDStoRS.dll Visual Basic .NET DLL is created.

Back to the top

Create a Visual Basic .NET Project

1. To create a new Visual Basic .NET console application, follow these steps:
   1. Start Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. Click Visual Basic Projects under Project Types, and then click Console Application under Templates.
2. To add references to ConvertDStoRS.dll and the ActiveX Data Objects (ADO) Library, follow these steps:
   1. On the Project menu, click Add Reference.
   2. In the Add Reference dialog box, on the .NET tab, click Browse, and then locate the ConvertDStoRS.dll file that you created in Step 6 of the Create a Visual Basic .NET DLL subsection of this article.
   3. Click ConvertDStoRS.dll, click Select, and then click OK.
   4. In the Add Reference dialog box, click the COM tab.
   5. Click Microsoft ActiveX Data Objects Library 2.6 (or a later version) in the Component Name list, click Select, and then click OK.
3. In Module1.vb, replace the existing code with following code:

   Imports System.Data
   Imports System.Data.SqlClient
   Module Module1
   
       Sub Main()
   
           Dim cnNwind As New SqlConnection("data source=mySQLServer;Integrated Security=SSPI;initial catalog=pubs;")
           Dim daAuthors As New SqlDataAdapter("Select * from Authors", cnNwind)
           Dim ds As New DataSet()
           Try
               daAuthors.Fill(ds, "Authors")
               daAuthors.FillSchema(ds.Tables("Authors"), SchemaType.Source)
   
               Dim objconvert As New ConvertDStoRS.ConvertToRs()
               Dim result As Long
               result = objconvert.GetADORS(ds, "Northwind", "c:\temp\test.xsl", "c:\temp\result.xml")
               If result = 1 Then
   
                   Dim rs As New ADODB.Recordset()
                   rs.Open("c:\temp\result.xml")
                   Console.WriteLine("RecordCount =" & str(rs.RecordCount))
                   Console.Read()
               End If
   
           Catch sqlex As SqlException
               Console.WriteLine(sqlex.Message)
           Catch ex As Exception
               Console.WriteLine(ex.Message)
           End Try
          
       End Sub
   
   End Module
   					

4. Build and then run the project. Notice that the record count is displayed in the Console window.

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml version="1.0" encoding="UTF-8"?> 

Public Sub New(Stream, Encoding)

Back to the top