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

来源：互联网发布：天猫和淘宝有什么关系编辑：程序博客网时间：2024/04/30 00:50

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