加密  Cookies
Response.Cookies["askCRM"]["USERID"] = Tools.Encrypt(strUserName.Trim(), Tools.myKey);
解密
string strUid = Request.Cookies["askCRM"]["USERID"];
strUid = Tools.Decrypt(strUid, Tools.myKey);

加密解密 掉用的方法
using System;
using System.Data;
using System.Configuration;
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;
using System.Security.Cryptography;
using System.IO;
using System.Text;

/// <summary>
/// Tools 的摘要说明

/// </summary>
public class Tools
{
  

    /// <summary>
    /// 当前程序加密所使用的密钥

    /// </summary>
    public static readonly string myKey = "q0m3sd8l";

    #region 加密方法
    /// <summary>
    /// 加密方法
    /// </summary>
    /// <param name="pToEncrypt">需要加密字符串</param>
    /// <param name="sKey">密钥</param>
    /// <returns>加密后的字符串</returns>
    public static string Encrypt(string pToEncrypt, string sKey)
    {
        try
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            //把字符串放到byte数组中

            //原来使用的UTF8编码，我改成Unicode编码了，不行
            byte[] inputByteArray = Encoding.Default.GetBytes(pToEncrypt);

            //建立加密对象的密钥和偏移量

            //使得输入密码必须输入英文文本
            des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
            des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
            MemoryStream ms = new MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write);

            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            StringBuilder ret = new StringBuilder();
            foreach (byte b in ms.ToArray())
            {
                ret.AppendFormat("{0:X2}", b);
            }
            ret.ToString();
            return ret.ToString();
        }
        catch (Exception ex)
        {
            JS.Alert("写入配置信息失败，详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }

        return "";
    }
    #endregion

    #region 解密方法
    /// <summary>
    /// 解密方法
    /// </summary>
    /// <param name="pToDecrypt">需要解密的字符串</param>
    /// <param name="sKey">密匙</param>
    /// <returns>解密后的字符串</returns>
    public static string Decrypt(string pToDecrypt, string sKey)
    {
        try
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            byte[] inputByteArray = new byte[pToDecrypt.Length / 2];
            for (int x = 0; x < pToDecrypt.Length / 2; x++)
            {
                int i = (Convert.ToInt32(pToDecrypt.Substring(x * 2, 2), 16));
                inputByteArray[x] = (byte)i;
            }

            //建立加密对象的密钥和偏移量，此值重要，不能修改
            des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
            des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
            MemoryStream ms = new MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            //建立StringBuild对象，CreateDecrypt使用的是流对象，必须把解密后的文本变成流对象
            StringBuilder ret = new StringBuilder();
            return System.Text.Encoding.Default.GetString(ms.ToArray());
        }
        catch (Exception ex)
        {
            JS.Alert("读取配置信息失败，详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }
        return "";
    }
    #endregion
}

前台
<asp:TemplateColumn HeaderText="绑定网址">
            <HeaderStyle HorizontalAlign="Center" Width="100px"></HeaderStyle>
            <ItemStyle HorizontalAlign="Center"></ItemStyle>
            <ItemTemplate>
             <FONT face="宋体">
              <asp:HyperLink id="HyperLink1" runat="server" Target=_blank text='<%# PartSubString(DataBinder.Eval(Container.DataItem,"url").ToString())%>' NavigateUrl='<%# DataBinder.Eval(Container.DataItem,"url")%>' ToolTip='<%# DataBinder.Eval(Container.DataItem,"url")%>'>
              </asp:HyperLink></FONT>
            </ItemTemplate>
            <FooterStyle HorizontalAlign="Center"></FooterStyle>
           </asp:TemplateColumn>
后台.cs
protected string PartSubString(string s)
  {
   if(s.Length>15)
   {
    return s.Substring(0,15)+"..."; 
   }
   return s;
  }

 DateTime tmpDate;
       DateTime tmpDate2;
        if ((!DateTime.TryParse(this.txtBoxStartTime.Text.Trim(), out tmpDate))
        || (!DateTime.TryParse(this.txtBoxEndTime.Text.Trim(), out tmpDate2)))
        {
            JS.Alert("时间格式不正确（格式最好为：时:分:秒），请重新输入。");
            return;
        }
        else
        {
            if (tmpDate2 < tmpDate)
            {
                JS.Alert("请输入合理的开始(结束)时间");
                return;
            }
        }

刷新夫窗体用的JS

/// </summary>
public class JS
{
    public JS()
    {

    }

    /// <summary>
    /// 刷新父窗口

    /// </summary>
    public static void refreshParent(string strIndex)
    {
        
        HttpContext.Current.Response.Write("<script language=javascript>var str=window.opener.location.href; var ch=str.indexOf(\"?\"); if(ch>=0){var i=str.indexOf(\"?index\");if(i<0){i=str.indexOf(\"&index\")} if (i>=0){i=i+1;var strTmp = str.substr(i);var itemp = strTmp.indexOf(\"&\");if(itemp>0){strTmp=strTmp.substr(itemp);}str=str.replace(strTmp, \"\");}else{str+=\"&\";}}else{str+=\"?\";}str += \"index=" + strIndex + "\"; opener.document.location=str;</script>");
    }

    #region 验证COOKIE信息是否可用
    /// <summary>
    /// 判断程序所使用的相关信息是否有效

    /// </summary>
    /// <returns></returns>
    public static bool isCookiesAvailabile()
    {
        if ((null == HttpContext.Current.Request.Cookies["askCRM"])
            || (null == HttpContext.Current.Request.Cookies["askCRM"]["USERID"])
            || (null == HttpContext.Current.Request.Cookies["askCRM"]["DEPARTMENTID"])
            || (null == HttpContext.Current.Request.Cookies["askCRM"]["LEVELID"])
            || (null == HttpContext.Current.Request.Cookies["askCRM"]["ANALYSEPOWER"]))
        {
            Alert("权限验证失败（信息失效），请重新登录。");
            return false;
        }
        return true;
    }
    #endregion 验证COOKIE信息是否可用

    #region 从COOKIE读取当前用户的相当配置信息

    #region 得到当前用户的登录名
    /// <summary>
    /// 得到当前用户的登录名
    /// </summary>
    /// <returns>当前用户的登录名</returns>
    public static String getUserID()
    {
        if (isCookiesAvailabile())
        {
            string strUid = HttpContext.Current.Request.Cookies["askCRM"]["USERID"];

            if (string.IsNullOrEmpty(strUid))
            {
                JS.Alert("验证权限失败，请重新登录。");
                return null;
            }

            return Tools.Decrypt(strUid, Tools.myKey);
        }
        else
        {
            return null;
        }
    }
    #endregion 得到当前用户的登录名

    #region 得到当前用户的权限分配类型ID
    /// <summary>
    /// 得到当前用户的权限分配类型ID
    /// </summary>
    /// <returns>当前用户的权限分配类型ID</returns>
    public static String getUserLevel()
    {
        if (isCookiesAvailabile())
        {
            string strLevel = HttpContext.Current.Request.Cookies["askCRM"]["LEVELID"];

            if (string.IsNullOrEmpty(strLevel))
            {
                JS.Alert("验证权限失败，请重新登录。");
                return null;
            }

            return Tools.Decrypt(strLevel, Tools.myKey);
        }
        else
        {
            return null;
        }
    }
    #endregion 得到当前用户的权限分配类型ID

    #region 得到当前用户所在部门的
    /// <summary>
    /// 得到当前用户所在部门的ID
    /// </summary>
    /// <returns>部门ID</returns>
    public static String getUserDepartMent()
    {
        if (isCookiesAvailabile())
        {
            string strDepartmentID = HttpContext.Current.Request.Cookies["askCRM"]["DEPARTMENTID"];

            if (string.IsNullOrEmpty(strDepartmentID))
            {
                JS.Alert("验证权限失败，请重新登录。");
                return null;
            }

            return Tools.Decrypt(strDepartmentID, Tools.myKey);
        }
        else
        {
            return null;
        }
    }
    #endregion 得到当前用户所在部门的

    #region 获取当前用户分析时是否可以用全局数据
    /// <summary>
    /// 获取当前用户分析时是否可以用全局数据
    /// </summary>
    /// <returns>是否可以使用全局数据</returns>
    public static bool getUserPurview()
    {
        if (isCookiesAvailabile())
        {
            bool bRestrict;//是否准许分析所有数据

            if (!Boolean.TryParse(Tools.Decrypt(HttpContext.Current.Request.Cookies["askCRM"]["ANALYSEPOWER"], Tools.myKey), out bRestrict))
            {
                JS.Alert("验证权限失败，请重新登录。");
                return false;
            }

            return bRestrict;
        }
        else
        {
            return false;
        }
    }
    #endregion 获取当前用户分析时是否可以用全局数据
    #endregion 从COOKIE读取当前用户的相当配置信息

}

子窗体
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ChanceList.aspx.cs" Inherits="BaseOperation_ChanceList" %>

<!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>
<link href="../css/CRM.css" type="text/css" rel="stylesheet" />
<script language="javascript" type="text/javascript">
<!--

function IMG1_onclick() {
window.opener=null;
window.close();
}

// -->
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table cellpadding="0" cellspacing="0" width="100%">
            <tbody>
                <%--<table style="width: 823px; height: 284px">--%>
                <tr>
                    <td colspan="3" style="font-weight: bold; font-size: 20px; color: white; font-family: 楷体_GB2312;
                        height: 27px; background-color: #63769b">
                        <asp:Label ID="sTitle" Width="100%" runat="server" Text="添加明细"></asp:Label>
                        </td>
                </tr>
                <tr>
                    <td style="width: 149px; height: 26px">
                    </td>
                    <td style="width: 129px; height: 26px">
                    </td>
                    <td style="width: 385px; height: 26px">
                    </td>
                </tr>
                <tr>
                    <td style="width: 149px; height: 20px">
                    </td>
                    <td style="width: 129px; height: 20px">
                        产品类别：</td>
                    <td style="width: 385px; height: 20px"><asp:DropDownList ID="listProductType" runat="server" Width="160px" DataTextField="PRODUCTTYPENAME" DataValueField="PRODUCTTYPEID" AutoPostBack="True" OnSelectedIndexChanged="listProductType_SelectedIndexChanged">
                    </asp:DropDownList></td>
                </tr>
                <tr>
                    <td style="width: 149px; height: 30px">
                    </td>
                    <td style="width: 129px; color: #000000; height: 30px">
                        <font color="red">产品名称：</font></td>
                    <td style="width: 385px; height: 30px">
                        <asp:DropDownList ID="listProductName" runat="server" Width="160px" DataTextField="PRODUCTNAME" DataValueField="PRODUCTID">
                        </asp:DropDownList></td>
                </tr>
                <tr>
                    <td style="width: 149px; height: 30px;">
                    </td>
                    <td style="width: 129px; height: 30px;">
                        数量：</td>
                    <td style="width: 385px; height: 30px;">
                        <asp:TextBox ID="txtBoxNumber" runat="server" Height="20px" Width="155px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td style="width: 149px; height: 25px">
                    </td>
                    <td style="width: 129px; height: 25px">
                        总金额（元）：</td>
                    <td style="width: 385px; height: 25px">
                        <asp:TextBox ID="txtBoxMoneysum" runat="server" Height="20px" Width="155px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td style="width: 149px; height: 40px">
                    </td>
                    <td style="width: 129px; height: 40px;">
                        折扣：</td>
                    <td style="width: 385px; height: 40px;">
                        <asp:TextBox ID="txtBoxAgio" runat="server" Height="20px" Width="155px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td style="width: 149px; height: 40px">
                    </td>
                    <td style="width: 129px; height: 40px" align="right">
                        <asp:ImageButton ID="imgBtnOK" runat="server" ImageUrl="~/Images/button/button01.gif"
                            OnClick="imgBtnOK_Click" /></td>
                    <td align="left" style="width: 385px; height: 40px">
                        &nbsp; &nbsp; &nbsp;&nbsp;
                        <img id="IMG1" src="../Images/button/btClose.gif" language="javascript" onclick="return IMG1_onclick()" /></td>
                </tr>
            </tbody>
        </table>
    </div>
    </form>
</body>
</html>

    子窗体。CS
using System;
using System.Data;
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;

/// <summary>
/// 调用当前页面有两个参数需要传递：
/// [Chanceid]，标识（ID），必须传递。

/// [id]，明细标识（ID），只在查看修改明细时必须传递。

/// </summary>
public partial class BaseOperation_ChanceList : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (JS.isCookiesAvailabile())
            {
                //添加明细，必须要有ID
                if ((null == Request.QueryString["Chanceid"])
                    || (string.IsNullOrEmpty(Request.QueryString["Chanceid"].Trim())))
                {
                    return;
                }

                initProductTypeList();
                this.initProductList(this.listProductType.SelectedValue);

                if ((Request.QueryString["id"] != null)
                    && (!string.IsNullOrEmpty(Request.QueryString["id"].Trim())))
                {
                    initChanceList(Request.QueryString["id"].Trim());
                    this.sTitle.Text = this.Title = "查看/修改明细信息";
                }
            }
        }
    }

    protected void imgBtnOK_Click(object sender, ImageClickEventArgs e)
    {
        if (!JS.isCookiesAvailabile())
        {
            return;
        }

        if ((string.IsNullOrEmpty(Request.Cookies["askCRM"]["USERID"].Trim())))
        {
            JS.Alert("信息失效，请重新登录。");
            return;
        }
        if (string.IsNullOrEmpty(this.listProductName.Text.Trim()))
        {
            JS.Alert("请选择产品名称");
            return;
        }

        int iTmp;
        if ((!string.IsNullOrEmpty(this.txtBoxNumber.Text.Trim()))
            && (!Int32.TryParse(this.txtBoxNumber.Text.Trim(), out iTmp)))
        {
            JS.Alert("数量必须为数字，请重新输入。");
            return;
        }

        double dbTmp;
        if ((!string.IsNullOrEmpty(this.txtBoxMoneysum.Text.Trim())) &&
            (!Double.TryParse(this.txtBoxMoneysum.Text.Trim(), out dbTmp)))
        {
            JS.Alert("总金额必须为数字，请重新输入。");
            return;
        }

        if ((Request.QueryString["id"] != null)
            && (!string.IsNullOrEmpty(Request.QueryString["id"].Trim())))
        {
            modifyChanceListInfo(Request.QueryString["id"].Trim());
        }
        else
        {
            newChanceListInfo(Tools.Decrypt(Request.Cookies["askCRM"]["USERID"].Trim(), Tools.myKey));
        }

        //Response.Write("<script>opener.document.location=opener.document.location;</script>");
        if ((Request.QueryString["backindex"] != null) && (Request.QueryString["backindex"].Trim() != ""))
        {
            JS.refreshParent(Request.QueryString["backindex"].Trim());
        }
    }

    #region 清除所有界面文本内容

    private void clearAllText()
    {
        this.txtBoxAgio.Text = "";
        this.txtBoxMoneysum.Text = "";
        this.txtBoxNumber.Text = "";
        if (this.listProductType.Items.Count > 0)
        {
            this.listProductType.SelectedIndex = 0;
            this.initProductList(this.listProductType.SelectedValue);
        }
    }
    #endregion

    #region 初始化下拉框内容
    /// <summary>
    /// 初始化产品类型下拉框
    /// </summary>
    ///
    private void initProductTypeList()
    {
        if ((null == DbHelperSQL.dtMemoryData)
            || (!DbHelperSQL.dtMemoryData.Columns[0].Caption.Equals("PRODUCTTYPEID")))
        {
            try
            {
                DbHelperSQL.dtMemoryData = DbHelperSQL.gettable("SELECT PRODUCTTYPEID, PRODUCTTYPENAME FROM DICT_PRODUCTTYPE WHERE PRODUCTTYPEID>0;");
            }
            catch (Exception ex)
            {
                JS.Alert("初始化产品类别信息失败。详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
            }
        }

        this.listProductType.DataSource = DbHelperSQL.dtMemoryData;
        this.listProductType.DataBind();
    }

    /// <summary>
    /// 初始化产品列表

    /// </summary>
    private void initProductList(string strProductTypeId)
    {
        if (string.IsNullOrEmpty(strProductTypeId))
        {
            return;
        }

        DataTable tmpData = null;
        try
        {
            tmpData = DbHelperSQL.gettable("SELECT PRODUCTID, PRODUCTNAME FROM PRODUCTS WHERE PRODUCTTYPEID=" + strProductTypeId);
        }
        catch (Exception ex)
        {
            JS.Alert("初始化产品类别信息失败。详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }

        this.listProductName.DataSource = tmpData;
        this.listProductName.DataBind();
    }
    #endregion 初始化下拉框内容

    private void initChanceList(string strCid)
    {
        if (!DbHelperSQL.IsSafe(strCid))
        {
            JS.Alert(string.Format("参数｛{0}｝包含不合法字符。", strCid.Replace("\r\n", "\n").Replace("'", "")));
            return;
        }

        string strSql = "SELECT CHANCELIST.PRODUCTID, CHANCELIST.AMOUNT, CHANCELIST.AGIO, CHANCELIST.MONEYSUM, PRODUCTS.PRODUCTTYPEID FROM CHANCELIST LEFT OUTER JOIN PRODUCTS ON CHANCELIST.PRODUCTID = PRODUCTS.PRODUCTID WHERE CHANCEDETAILID={0}";

        try
        {
            using (DataTable dt = DbHelperSQL.gettable(string.Format(strSql, strCid)))
            {
                if ((dt != null) && (dt.Rows.Count > 0))
                {
                    this.txtBoxAgio.Text = dt.Rows[0]["AGIO"].ToString();
                    this.txtBoxMoneysum.Text = dt.Rows[0]["MONEYSUM"].ToString();
                    this.txtBoxNumber.Text = dt.Rows[0]["AMOUNT"].ToString();
                    //this.listProductType.SelectedValue = dt.Rows[0]["PRODUCTTYPEID"].ToString();
                    //this.listProductName.SelectedValue = dt.Rows[0]["PRODUCTID"].ToString();
                    initProductList(this.listProductType.SelectedValue = dt.Rows[0]["PRODUCTTYPEID"].ToString());
                }
                else
                {
                    JS.Alert(string.Format("未能读取到明细｛{0}｝的相关信息。", strCid));
                   
                }
            }
        }
        catch (Exception ex)
        {
            JS.Alert("初始化数据失败。详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }
    }

    #region 添加新的明细
    private void newChanceListInfo(string strUid)
    {
        if ((null == Request.QueryString["type"])
            || ("" == Request.QueryString["type"].Trim()))
        {
            JS.Alert("缺少类型字段。");
            return;
        }

        string strSql = "INSERT INTO CHANCELIST (CHANCEID, PRODUCTID, AMOUNT, AGIO, MONEYSUM, OPERATOR, BCHANCE) VALUES ('{0}',{1},'{2}','{3}','{4}','{5}','{6}')";

        try
        {
            int count = DbHelperSQL.ExecuteSql(string.Format(strSql
                , Request.QueryString["Chanceid"].Trim()
                , string.IsNullOrEmpty(this.listProductName.Text.Trim()) ? "0" : this.listProductName.SelectedValue
                , this.txtBoxNumber.Text.Trim()
                , this.txtBoxAgio.Text.Trim()
                , this.txtBoxMoneysum.Text.Trim()
                , strUid
                , Request.QueryString["type"].Trim()));

            if (count > 0)
            {
                JS.Alert("明细信息保存成功。");
                clearAllText();
            }
            else
            {
                JS.Alert("明细信息保存失败。请稍候再试。");
            }
        }
        catch (Exception ex)
        {
            JS.Alert("添加新明细信息失败。详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
            return;
        }
    }
    #endregion 添加新的明细

    #region 修改已存在明细的信息
    private void modifyChanceListInfo(string strCid)
    {
        string strSql = "UPDATE CHANCELIST SET PRODUCTID='{0}', AMOUNT='{1}', AGIO='{2}', MONEYSUM='{3}' WHERE CHANCEDETAILID={4}";

        try
        {
            int count = DbHelperSQL.ExecuteSql(string.Format(strSql
                , string.IsNullOrEmpty(this.listProductName.Text.Trim()) ? "0" : this.listProductName.SelectedValue
                , this.txtBoxNumber.Text.Trim()
                , this.txtBoxAgio.Text.Trim()
                , this.txtBoxMoneysum.Text.Trim()
                , strCid));

            if (count > 0)
            {
                JS.Alert("明细信息保存成功。");
            }
            else
            {
                JS.Alert("明细信息保存失败。请稍候再试。");
            }
        }
        catch (Exception ex)
        {
            JS.Alert("修改明细信息失败。详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
            return;
        }
    }
    #endregion 修改已存在明细的信息
    protected void listProductType_SelectedIndexChanged(object sender, EventArgs e)
    {
        this.initProductList(this.listProductType.SelectedValue);
    }
}
帮定用的数据库连接对象

 /// <summary>
 /// Copyright (C) 2004-2008 
 /// 数据访问基础类(基于SQLServer)
 /// 用户可以修改满足自己项目的需要。
 /// </summary>
 public abstract class DbHelperSQL
 {
  //数据库连接字符串(web.config来配置)
  //<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />  
  public static string connectionString = Tools.Decrypt(ConfigurationSettings.AppSettings["ConnectionString"], Tools.myKey);
        public static DataTable dtMemoryData = null;

  public DbHelperSQL()
  {   
  }

  #region  执行简单SQL语句

  

        public static DataTable gettable(string sql)
        {

            try
            {
                SqlConnection connection = new SqlConnection(connectionString);

                connection.Open();
                SqlDataAdapter sa = new SqlDataAdapter(sql, connection);

                DataSet ds = new DataSet();
                sa.SelectCommand.CommandTimeout = 600;
                sa.Fill(ds);

                connection.Close();

                return ds.Tables[0]; }
            catch (System.Data.SqlClient.SqlException E)
            {
             
                throw new Exception(E.Message);
            }

          }

 }

父窗题ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AddChance.aspx.cs" Inherits="BaseOperation_AddChance"%>
<%@ Register Assembly="Microsoft.Web.UI.WebControls" Namespace="Microsoft.Web.UI.WebControls"
    TagPrefix="iewc" %>
<%@ Register Assembly="WebPager" Namespace="Hooyee.WebControls" TagPrefix="cc1" %>
<%@ Register Assembly="ZoneSoft.Web.UI" Namespace="ZoneSoft.Web.UI" TagPrefix="ZoneSoft" %>

<html>
<head runat="server">
    <title>添加渠道信息</title>
    <link href="../css/CRM.css" type="text/css" rel="stylesheet" />
    <script language="javascript" src="../css/Calendar.js"></script>
     <script language="javascript">

    function viewCustomer_onclick()
    {
        if ((document.all["listCompany"].value != '') && (document.all["listCompany"].value != '0'))
        {
            window.open('AddCustomer.aspx?id=' + document.all["listCompany"].value ,'','height=550,width=700,top=100,left=100,toolbar=no,menubar=no,scrollbars=yes, resizable=yes,location=no, status=yes');
        }
    }

    function viewContact_onclick()
    {
        if ((document.all["listContact"].value != '') && (document.all["listContact"].value != '0'))
        {
            window.open('AddContact.aspx?id=' + document.all["listContact"].value ,'','height=500,width=700,top=100,left=100,toolbar=no,menubar=no,scrollbars=yes, resizable=yes,location=no, status=yes');
        }
    }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    <iewc:Toolbar ID="topToolBar" runat="server" DefaultStyle="background-repeat:repeat;border:0;background-image:url(../images/menu/silver.gif);"
                                            Height="28px" OnButtonClick="Toolbar_ButtonClick" Style="border-right: #466094 1px solid;
                                            border-top: #466094 1px solid; font-size: 12px; border-left: #466094 1px solid;
                                            width: 100%; border-bottom: #466094 1px; font-family: 宋体">
            <iewc:ToolbarButton ID="chanceInfo" DefaultStyle="background-image:url(../images/menu/silver.gif);"
                                                HoverStyle="background-image:url(../images/menu/2k3OlBarOver.gif);border:solid 1px #000080;"
                                                ImageUrl="../Images/ico/ico_18_service.gif" SelectedStyle="background-image:url(../images/menu/2k3OlBarDown.gif);"
                                                Text="渠道信息" />
            <iewc:ToolbarSeparator />
            <iewc:ToolbarButton ID="chanceListInfo" DefaultStyle="background-image:url(../images/menu/silver.gif);"
                                                HoverStyle="background-image:url(../images/menu/2k3OlBarOver.gif);border:solid 1px #000080;"
                                                ImageUrl="../Images/ico/ico_16_changestatus.gif" SelectedStyle="background-image:url(../images/menu/2k3OlBarDown.gif);"
                                                Text="渠道明细" />
            <iewc:ToolbarSeparator />
            <iewc:ToolbarLabel DefaultStyle="background-image:url(../images/menu/silver.gif);width:100%;" />
        </iewc:Toolbar>
        <asp:MultiView ID="MultiView1" runat="server" ActiveViewIndex="0">
     
            <asp:View ID="vChanceListInfo" runat="server">
                <table style="width: auto; height: 100%">
                    <tr>
                        <td colspan="3" style="font-weight: bold; font-size: 20px; color: white; font-family: 楷体_GB2312;
                            height: 27px; background-color: #63769b">
                            渠道明细</td>
                    </tr>
                    <tr>
                        <td colspan="3" style="height: 277px">
                            <table style="left: 10px; width: 100%; height: 100%">
                                <tr>
                                    <td style="width: 928px; height: 24px">
                                        <iewc:Toolbar ID="Toolbar2" runat="server" DefaultStyle="background-repeat:repeat;border:0;background-image:url(../images/menu/silver.gif);"
                                            Height="28px" OnButtonClick="Toolbar_ButtonClick" Style="border-right: #466094 1px solid;
                                            border-top: #466094 1px solid; font-size: 12px; border-left: #466094 1px solid;
                                            width: 100%; border-bottom: #466094 1px; font-family: 宋体">
                                            <iewc:ToolbarButton ID="newChanceList" DefaultStyle="background-image:url(../images/menu/silver.gif);"
                                                HoverStyle="background-image:url(../images/menu/2k3OlBarOver.gif);border:solid 1px #000080;"
                                                ImageUrl="../Images/ico/18_addResources.gif" SelectedStyle="background-image:url(../images/menu/2k3OlBarDown.gif);"
                                                Text="新建渠道明细" />
                                            <iewc:ToolbarSeparator />
                                        <iewc:ToolbarButton Text="导出到Excel" ImageUrl="../Images/ico/16_excel.gif" DefaultStyle="background-image:url(../images/menu/silver.gif);"
        ID="ChanceExcel" HoverStyle="background-image:url(../images/menu/2k3OlBarOver.gif);border:solid 1px #000080;"
        SelectedStyle="background-image:url(../images/menu/2k3OlBarDown.gif);"></iewc:ToolbarButton>
       <iewc:ToolbarSeparator></iewc:ToolbarSeparator>
                                            <iewc:ToolbarLabel DefaultStyle="background-image:url(../images/menu/silver.gif);width:100%;" />
                                        </iewc:Toolbar>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="center" style="border-right: #ff9966 1px solid; border-top: #ff9966 1px solid;
                                        border-left: #ff9966 1px solid; width: 928px; border-bottom: #ff9966 1px solid;
                                        height: 371px" valign="top">
                                        <asp:GridView ID="GridView1" runat="server" AllowPaging="false" AutoGenerateColumns="False"
                                            CellPadding="4" CssClass="Grid" DataKeyNames="CHANCEDETAILID" EnableTheming="False"
                                            ForeColor="#333333" GridLines="None" OnRowCreated="GridView1_RowCreated" OnRowDeleting="GridView1_RowDeleting"
                                            PageSize="15" Style="word-break: keep-all" Width="100%">
                                            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                            <Columns>
                                                <asp:HyperLinkField DataNavigateUrlFields="PRODUCTID" DataNavigateUrlFormatString="AddProduct.aspx?id={0}"
                                                    DataTextField="PRODUCTNAME" HeaderText="产品名称" ItemStyle-HorizontalAlign=Center>
                                                    <%--<ItemStyle Wrap="False" />--%>
                                                    <HeaderStyle Wrap="False" />
                                                </asp:HyperLinkField>
                                                <asp:BoundField DataField="AMOUNT" HeaderText="数量" ItemStyle-HorizontalAlign=Center>
                                                    <HeaderStyle Wrap="False" />
                                                </asp:BoundField>
                                                <asp:BoundField DataField="AGIO" HeaderText="折扣" ItemStyle-HorizontalAlign=Center>
                                                    <%--<ItemStyle Wrap="False" />--%>
                                                    <HeaderStyle Wrap="False" />
                                                </asp:BoundField>
                                                <asp:BoundField DataField="MONEYSUM" HeaderText="总金额" DataFormatString="{0:N}" HtmlEncode="False" ItemStyle-HorizontalAlign=Center>
                                                    <%--<ItemStyle Wrap="False" />--%>
                                                    <HeaderStyle Wrap="False" />
                                                </asp:BoundField>
                                                <asp:CommandField CausesValidation="False" HeaderText="删除" InsertVisible="False" ItemStyle-HorizontalAlign=Center
                                                    ShowCancelButton="False" ShowDeleteButton="True" />
                                            </Columns>
                                            <RowStyle BackColor="#EFF3FB" />
                                            <EditRowStyle BackColor="#2461BF" />
                                            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                                            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                                            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                            <AlternatingRowStyle BackColor="White" />
                                            <PagerSettings Visible="False" />
                                            <EmptyDataTemplate>
                                                <font color="Navy">暂时无数据内容。</font>
                                            </EmptyDataTemplate>
                                        </asp:GridView>
                                    </td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                </table>
            </asp:View>
       
        </asp:MultiView>
    </form>
</body>
</html>

父窗提.CS
using System;
using System.Data;
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;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Web.UI.WebControls;

public partial class BaseOperation_AddChance : System.Web.UI.Page
{
    private static string strSqlQuery;//存储查询串，带格式的，可配置参
    protected void Page_Load(object sender, EventArgs e)
    {
        if ((Request.QueryString["id"] != null)
            && (!string.IsNullOrEmpty(Request.QueryString["id"].Trim())))
        {
            this.sTitle.Text = this.Title = "查看/修改渠道信息";
        }

        if (!IsPostBack)
        {
            if (JS.isCookiesAvailabile())
            {
                if ((null == Request.QueryString["type"])
                    || (string.IsNullOrEmpty(Request.QueryString["type"].Trim())))
                {
                    JS.Alert("缺少类型字段。");
                    return;
                }

            

                if ((Request.QueryString["id"] != null)
                    && (!string.IsNullOrEmpty(Request.QueryString["id"].Trim()))
                    && initChanceInfo(Request.QueryString["id"].Trim()))
                {
                    this.GridView1.PageIndex = 0;
                    initChanceListInfoList();

                  
                }
             
            }
        }
    }

    #region 工具条事件（新建、打印）
    protected void Toolbar_ButtonClick(object sender, EventArgs e)
    {
        if (sender is Microsoft.Web.UI.WebControls.ToolbarButton)
        {
            ToolbarButton button = (ToolbarButton)sender;

            switch (button.ID)
            {
                case "newChanceList"://新建渠道明细
                    {
                        JS.OpenNewWindow("ChanceList.aspx?backindex=1&type=1&Chanceid=" + Request.QueryString["id"].Trim(), "", 300, 220, 400, 270);
                        break;
                    }
                case "printList"://打印查询结果
                    {
                        break;
                    }
                case "chanceInfo"://渠道信息
                    {
                        if (this.MultiView1.ActiveViewIndex != 0)
                        {
                            this.MultiView1.ActiveViewIndex = 0;
                        }
                        break;
                    }
                case "chanceListInfo"://渠道明细
                    {
                        if (this.MultiView1.ActiveViewIndex != 1)
                        {
                            this.MultiView1.ActiveViewIndex = 1;
                        }
                        break;
                    }
                case "ChanceExcel"://导出渠道列表
                    {
                        if (this.GridView1.Rows.Count == 0)
                        {
                            return;
                        }

                        DataTable ToExcelDataTable = DbHelperSQL.gettable(string.Format(strSqlQuery, "", "").Replace("TOP", ""));//导出Excel的DataTable

                        string[] listname = { "产品名称", "数量", "折扣", "总金额" };
                        string[] cols = { "PRODUCTNAME", "AMOUNT", "AGIO","MONEYSUM" };
                        ToExcel.tableToExcel(ToExcelDataTable, listname, cols);

                        break;
                    }
                default:
                    {
                        return;
                    }
             }
        }
    }
    #endregion 工具条事件（新建、打印)
    #region 初始化表格行事件
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int index = e.Row.RowIndex;
            if (index >= 0)
            {
                string cid = this.GridView1.DataKeys[index][0].ToString();

                string s1 = "window.open('ChanceList.aspx?Chanceid={0}&id={1}&backindex=1','','height=470,width=700,top=100,left=250,toolbar=no,menubar=no,scrollbars=yes, resizable=yes,location=no, status=yes')";

                e.Row.Attributes.Add("ondblclick", string.Format(s1, Request.QueryString["id"].Trim(), cid));
                e.Row.Cells[e.Row.Cells.Count - 1].Attributes.Add("onclick", "return confirm('确定要永久删除该条记录吗？');");
            }
        }
    }
    #endregion 初始化表格行事件

    #region 表格记录的删除事件
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        if ((!JS.isCookiesAvailabile()))
        {
            return;
        }

        //this.GridView1.Rows[e.RowIndex].ToString()
        try
        {
            if (DbHelperSQL.ExecuteSql("DELETE FROM CHANCELIST WHERE CHANCEDETAILID = " + this.GridView1.DataKeys[e.RowIndex].Value) > 0)
            {
                this.initChanceListInfoList();
            }
            else
            {
                JS.Alert("删除记录失败，请稍候再试。");
            }
        }
        catch (Exception ex)
        {
            JS.Alert("删除记录失败，详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }
    }
    #endregion 表格记录的删除事件

    #region 初始化渠道明细页面内容

    protected void initChanceListInfoList()
    {
        
        StringBuilder strSql = new StringBuilder("SELECT CHANCELIST.CHANCEDETAILID, CHANCELIST.AMOUNT, CHANCELIST.AGIO, CHANCELIST.MONEYSUM, PRODUCTS.PRODUCTNAME, CHANCELIST.PRODUCTID FROM PRODUCTS INNER JOIN CHANCELIST ON PRODUCTS.PRODUCTID = CHANCELIST.PRODUCTID WHERE CHANCELIST.CHANCEID =").Append(Request.QueryString["id"].Trim()).Append(" AND CHANCELIST.BCHANCE=").Append(Request.QueryString["type"].Trim());

        String strLevel = Request.Cookies["askCRM"]["LEVELID"];
        string strUid = Request.Cookies["askCRM"]["USERID"];

        if (string.IsNullOrEmpty(strLevel)
            || string.IsNullOrEmpty(strUid))
        {
            return;
        }

        strUid = Tools.Decrypt(strUid, Tools.myKey);
        strLevel = Tools.Decrypt(strLevel, Tools.myKey);

        switch (Convert.ToInt32(strLevel))
        {
            case 0://超级用户
                break;
            default://其他用户
                strSql.Append(" AND CHANCELIST.OPERATOR = '").Append(strUid).Append("'");
                break;
        }

        try
        {
            strSqlQuery = strSql.ToString();
            this.GridView1.DataSource = DbHelperSQL.gettable(strSql.ToString());
            this.GridView1.DataBind();
        }
        catch (Exception ex)
        {
            JS.Alert("初始化数据失败。详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
            return;
        }
    }
    #endregion 初始化渠道明细页面内容
}

。CS
using System;
using System.Data;
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;
using System.Text;

public partial class System_ManageDepartment : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GridView1.DataSource = null;
            BindGridView();
        }
    }

    protected void ImgDepartment_Click(object sender, ImageClickEventArgs e)
    {
        if (!JS.isCookiesAvailabile())
        {
            return;
        }

        //插入部门名称的SQL语句
        StringBuilder strsql = new StringBuilder("INSERT INTO DICT_DEPARTMENT(DEPARTMENTNAME) VALUES('")
                               .Append(txbDepartment.Text.Trim())
                               .Append("')");

        try
        {
            if (txbDepartment.Text.Trim() != "")
            {
                //插入用户增加的部门名称

                DbHelperSQL.ExecuteSql(strsql.ToString());

                //显示所有的部门名称
                BindGridView();

                //清空文本框中的内容

                txbDepartment.Text = "";
            }
            else
            {
                JS.Alert("部门名称不能为空！请输入部门名称！");
            }
        }
        catch (Exception ex)
        {
            JS.Alert("操作失败，详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }
    }

    #region 绑定GridView1
    protected void BindGridView()
    {
        if (!JS.isCookiesAvailabile())
        {
            return;
        }

        try
        {
            DataTable dt = DbHelperSQL.gettable("SELECT * FROM DICT_DEPARTMENT WHERE DEPARTMENTID>0");
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
        catch (Exception ex)
        {
            JS.Alert("操作失败，详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }
    }
    #endregion

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        BindGridView();
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        BindGridView();
    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string departmentname = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txbDepartment")).Text.Trim();
        if (!string.IsNullOrEmpty(departmentname))
        {
            StringBuilder strsql = new StringBuilder("UPDATE DICT_DEPARTMENT SET DEPARTMENTNAME='")
                                   .Append(departmentname)
                                   .Append("'")
                                   .Append(" WHERE DEPARTMENTID='")
                                   .Append(GridView1.DataKeys[e.RowIndex].Value)
                                   .Append("'");
         
            try
            {
                DbHelperSQL.ExecuteSql(strsql.ToString());
                GridView1.EditIndex = -1;
                BindGridView();
            }
            catch (Exception ex)
            {
                JS.Alert("修改部门名称失败，详细信息：" + ex.Message.Replace("\r\n", "").Replace("'", ""));
            }
        }
        else
        {
            JS.Alert("部门名称不可为空，请重新输入！");
        }
    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        StringBuilder strsql = new StringBuilder("DELETE FROM DICT_DEPARTMENT WHERE DEPARTMENTID='")
                               .Append(GridView1.DataKeys[e.RowIndex].Value)
                               .Append("'");
       
        try
        {
            DbHelperSQL.ExecuteSql(strsql.ToString());
            BindGridView();

        }
        catch (Exception ex)
        {
            JS.Alert("删除部门名称失败！" + ex.Message.Replace("\r\n", "").Replace("'", ""));
        }
    }

    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (null == this.GridView1.DataSource)
        {
            return;
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[e.Row.Cells.Count - 1].Attributes.Add("onclick", "return confirm('确定要永久删除该条记录吗？');");

            int index = e.Row.RowIndex;
            if (index >= 0)
            {
                if (this.GridView1.DataSource != null)
                {
                    DataTable dt = ((DataTable)this.GridView1.DataSource);
                    e.Row.Cells[1].Text = "分配权限";
                    e.Row.Cells[1].Attributes.Add("onclick", string.Format("window.open('DepartmentPower.aspx?id={0}','','left=250,top=100,scrollbars=yes,width=700,height=470,resizable=yes');", dt.Rows[e.Row.RowIndex]["DEPARTMENTID"]));
            
                }
            }
        }
    }
}
.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ManageDepartment .aspx.cs" Inherits="System_ManageDepartment" %>

<!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 id="Head1" runat="server">
    <title>部门管理</title>
    <link href="../css/CRM.css" type="text/css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
        <table width="100%" height="100%">
             <tr height="20px">
           <td colspan="3" style="font-weight: bold; font-size: 20px; color: white;  height: 5px;
                    font-family: 楷体_GB2312; height: 27px; background-color: #63769b">
                    部门管理
           </td>
       </tr>
            <tr height="20px">
                <td align="center" style="width: 20%; font-weight: bold; font-size:20px; color:Black; height:5px; font-family: 楷体_GB2312; height:27px;">
                   部门名称：</td>
                <td width="30%">
                    <asp:TextBox ID="txbDepartment" MaxLength="20" Width="300px" Height="20px" runat="server"></asp:TextBox>
                </td>
                <td align="left">
                    &nbsp;<asp:ImageButton ID="ImgDepartment" runat="server" ImageUrl="~/Images/button/btnAdd.gif"
                        OnClick="ImgDepartment_Click" /></td>
            </tr>
            <tr valign="top">
                <td colspan="3">
                    <asp:GridView ID="GridView1" runat="server" Width="100%" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333"
                    GridLines="None" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing"
                    OnRowUpdating="GridView1_RowUpdating" DataKeyNames="DEPARTMENTID" OnRowCreated="GridView1_RowCreated"
                    OnRowDeleting="GridView1_RowDeleting">
                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <RowStyle BackColor="#EFF3FB" Height="13px" />
                        <EditRowStyle BackColor="#2461BF" />
                        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" Height="13px" />
                        <AlternatingRowStyle BackColor="White" />
                        <Columns>
                            <asp:TemplateField HeaderText="部门名称" >
                                <EditItemTemplate>
                                    <asp:TextBox ID="txbDepartment" MaxLength="20" runat="server" Text='<%# Eval("DEPARTMENTNAME") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblDepartment" runat="server" Text='<%# Eval("DEPARTMENTNAME") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                             <asp:BoundField  HeaderText="权限">
                                            <HeaderStyle Wrap="False"  />
                                            <ItemStyle Wrap="False" Font-Bold="True" Width="60" ForeColor="MidnightBlue"/>
                                        </asp:BoundField>
                            <%--<asp:HyperLinkField DataNavigateUrlFields="DEPARTMENTID" DataNavigateUrlFormatString="DepartmentPower.aspx?id={0}"
                               　 HeaderText="权限" Target="_blank" Text="分配权限" ItemStyle-Width=60>
                                <HeaderStyle Wrap="False" />
                            </asp:HyperLinkField>--%>
                            <asp:CommandField HeaderText="编辑" ShowEditButton="True" ItemStyle-Width=40 />
                            <asp:CommandField HeaderText="删除" ShowDeleteButton="True"  ItemStyle-Width=40/>
                        </Columns>
                    </asp:GridView>
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;

 /// <summary>
 /// Copyright (C) 2004-2008 
 /// 数据访问基础类(基于SQLServer)
 /// 用户可以修改满足自己项目的需要。
 /// </summary>
 public abstract class DbHelperSQL
 {
  //数据库连接字符串(web.config来配置)
  //<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />  
  public static string connectionString = Tools.Decrypt(ConfigurationSettings.AppSettings["ConnectionString"], Tools.myKey);
        public static DataTable dtMemoryData = null;

  public DbHelperSQL()
  {   
  }

  #region 公用方法

        //public static int GetMaxID(string FieldName,string TableName)
        //{
        //    string strsql = "select max(" + FieldName + ")+1 from " + TableName;
        //    object obj = GetSingle(strsql);
        //    if (obj == null)
        //    {
        //        return 1;
        //    }
        //    else
        //    {
        //        return int.Parse(obj.ToString());
        //    }
        //}
       
        /// <summary>
        /// 验证查询条件是否合法
        /// </summary>
        /// <param name="strSql">要验证的字符串</param>
        /// <returns>是否合法</returns>
        public static bool IsSafe(String strSql)
        {
            //if ((Regex.IsMatch(strSql, @"/((\%3D)|(=))[^ ]*((\%27)|(’)|(--)|(\%3B)|(:))/i"))
            //    || (Regex.IsMatch(strSql, @"/w*((\%27)|(’))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix"))
            //    || (Regex.IsMatch(strSql, @"/exec(\s|\+)+(s|x)p\w+/ix"))
            //    || (Regex.IsMatch(strSql, @"/((\%27)|(\'))(union|select|insert|update|delete|drop)/ix")))
            //{
            //    return false;
            //}
           
            return true;
        }

        //
  public static bool Exists(string strSql, params SqlParameter[] cmdParms)
  {
   object obj = GetSingle(strSql, cmdParms);
   int cmdresult;
   if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
   {
    cmdresult = 0;
   }
   else
   {
    cmdresult = int.Parse(obj.ToString());
   }
   if (cmdresult == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
  #endregion

  #region  执行简单SQL语句

  /// <summary>
  /// 执行SQL语句，返回影响的记录数
  /// </summary>
  /// <param name="SQLString">SQL语句</param>
  /// <returns>影响的记录数</returns>
  public static int ExecuteSql(string SQLString)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {    
    using (SqlCommand cmd = new SqlCommand(SQLString,connection))
    {
     try
     {  
      connection.Open();
      int rows=cmd.ExecuteNonQuery();
      return rows;
     }
     catch(System.Data.SqlClient.SqlException E)
     {     
      connection.Close();
      throw new Exception(E.Message);
     }
    }    
   }
  }

        public static DataTable gettable(string sql)
        {

            try
            {
                SqlConnection connection = new SqlConnection(connectionString);

                connection.Open();
                SqlDataAdapter sa = new SqlDataAdapter(sql, connection);

                DataSet ds = new DataSet();
                sa.SelectCommand.CommandTimeout = 600;
                sa.Fill(ds);

                connection.Close();

                return ds.Tables[0];

             

           }
            catch (System.Data.SqlClient.SqlException E)
            {
             
                throw new Exception(E.Message);
            }

          }

        public static void exce(string sql)
        {
          try
            {
                SqlConnection connection = new SqlConnection(connectionString);

                connection.Open();

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = connection;

                cmd.CommandType = CommandType.Text;
              cmd.CommandText =sql;

                cmd.ExecuteNonQuery();
                connection.Close();

            }
            catch (System.Data.SqlClient.SqlException E)
            {

                throw new Exception(E.Message);
            }

 

 

        }
      
  /// <summary>
  /// 执行多条SQL语句，实现数据库事务。
  /// </summary>
  /// <param name="SQLStringList">多条SQL语句</param>  
  public static void ExecuteSqlTran(ArrayList SQLStringList)
  {           
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection=conn;    
    SqlTransaction tx=conn.BeginTransaction();   
    cmd.Transaction=tx;    
    try
    {     
     for(int n=0;n<SQLStringList.Count;n++)
     {
      string strsql=SQLStringList[n].ToString();
      if (strsql.Trim().Length>1)
      {
       cmd.CommandText=strsql;
       cmd.ExecuteNonQuery();
      }
     }          
     tx.Commit();     
    }
    catch(System.Data.SqlClient.SqlException E)
    {  
     tx.Rollback();
     throw new Exception(E.Message);
    }
   }
  }
  /// <summary>
  /// 执行带一个存储过程参数的的SQL语句。
  /// </summary>
  /// <param name="SQLString">SQL语句</param>
  /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
  /// <returns>影响的记录数</returns>
  public static int ExecuteSql(string SQLString,string content)
  {    
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(SQLString,connection);  
    System.Data.SqlClient.SqlParameter  myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
    myParameter.Value = content ;
    cmd.Parameters.Add(myParameter);
    try
    {
     connection.Open();
     int rows=cmd.ExecuteNonQuery();
     return rows;
    }
    catch(System.Data.SqlClient.SqlException E)
    {    
     throw new Exception(E.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    } 
   }
  }  
  /// <summary>
  /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
  /// </summary>
  /// <param name="strSQL">SQL语句</param>
  /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
  /// <returns>影响的记录数</returns>
  public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
  {  
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(strSQL,connection); 
    System.Data.SqlClient.SqlParameter  myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);
    myParameter.Value = fs ;
    cmd.Parameters.Add(myParameter);
    try
    {
     connection.Open();
     int rows=cmd.ExecuteNonQuery();
     return rows;
    }
    catch(System.Data.SqlClient.SqlException E)
    {    
     throw new Exception(E.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }    
   }
  }
  
  /// <summary>
  /// 执行一条计算查询结果语句，返回查询结果（object）。
  /// </summary>
  /// <param name="SQLString">计算查询结果语句</param>
  /// <returns>查询结果（object）</returns>
  public static object GetSingle(string SQLString)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    using(SqlCommand cmd = new SqlCommand(SQLString,connection))
    {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
    }
   }
  }
  /// <summary>
  /// 执行查询语句，返回SqlDataReader
  /// </summary>
  /// <param name="strSQL">查询语句</param>
  /// <returns>SqlDataReader</returns>
  public static SqlDataReader ExecuteReader(string strSQL)
  {
   SqlConnection connection = new SqlConnection(connectionString);   
   SqlCommand cmd = new SqlCommand(strSQL,connection);    
   try
   {
    connection.Open(); 
    SqlDataReader myReader = cmd.ExecuteReader();
    return myReader;
   }
   catch(System.Data.SqlClient.SqlException e)
   {        
    throw new Exception(e.Message);
   }   
   
  }  
  /// <summary>
  /// 执行查询语句，返回DataSet
  /// </summary>
  /// <param name="SQLString">查询语句</param>
  /// <returns>DataSet</returns>
  public static DataSet Query(string SQLString)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    DataSet ds = new DataSet();
    try
    {
     connection.Open();
     SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);    
     command.Fill(ds,"ds");
    }
    catch(System.Data.SqlClient.SqlException ex)
    {    
     throw new Exception(ex.Message);
    }   
    return ds;
   }   
  }

  #endregion

  #region 执行带参数的SQL语句

  /// <summary>
  /// 执行SQL语句，返回影响的记录数
  /// </summary>
  /// <param name="SQLString">SQL语句</param>
  /// <returns>影响的记录数</returns>
  public static int ExecuteSql(string SQLString,params SqlParameter[] cmdParms)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {    
    using (SqlCommand cmd = new SqlCommand())
    {
     try
     {  
      PrepareCommand(cmd, connection, null,SQLString, cmdParms);
      int rows=cmd.ExecuteNonQuery();
      cmd.Parameters.Clear();
      return rows;
     }
     catch(System.Data.SqlClient.SqlException E)
     {    
      throw new Exception(E.Message);
     }
    }    
   }
  }

        //多余的代码
        //public static void ExecuteSqlTran1(string SQLString1, string SQLString2)
        //{
        //    using (SqlConnection connection = new SqlConnection(connectionString))
        //    {
        //        connection.Open();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.Connection = connection;
        //        SqlTransaction tx = connection.BeginTransaction();
        //        cmd.Transaction = tx;
        //        try
        //        {
        //            cmd.CommandText = SQLString1;
        //            cmd.ExecuteNonQuery();
        //            cmd.CommandText = SQLString2;
        //            cmd.ExecuteNonQuery();
        //            tx.Commit();
        //        }
        //        catch (System.Data.SqlClient.SqlException E)
        //        {
        //            tx.Rollback();
        //            throw new Exception(E.Message);
        //        }
        //        finally
        //        {
        //            cmd.Dispose();
        //            connection.Close();
        //        }
        //    }
        //}

  
   
  /// <summary>
  /// 执行多条SQL语句，实现数据库事务。
  /// </summary>
  /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
  public static void ExecuteSqlTran(Hashtable SQLStringList)
  {   
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
    conn.Open();
    using (SqlTransaction trans = conn.BeginTransaction())
    {
     SqlCommand cmd = new SqlCommand();
     try
     {
      //循环
      foreach (DictionaryEntry myDE in SQLStringList)
      { 
       string  cmdText=myDE.Key.ToString();
       SqlParameter[] cmdParms=(SqlParameter[])myDE.Value;
       PrepareCommand(cmd,conn,trans,cmdText, cmdParms);
       int val = cmd.ExecuteNonQuery();
       cmd.Parameters.Clear();
       trans.Commit();
      }     
     }
     catch
     {
      trans.Rollback();
      throw;
     }
    }    
   }
  }
 
    
  /// <summary>
  /// 执行一条计算查询结果语句，返回查询结果（object）。
  /// </summary>
  /// <param name="SQLString">计算查询结果语句</param>
  /// <returns>查询结果（object）</returns>
  public static object GetSingle(string SQLString,params SqlParameter[] cmdParms)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    using (SqlCommand cmd = new SqlCommand())
    {
     try
     {
      PrepareCommand(cmd, connection, null,SQLString, cmdParms);
      object obj = cmd.ExecuteScalar();
      cmd.Parameters.Clear();
      if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
      {     
       return null;
      }
      else
      {
       return obj;
      }    
     }
     catch(System.Data.SqlClient.SqlException e)
     {    
      throw new Exception(e.Message);
     }     
    }
   }
  }
  
  /// <summary>
  /// 执行查询语句，返回SqlDataReader
  /// </summary>
  /// <param name="strSQL">查询语句</param>
  /// <returns>SqlDataReader</returns>
  public static SqlDataReader ExecuteReader(string SQLString,params SqlParameter[] cmdParms)
  {  
   SqlConnection connection = new SqlConnection(connectionString);
   SqlCommand cmd = new SqlCommand();    
   try
   {
    PrepareCommand(cmd, connection, null,SQLString, cmdParms);
    SqlDataReader myReader = cmd.ExecuteReader();
    cmd.Parameters.Clear();
    return myReader;
   }
   catch(System.Data.SqlClient.SqlException e)
   {        
    throw new Exception(e.Message);
   }     
   
  }  
  
  /// <summary>
  /// 执行查询语句，返回DataSet
  /// </summary>
  /// <param name="SQLString">查询语句</param>
  /// <returns>DataSet</returns>
  public static DataSet Query(string SQLString,params SqlParameter[] cmdParms)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand();
    PrepareCommand(cmd, connection, null,SQLString, cmdParms);
    using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
    {
     DataSet ds = new DataSet(); 
     try
     {            
      da.Fill(ds,"ds");
      cmd.Parameters.Clear();
     }
     catch(System.Data.SqlClient.SqlException ex)
     {    
      throw new Exception(ex.Message);
     }
               
     return ds;
    }    
   }   
  }

  private static void PrepareCommand(SqlCommand cmd,SqlConnection conn,SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
  {
   if (conn.State != ConnectionState.Open)
    conn.Open();
   cmd.Connection = conn;
   cmd.CommandText = cmdText;
   if (trans != null)
    cmd.Transaction = trans;
   cmd.CommandType = CommandType.Text;//cmdType;
   if (cmdParms != null)
   {
    foreach (SqlParameter parm in cmdParms)
     cmd.Parameters.Add(parm);
   }
  }

  #endregion

  #region 存储过程操作

  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <returns>SqlDataReader</returns>
  public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
  {
   SqlConnection connection = new SqlConnection(connectionString);
   SqlDataReader returnReader;
   connection.Open();
   SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );
   command.CommandType = CommandType.StoredProcedure;
   returnReader = command.ExecuteReader();    
   return returnReader;   
  }
  
  
  /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <param name="tableName">DataSet结果中的表名</param>
  /// <returns>DataSet</returns>
  public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
  {
            string ds_tableName = "";
            if (tableName == string.Empty) ds_tableName = "tableName";
            else ds_tableName = tableName;

   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    DataSet dataSet = new DataSet();
    connection.Open();
    SqlDataAdapter sqlDA = new SqlDataAdapter();
    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
                sqlDA.Fill(dataSet, ds_tableName);
 
    connection.Close();
    return dataSet;
   }
  }

  
  /// <summary>
  /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
  /// </summary>
  /// <param name="connection">数据库连接</param>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <returns>SqlCommand</returns>
  private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
  {   
   SqlCommand command = new SqlCommand( storedProcName, connection );
   command.CommandType = CommandType.StoredProcedure;

            SqlParameter[] clonedParameters = new SqlParameter[parameters.Length];

            for (int i = 0 ; i < clonedParameters.Length; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)parameters[i]).Clone();
            }

            foreach (SqlParameter parameter in clonedParameters)
   {
    command.Parameters.Add( parameter );
 
                if (parameter != null)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input  ) && parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;

                    }
                }
             
   }

   return command;   
  }
  
  /// <summary>
  /// 执行存储过程，返回影响的行数  
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <param name="rowsAffected">影响的行数</param>
  /// <returns></returns>
  public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    int result;
    connection.Open();
    SqlCommand command = BuildIntCommand(connection,storedProcName, parameters );
    rowsAffected = command.ExecuteNonQuery();
    result = (int)command.Parameters["ReturnValue"].Value;
    //Connection.Close();
    return result;
   }
  }
  
  /// <summary>
  /// 创建 SqlCommand 对象实例(用来返回一个整数值) 
  /// </summary>
  /// <param name="storedProcName">存储过程名</param>
  /// <param name="parameters">存储过程参数</param>
  /// <returns>SqlCommand 对象实例</returns>
  private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
  {
   SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters );
   command.Parameters.Add( new SqlParameter ( "ReturnValue",
    SqlDbType.Int,4,ParameterDirection.ReturnValue,
    false,0,0,string.Empty,DataRowVersion.Default,null ));
   return command;
  }
  #endregion 

 }

