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

Article translations Article translations
Article ID: 316337 - View products that this article applies to.
This article was previously published under Q316337
Caution ADO and ADO MD have not been fully tested in a Microsoft .NET Framework environment. They may cause intermittent issues, especially in service-based applications or in multithreaded applications. The techniques that are discussed in this article should only be used as a temporary measure during migration to ADO.NET. You should only use these techniques after you have conducted complete testing to make sure that there are no compatibility issues. Any issues that are caused by using ADO or ADO MD in this manner are unsupported. For more information, see the following article in the Microsoft Knowledge Base:
840667 You receive unexpected errors when using ADO and ADO MD in a .NET Framework application
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article describes how to convert an ADO.NET DataSet object to an ActiveX Data Objects (ADO) Recordset object.

This article uses the XML approach to convert the DataSet to an ADO Recordset. The XML format that is persisted in ADO differs from the XML format of the DataSet. Therefore, you can simplify the conversion process if you convert from one XML format to the other. The ADO Persist provider facilitates to load the .xml file to an ADO Recordset, as long as it is in the ADO XML format.


Note When you use this method, you can only create a read-only ADODB Recordset. To create an updatable Recordset, create an empty connected Recordset, and then insert all the rows.


Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • 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
This article assumes that you are familiar with the following topics:
  • Microsoft Visual Basic .NET syntax
  • ADO.NET and earlier versions of ADO
  • DataSet and ADO Recordset XML formats

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.

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.

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.
Note To handle special characters in the data, write the processing instruction with the encoding attribute. You can make this change in the ConvertToRs class that is mentioned in the class1.vb file of the "Create a Visual Basic .NET DLL" section of this article or in the result.xml file. Note that the processing instruction only informs the encoding format to the parser, and the data must be in the specified encoding format. For example:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml version="1.0" encoding="UTF-8"?> 
You can control the encoding of the data in .NET several ways. For example: XmlTextWriter constructor can be used.
Public Sub New(Stream, Encoding)

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
308064 How To Persist an ADO.NET DataSet as XML by Using Visual Basic .NET
301216 How To Populate a DataSet Object from a Database by Using Visual Basic .NET
313590 INFO: Roadmap for ADO.NET
313649 INFO: Roadmap for XML Integration with ADO.NET

Properties

Article ID: 316337 - Last Review: November 28, 2007 - Revision: 7.1
APPLIES TO
  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbdataadapter kbhowtomaster kbsqlclient kbsystemdata KB316337

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com