一个简单的在线 SQL Server 管理工具

几个月前因为虚拟主机服务商提供的在线 SQL Sever 管理工具总是出错，我就自己写了这个在线 SQL 管理小工具，但是因为是自己用，写得很简单。因此这个小工具是非常不完善的。使用它需要你具备一定的写 SQL语句的能力。

希望它能为您带来一点点便利，或者能给你增加一点点知识。

本文作者：willmove
作者主页：http://www.amuhouse.com

页面代码如下：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Query.aspx.cs" Inherits="Query"

ValidateRequest="false" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Query Database</title>

<!--

#wrapper {

margin-right: auto;

margin-left: auto;

width:953px;

overflow: hidden;

text-align: left;

}

#innerWrapper {

width: 100%;

}

#header {

background: url('images/logo.jpg') no-repeat;

}

#innerHeader {

background: url('images/logo.jpg') no-repeat;

padding: 25px 12px 0px 10px;

height: 100px;

}

#footer {

float: left;

clear: both;

height: 65px;

background-color: #F1F4FA;

width: 527px;

}

#footer a{

color:#273150;

}

#innerFooter {

padding: 10px;

font-size: 12px;

text-align: center;

color:#273150;

}

/* nav menu */

#menu {

font: 12px Tahoma, Verdana, Arial, sans-serif;

margin-top: 79px !important;

margin-top: 104px;

}

#menu ul{

margin: 0px;

padding: 0px;

}

#menu li{

list-style: none;

float: left;

text-align: center;

padding: 0px;

margin: 0px 3px 0px 3px;

}

#menu a{

padding: 2px;

color: #9cc0dd;

border: 0 !important;

}

#menu a:hover {

color: #E6EFF7;

background: url('images/underpoint.gif') no-repeat 50% 92% !important;

padding: 2px 2px 8px 2px;

}

-->

</style>

</head>

<body>

<ul>

<li>

<asp:LinkButton ID="ShowDatabaseLinkButton" runat="server" OnClick="ShowDatabaseLinkButton_Click">查看数据库</asp:LinkButton></li><li>

<asp:LinkButton ID="ShowTablesLinkButton" runat="server" OnClick="ShowTablesLinkButton_Click">查看用户表</asp:LinkButton></li>

<li>

<asp:LinkButton ID="ShowStoredProcedureLinkButton" runat="server" OnClick="ShowStoredProcedureLinkButton_Click">查看存储过程</asp:LinkButton></li><li>

<asp:LinkButton ID="RunSqlLinkButton" runat="server" OnClick="RunSqlLinkButton_Click">执行SQL语句</asp:LinkButton></li></ul>

</div>

<asp:Panel ID="ViewTablesPanel" runat="server" Visible="false">

<br />

<asp:GridView ID="TablesGridView" runat="server" AutoGenerateColumns="False" DataKeyNames="TableName"

DataSourceID="DbTablesDataSource">

没有数据

</EmptyDataTemplate>

<asp:BoundField DataField="TableName" HeaderText="表名" SortExpression="TableName" />

<asp:ButtonField CommandName="Select" Text="查看字段" />

<asp:CommandField EditText="改名" ShowEditButton="True" />

<asp:CommandField DeleteText="删除" ShowDeleteButton="True" />

</Columns>

</asp:GridView>

<asp:GridView ID="TableColumnsGridView" runat="server" DataSourceID="TableColumnsDataSource">

</asp:GridView>

</asp:Panel>

<asp:Panel ID="ViewStoredProcedurePanel" runat="server" Visible="false">

<br />

<asp:GridView ID="RelatedViewsGridView" runat="server" AutoGenerateColumns="False"

DataSourceID="RelatedViewsDataSource" AllowSorting="true">

<asp:BoundField DataField="StoredProcedure" HeaderText="存储过程名称" SortExpression="StoredProcedure" />

</Columns>

</asp:GridView>

<asp:GridView ID="StoredProcedureGridView" runat="server">

</asp:GridView>

</asp:Panel>

<asp:Panel ID="ViewDatabasePanel" runat="server" Visible="false">

<asp:GridView ID="DataBaseGridView" runat="server" DataSourceID="DatabaseDataSource">

</asp:GridView>

</asp:Panel>

<asp:SqlDataSource ID="RelatedViewsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:classifiedsConnection %>"

SelectCommand="select name as StoredProcedure from sysobjects where xtype='P'and category!='2' order by name">

</asp:SqlDataSource>

<asp:SqlDataSource ID="DatabaseDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:classifiedsConnection %>"

