How can I convert an ADO.Net Dataset into a Classic ADO Recordset?

[Phil Agee]

Is there an easy way to create an XML file in ADO.Net for a dataset (using
WriteXML) that can then be opened by classic ADO as a recordset (using the
Open method of the recordset object with the adCmdFile parameter)?

Thanks,
Phil

 

[bruce barker]

there is no buildin method for this but its easy to write. lookup the xml
spec for the old ado (or produce one and look at the output) and write your
own method.

 

[Phil Agee]

Is there any XSLT out there that will help to transform the ADO.Net dataset
into XML that will be readable by Classic ADO?

Thanks,
Phil

 

[george]

hi,
here is a sample that works fine for me.

<WebMethod()> Public Function getmyrecordsInXML(ByVal
mydatabase As String) As DataSet
Dim conn As SqlConnection = ......... your connection .....
Dim sc As SqlDataAdapter = New SqlDataAdapter("select....
your selection .......... ", conn)
Dim ds As DataSet = New DataSet
sc.Fill(ds)
conn.Close()
Return ds
End Function

 

[Mike]

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Reflection;

using ADODB;

public class RecordsetFromDataTable
{
static public Recordset Create(DataTable oDataTable)
{
Recordset oRS = new Recordset();

try
{
//Loop through each column in the Dataset
foreach(DataColumn oColumn in oDataTable.Columns)
{
//Create the Field Types for the recordset
oRS.Fields.Append(oColumn.ColumnName,
GetADOType(oColumn.DataType.ToString()),
GetADOTypeSize(oColumn),
FieldAttributeEnum.adFldIsNullable,
System.Reflection.Missing.Value);
}

//Open the recordset
oRS.Open(System.Reflection.Missing.Value,
System.Reflection.Missing.Value, CursorTypeEnum.adOpenStatic,
LockTypeEnum.adLockOptimistic, 1);

//Loop through the table and fill the ADO Recordset
for(int i = 0; i < oDataTable.Rows.Count; i ++)
{
oRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);

string guid = "";

int columnCount = oDataTable.Columns.Count;

//Loop through each column
for(int j = 0; j < columnCount; j ++)
{
if(oDataTable.Columns[j].DataType.ToString() == "System.Guid")
{
guid = "{" + Guid.NewGuid().ToString() + "}";
oRS.Fields[j].Value = guid;
}
else
{
oRS.Fields[j].Value = oDataTable.Rows[j];
}
}
}

//Move to the first record
oRS.MoveFirst();
return oRS;
}
catch
{
return null;
}
}

static private DataTypeEnum GetADOType(string sType)
{
switch(sType)
{
case null:
//adEmpty 0
return DataTypeEnum.adEmpty;
case "System.SByte":
//adTinyInt 16
return DataTypeEnum.adTinyInt;
case "System.Boolean":
//adBoolean 11
return DataTypeEnum.adBoolean;
case "System.Int16":
//adSmallInt 2
return DataTypeEnum.adSmallInt;
case "System.Int32":
//adInteger 3
return DataTypeEnum.adInteger;
case "System.Int64":
//adBigInt 20
return DataTypeEnum.adBigInt;
case "System.Single":
//adSingle 4
return DataTypeEnum.adSingle;
case "System.Double":
//adDouble 5
return DataTypeEnum.adDouble;
case "System.Decimal":
//adDecimal 14
return DataTypeEnum.adDecimal;
case "System.DateTime":
//adDate 7
return DataTypeEnum.adDate;
case "System.Guid":
//adGUID 72
return DataTypeEnum.adGUID;
case "System.String":
//adChar 129
return DataTypeEnum.adChar;
case "System.byte[]":
//adBinary
return DataTypeEnum.adBinary;
default:
return 0;
}
}

static private int GetADOTypeSize(DataColumn col)
{
switch(col.DataType.ToString())
{
case null:
//adEmpty 0
return -1;
case "System.SByte":
//adTinyInt 16
return -1;
case "System.Boolean":
//adBoolean 11
return -1;
case "System.Int16":
//adSmallInt 2
return -1;
case "System.Int32":
//adInteger 3
return -1;
case "System.Int64":
//adBigInt 20
return -1;
case "System.Single":
//adSingle 4
return -1;
case "System.Double":
//adDouble 5
return -1;
case "System.Decimal":
//adDecimal 14
return -1;
case "System.DateTime":
//adDate 7
return -1;
case "System.Guid":
return col.MaxLength;
//adGUID 72
case "System.String":
//adChar 129
return 32767;
case "System.byte[]":
//adBinary
return 32767;
default:
return 1;
}
}
}