C#访问Access表结构，查询表列信息

access不像sqlserver，oracle有提供直接的系统表，可以查询相关表的列信息，因此直接用sql无法查找到相关信息，需要通过程序实现。

代码如下：

public static DataSet GetOleDbColumns(DBlink dbLink, string tableName)
        {
            DBHelperOleDb.connectionString = dbLink.linkConnStr;
            DataTable dtKey = DBHelperOleDb.GetPrimaryInfo(tableName);//获取主键信息
            DataTable result = DBHelperOleDb.GetColumnInfo(tableName);//获取列信息
            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;//还未找到对应项
                r["IsPrimaryKey"] = dtKey.Select(string.Format("COLUMN_NAME='{0}'", row["COLUMN_NAME"].ToString())).Length > 0 ? true : false;//是否是主键
                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>
        /// 获取Access表列信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetColumnInfo(string tableName)
        {
            //connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Project\OrgCertificate\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>
        /// 获取Access表主键信息
        /// </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();
            }
        }

示例：和GetOleDbSchemaTable()差不多

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

可以替换“Columns”的还有如下字段，来源网上（http://topic.csdn.net/u/20080918/12/fc76f127-87bc-46e4-b273-7da1ca514c2c.html），请自行验证。

MetaDataCollections 
DataSourceInformation 
DataTypes 
Restrictions 
ReservedWords 
Users 
Databases 
Tables 
Columns 
StructuredTypeMembers 
Views 
ViewColumns 
ProcedureParameters 
Procedures 
ForeignKeys 
IndexColumns 
Indexes 
UserDefinedTypes

OleDbSchemaGuid的类信息

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();
    }