C # access the Access Table Structure and query the table column information

Access is not like sqlserver. Oracle provides a direct system table that can query the column information of the relevant table. Therefore, you cannot find the relevant information directly using SQL, which must be implemented through a program.

The Code is as follows:

Public static dataset getoledbcolumns (dblink, string tablename) {dbhelperoledb. connectionstring = dblink. linkconnstr; datatable dtkey = dbhelperoledb. getprimaryinfo (tablename); // obtain the primary key information datatable result = dbhelperoledb. getcolumninfo (tablename); // get the column information datatable dt = new datatable (); DT. columns. add (New datacolumn ("tablename", typeof (string); DT. columns. add (New datacolumn ("tabledescription ", Typeof (string); DT. columns. add (New datacolumn ("colorder", typeof (string); DT. columns. add (New datacolumn ("columnname", typeof (string); DT. columns. add (New datacolumn ("isidentity", typeof (string); DT. columns. add (New datacolumn ("isprimarykey", typeof (string); DT. columns. add (New datacolumn ("typename", typeof (string); DT. columns. add (New datacolumn ("length", typeof (string); DT. columns. add (New datacolumn ("precision", typeof (string); DT. columns. add (New datacolumn ("scale", typeof (string); DT. columns. add (New datacolumn ("nullable", typeof (string); DT. columns. add (New datacolumn ("defaultval", typeof (string); DT. columns. add (New datacolumn ("Description", typeof (string); foreach (datarow row in result. rows) {datarow r = DT. newrow (); R ["tablename"] = row ["table_name"]. tostring (); R ["Tabledescription"] = row ["table_catalog"]. tostring (); R ["colorder"] = row ["ordinal_position"]. tostring (); // R ["columnname"] = row ["column_name"]. tostring (); R ["isidentity"] = false; // the corresponding item R ["isprimarykey"] = dtkey is not found. select (string. format ("column_name = '{0}'", row ["column_name"]. tostring ())). length> 0? True: false; // whether the primary key is R ["typename"] = row ["data_type"]. tostring (); R ["length"] = row ["character_maximum_length"]. tostring (); R ["precision"] = row ["numeric_precision"]. tostring (); R ["scale"] = row ["numeric_scale"]. tostring (); R ["nullable"] = bool. parse (row ["is_nullable"]. tostring (); R ["defaultval"] = row ["column_default"]. tostring (); R ["Description"] = row ["Description"]. tostring (); DT. rows. add (r);} dataset DS = new dataset (); DS. tables. add (DT); Return Ds ;} /// <summary> /// obtain access table column information /// </Summary> /// <returns> </returns> Public static datatable getcolumninfo (string tablename) {// connectionstring = @ "provider = Microsoft. jet. oledb.4.0; Data Source = E: \ project \ orgcertificate \ bin \ debug \ orgcertificatedb. MDB; user id =; Password =; "; using (oledbconnection connection = new oledbconnection (connectionstring) {connection. open (); datatable dt = connection. getoledbschematable (oledbschemaguid. columns, new object [] {null, null}); dataview view = new dataview (); view. table = DT; view. rowfilter = string. format ("table_name = '{0}'", tablename); Return view. totable ();}} /// <summary> /// obtain the primary key information of the Access Table // </Summary> /// <Param name = "tablename"> </param> // <returns> </returns> Public static datatable getprimaryinfo (string tablename) {using (oledbconnection connection = new oledbconnection (connectionstring) {connection. open (); datatable dt = connection. getoledbschematable (oledbschemaguid. primary_keys, new object [] {null, null}); dataview view = new dataview (); view. table = DT; view. rowfilter = string. format ("table_name = '{0}'", tablename); Return view. totable ();}}

Example: similar to getoledbschematable ()

DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null });

