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