• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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
0
tristan256
Asked:
tristan256
  • 3
  • 2
1 Solution
 
tristan256Author Commented:
Extra bonus points for anyone who tells me how to get the list of tables in an access database from .net.

Cheers,
Tristan
0
 
arif_eqbalCommented:
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(sConnString)
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(OleDbSchemaGuid.Tables, Restricts)
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(CommandBehavior.SchemaOnly)
                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
0
 
tristan256Author Commented:
" 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.
0
 
tristan256Author Commented:
Ok then , it is possible to retrieve the datatypes in access, ie with vb6?
0
 
arif_eqbalCommented:
Yes it is possible to Retrieve Acees Table strucure and datatypes in VB6 also you'll have to use
ADOX i.e. ADO Ext. for DDL and Security

As for checking AutoNumber field, Primary Key info etc. all information is available in the previous code in the loop just check for Item(13)

For each TbRow in Tbl.Rows
               Col_Name = TbRow.Item(0)
               Col_DataType = TbRow.Item(5)
               Col_Size = TbRow.Item(2)

               Is_AutoNumber = TbRow.Item(13)
               'THIS IS A BOOLEAN VALUE
 Next

Actually the table TbRow has 14 Columns (0 Through 13) each column has certain information like IsKey, IsUnique etc. you can loop through all columns and check what information they hold.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now