asp.net 读取Sql2005 过程,函数等脚本
最近做了一个读取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(" ", " ")); } /// <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; } } }
