asp.net 读取Sql2005 过程,函数等脚本

最新推荐文章于 2024-05-11 19:42:25 发布

dfjtnx7132

最新推荐文章于 2024-05-11 19:42:25 发布

阅读量65

点赞数

文章标签： xhtml ui c#

原文链接：http://www.cnblogs.com/JL1983/archive/2013/03/28/2986985.html

版权

最近做了一个读取Sql2005各种脚本的例子

下面是页面代码：

View Code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TestWeb._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        数据库连接字符：<asp:TextBox runat="server" ID="txtstr" TextMode="MultiLine" Width="90%"></asp:TextBox></div>
    <div>
        脚本类型:<asp:DropDownList runat="server" ID="ddltype">
            <asp:ListItem Text="存储过程" Value="P"></asp:ListItem>
            <asp:ListItem Text="表值函数" Value="TF"></asp:ListItem>
            <asp:ListItem Text="标量函数" Value="FN"></asp:ListItem>
            <asp:ListItem Text="内嵌表函数" Value="IF"></asp:ListItem>
            <asp:ListItem Text="用户表" Value="U"></asp:ListItem>
            <asp:ListItem Text="系统表" Value="S"></asp:ListItem>
            <asp:ListItem Text="视图" Value="V"></asp:ListItem>
            <asp:ListItem Text="触发器" Value="TR"></asp:ListItem>
            <asp:ListItem Text="CHECK约束" Value="C"></asp:ListItem>
            <asp:ListItem Text="默认值或DEFAULT约束" Value="D"></asp:ListItem>
            <asp:ListItem Text="Foreign key 约束" Value="F"></asp:ListItem>
            <asp:ListItem Text="日志" Value="L"></asp:ListItem>
            <asp:ListItem Text="PRIMARY KEY 约束（类型是 K）" Value="PK"></asp:ListItem>
            <asp:ListItem Text="复制筛选存储过程" Value="RF"></asp:ListItem>
            <asp:ListItem Text="UNIQUE 约束（类型是 K）" Value="UQ"></asp:ListItem>
            <asp:ListItem Text="扩展存储过程" Value="X"></asp:ListItem>
        </asp:DropDownList>
        脚步模糊查询：
        <asp:TextBox runat="server" ID="txtP" Text="SPS_Portal,SPS_Proc" Width="300px" Height="30px"></asp:TextBox>
        <asp:Button runat="server" ID="btn" Text="确定" OnClick="btn_Click" />
    </div>
    </form>
</body>
</html>

后台代码：

View Code

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

namespace TestWeb
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                txtstr.Text = WebConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
            ShowInfo();
        }
        void ShowInfo()
        {
            string[] v = this.txtP.Text.Split(',');
            DataHelper dh = new DataHelper();
            dh.StrConn = txtstr.Text;
            DataSet ds = dh.GetDataSet(dh.GetSqlStr(ddltype.SelectedValue, v));
            StringBuilder str = new StringBuilder();
            str.AppendFormat("USE [ScheduleDB]  {0}<br/>", System.Environment.NewLine);
            str.AppendFormat("GO  {0}<br/>", System.Environment.NewLine);
            str.AppendFormat("SET ANSI_NULLS ON  {0}<br/>", System.Environment.NewLine);
            str.AppendFormat("GO  {0}<br/>", System.Environment.NewLine);
            str.AppendFormat("SET QUOTED_IDENTIFIER ON  {0}<br/>", System.Environment.NewLine);
            str.AppendFormat("GO  {0}<br/>", System.Environment.NewLine);

            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataTable dt in ds.Tables)
                {

                    foreach (DataRow dr in dt.Rows)
                    {
                        str.AppendFormat("{0}{1}<br/>", dr["Text"], System.Environment.NewLine);
                    }
                    str.AppendFormat("GO {0}<br/>", System.Environment.NewLine);
                }
            }
            Response.Write(str.ToString().Replace(" ", "&nbsp;"));

        }
        /// <summary>
        /// 展示脚本
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btn_Click(object sender, EventArgs e)
        {
            ShowInfo();
        }
    }
    public class DataHelper
    {
        /// <summary>
        /// 获得sql语句
        /// </summary>
        /// <returns></returns>
        public string GetSqlStr(string xtype, params string[] v)
        {
            StringBuilder strSql = new StringBuilder();
            foreach (string where in v)
            {
                strSql.AppendFormat("select 'exec sp_helptext ' +'''dbo.'+[name] +''';' from sysobjects where xtype='{0}' and name like '%{1}%' order by [name]   {2}", xtype, where, System.Environment.NewLine);
            }
            StringBuilder str = new StringBuilder();
            DataSet ds = GetDataSet(strSql.ToString());
            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataTable dt in ds.Tables)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        str.AppendFormat("{0}{1}", dr["Column1"].ToString(), System.Environment.NewLine);
                    }
                }
            }

            return str.ToString();
        }
        private string _StrConn;

        public string StrConn
        {
            get
            {
                if (!string.IsNullOrEmpty(_StrConn))
                    return _StrConn;
                return WebConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
            }
            set { _StrConn = value; }
        }
        private SqlConnection Conn
        {
            get
            {
                return new SqlConnection(StrConn);
            }
        }
        public DataSet GetDataSet(string sqlStr)
        {
            if (string.IsNullOrEmpty(sqlStr))
                return null;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Conn;
            cmd.CommandText = sqlStr;
            da.SelectCommand = cmd;
            da.Fill(ds);
            return ds;
        }
    }
}

转载于:https://www.cnblogs.com/JL1983/archive/2013/03/28/2986985.html

dfjtnx7132

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
asp.net 读取Sql2005 过程,函数等脚本

最近做了一个读取Sql2005各种脚本的例子下面是页面代码：View Code <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TestWeb._Default" %><!DOCTYPE html PUBLIC "-//W3C...
复制链接

扫一扫