none
Get Column Name and Data Types of Access Tables.

    Question

  • How to Get Column Name and Data Types of Access Tables.

    I got the Result for List of Tables in Database

     

    Any Ideas

     

     

    • Moved by Leo Liu - MSFT Tuesday, September 06, 2011 6:32 AM Moved for better support. (From:Visual C# General)
    Sunday, September 04, 2011 12:27 PM

Answers

All replies

  • I am trying to get the Datatype and ColumnName in Access Tables; however i am able to get the table names in Access Database, but i am unable to get field names
    Here is the code below using which i got Table names of Access Database
    OleDbConnection cn = new ("Provider=Microsoft.ACE.OLEDB.12.0;Data=C:\\Documents and Settings\\User\\My Documents\\Database1.accdb");
                    cn.Open();
                    
                    DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
                    //List the table name from each row in the schema table.
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        string Current_Str = schemaTable.Rows[i].ItemArray[2].ToString();
                    }
    How should i get table's Column Name and Datatypes

    • Edited by amit_kumar Sunday, September 04, 2011 11:50 AM
    • Merged by Rudedog2MVP Sunday, September 04, 2011 3:48 PM duplicate post on same topic
    Sunday, September 04, 2011 11:49 AM
  • You can query a tabled called MSysIMEXColumns  which will have the data types and column names.
    Tom Overton
    Sunday, September 04, 2011 12:45 PM
  • OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Documents and Settings\\User\\My Documents\\Database1.accdb");
            cn.Open();
            DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, "amitkumar", null });
            foreach (DataRow row in dbSchema.Rows)
            {
                string MyName = row["COLUMN_NAME"].ToString();
                string myDT = row["DATA_TYPE"].ToString();
            }
    Using the above code i got the field name and Datatype, but the problem is Datatype is in Numerical Format.
    Sunday, September 04, 2011 12:51 PM
  • Hello amit_kumer,

    OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Documents and Settings\\User\\My Documents\\Database1.accdb");
            cn.Open();
            DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, "amitkumar", null });
            foreach (DataRow row in dbSchema.Rows)
            {
                string MyName = row["COLUMN_NAME"].ToString();
                string myDT = row["DATA_TYPE"].ToString();
            }
    Using the above code i got the field name and Datatype, but the problem is Datatype is in Numerical Format.


    datatype can be used for parsing, for example, the method Int.TryParse, more information below.

    http://msdn.microsoft.com/en-us/library/system.int32.tryparse.aspx

    http://msdn.microsoft.com/en-us/library/f02979c7.aspx

     

    Regards.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Sunday, September 04, 2011 12:59 PM
  • you're going to have to translate the decimal numbers (the ADO data type values) that come back in the data_type field into what they mean.  Probably will have to write a function with a case statement in it to do it.  Here is a link to the column types and their numeric values.

     http://allenbrowne.com/ser-49.html


    Tom Overton
    Sunday, September 04, 2011 1:46 PM
  • I feel your pain and frustration.  The "GetSchema" methods are some of the most unusual methods that I have found in the entire Framework.  They are also some of the most powerful.  See this thread for an explanation of how it works.

    http://social.msdn.microsoft.com/Forums/eu/csharpgeneral/thread/1305eae4-8358-4336-bbc7-68824c8e7121

    You must make repeated calls with the method to "drill down" to the information you seek.  The size of the restrictions array can vary in size, depending upon which schema you are trying to get.  The only way to truly understand it is to play with it.  Anyone can tell you how to ride a bike, but you will not "know how" ride a bike until you get on it and fall off a few times. 

    It takes some practice to climb the learning curve.  The thread at the  previous link contains several links in my reply marked as answer. 

    http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/b169bb10-18de-46db-9c25-b2d499117b5c/

    One of those links is to a database oriented forum where I posted a sample C# form to allow you to "practice" at callling the method and seeing the results.  The thread with the sample became corrupted after going through a couple of forum migrations to new servers.  Other threads with the sample code either disappeared, or "Search" does not return them in the results.  However, as garbled as the thread may appear, all of the C# code is still visible.  Here is a VB version of the form posted on this thread below.

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/22180a01-2cd6-4167-951e-bc6a479af861/

    Here's a link that appears in that thread explains the GetSchema method in detail.

    http://support.microsoft.com/kb/309488

    Here is a line of code that retrieves the details for a datatable named "Employees".

      DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {null, null, "Employees", null});

    The VB links explain how to use the OleDb provider and an .mdb file.  The samples in my code work with SQL.

    Hope this helps.

    Rudy   =8^D

     


    Mark the best replies as answers. "Fooling computers since 1971."

    http://thesharpercoder.blogspot.com/



    • Edited by Rudedog2MVP Sunday, September 04, 2011 2:28 PM
    Sunday, September 04, 2011 2:17 PM
  • The following returns one row per column containing column name and column data type.

    Private Sub Demo(ByVal ConnectionString As String)
       Using cn As New OleDbConnection(ConnectionString)
          Dim Result = SchemaInfo(cn.ConnectionString, "Table1")
          For Each row As DataRow In Result.Rows
             Console.WriteLine("Name={0} Type={1}", row("ColumnName"), row("DataType"))
          Next
       End Using
    End Sub
    Public Function SchemaInfo(ByVal ConnectionString As String, ByVal TableName As String) As DataTable
       Dim dt As New DataTable With {.TableName = "Schema"}
    
       dt.Columns.AddRange( _
          New DataColumn() _
             { _
                New DataColumn("ColumnName", GetType(System.String)), _
                New DataColumn("DataType", GetType(System.String)) _
             } _
          )
    
       Using cn As New OleDbConnection(ConnectionString)
          Using cmd As New OleDbCommand("SELECT * FROM " & TableName, cn)
             cn.Open()
             Dim Reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
             Dim schemaTable = Reader.GetSchemaTable()
             schemaTable.TableName = "TableSchema"
    
             Dim sw As New IO.StringWriter
             schemaTable.WriteXml(sw)
             Dim Doc = New XDocument
             Doc = XDocument.Parse(sw.ToString)
             Dim query = _
                ( _
                   From T In Doc...<TableSchema> _
                   Select _
                      Name = T.<ColumnName>.Value, _
                      DataType = T.<DataType>.Value.Split(","c)(0).Replace("System.", "") _
                ).ToList
    
             For Each item In query
                Dim Row As DataRow
                Row = dt.NewRow
                Row("ColumnName") = item.Name
                Row("DataType") = item.DataType
                dt.Rows.Add(Row)
             Next
    
          End Using
       End Using
    
       Return dt
    
    End Function
    

     


    KSG
    Tuesday, September 06, 2011 6:36 PM
  • There seems no way to differentiate between binary and varbinary. GetSchema returns the same values for both types!!

    What can I do to distinguish binary and varbinary?

    Friday, October 21, 2011 11:27 AM
  • You can cast the datatype to OleDbType:

    OleDbType myDT = (OleDbType)row["DATA_TYPE"];

    Using the enum will make easy to identify the datatype.

    Monday, May 20, 2013 1:56 PM