Just Code‎ > ‎

C# - DataTable to RecordSet

posted Feb 14, 2010, 3:48 AM by Peter Henell

public ADODB.Recordset TransformToRecordset(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 - 1; columnIndex++) {
            resultFields(columnIndex).Value = dr(columnIndex);
        }
    }
    
    result.MoveFirst();
    
    return result;
}



private 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;
        default:
            return ADODB.DataTypeEnum.adVarChar;
    }
}
Comments