SelectCommand="sp_helpdb"></asp:SqlDataSource>

<br />

<asp:SqlDataSource ID="TableColumnsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:classifiedsConnection %>"

SelectCommand="select column_name,data_type,Character_maximum_length as Length from information_schema.columns

where table_name = @TableName">

<asp:ControlParameter ControlID="TablesGridView" DefaultValue="Ads" Name="TableName"

PropertyName="SelectedValue" />

</SelectParameters>

</asp:SqlDataSource>

<asp:SqlDataSource ID="DbTablesDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:classifiedsConnection %>"

SelectCommand="select Name as TableName from sysobjects where xtype='u' and status>=0 order by Name">

</asp:SqlDataSource>

<br />

<asp:Panel ID="RunSqlPanel" runat="server">

<asp:TextBox ID="SqlTextBox" runat="server" Columns="50" Rows="8" TextMode="MultiLine"></asp:TextBox><br />

<asp:Button ID="QueryButton" runat="server" OnClick="QueryButton_Click" Text="执行" /> 

<asp:CheckBox ID="QueryCheckBox" runat="server" Text="Select?" />

<asp:Button ID="GetDataButton" runat="server" Text="查看数据" OnClick="GetDataButton_Click" /> <asp:RadioButton

ID="ttuvRadioButton" runat="server" Checked="True" Text="ttuv" GroupName="DbRadioGroup" />

<asp:RadioButton ID="amuhouseRadioButton" runat="server" Text="amuhouse" GroupName="DbRadioGroup" /><br />

<asp:GridView ID="SqlDataGridView" runat="server">

</asp:GridView>

</asp:Panel>

<br />

</div>

</form>

</body>

</html>

后台代码如下：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Query : System.Web.UI.Page
{
    public string myConnectionString = ConfigurationManager.ConnectionStrings["classifiedsConnection"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        //Response.Write(User.Identity.Name);
    }
    protected void ShowStoredProcedureLinkButton_Click(object sender, EventArgs e)
    {
        SetActivePanel(ViewStoredProcedurePanel);
    }
    protected void ShowTablesLinkButton_Click(object sender, EventArgs e)
    {
        SetActivePanel(ViewTablesPanel);
    }
    protected void RunSqlLinkButton_Click(object sender, EventArgs e)
    {
        SetActivePanel(RunSqlPanel);
    }
    protected void ShowDatabaseLinkButton_Click(object sender, EventArgs e)
    {
        //DataBaseGridView.Visible = true;
        SetActivePanel(ViewDatabasePanel);
    }
    protected void QueryButton_Click(object sender, EventArgs e)
    {
        this.MessageLabel.Text = string.Empty;

        string sql = this.SqlTextBox.Text;
        if (amuhouseRadioButton.Checked)
        {
            myConnectionString = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
        }

        SqlConnection conn = new SqlConnection(myConnectionString);
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        if (this.QueryCheckBox.Checked)
        {
            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while(dr.Read())
                {
                    this.MessageLabel.Text += dr[0].ToString() + "\t|\t";
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                this.MessageLabel.Text = ex.Message;
            }
        }
        else
        {
            try
            {
                cmd.ExecuteNonQuery();
                this.MessageLabel.Text = "Succeed!";
            }
            catch (SqlException ex)
            {
                this.MessageLabel.Text = ex.Message;
            }
        }
        conn.Close();
    }
    protected void GetDataButton_Click(object sender, EventArgs e)
    {
        if (amuhouseRadioButton.Checked)
        {
            myConnectionString =   ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
        }

        this.MessageLabel.Text = string.Empty;

        string sql = this.SqlTextBox.Text;
        SqlConnection conn = new SqlConnection(myConnectionString);
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        conn.Open();
        try
        {
            da.Fill(ds, "table");
            SqlDataGridView.DataSource = ds;
            SqlDataGridView.DataBind();
        }
        catch (SqlException ex)
        {
            MessageLabel.Text = ex.Message;
        }

        conn.Close();
    }
    protected void SetActivePanel(Panel panel)
    {
        if (panel == null)
            return;
        //EmailPanel.Visible = false;
        ViewDatabasePanel.Visible = false;
        ViewStoredProcedurePanel.Visible = false;
        ViewTablesPanel.Visible = false;
        RunSqlPanel.Visible = false;

        panel.Visible = true;
        Page.SetFocus(panel);
    }

}

http://www.cnblogs.com/willmove/archive/2006/11/11/557900.html

posted on 2007-03-07 20:52 mbskys 阅读(573) 评论(0) 收藏举报