首页 新闻 会员 周边

前台写sql语句执行查询结果

0
[已关闭问题]

细说就是如:前台输入select * from 表A where id=123 然后就显示数据库中记录,我以前在博客里面看过,现在找不到了,谁帮我找找看看,谢谢了

老鼠的主页 老鼠 | 初学一级 | 园豆:145
提问于:2009-08-10 16:08
< >
分享
其他回答(1)
0
MartinGao | 园豆:663 (小虾三级) | 2009-08-10 18:40
0

没什么难度啊,只要把输入的文本直接作为SQL命令传入数据库,再返回数据就可以了啊。

给你一个我写好的用户控件:

前台:

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="SqlOperate.ascx.cs" Inherits="MySite.Admin.SuperAdmin.SqlOperate" %>
<p>查阅表:<asp:DropDownList ID="DropDownList1" runat="server">
    </asp:DropDownList>
    <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="查看" />
</p>
SQL指令:<br />
<asp:TextBox ID="TextBox1" runat="server" Rows="8" TextMode="MultiLine" Width="500px"></asp:TextBox>
<p>
    <asp:Button ID="Button1" runat="server" Text="执行" onclick="Button1_Click" /></p>
    <p>
        <asp:Label ID="Label1" runat="server" ForeColor="Red"></asp:Label></p>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

后台:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Text.RegularExpressions;

namespace MySite.Admin.SuperAdmin
{
    public partial class SqlOperate : System.Web.UI.UserControl
    {
        static readonly Regex 表达式 = new Regex(@"\s+", RegexOptions.Compiled);

        /// <summary>
        /// 数据库连接字串名属性,基于ViewState
        /// </summary>
        [CategoryAttribute("行为"), DescriptionAttribute("Web.Config中定义的connectionStrings名称"), DefaultValue(null)]
        public string 数据库连接字串名
        {
            get
            {
                if (ViewState["数据库连接字串名"] == null) return null;
                return (string)ViewState["数据库连接字串名"];
            }
            set
            {
                ViewState["数据库连接字串名"] = value;
            }
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (string.IsNullOrEmpty(数据库连接字串名)) throw new Exception("数据库连接字串名未被指定有效值");
                using (SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings[数据库连接字串名].ConnectionString))
                {
                    c.Open();
                    try
                    {
                        SqlCommand cmd = new SqlCommand("SELECT OBJECT_NAME (id) FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0", c);
                        SqlDataReader dr = cmd.ExecuteReader();
                        var l = new List<string>();
                        while (dr.Read())
                        {
                            l.Add(dr.GetString(0));
                        }
                        l.Sort();
                        DropDownList1.DataSource = l;
                        DropDownList1.DataBind();
                    }
                    catch (Exception er)
                    {
                        Label1.Text = er.Message;
                    }
                    finally
                    {
                        c.Close();
                    }
                }
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            var t = string.Format("SELECT TOP 100 * FROM [{0}]", DropDownList1.SelectedItem);
            TextBox1.Text = t;
            执行SQL指令(t);
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            执行SQL指令(TextBox1.Text);
        }

        /// <summary>
        /// 执行SQL指令事件代理
        /// </summary>
        public delegate void 执行SQL指令代理(object sender, string 指令, int 改变记录数);

        /// <summary>
        /// 执行SQL指令事件
        /// </summary>
        public event 执行SQL指令代理 执行SQL指令事件;

        protected virtual void 触发执行SQL指令事件(string 指令, int 改变记录数)
        {
            if (执行SQL指令事件 != null) 执行SQL指令事件(this, 指令, 改变记录数);
        }

        protected void 执行SQL指令(string SQL指令)
        {
            using (SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings[数据库连接字串名].ConnectionString))
            {
                c.Open();
                try
                {
                    SqlCommand cmd = new SqlCommand(SQL指令, c);
                    var table = new DataTable();
                    var b = true;
                    var d = 0;
                    var s = new string[] { "update", "delete", "truncate" };
                    foreach (var f in 表达式.Split(SQL指令.ToLower()))
                    {
                        if (s.Contains(f))
                        {
                            b = false;
                            break;
                        }
                    }
                    if (b) new SqlDataAdapter(cmd).Fill(table);
                    else
                    {
                        d = cmd.ExecuteNonQuery();
                        table.Columns.Add("影响记录总数");
                        table.Rows.Add(d);
                    }
                    GridView1.DataSource = table;
                    GridView1.DataBind();
                    Label1.Text = string.Empty;
                    触发执行SQL指令事件(SQL指令, d);
                }
                catch (Exception er)
                {
                    Label1.Text = er.Message;
                }
                finally
                {
                    c.Close();
                }
            }
        }
    }
}

斯克迪亚 | 园豆:4124 (老鸟四级) | 2009-08-10 23:41
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册