public-----成员可以有任何代码访问
private--- 成员只能有可类中的代码访问
internal---成员只能有定义他的工程内部代码访问
protected --成员只能有类或派生类的代码访问
virtual---方法可以重写
abstract--- 方法必须重写（只用于一抽象类）
override---方法重写一个基类方法（如果方法被重写，就必须使用该关键字）
extern---方法定义放在其他地方
none or internal--类只能在当前工程中访问
abstract or internal anstract--类只能在当前工程中访问，不能实例化，只能继承
public abstact---类可以在任何地方访问，不能实例化 ，只能继承
sealed or internal sealed--类只能在当前工程中访问 不能派生 只能实例化
public sealed----类可以在任何地方访问 不能拍派生 只能实例化

海量数据库的查询优化及分页算法方案
(转自 http://www.pconline.com.cn)

--------------------------------------------------------------------------------
实现小数据量和海量数据的通用分页显示存储过程

建立一个web 应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法，也就是利用ADO自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据量的情形，因为游标本身有缺点：游标是存放在内存中，很费内存。游标一建立，就将相关的记录锁住，直到取消游标。游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等待甚至死机。

更重要的是，对于非常大的数据模型而言，分页检索时，如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据，而非检索所有的数据，然后单步执行当前行。

最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标，由于游标的局限性，所以这个方法并没有得到大家的普遍认可。

后来，网上有人改造了此存储过程，下面的存储过程就是结合我们的办公自动化实例写的分页存储过程：

CREATE procedure pagination1

(@pagesize int, --页面大小，如每页存储20条记录

@pageindex int --当前页码

)

as

set nocount on

begin

declare @indextable table(id int identity(1,1),nid int) --定义表变量

declare @PageLowerBound int --定义此页的底码

declare @PageUpperBound int --定义此页的顶码

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set rowcount @PageUpperBound

insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc

select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

end

set nocount off

以上存储过程运用了SQL SERVER的最新技术――表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然，在这个过程中，您也可以把其中的表变量写成临时表：CREATE TABLE #Temp。但很明显，在SQL SERVER中，用临时表是没有用表变量快的。所以笔者刚开始使用这个存储过程时，感觉非常的不错，速度也比原来的ADO的好。但后来，我又发现了比此方法更好的方法。

笔者曾在网上看到了一篇小短文《从数据表中取出第n条到第m条的记录的方法》，全文如下：

从publish 表中取出第 n 条到第 m 条的记录：
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
(SELECT TOP n-1 id
FROM publish))

