You can do as suggested by many books and manually populate the Classic ADO Recordset by moving through the DataSet and retrieving the values. This will give you an updatable ADO recordset if you have the cursortype and locktype set properly.
When an ADO Recordset is persisted to XML format, you will notice the file consists of both the schema and the data information. An ADO Recordset can only
understand this XML format. There are a number of ways to achive this ADO understandable XML format.
1. Load the XML Data from DataSet into a XML DOMDocument object, and manually fabricate an ADO Recordset. This would give the ADO Recordset, but for only readonly purposes.
2. Use XSLT to transform the XML from ADO.NET format to ADO Recordset. In this case, each time, the table changes, the xslt needs to changed.
3. Using the DataSet's FillSchema method, get the schema information into the dataset, and build a XML file according to the ADO Recordset. Then create an XSLT file to transform the data. This makes the process automatic. The part where schema is created is going to be almost same. The XSLT in this case is very small, since it is just to convert the data. Below is some code to get you started:
1. The following code shows how to create the Schema according to ADO Recordset format.
Dim cnNwind As New SqlConnection("data source=neutron;user
id=sa;password=sasa;initial catalog=Northwind;")
Dim daOrders As New SqlDataAdapter("Select
orderid,customerid,orderdate from Orders", cnNwind)
Dim ds As New DataSet()
daOrders.Fill(ds, "Orders")
daOrders.FillSchema(ds.Tables("Orders"), SchemaType.Source)
Dim xmldom As New Xml.XmlDocument()
Dim strADORs As String
strADORs = "<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'>" & _
"<s:Schema id='RowsetSchema'>" & _
"<s:ElementType name='row'
content='eltOnly' rs:updatable='true'>" & _
"</s:ElementType>" & _
"</s:Schema>" & _
"</xml>"
xmldom.LoadXml(strADORs)
Dim ns As New Xml.XmlNamespaceManager(xmldom.NameTable)
ns.AddNamespace("s",
"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
ns.AddNamespace("dt",
"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
ns.AddNamespace("rs", "urn:schemas-microsoft-com:rowset")
ns.AddNamespace("z", "#RowsetSchema")
Dim curnode As Xml.XmlNode
curnode = xmldom.SelectSingleNode("//s:ElementType", ns)
Dim i As Int32
Dim dc As DataColumn
For Each dc In ds.Tables("Orders").Columns
dc.ColumnMapping = MappingType.Attribute
Dim ele As Xml.XmlElement = xmldom.CreateElement("s",
"AttributeType", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
Dim attName As Xml.XmlAttribute =
xmldom.CreateAttribute("", "name", "")
attName.Value = dc.ToString
ele.SetAttributeNode(attName)
Dim attNumber As Xml.XmlAttribute =
xmldom.CreateAttribute("rs", "number", "urn:schemas-microsoft-com:rowset")
attNumber.Value = i.ToString
ele.SetAttributeNode(attNumber)
Dim attbaseCatalog As Xml.XmlAttribute =
xmldom.CreateAttribute("rs", "baseCatalog",
"urn:schemas-microsoft-com:rowset")
attbaseCatalog.Value = "Northwind"
ele.SetAttributeNode(attbaseCatalog)
Dim attbaseTable As Xml.XmlAttribute =
xmldom.CreateAttribute("rs", "baseTable",
"urn:schemas-microsoft-com:rowset")
attbaseTable.Value = dc.Table.TableName.ToString
ele.SetAttributeNode(attbaseTable)
Dim attkeycolumn As Xml.XmlAttribute =
xmldom.CreateAttribute("rs", "keycolumn",
"urn:schemas-microsoft-com:rowset")
attkeycolumn.Value = dc.Unique.ToString
ele.SetAttributeNode(attkeycolumn)
Dim attautoincrement As Xml.XmlAttribute =
xmldom.CreateAttribute("rs", "autoincrement",
"urn:schemas-microsoft-com:rowset")
attautoincrement.Value = dc.AutoIncrement.ToString
ele.SetAttributeNode(attautoincrement)
Dim dataele As Xml.XmlElement =
xmldom.CreateElement("s", "datatype",
"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
Dim atttype As Xml.XmlAttribute =
xmldom.CreateAttribute("dt", "type",
"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
atttype.Value = GetDatatype(dc.DataType.ToString)
dataele.SetAttributeNode(atttype)
Dim attmaxlength As Xml.XmlAttribute =
xmldom.CreateAttribute("dt", "maxlength",
"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
attmaxlength.Value = dc.MaxLength.ToString
dataele.SetAttributeNode(attmaxlength)
Dim attmaybenull As Xml.XmlAttribute =
xmldom.CreateAttribute("rs", "maybenull",
"urn:schemas-microsoft-com:rowset")
attmaybenull.Value = dc.AllowDBNull.ToString
dataele.SetAttributeNode(attmaybenull)
ele.AppendChild(dataele)
curnode.AppendChild(ele)
Next
xmldom.Save("C:\neworders.xml")
2. The following code shows how to get the xml data from the dataset and convert it to ADO Recordset Format.
Dim sw As New StreamWriter("C:\orders.xml")
'write only the xml data to c:\orders.xml
ds.WriteXml(sw)
sw.Flush()
sw.Close()
Dim Ordersxml As New XmlDocument()
Ordersxml.Load("C:\orders.xml")
Dim xslt As New Xsl.XslTransform()
xslt.Load("c:\order.xsl")
Dim result As New XmlTextWriter("C:\results.xml", Nothing)
xslt.Transform(Ordersxml, Nothing, result)
result.Flush()
result.Close()
3. The following is the order.xsl file.
<?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:template match="NewDataSet">
<rs:data>
<xsl:apply-templates select="Orders"/>
</rs:data>
</xsl:template>
<xsl:template match="Orders">
<z:row>
<xsl:for-each select="@*">
<xsl:copy-of select="."/>
</xsl:for-each>
</z:row>
</xsl:template>
</xsl:stylesheet>
4. Now the last piece is to combine the Schema and the data.
Dim Ordersxml As New XmlDocument()
Ordersxml.Load("C:\orders.xml")
Dim xslt As New Xsl.XslTransform()
xslt.Load("c:\order.xsl")
Dim result As New XmlTextWriter("C:\results.xml", Nothing)
xslt.Transform(Ordersxml, Nothing, result)
result.Flush()
result.Close()
Dim resultdom As New XmlDocument()
resultdom.Load("C:\results.xml")
Dim rowelement As XmlElement = xmldom.CreateElement("rs",
"data", "urn:schemas-microsoft-com:rowset")
rowelement.InnerXml = resultdom.DocumentElement.InnerXml
xmldom.DocumentElement.AppendChild(rowelement)
xmldom.Save("C:\resultorders.xml")
5. Now resultorders.xml file can be used to load an ADO Recordset Object.