Asp.Net(C#) 遍历数据库表名，列名

ADO.NET提供了个很简单的方法就能实现遍历数据库表名，列名。不用写什么语句的。

ACCESS：

 

 

 1     private readonly static string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.Combine(GetConfig.AppPath, GetConfig.ConnectString);
 2     protected void Page_Load(object sender, EventArgs e)
 3     {
 4         OleDbConnection conn = new OleDbConnection(ConnectionString);
 5         conn.Open();
 6         DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
 7         conn.Close();
 8         GridView1.DataSource = dt;
 9         GridView1.DataBind();
10     }

 

 

以上取得了所有的表格，可以在GridView1里预览表格的各个属性。自己建立的表格就是TABLE_TYPE为Table的行对应的记录。很简单就能拿到所有的表格名。

效果如图：

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_GUID	DESCRIPTION	TABLE_PROPID	DATE_CREATED	DATE_MODIFIED
		MSysAccessObjects	ACCESS TABLE				2003-3-7 17:53:26	2003-3-7 17:53:26
		MSysAccessXML	ACCESS TABLE				2007-6-1 14:17:59	2007-6-1 14:17:59
		MSysACEs	SYSTEM TABLE				2000-9-21 5:31:07	2000-9-21 5:31:07
		MSysObjects	SYSTEM TABLE				2000-9-21 5:31:07	2000-9-21 5:31:07
		MSysQueries	SYSTEM TABLE				2000-9-21 5:31:07	2000-9-21 5:31:07
		MSysRelationships	SYSTEM TABLE				2000-9-21 5:31:07	2000-9-21 5:31:07
		Users	TABLE				2007-6-1 14:17:59	2007-6-1 14:31:41

得到所有的列名也类似，把OleDbSchemaGuid.Tables换成OleDbSchemaGuid.Columns就可以了。
代码：

    private readonly static string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.Combine(GetConfig.AppPath, GetConfig.ConnectString);
    protected void Page_Load(object sender, EventArgs e)
    {
        OleDbConnection conn = new OleDbConnection(ConnectionString);
        conn.Open();
        DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,null);
        conn.Close();
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

效果：

 

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	COLUMN_GUID	COLUMN_PROPID	ORDINAL_POSITION	COLUMN_HASDEFAULT	COLUMN_DEFAULT	COLUMN_FLAGS	IS_NULLABLE	DATA_TYPE	TYPE_GUID	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_CATALOG	CHARACTER_SET_SCHEMA	CHARACTER_SET_NAME	COLLATION_CATALOG	COLLATION_SCHEMA	COLLATION_NAME	DOMAIN_CATALOG	DOMAIN_SCHEMA	DOMAIN_NAME	DESCRIPTION
		MSysAccessObjects	Data			1			106		128		3992	3992
		MSysAccessObjects	ID			2			122		3				10
		MSysAccessXML	Id			1			90		3				10
		MSysAccessXML	LValue			2			234		128		0	0
		MSysAccessXML	ObjectGuid			3			122		72
		MSysAccessXML	ObjectName			4			106		130		65	130
		MSysAccessXML	Property			5			106		130		65	130
		MSysAccessXML	Value			6			106		130		255	510
		Users	Password			3			106		130		50	100
		Users	UserAns			5			106		130		50	100
		Users	UserCoin			6		0	122		3				10
		Users	UserID			1			90		3				10
		Users	UserName			2			106		130		50	100
		Users	UserQue			4			106		130		50	100

SQL Server:
获得表

 1     private readonly static string ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AMT;Data Source=(local);";
 2     protected void Page_Load(object sender, EventArgs e)
 3     {
 4         SqlConnection conn = new SqlConnection(ConnectionString);
 5         conn.Open();
 6         DataTable dt = conn.GetSchema("Tables",null);
 7         conn.Close();
 8         GridView1.DataSource = dt;
 9         GridView1.DataBind();
10     }

获得列
 private readonly static string ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AMT;Data Source=(local);";
    protected void Page_Load(object sender, EventArgs e)
    {
  SqlConnection conn = new SqlConnection(ConnectionString);
  conn.Open();
  DataTable dt = conn.GetSchema("Columns",null);
  conn.Close();
  GridView1.DataSource = dt;
  GridView1.DataBind();
    }