id 为publish 表的关键字

我当时看到这篇文章的时候，真的是精神为之一振，觉得思路非常得好。等到后来，我在作办公自动化系统（ASP.NET+ C#＋SQL SERVER）的时候，忽然想起了这篇文章，我想如果把这个语句改造一下，这就可能是一个非常好的分页存储过程。于是我就满网上找这篇文章，没想到，文章还没找到，却找到了一篇根据此语句写的一个分页存储过程，这个存储过程也是目前较为流行的一种分页存储过程，我很后悔没有争先把这段文字改造成存储过程：

CREATE PROCEDURE pagination2
(
@SQL nVARCHAR(4000), --不带排序语句的SQL语句
@Page int, --页码
@RecsPerPage int, --每页容纳的记录数
@ID VARCHAR(255), --需要排序的不重复的ID号
@Sort VARCHAR(255) --排序字段及规则
)
AS

DECLARE @Str nVARCHAR(4000)

SET @Str=’SELECT TOP ’+CAST(@RecsPerPage AS VARCHAR(20))+’ * FROM (’+@SQL+’) T WHERE T.’+@ID+’NOT IN
(SELECT TOP ’+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+’ ’+@ID+’ FROM (’+@SQL+’) T9 ORDER BY ’+@Sort+’) ORDER BY ’+@Sort

