Introduction
When working with a legacy ASP application, you might want to convert the core business objects to .NET class libraries while still allow the use of existing ASP pages. If those pages are based on manipulating ADODB Recordset
s, it can be a real pain to expose them as an ADO.NET DataTable
.
The existing examples I've seen are oriented around generating a full-fidelity replica of the DataTable
which allows updates. In many situations, this is not needed and the added burden and fragility of these solutions is not warranted.
This article is a quick code snippet that shows what I believe is the simplest possible way to handle the conversion from an ADO.NET DataTable
to an ADODB.Recordset
, which can then be handled as if it was still coming from the older middleware component.
The Code
There are two functions, designed to be include as static
members of some helper class. The main function is simple:
static public ADODB.Recordset ConvertToRecordset(DataTable inTable)
{
ADODB.Recordset result = new ADODB.Recordset();
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
ADODB.Fields resultFields = result.Fields;
System.Data.DataColumnCollection inColumns = inTable.Columns;
foreach (DataColumn inColumn in inColumns)
{
resultFields.Append(inColumn.ColumnName
, TranslateType(inColumn.DataType)
, inColumn.MaxLength
, inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable :
ADODB.FieldAttributeEnum.adFldUnspecified
, null);
}
result.Open(System.Reflection.Missing.Value
, System.Reflection.Missing.Value
, ADODB.CursorTypeEnum.adOpenStatic
, ADODB.LockTypeEnum.adLockOptimistic, 0);
foreach (DataRow dr in inTable.Rows)
{
result.AddNew(System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
for (int columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
{
resultFields[columnIndex].Value = dr[columnIndex];
}
}
return result;
}
A helper function (which can easily be extended) maps the .NET data types to the correct ADODB field type enumeration.
static ADODB.DataTypeEnum TranslateType(Type columnType)
{
switch (columnType.UnderlyingSystemType.ToString())
{
case "System.Boolean":
return ADODB.DataTypeEnum.adBoolean;
case "System.Byte":
return ADODB.DataTypeEnum.adUnsignedTinyInt;
case "System.Char":
return ADODB.DataTypeEnum.adChar;
case "System.DateTime":
return ADODB.DataTypeEnum.adDate;
case "System.Decimal":
return ADODB.DataTypeEnum.adCurrency;
case "System.Double":
return ADODB.DataTypeEnum.adDouble;
case "System.Int16":
return ADODB.DataTypeEnum.adSmallInt;
case "System.Int32":
return ADODB.DataTypeEnum.adInteger;
case "System.Int64":
return ADODB.DataTypeEnum.adBigInt;
case "System.SByte":
return ADODB.DataTypeEnum.adTinyInt;
case "System.Single":
return ADODB.DataTypeEnum.adSingle;
case "System.UInt16":
return ADODB.DataTypeEnum.adUnsignedSmallInt;
case "System.UInt32":
return ADODB.DataTypeEnum.adUnsignedInt;
case "System.UInt64":
return ADODB.DataTypeEnum.adUnsignedBigInt;
case "System.String":
default:
return ADODB.DataTypeEnum.adVarChar;
}
}
Gotchas
- You have to define the fields in the
Recordset
before you call the Open
method or ADODB will give you an error.
- This is a disconnected
Recordset
, so it cannot be used to update a table without further handling.
- It only copies over the "current" values of the columns.
- It copies all the rows, so be careful with large row-sets.
Other Articles