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

最新推荐文章于 2024-06-12 18:23:20 发布

weixin_34320724

最新推荐文章于 2024-06-12 18:23:20 发布

阅读量66

点赞数

文章标签：数据库 xhtml c#

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

Code
<%@ 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>

weixin_34320724

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【原】获取数据库（SQL SERVER 2005）的所有信息 Get all database information from SQL Server 2005 测试通过...

公司同事要交毕业论文，说是要做一个小项目交差。开始我给这个同事SHOW 了一下 LINQ, 同事说，LINQ 看起来代码很少（其实LINQ代码不少，只是自己写的那部分少了而已！数据库生成实体的代码很多的！），说要代码多点的！（忽悠老师不懂呗！），我说行！我就给他推荐了CODEPLUS,CODESMITH以及DBToCode的代码生成器，用代码生成器生成代码，那样看起来代码也多，洋气！这位同事满意...
复制链接

扫一扫