Click here to Skip to main content
13,900,472 members
Click here to Skip to main content
Add your own
alternative version


43 bookmarked
Posted 27 May 2005
Licenced Ms-PL

Simplest code to convert an ADO.NET DataTable to an ADODB.Recordset

, 27 May 2005
Rate this:
Please Sign up or sign in to vote.
Simple C# code to convert an ADO.NET (System.Data) DataTable to an ADODB Recordset.


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 Recordsets, 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)
            , TranslateType(inColumn.DataType)
            , inColumn.MaxLength
            , inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable : 
            , null);

            , System.Reflection.Missing.Value
            , ADODB.CursorTypeEnum.adOpenStatic
            , ADODB.LockTypeEnum.adLockOptimistic, 0);

    foreach (DataRow dr in inTable.Rows)

        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":
            return ADODB.DataTypeEnum.adVarChar;


  • 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


This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)


About the Author

Marc Brooks
Web Developer
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

PraiseBrilliant! Pin
Renny H.Park20-Mar-17 0:19
memberRenny H.Park20-Mar-17 0:19 
QuestionPerformance issue when the number of rows is huge? Pin
tetelee19-Aug-13 21:16
membertetelee19-Aug-13 21:16 
AnswerRe: Performance issue when the number of rows is huge? Pin
Marc Brooks15-Oct-15 7:11
memberMarc Brooks15-Oct-15 7:11 
QuestionGood Article Pin
bala_prodin@india.com25-Apr-13 5:01
memberbala_prodin@india.com25-Apr-13 5:01 
Question'Sort' property doesn't work for the converted Recordset Pin
ronnie_bleum14-Oct-10 19:46
memberronnie_bleum14-Oct-10 19:46 
GeneralMethod's type signature is not Interop compatible Pin
_Matt_Wilkinson_8-Apr-10 6:12
member_Matt_Wilkinson_8-Apr-10 6:12 
GeneralRe: Method's type signature is not Interop compatible Pin
Marc Brooks8-Apr-10 13:42
memberMarc Brooks8-Apr-10 13:42 
GeneraladVarChar becomes adLongVarChar Pin
Chad Jones29-Jul-09 10:13
memberChad Jones29-Jul-09 10:13 
GeneralRe: adVarChar becomes adLongVarChar Pin
Marc Brooks29-Jul-09 11:14
memberMarc Brooks29-Jul-09 11:14 
GeneralRe: adVarChar becomes adLongVarChar Pin
Chad Jones29-Jul-09 11:24
memberChad Jones29-Jul-09 11:24 
GeneralThanx! [modified] Pin
Dennis Betten13-Mar-08 13:57
memberDennis Betten13-Mar-08 13:57 
GeneralError in parsing a TIME column Pin
fiwi463pfa1-Apr-07 23:08
memberfiwi463pfa1-Apr-07 23:08 
GeneralRe: Error in parsing a TIME column Pin
Tim McCurdy19-Sep-07 9:37
memberTim McCurdy19-Sep-07 9:37 
Generaladodb to Pin
smurfnz16-Jan-07 12:12
membersmurfnz16-Jan-07 12:12 
General"System.byte[]" Pin
jwcarroll21-Sep-06 5:22
memberjwcarroll21-Sep-06 5:22 
GeneralFix Pin
jwcarroll21-Sep-06 5:29
memberjwcarroll21-Sep-06 5:29 
GeneralOne more thing... Pin
jwcarroll21-Sep-06 5:37
memberjwcarroll21-Sep-06 5:37 
GeneralRe: One more thing... Pin
Tim McCurdy19-Sep-07 9:40
memberTim McCurdy19-Sep-07 9:40 
GeneralGreat article Pin
DummyDUmb8-Sep-06 9:11
memberDummyDUmb8-Sep-06 9:11 
GeneralRe: Great article Pin
Marc Brooks8-Sep-06 9:30
memberMarc Brooks8-Sep-06 9:30 
GeneralProblem AddNew Pin
rbarzallo17-May-06 7:02
memberrbarzallo17-May-06 7:02 
GeneralRe: Problem AddNew Pin
rbarzallo17-May-06 7:39
memberrbarzallo17-May-06 7:39 
GeneralRe: Problem AddNew Pin
Tim McCurdy19-Sep-07 9:41
memberTim McCurdy19-Sep-07 9:41 
GeneralRe: Problem AddNew Pin
Cleber L.14-Dec-09 1:10
memberCleber L.14-Dec-09 1:10 
GeneralFunction to convert ADODB.DataTypeEnum to System.Type Pin
Michael Freidgeim12-Nov-05 16:37
memberMichael Freidgeim12-Nov-05 16:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190306.1 | Last Updated 27 May 2005
Article Copyright 2005 by Marc Brooks
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid