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