PRINT @Str

EXEC sp_ExecuteSql @Str
GO

其实，以上语句可以简化为：

SELECT TOP 页大小 *

FROM Table1

WHERE (ID NOT IN

(SELECT TOP 页大小*页数 id

FROM 表

ORDER BY id))

ORDER BY ID

但这个存储过程有一个致命的缺点，就是它含有NOT IN字样。虽然我可以把它改造为：

SELECT TOP 页大小 *

FROM Table1

WHERE not exists

(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id )

order by id

即，用not exists来代替not in，但我们前面已经谈过了，二者的执行效率实际上是没有区别的。

既便如此，用TOP 结合NOT IN的这个方法还是比用游标要来得快一些。

虽然用not exists并不能挽救上个存储过程的效率，但使用SQL SERVER中的TOP关键字却是一个非常明智的选择。因为分页优化的最终目的就是避免产生过大的记录集，而我们在前面也已经提到了TOP的优势，通过TOP 即可实现对数据量的控制。

在分页算法中，影响我们查询速度的关键因素有两点：TOP和NOT IN。TOP可以提高我们的查询速度，而NOT IN会减慢我们的查询速度，所以要提高我们整个分页算法的速度，就要彻底改造NOT IN，同其他方法来替代它。

我们知道，几乎任何字段，我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值，所以如果这个字段不重复，那么就可以利用这些不重复的字段的max或min作为分水岭，使其成为分页算法中分开每页的参照物。在这里，我们可以用操作符“>”或“<”号来完成这个使命，使查询语句符合SARG形式。如：

