【原】获取数据库(SQL SERVER 2005)的所有信息 Get all database information from SQL Server 2005 测试通过

公司同事要交毕业论文,说是要做一个小项目交差。 开始我给这个同事SHOW 了一下 LINQ, 同事说,LINQ 看起来代码很少(其实LINQ代码不少,只是自己写的那部分少了而已!数据库生成实体的代码很多的!),说要代码多点的!(忽悠老师不懂呗!),我说行!我就给他推荐了CODEPLUS,CODESMITH以及DBToCode的代码生成器,用代码生成器生成代码,那样看起来代码也多,洋气!这位同事满意而去!不过第二天,其想法变了,说要做个代码生成器,且要基于数据库的,可是其对数据库不熟悉,我说给其写个Oracle,可是人家更不熟悉Oracle啊!晕倒,我说救人救到底,送佛送到西!我闷头花了半个小时写个个获取SQL SERVER 2005数据库的全部信息,是全部数据库,如今单个数据库不是什么问题了!嘿嘿!听起来悬乎悬乎的吧,哈哈,不蒙大家了,上代码:


<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace=" System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
string connectionString = "Data Source=.;Initial Catalog=master;Persist Security Info=True;User ID=sa;Password=sa";
protected void Page_Load(object sender, EventArgs e)
{
List<string> DatabasesNameList = GetDatabasesName();
foreach (string DatabaseName in DatabasesNameList)
{
Response.Write("Database Name: ");
Response.Write(DatabaseName); Response.Write("<br />");
Response.Write("----------------------------------------------------------------------------------------------------------"); Response.Write("<br />");
Response.Write("----------------------------------------------------------------------------------------------------------");
Response.Write("<br />");
List<string> TablesNameslist = GetTablesName(DatabaseName);
foreach (string TableName in TablesNameslist)
{
Response.Write("Table Name: ");
Response.Write(TableName);
Response.Write("<br />");
DisplayTables(TableName, DatabaseName);
Response.Write("<br />"); Response.Write("<br />"); Response.Write("<br />");
}
Response.Write("<br />"); Response.Write("<br />"); Response.Write("<br />");
}
}
void DisplayTables(string tablename, string databasename)
{
string connectionString = GetDatabaseConnectionString(databasename);
string Query = "SELECT * FROM [" + tablename + "]";
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter sda = new SqlDataAdapter(Query, conn);
conn.Open();
DataSet ds = new DataSet();
sda.Fill(ds, tablename);
DataTable tblSchema = ds.Tables[tablename];
conn.Close();
Response.Write("Column Name ------ Data Type ------ Unique ------ Auto Increment ------ Allow DBNull");
Response.Write("<br />");
foreach (DataColumn dc in tblSchema.Columns)
{
string DatabaseInfo = dc.ColumnName + "------" + dc.DataType + "------" + dc.Unique + "------" + dc.AutoIncrement + "------" + dc.AllowDBNull;
Response.Write(DatabaseInfo);
Response.Write("<br />");
}
}
string GetDatabaseConnectionString(string DatabaseName)
{
string Res = "Data Source=.;Initial Catalog = DatabaseName ;Persist Security Info=True;User ID=sa;Password=sa";
Res = Res.Replace("DatabaseName", DatabaseName);
return Res;
}
List<string> GetTablesName(string DatabaseName)
{
string connectionString = GetDatabaseConnectionString(DatabaseName);
DataTable tables = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'";
connection.Open();
tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
}
List<string> list = new List<string>();
foreach (DataRow row in tables.Rows)
{
list.Add(row[0].ToString());
}
return list;
}
List<string> GetDatabasesName()
{
DataTable tables = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "select name from master..sysdatabases";
connection.Open();
tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
}
List<string> list = new List<string>();
foreach (DataRow row in tables.Rows)
{
list.Add(row[0].ToString());
}
// We can not visit the system databases
list.Remove("msdb");
list.Remove("master");
list.Remove("tempdb");
list.Remove("model");
list.Remove("ReportServer");
list.Remove("ReportServerTempDB");
list.Remove("aspnetdb");
return list;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Visit Database</title>
</head>
<body>
<form id="form1" runat="server">
</form>
</body>
</html>