Convert Datatable To Recordset

Public Function convertToADODB(ByRef table As DataTable) As ADODB.Recordset 'Try Dim result As New ADODB.Recordset result.CursorLocation = CursorLocationEnum.adUseClient Dim resultFields As ADODB.Fields = result.Fields Dim col As DataColumn For Each col In table.Columns resultFields.Append(col.ColumnName, TranslateType(col.DataType), col.MaxLength, col.AllowDBNull = ADODB.FieldAttributeEnum.adFldIsNullable) Next result.Open(System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0) For Each row As DataRow In table.Rows result.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value) For i As Integer = 0 To table.Columns.Count resultFields(i).Value = row(i) Next Next Return result 'Catch ex As Exception 'End Try End Function

Public Function TranslateType(ByRef type As Type) As ADODB.DataTypeEnum Try Select Case type.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" 'case default Return ADODB.DataTypeEnum.adVarWChar End Select Catch ex As Exception MsgBox(ex.Message) End Try End Function