Select top 10 * from table1 where id>200

于是就有了如下分页方案：

select top 页大小 *

from table1

where id>

(select max (id) from

(select top ((页码-1)*页大小) id from table1 order by id) as T

)

order by id

在选择即不重复值，又容易分辨大小的列时，我们通常会选择主键。下表列出了笔者用有着1000万数据的办公自动化系统中的表，在以GID（GID是主键，但并不是聚集索引。）为排序列、提取gid,fariqi,title字段，分别以第1、10、100、500、1000、1万、10万、25万、50万页为例，测试以上三种分页方案的执行速度：（单位：毫秒）

页 码
方案1
方案2
方案3

1
60
30
76

10
46
16
63

100
1076
720
130

500
540
12943
83

1000
17110
470
250

1万
24796
4500
140

10万
38326
42283
1553

25万
28140
128720
2330

50万
121686
127846
7168

从上表中，我们可以看出，三种存储过程在执行100页以下的分页命令时，都是可以信任的，速度都很好。但第一种方案在执行分页1000页以上后，速度就降了下来。第二种方案大约是在执行分页1万页以上后速度开始降了下来。而第三种方案却始终没有大的降势，后劲仍然很足。

在确定了第三种分页方案后，我们可以据此写一个存储过程。大家知道SQL SERVER的存储过程是事先编译好的SQL语句，它的执行效率要比通过WEB页面传来的SQL语句的执行效率要高。下面的存储过程不仅含有分页方案，还会根据页面传来的参数来确定是否进行数据总数统计。

