tristan256
asked on
Get Access Datatype in .net
I have access database, and I need to get the field datatypes of a table. How do I do this in .net? Note I don't mean the oledb datatype ie System.Type.Int32 etc, I mean whether its a an Autonumber,number,yes/no etc.
Cheers,
Tristan
Cheers,
Tristan
Well here's a code for getting Tables in a database its columns and its datatypes (It works with all databases however it might give some unrequired system tables which you can filter with a smart if clause)
Dim Cn As OleDbConnection
Cn = New OleDbConnection(sConnStrin
Cn.Open()
Dim Restricts() As Object = {Nothing, Nothing, Nothing, "Table"}
Dim TblSchema As DataTable
Dim Cmd As OleDbCommand
Dim DR As OleDbDataReader
Dim Tbl As DataTable
TblSchema = Cn.GetOleDbSchemaTable(Ole
Dim R As DataRow
For Each R In TblSchema.Rows
If CStr(R(3) & "").ToUpper = "TABLE" Then
Cmd = New OleDbCommand("Select * From " & CStr(R(2)), Cn)
DR = Cmd.ExecuteReader(CommandB
Tbl = DR.GetSchemaTable
Tbl.TableName = R(2).ToString
Dim TbRow as DataRow
Dim Col_Name as String
Dim Col_DataType as String
Dim Col_Size as String
For each TbRow in Tbl.Rows
Col_Name = TbRow.Item(0)
Col_DataType = TbRow.Item(5)
Col_Size = TbRow.Item(2)
Next
Dr.Close
End If
Next
Cn.Close
'However your first problem remains unsolved, i.e. the DataType returned is
'that of .Net framework and not that of the DataBase. I have so far not found any direct method of getting
'DataBase datatypes, So I use a little turna-round. I keep a table of dataMappings
'i.e. Access Text DataType is mapped to .Net String dataType
'Number is mapped to Integer and so on, so when I get System.String I refer the look up table
'and get corresponding access type
Dim Cn As OleDbConnection
Cn = New OleDbConnection(sConnStrin
Cn.Open()
Dim Restricts() As Object = {Nothing, Nothing, Nothing, "Table"}
Dim TblSchema As DataTable
Dim Cmd As OleDbCommand
Dim DR As OleDbDataReader
Dim Tbl As DataTable
TblSchema = Cn.GetOleDbSchemaTable(Ole
Dim R As DataRow
For Each R In TblSchema.Rows
If CStr(R(3) & "").ToUpper = "TABLE" Then
Cmd = New OleDbCommand("Select * From " & CStr(R(2)), Cn)
DR = Cmd.ExecuteReader(CommandB
Tbl = DR.GetSchemaTable
Tbl.TableName = R(2).ToString
Dim TbRow as DataRow
Dim Col_Name as String
Dim Col_DataType as String
Dim Col_Size as String
For each TbRow in Tbl.Rows
Col_Name = TbRow.Item(0)
Col_DataType = TbRow.Item(5)
Col_Size = TbRow.Item(2)
Next
Dr.Close
End If
Next
Cn.Close
'However your first problem remains unsolved, i.e. the DataType returned is
'that of .Net framework and not that of the DataBase. I have so far not found any direct method of getting
'DataBase datatypes, So I use a little turna-round. I keep a table of dataMappings
'i.e. Access Text DataType is mapped to .Net String dataType
'Number is mapped to Integer and so on, so when I get System.String I refer the look up table
'and get corresponding access type
ASKER
" So I use a little turna-round. I keep a table of dataMappings
i.e. Access Text DataType is mapped to .Net String dataType"
Yeah, I thought of that, but what if the Access type is Autonumber, NOT number? It will still say System.Int32, there will be no way of telling them apart.
i.e. Access Text DataType is mapped to .Net String dataType"
Yeah, I thought of that, but what if the Access type is Autonumber, NOT number? It will still say System.Int32, there will be no way of telling them apart.
ASKER
Ok then , it is possible to retrieve the datatypes in access, ie with vb6?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers,
Tristan