com.overset.dev» Blog Archive » Convert ADODB RecordSet XML To ADO.NET DataSet Object Natively In C#

4.21.08

Convert ADODB RecordSet XML To ADO.NET DataSet Object Natively In C#

Problem: You have a 3rd party API that returns an ADODB XML RecordSet that you need to incorporate in your .NET application.

ADO.NET is completely different than the Legacy ADODB and offers no completely transparent backward compatibility. As per Microsoft’s admission, http://support.microsoft.com/kb/910696, using TlbImp.exe’d legacy ADODB dll’s or those that were included with Visual Studio 2005 in the Primary Interop Assembly (PIA) are NOT to be used in any environment that remotely resembles a production environment. The best option for “Interop” appeared to just access ADODB directly through COM+ Services. This does come with a very large performance penalty (BIG OHH) as opposed to being able to use native .NET objects - but it’s reliable in the sense that it’s production quality.

The solution I settled with was accessing the ADODB directly through COM+ Services in C#.NET 2.0 and instantiating/calling the objects through Reflection. Once the ADODB RecordSet had been read and cursor set back to 0 used the Microsoft-provided System.Data.OleDb.OleDbDataAdapter to convert the ADODB.RecordSet -> System.Data.DataSet for me. The details on this OleDataAdapter are in the following technote: Accessing an ADO Recordset or Record from ADO.NET

The code below shows how to go from an ADODB XML string directly to a .NET DataSet object without creating a COM+ reference in visual studio for ADODB:

string ADODBxml = @"<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:CommandTimeout='30'>

        <s:AttributeType name='guest_no' rs:number='1' rs:writeunknown='true'>

            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19'

             rs:scale='0' rs:precision='17' rs:fixedlength='true' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='operator' rs:number='4' rs:writeunknown='true'>

            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6'

             rs:fixedlength='true' rs:maybenull='false'/>

        </s:AttributeType>

        <s:AttributeType name='date_time' rs:number='5' rs:writeunknown='true'>

            <s:datatype dt:type='dateTime' rs:dbtype='timestamp'

             dt:maxLength='16' rs:scale='3' rs:precision='23' rs:fixedlength='true'

             rs:maybenull='false'/>

        </s:AttributeType>

        <s:extends type='rs:rowbase'/>

    </s:ElementType>

</s:Schema>

<rs:data>

    <z:row guest_no='5519000' operator='JDENAR' date_time='2003-12-22T10:00:51'/>

</rs:data>

</xml>";
Type adoStreamType = Type.GetTypeFromProgID("ADODB.Stream");

object adoStream = Activator.CreateInstance(adoStreamType);

adoStreamType.InvokeMember("Open", System.Reflection.BindingFlags.InvokeMethod, null, adoStream, new object[] {});

adoStreamType.InvokeMember("WriteText", System.Reflection.BindingFlags.InvokeMethod, null, adoStream, new object[] { ADODBxml });

adoStreamType.InvokeMember("Position", System.Reflection.BindingFlags.SetProperty, null, adoStream, new object[] { 0 });
Type adoRecordsetType = Type.GetTypeFromProgID("ADODB.Recordset");

object adoRecordset = Activator.CreateInstance(adoRecordsetType);

adoRecordsetType.InvokeMember("Open", System.Reflection.BindingFlags.InvokeMethod, null, adoRecordset, new object[] {adoStream});
OleDbDataAdapter odb = new OleDbDataAdapter();

DataSet ods = new DataSet();

odb.Fill(ods, adoRecordset, "data");
foreach(DataTable t in ods.Tables){

    Console.Write("<table border=1>");

    foreach(DataRow r in t.Rows){

        foreach(DataColumn c in t.Columns){

            Console.Write("<tr><td>" + c + "</td><td> " + r[c] + "</td></tr>\n");

        }

    }

    Console.Write("</table>");

}

Download this code: RecordSetToDataSet.cs

Here’s what the DataSet HTML output (from the last foreach) would look like with the given ADOXML “data”:

guest_no	5519000
operator	JDENAR
date_time	12/22/2003 10:00:51 AM

In Visual Studio it is possible to add a reference for the ADODB COM+ Service which will enable cleaner code and not having to use Reflection like I did. I’ve seen the same article outlining this re-purposed all over the intarweb so hopefully I’m referring to the source here:
ADODB Services in .NET applications using C#

References:
How to use ADO Recordsets in Visual Basic .NET
How To Obtain an ADO Recordset from XML
Known issues for the ADODB Primary Interop Assembly (PIA) that is included with Visual Studio 2005
ADO.NET Programmer’s Reference - Chapter 16 - COM Interoperability
http://www.eggheadcafe.com/articles/20021025.asp

No comments