ASP.NET 数据库访问通用工具-阿里云开发者社区

开发者社区> 清山> 正文

ASP.NET 数据库访问通用工具

简介:
+关注继续查看

 在工作中,有很多项目已上线后,很多项目的数据库服务器都不会对外开放的,外网想直接访问客户数据库服务器时,可能会出现困难。

 这时就需要一个可以查询,更新数据库操作的页面了;

 本来用sql语句直接操作数据库数据是不好的,所以此页面工具只针对已经非常熟悉数据库结构的人来使用,因为可以执行更新/修改语句,请慎用。

 目前没对这个页面做任何的访问限制,出于对数据的安全性考虑,强烈建议对本页面的访问做访问限制处理!

效果图:


在线体验:(个人站点域名审核还未通过,敬请关注害羞

打包下载:http://download.csdn.net/detail/a497785609/7283839

源代码:

为了方便使用,所有代码都写在了一个apsx页面,便于携带,另存为一个aspx,放到您的站点访问即可。


<%@ Page Language="C#" %>

<%@ Import Namespace="System" %>
<%@ 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">

    /// <summary>
    /// 【数据库连接字符串】
    /// 注意:在这里需与webconfig里的数据库连接字符串配置节一致;
    /// </summary>
    protected string Connection = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

    /// <summary>
    /// 所有数据表,前台js使用
    /// </summary>
    protected string DataTables { get; set; }
    /// <summary>
    /// 所有字段,前台js使用
    /// </summary>
    protected string Fields { get; set; }

    /// <summary>
    ///     判断连接是否成功!
    /// </summary>
    /// <param name="con"> 链接字符串</param>
    /// <returns>true 表示链接成功,false表示连接失败</returns>
    public static string CheckConnection(string con)
    {
        string status = "";
        var conn = new SqlConnection(con);
        try
        {
            conn.Open();
            status = "true|";
        }
        catch (Exception ex)
        {
            status = "false|" + ex.Message;
        }
        finally
        {
            conn.Close();
        }
        return status;
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (CheckConnection(Connection).ToLower().StartsWith("false"))
        {
            Response.Clear();
            Response.Write("<span style='color:red'>对不起,数据库链接失败.<br/><br/>请检查数据库链接:\"" + Connection + "\" 是否正确!</span>");
            Response.End();
        }
        else
        {
            //所有表
            if (Cache.Get("tables") == null)
            {
                List<string> tables = GetTableNames(Connection);
                DataTables = "[";
                foreach (string table in tables)
                {
                    DataTables += "'" + table + "',";
                }
                DataTables = DataTables.TrimEnd(',');
                DataTables += "]";
                Cache.Insert("tables", DataTables);
            }
            else
            {
                DataTables = Cache.Get("tables").ToString();
            }

            //所有字段
            if (Cache.Get("fields") == null)
            {
                DataTable fields = GetFileds(Connection);
                Fields = "[";
                foreach (DataRow row in fields.Rows)
                {
                    Fields += "{tName:'" + row["表名"] + "',fName:'" + row["字段名"] + "'},";
                }
                Fields = Fields.TrimEnd(',');
                Fields += "]";
                Cache.Insert("fields", Fields);
            }
            else
            {
                Fields = Cache.Get("fields").ToString();
            }

            if (!IsPostBack)
            {
            }
        }
    }

    #region 查询

    /// <summary>
    /// 需过滤掉的关键字
    /// </summary>
    public static string[] ForbidSqlKeyword = { "ALTER", "UPDATE", "DELETE", "DROP", "CREATE", "COMMIT" };

    public static bool IsContainDangerWords(string sql)
    {
        bool haveForbidWord = false;
        sql = sql.ToLower().Replace("\r", " ").Replace("\n", " ");
        string[] sqlWordArr = sql.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries);
        foreach (string word in sqlWordArr)
        {
            foreach (string forbidWord in ForbidSqlKeyword)
            {
                if (String.Compare(forbidWord.Trim(), word.Trim(), StringComparison.OrdinalIgnoreCase) == 0)
                {
                    haveForbidWord = true;
                    break;
                }
            }
        }
        return haveForbidWord;
    }

    /// <summary>
    /// 查询
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void BtnQueryClick(object sender, EventArgs e)
    {
        string sql = txtSql.Text.Trim();
        if (!IsContainDangerWords(sql))
        {
            DataTable data = ExcuteDataTable(sql, Connection);
            string html = GetHtmlTable(data);
            ltrResult.Text = html;
        }
        else
        {
            var dt = new DataTable();
            dt.Columns.Add("执行结果");
            DataRow row = dt.NewRow();
            row["执行结果"] = "<span class='fail'>查询语句中含有修改数据相关的关键字,操作已忽略</span>";
            dt.Rows.Add(row);
            string html = GetHtmlTable(dt);
            ltrResult.Text = html;
        }
    }

    #endregion

    #region 执行

    /// <summary>
    /// 执行
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void BtnExcuteClick(object sender, EventArgs e)
    {
        var dt = new DataTable();
        string sql = txtSql.Text.Trim();
        try
        {
            if (Execute(sql, Connection))
            {
                dt.Columns.Add("执行结果");
                DataRow row = dt.NewRow();
                row["执行结果"] = "<span class='sucess'>执行成功</span>";
                dt.Rows.Add(row);
            }
            else
            {
                dt.Columns.Add("执行结果");
                DataRow row = dt.NewRow();
                row["执行结果"] = "<span class='fail'>执行失败</span>";
                dt.Rows.Add(row);
            }
        }
        catch (Exception ex)
        {
            dt.Columns.Add("异常信息");
            DataRow row = dt.NewRow();
            row["异常信息"] = ex.Message;
            dt.Rows.Add(row);
        }
        string html = GetHtmlTable(dt);
        ltrResult.Text = html;
    }

    #endregion

    #region 执行SQL语句返回DataTable

    /// <summary>
    ///     执行SQL语句返回DataTable
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="con"></param>
    /// <returns></returns>
    public static DataTable ExcuteDataTable(string sql, string con)
    {
        var cmd = new SqlCommand();
        var connection = new SqlConnection(con);
        try
        {
            using (connection)
            {
                cmd.Connection = connection;
                cmd.CommandText = sql;
                connection.Open();
                var da = new SqlDataAdapter(cmd);
                var ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
        }
        catch (Exception ex)
        {
            var dt = new DataTable();
            dt.Columns.Add("异常信息");
            DataRow row = dt.NewRow();
            row["异常信息"] = ex.Message;
            dt.Rows.Add(row);
            return dt;
        }
    }

    #endregion

    #region DataSet转换为Html表格

    /// <summary>
    /// 将DataSet转换为Html表格
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static string GetHtmlTable(DataTable dt)
    {
        var html = new StringBuilder();
        if (dt.Rows.Count == 0)
        {
            html.Append(
                @"<table class='data'  cellspacing='1' cellpadding='0' border='1' style='width: 100%;'>
                                                            <tbody>
                                                                <tr class='tdbg' align='center' style='height: 100px;'>
                                                                    <td colspan='12'>
                                                                        没有任何记录!
                                                                    </td>
                                                                </tr>
                                                            </tbody>
                                                        </table>");
        }
        else
        {
            #region 表头行

            html.Append(@"<table class='data' cellspacing='1' cellpadding='0' border='1'  style='width: 100%;'>");
            html.Append(@"<tr class='gridtitle'>");
            foreach (DataColumn columns in dt.Columns)
            {
                string title = columns.ColumnName;
                html.Append(string.Format(@"<th scope='col' class='data_th'>{0}</th>", title));
            }
            html.Append(@"</tr>");

            #endregion

            #region 表数据行

            foreach (DataRow row in dt.Rows)
            {
                html.Append(string.Format("<tr class='data_tr'>"));
                foreach (DataColumn columns in dt.Columns)
                {
                    string cellValue = row[columns.ColumnName].ToString();
                    html.Append(string.Format("<td class='data_td'>{0}</td>", cellValue));
                }
                html.Append(@"</tr>");
            }

            #endregion

            #region 表尾

            html.Append(string.Format("<tr class='data_tr'>"));
            html.Append(string.Format("<td  class='data_td' colspan='{0}'>合计:{1}条记录</td>", dt.Columns.Count,
                                      dt.Rows.Count));
            html.Append(@"</tr>");
            html.Append(@"</table>");

            #endregion
        }
        return html.ToString();
    }

    #endregion

    #region 执行不带参数sql语句

    /// <summary>
    ///     执行不带参数sql语句
    /// </summary>
    /// <param name="sql">增,删,改sql语句</param>
    /// <param name="con"></param>
    /// <returns>返回所影响的行数</returns>
    public static bool Execute(string sql, string con)
    {
        var cmd = new SqlCommand();
        var connection = new SqlConnection(con);
        try
        {
            using (connection)
            {
                cmd.Connection = connection;
                cmd.CommandText = sql;
                connection.Open();
                cmd.ExecuteNonQuery();
                return true;
            }
        }
        catch (Exception)
        {
            return false;
        }
    }

    #endregion

    /// <summary>
    ///     获取指定数据库中所有表的名字;
    /// </summary> 
    /// <param name="con">连接字符串</param>
    /// <returns>所有表名的集合</returns>
    public static List<string> GetTableNames(string con)
    {
        var tableNames = new List<string>();
        try
        {
            var regex = new Regex(
                "(?i)(Initial Catalog|database)=(?<database>[^;]*);",
                RegexOptions.CultureInvariant
                | RegexOptions.Compiled
                );
            Group database = regex.Match(con).Groups["database"];
            string sql =
                "SELECT  SYSOBJECTS.NAME   FROM   SYSOBJECTS,MASTER..SYSDATABASES T  WHERE TYPE='U'AND T.NAME='" +
                database + "' ORDER BY SYSOBJECTS.NAME ASC";
            var cmd = new SqlCommand();
            var sqlcon = new SqlConnection(con);
            using (sqlcon)
            {
                cmd.Connection = sqlcon;
                cmd.CommandText = sql;
                sqlcon.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    tableNames.Add(sdr[0].ToString());
                }
            }
        }
        catch (Exception ex)
        {
            tableNames.Add("异常:" + ex.Message);
        }
        return tableNames;
    }

    /// <summary>
    /// 取得所有字段
    /// </summary>
    /// <param name="connection"></param>
    /// <returns></returns>
    public DataTable GetFileds(string connection)
    {
        const string sql = @" 
SELECT 表名 = O.name,
       字段名 = C.name
FROM   sys.columns C
       INNER JOIN sys.objects O
            ON  C.[object_id] = O.[object_id]
            AND O.type = 'U'
            AND O.is_ms_shipped = 0
       INNER JOIN sys.types T
            ON  C.user_type_id = T.user_type_id
       LEFT JOIN sys.default_constraints D
            ON  C.[object_id] = D.parent_object_id
            AND C.column_id = D.parent_column_id
            AND C.default_object_id = D.[object_id]
       LEFT JOIN sys.extended_properties PFD
            ON  PFD.class = 1
            AND C.[object_id] = PFD.major_id
            AND C.column_id = PFD.minor_id
       LEFT JOIN sys.extended_properties PTB
            ON  PTB.class = 1
            AND PTB.minor_id = 0
            AND C.[object_id] = PTB.major_id
       LEFT JOIN -- 索引及主键信息  
            (
                SELECT IDXC.[object_id],
                       IDXC.column_id,
                       Sort = CASE INDEXKEY_PROPERTY(
                                       IDXC.[object_id],
                                       IDXC.index_id,
                                       IDXC.index_column_id,
                                       'IsDescending'
                                   )
                                   WHEN 1 THEN 'DESC'
                                   WHEN 0 THEN 'ASC'
                                   ELSE ''
                              END,
                       PrimaryKey = CASE 
                                         WHEN IDX.is_primary_key = 1 THEN N'√'
                                         ELSE N''
                                    END,
                       IndexName = IDX.Name
                FROM   sys.indexes IDX
                       INNER JOIN sys.index_columns IDXC
                            ON  IDX.[object_id] = IDXC.[object_id]
                            AND IDX.index_id = IDXC.index_id
                       LEFT JOIN sys.key_constraints KC
                            ON  IDX.[object_id] = KC.[parent_object_id]
                            AND IDX.index_id = KC.unique_index_id
                       INNER JOIN -- 对于一个列包含多个索引的情况,只显示第个索引信息  
                            (
                                SELECT [object_id],
                                       Column_id,
                                       index_id = MIN(index_id)
                                FROM   sys.index_columns
                                GROUP BY
                                       [object_id],
                                       Column_id
                            ) IDXCUQ
                            ON  IDXC.[object_id] = IDXCUQ.[object_id]
                            AND IDXC.Column_id = IDXCUQ.Column_id
                            AND IDXC.index_id = IDXCUQ.index_id
            ) IDX
            ON  C.[object_id] = IDX.[object_id]
            AND C.column_id = IDX.column_id
ORDER BY
       O.name,
       C.column_id";
        return ExcuteDataTable(sql, Connection);
    }


</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>数据库管理工具_V1.0</title>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"> </script>
    <style type="text/css">
        body { font-size: 12px; }
        td, th { border: 1px solid gray; border-collapse: collapse; display: table-cell; line-height: 22px; vertical-align: inherit; }
        table { border-bottom-width: 0px; border-collapse: collapse; border-color: gray; border-color: gray; border-left-width: 0px; border-right-width: 0px; border-spacing: 0px; border-spacing: 0px; border-spacing: 2px; border-top-width: 0px; display: table; }
        fieldset { border: 1px solid gray; margin-bottom: 10px; padding: 8px; }
        .demo_title { background-color: lightgray; }
        .result { max-height: 400px; max-width: 100%; overflow: auto; }
        .data_th { background-color: #D3D8E0; }
        .data_td { padding-left: 5px; }
        .fail { color: red; font-weight: bold; }
        .sucess { color: green; font-weight: bold; }
        .demo_main { }
        .demo_content { }
        .keyword { background-color: lavender; border: 1px dashed darkgray; border-radius: 4px; cursor: pointer; margin-right: 10px; padding: 5px; }
        textarea { -moz-box-shadow: 1px 1px 0 #E7E7E7; -moz-box-sizing: border-box; border-color: #CCCCCC #999999 #999999 #CCCCCC; border-style: solid; border-width: 1px; font-family: arial, sans-serif; font-size: 13px; height: 160px; margin: 10px auto; outline-color: -moz-use-text-color; outline-style: none; outline-width: medium; padding: 2px; width: 100%; }
        select { height: 25px; border: 1px solid lightgray; }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <div class="demo_main">
        <fieldset class="demo_title">
            数据库管理工具_V1.0
        </fieldset>
        <fieldset class="demo_content">
            <legend>查询语句: </legend>
            <asp:TextBox runat="server" ID="txtSql" Style="text-transform: uppercase" TextMode="MultiLine"
                Width="99%" Height="100"></asp:TextBox>
            <p style="margin: 0; padding: 8px 0 0 0;">
                可用表名:
                <select id="selectTables" style="width: 200px;">
                </select>
                可用字段:
                <select id="selectFields" style="width: 200px;">
                    <option>请选择表名</option>
                </select>
                可用变量: <span class="keyword">SELECT</span><span class="keyword">TOP</span><span class="keyword">100</span><span
                    class="keyword">*</span><span class="keyword">FROM</span><span class="keyword">DELETE</span><span
                        class="keyword">UPDATE </span><span class="keyword">CREATE</span>
            </p>
        </fieldset>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <fieldset class="demo_content">
                    <legend>操作选项</legend>
                    <asp:Button runat="server" ID="btnQuery" Text="查询" OnClick="BtnQueryClick" />
                    <asp:Button runat="server" ID="btnExcute" Text="执行" OnClientClick=" return confirm('确定SQL语句正确并立即执行吗?') "
                        OnClick="BtnExcuteClick" />
                    <asp:UpdateProgress ID="UpdateProgress1" runat="server">
                        <ProgressTemplate>
                            <asp:Label ForeColor="green" runat="server" ID="lblStatus" Text="处理中,请稍候..." />
                        </ProgressTemplate>
                    </asp:UpdateProgress>
                </fieldset>
                <fieldset class="demo_content">
                    <legend>查询结果:</legend>
                    <div class="result">
                        <asp:Literal runat="server" ID="ltrResult"></asp:Literal>
                    </div>
                </fieldset>
            </ContentTemplate>
        </asp:UpdatePanel>
    </div>
    <script type="text/javascript">
        //====================================在文本框指定位置插入值=======================================
        (function ($) {
            $.fn.extend({
                insertAtCaret: function (myValue) {
                    var $t = $(this)[0];
                    if (document.selection) {
                        this.focus();
                        var sel = document.selection.createRange();
                        sel.text = myValue;
                        this.focus();
                    } else if ($t.selectionStart || $t.selectionStart == '0') {
                        var startPos = $t.selectionStart;
                        var endPos = $t.selectionEnd;
                        var scrollTop = $t.scrollTop;
                        $t.value = $t.value.substring(0, startPos) + myValue + $t.value.substring(endPos, $t.value.length);
                        this.focus();
                        $t.selectionStart = startPos + myValue.length;
                        $t.selectionEnd = startPos + myValue.length;
                        $t.scrollTop = scrollTop;
                    } else {
                        this.value += myValue;
                        this.focus();
                    }
                }
            });
        })(jQuery);
        //==================================================================================
        var tables = "<%= DataTables %>";
        var fields = "<%= Fields %>";
        $(function () {
            tables = eval(tables);

            for (var i = 0; i < tables.length; i++) {
                $("#selectTables").append("<option>" + tables[i] + "</option>");
            }

            //选择表
            $("#selectTables").change(function () {
                var table = $(this).val();
                fields = eval(fields);
                $("#selectFields").empty();
                for (var i = 0; i < fields.length; i++) {
                    if (fields[i].tName == table) {
                        $("#selectFields").append("<option>" + fields[i].fName + "</option>");
                    }
                }
                $("#<%= txtSql.ClientID %>").insertAtCaret(" " + table + " ");
            });

            //选择字段
            $("#selectFields").change(function () {
                var field = $(this).val();
                if (field != "请选择表名") {
                    $("#<%= txtSql.ClientID %>").insertAtCaret(" " + field + " ");
                }
            });

            //选择关键字
            $(".keyword").click(function () {
                var keyword = $(this).text();
                $("#<%= txtSql.ClientID %>").insertAtCaret(" " + keyword + " ");
            });

        });

    </script>
    </form>
</body>
</html>


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
快速搭建基于 Serverless 的 .NET Core 数据库应用
快速搭建基于 Serverless 的 .NET Core 数据库应用 简介 首先介绍下在本文出现的几个比较重要的概念: 函数计算(Function Compute):函数计算是一个事件驱动的服务,通过函数计算,用户无需管理服务器等运行情况,只需编写代码并上传。
19973 0
Linux网络进程监控工具nethogs
  Linux网络监控工具nethogs 标签: 监控工具linux 2015-12-17 22:06 448人阅读 评论(0) 收藏 举报  分类: linux(40)  版权声明:本文为博主原创文章,未经博主允许不得转载。
944 0
.NET数据库编程求索之路--8.使用LinqToSQL实现
8.使用LinqToSQL实现   源码下载:     /Files/SummerRain/NetDbDevRoad/8使用LinqToSql实现1ORM手工简单三层.rar     /Files/SummerRain/NetDbDevRoad/8使用LinqToSql实现2ORM手工工厂模式.
626 0
iis站点 asp.net网站访问弹出提示框
www.lovegarden.cc 这个网站已经出现2次这种情况了。 访问,弹出提示框,需要输入用户名和密码。 解决方法: 在用户组里新建一个用户,test002,密码,密码永不过期。 在iis站点,权限里添加该用户。“允许匿名访问”,用户选择 test002。 ok了。
563 0
.NET数据库编程求索之路--7.使用ADO.NET实现(工厂模式-实现多数据库切换)(4)
7.使用ADO.NET实现(工厂模式-实现多数据库切换)(4) 7.7 业务逻辑层HomeShop.BLL OrderBiz.cs View Code  1 using System; 2 using System.
754 0
+关注
404
文章
3
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载