You can replace "columns" with the following fields, source online (http://topic.csdn.net/u/20080918/12/fc76f127-87bc-46e4-b273-7da1ca514c2c.html), please verify by yourself.

Metadatacollections
Performanceinformation
Datatypes
Restrictions
Reservedwords
Users
Databases
Tables
Columns
Structuredtypemembers
Views
Viewcolumns
Procedureparameters
Procedures
Foreignkeys
Indexcolumns
Indexes
Userdefinedtypes

Oledbschemaguid class information

public sealed class OleDbSchemaGuid    {        // Summary:        //     Returns the assertions defined in the catalog that is owned by a given user.        public static readonly Guid Assertions;        //        // Summary:        //     Returns the physical attributes associated with catalogs accessible from        //     the data source. Returns the assertions defined in the catalog that is owned        //     by a given user.        public static readonly Guid Catalogs;        //        // Summary:        //     Returns the character sets defined in the catalog that is accessible to a        //     given user.        public static readonly Guid Character_Sets;        //        // Summary:        //     Returns the check constraints defined in the catalog that is owned by a given        //     user.        public static readonly Guid Check_Constraints;        //        // Summary:        //     Returns the check constraints defined in the catalog that is owned by a given        //     user.        public static readonly Guid Check_Constraints_By_Table;        //        // Summary:        //     Returns the character collations defined in the catalog that is accessible        //     to a given user.        public static readonly Guid Collations;        //        // Summary:        //     Returns the columns defined in the catalog that are dependent on a domain        //     defined in the catalog and owned by a given user.        public static readonly Guid Column_Domain_Usage;        //        // Summary:        //     Returns the privileges on columns of tables defined in the catalog that are        //     available to or granted by a given user.        public static readonly Guid Column_Privileges;        //        // Summary:        //     Returns the columns of tables (including views) defined in the catalog that        //     is accessible to a given user.        public static readonly Guid Columns;        //        // Summary:        //     Returns the columns used by referential constraints, unique constraints,        //     check constraints, and assertions, defined in the catalog and owned by a        //     given user.        public static readonly Guid Constraint_Column_Usage;        //        // Summary:        //     Returns the tables that are used by referential constraints, unique constraints,        //     check constraints, and assertions defined in the catalog and owned by a given        //     user.        public static readonly Guid Constraint_Table_Usage;        //        // Summary:        //     Returns a list of provider-specific keywords.        public static readonly Guid DbInfoKeywords;        //        // Summary:        //     Returns a list of provider-specific literals used in text commands.        public static readonly Guid DbInfoLiterals;        //        // Summary:        //     Returns the foreign key columns defined in the catalog by a given user.        public static readonly Guid Foreign_Keys;        //        // Summary:        //     Returns the indexes defined in the catalog that is owned by a given user.        public static readonly Guid Indexes;        //        // Summary:        //     Returns the columns defined in the catalog that is constrained as keys by        //     a given user.        public static readonly Guid Key_Column_Usage;        //        // Summary:        //     Returns the primary key columns defined in the catalog by a given user.        public static readonly Guid Primary_Keys;        //        // Summary:        //     Returns information about the columns of rowsets returned by procedures.        public static readonly Guid Procedure_Columns;        //        // Summary:        //     Returns information about the parameters and return codes of procedures.        public static readonly Guid Procedure_Parameters;        //        // Summary:        //     Returns the procedures defined in the catalog that is owned by a given user.        public static readonly Guid Procedures;        //        // Summary:        //     Returns the base data types supported by the .NET Framework Data Provider        //     for OLE DB.        public static readonly Guid Provider_Types;        //        // Summary:        //     Returns the referential constraints defined in the catalog that is owned        //     by a given user.        public static readonly Guid Referential_Constraints;        //        // Summary:        //     Returns a list of schema rowsets, identified by their GUIDs, and a pointer        //     to the descriptions of the restriction columns.        public static readonly Guid SchemaGuids;        //        // Summary:        //     Returns the schema objects that are owned by a given user.        public static readonly Guid Schemata;        //        // Summary:        //     Returns the conformance levels, options, and dialects supported by the SQL-implementation        //     processing data defined in the catalog.        public static readonly Guid Sql_Languages;        //        // Summary:        //     Returns the statistics defined in the catalog that is owned by a given user.        public static readonly Guid Statistics;        //        // Summary:        //     Returns the table constraints defined in the catalog that is owned by a given        //     user.        public static readonly Guid Table_Constraints;        //        // Summary:        //     Returns the privileges on tables defined in the catalog that are available        //     to, or granted by, a given user.        public static readonly Guid Table_Privileges;        //        // Summary:        //     Describes the available set of statistics on tables in the provider.        public static readonly Guid Table_Statistics;        //        // Summary:        //     Returns the tables (including views) defined in the catalog that are accessible        //     to a given user.        public static readonly Guid Tables;        //        // Summary:        //     Returns the tables (including views) that are accessible to a given user.        public static readonly Guid Tables_Info;        //        // Summary:        //     Returns the character translations defined in the catalog that is accessible        //     to a given user.        public static readonly Guid Translations;        //        // Summary:        //     Identifies the trustees defined in the data source.        public static readonly Guid Trustee;        //        // Summary:        //     Returns the USAGE privileges on objects defined in the catalog that are available        //     to or granted by a given user.        public static readonly Guid Usage_Privileges;        //        // Summary:        //     Returns the columns on which viewed tables depend, as defined in the catalog        //     and owned by a given user.        public static readonly Guid View_Column_Usage;        //        // Summary:        //     Returns the tables on which viewed tables, defined in the catalog and owned        //     by a given user, are dependent.        public static readonly Guid View_Table_Usage;        //        // Summary:        //     Returns the views defined in the catalog that is accessible to a given user.        public static readonly Guid Views;        // Summary:        //     Initializes a new instance of the System.Data.OleDb.OleDbSchemaGuid class.        public OleDbSchemaGuid();    }