访问Access表结构查询表列信息代码,CSharp代码库,德仔网

代码语言

代码分类

【CSharp】访问Access表结构查询表列信息

作者:Dezai.CN / 发布于2013/2/23/ 828

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

试试其它关键字

　Access表结构　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)