C# - DataTable to RecordSet

Post date: Feb 14, 2010 11:48:46 AM

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; }}