DataSet --> ADO conversion ?
Yvonne
Is there a recognised way of converting a .NET DataSet to
an ADO recordset format..
Any help would be most appreciated
Y
Manisha Gupta
Yes there are ways to convert a .NET DataSet to an ADO recordset format..
The information below should give you the solution.
How to Convert from ADO.NET DataSet to ADO Recordset
----------------------------------------------------
Converting ADO.NET XML format to ADO Recordset format is more of task of
changing
the entire schema format to that of ADO Recordset. Note that it is not
possible
to create an updateable ADO Recordset using this method. To create an
updateable
recordset, it should have been opened with an active connection and with
adLockPessimistic, adLockOptimistic or adLockBatchOptmistic lock types. And
the
DataSet data should be manually synchronized with this recordset.
When an ADO Recordset is persisted to XML format, you will notice the file
consists of both the schema and the data information. And ADO Recordset can
only
understand this XML format.
There are a number of ways to achive this.
1. Using COM interoperability ADO can be used in .NET , therefore use ADO,
persist to XML and pass the data. This would be the easiest.
2. 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.
3. 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.
4. 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. Following Visual Basic
NET
code samples show how to do this.
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.
Hope this helps.
Manisha Gupta
Microsoft Developer Support Engineer
Microsoft Corporation
Email: mang...@online.microsoft.com
Want to know more? Check out the MSDN at msdn.microsoft.com or the
Microsoft Knowledge Base at support.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--------------------
Content-Class: urn:content-classes:message
From: "Yvonne" <yfra...@hotmail.com>
Sender: "Yvonne" <yfra...@hotmail.com>
Subject: DataSet --> ADO conversion ?
Date: Mon, 4 Mar 2002 05:16:20 -0800
Lines: 7
Message-ID: <acfa01c1c37e$c6889520$9ae62ecf@tkmsftngxa02>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Thread-Index: AcHDfsaIh6dJUinJTx6V9XzGOQ4cog==
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: TKMSFTNGXA02 10.201.232.161
Path: cpmsftngxa09!cpmsftngxa07
Xref: cpmsftngxa09 microsoft.public.dotnet.framework.adonet:12943
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
Yvonne
It does seem a little too involved though.
I was hoping for something simpler.
kind rgds
Y
>.NET
(xmldom.NameTableÍ{ wÀ M |LH5$ àEOÕ
>°ìx )