-- 获取指定页的数据

CREATE PROCEDURE pagination3

@tblName varchar(255), -- 表名

@strGetFields varchar(1000) = ’*’, -- 需要返回的列

@fldName varchar(255)=’’, -- 排序的字段名

@PageSize int = 10, -- 页尺寸

@PageIndex int = 1, -- 页码

@doCount bit = 0, -- 返回记录总数, 非 0 值则返回

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序

@strWhere varchar(1500) = ’’ -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL varchar(5000) -- 主语句

declare @strTmp varchar(110) -- 临时变量

declare @strOrder varchar(400) -- 排序类型

 

if @doCount != 0

begin

if @strWhere !=’’

set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

else

set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

 

if @OrderType != 0

begin

set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

--如果@OrderType不是0，就执行降序，这句很重要！

end

else

begin

set @strTmp = ">(select max"

set @strOrder = " order by [" + @fldName +"] asc"

end

 

if @PageIndex = 1

begin

if @strWhere != ’’

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder

else

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

 

if @strWhere != ’’

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["

+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

end

end

exec (@strSQL)

GO

上面的这个存储过程是一个通用的存储过程，其注释已写在其中了。

在大数据量的情况下，特别是在查询最后几页的时候，查询时间一般不会超过9秒；而用其他存储过程，在实践中就会导致超时，所以这个存储过程非常适用于大容量数据库的查询。

笔者希望能够通过对以上存储过程的解析，能给大家带来一定的启示，并给工作带来一定的效率提升，同时希望同行提出更优秀的实时数据分页算法。