GridView 实现自定义分页、排序、查询、添加、编辑、多选删除、导出、突出显示等常见应用实现

本人初学，其中参考了无锋不起浪之http://www.cnblogs.com/wf225/archive/2007/08/10/850218.html

不妥之处还望各位指正，谢谢！！

前台代码

BookInfo.aspx

 

<% ... @ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="BookInfo.aspx.cs" Inherits="ZDWH_BookInfo"  %>

<! 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 >
< script  language ="javascript"  type ="text/javascript" > ...
    
    function $(s)...{return document.getElementById(s);}
    
    function isNull(_sVal)...{return (_sVal == "" || _sVal == null || _sVal == "undefined");}
    
    //获取地址栏参数
    function GetURL(name)
    ...{
        var URLParams = new Array();
        var aParams = document.location.search.substr(1).split('&');
        for (i=0; i < aParams.length; i++)
        ...{
            var aParam = aParams.split( '=');
            URLParams[aParam[0]] = aParam[1];
        }
        //取得传过来的name参数
        return URLParams[name];
    }
    
    var tgs;
    var tmp_background_val;
    function tog(n,flags)
    ...{
        if (tgs)
        ...{tgs.style.background= tmp_background_val ;}
        n.style.background= '#99ccff' ;
        tmp_background_val=flags;
        tgs=n;
    }
    
    function CheckAll(spanChk)//全选
    ...{
        var oItem = spanChk.children;
        var theBox=(spanChk.type=="checkbox")?spanChk:spanChk.children.item[0];
        xState=theBox.checked;
        elm=theBox.form.elements;
        for(i=0;i<elm.length;i++)
        if(elm.type=="checkbox" && elm.id!=theBox.id)
        ...{
            if(elm.checked!=xState)
            elm.click();
        }
    }
    
    function GetRowIndex(obj)
    ...{
        $('<%=HiddenID.ClientID %>').value = obj;
    }
    
    var HiddenID;
    function checkSelect()
    ...{
        HiddenID=$('<%=HiddenID.ClientID %>').value;
        if(isNull(HiddenID))
        ...{
            alert("请选择一条记录!");
        }
        return !isNull(HiddenID);
    }
</ script >
     < title > 图书信息 </ title >
     < link  href ="../Main.css"  type ="text/css"  rel ="stylesheet"   />
</ head >
< body >
     < form  id ="Form1"  runat ="server" >
         < b > 图书信息增加 </ b > &nbsp;&nbsp;&nbsp;&nbsp;  
         < asp:Button  class ="input_button"  id ="BtnClear"  Text ="清  空"  runat ="server"  CausesValidation ="False"  OnClick ="BtnClear_Click" ></ asp:Button >
         &nbsp;&nbsp;&nbsp;&nbsp;  
         < asp:button  class ="input_button"  id ="BtnSave"   runat ="server"  text ="保  存"  OnClick ="BtnSave_Click" ></ asp:button >
         &nbsp;&nbsp;&nbsp;&nbsp;
         < asp:Label  ID ="LblCode1"  Text ="图书编号"  ForeColor ="Red"  runat ="server"  Visible ="false" ></ asp:Label >
         < asp:Label  ID ="LblCode"  Text =""  ForeColor ="Red"  runat ="server"  Visible ="false" ></ asp:Label >
         < hr  size ="1"   />
         < table  class ="table_1"  id ="search"  cellspacing ="1"  cellpadding ="0"  width ="95%"  align ="center" >
             < tbody >
                 < tr  class ="tr2" >
                     < td  style ="PADDING-LEFT: 5px"  height ="25" >
                        图书ISBN： 
                         < asp:TextBox  class ="input_text"  id ="TxtISBN"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                     < td  style ="PADDING-LEFT: 5px"  width ="47%"  height ="25" >
                        出 版 社： 
                         < asp:DropDownList  class ="input_text"  id ="DdlSelPress"  runat ="server"  width ="200" ></ asp:DropDownList >
                     </ td >
                 </ tr >
                 < tr  class ="tr1" >
                     < td  style ="PADDING-LEFT: 5px"  colspan ="2"  height ="25" >
                        图书名称： 
                         < asp:TextBox  class ="input_text"  id ="TxtName"  runat ="server"  width ="600" ></ asp:TextBox >
                         < asp:RequiredFieldValidator  id ="Requiredfieldvalidator1"  runat ="server"  ErrorMessage ="*"  ControlToValidate ="TxtName" > 此项必填 </ asp:RequiredFieldValidator >
                     </ td >
                 </ tr >
                 < tr  class ="tr2" >
                     < td  style ="PADDING-LEFT: 5px"  height ="25" >
                        定 &nbsp;&nbsp;&nbsp;&nbsp; 价： 
                         < asp:TextBox  class ="input_text"  id ="TxtBookPrice"  runat ="server"  width ="200" ></ asp:TextBox >
                         < asp:RequiredFieldValidator  id ="Requiredfieldvalidator2"  runat ="server"  ErrorMessage ="*"  ControlToValidate ="TxtBookPrice" > 此项必填 </ asp:RequiredFieldValidator >
                     </ td >
                     < td  style ="PADDING-LEFT: 5px"  width ="47%"  height ="25" >
                        图书源码： 
                         < asp:TextBox  class ="input_text"  id ="TxtSourceCode"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                 </ tr >
                 < tr  class ="tr1" >
                     < td  style ="PADDING-LEFT: 5px"  height ="25" >
                        自编代码： 
                         < asp:TextBox  class ="input_text"  id ="TxtBookSelfCode"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                     < td  style ="PADDING-LEFT: 5px"  width ="47%"  height ="25" >
                        出版年度： 
                         < asp:TextBox  class ="input_text"  id ="TxtCbyYear"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                 </ tr >
                 < tr  class ="tr2" >
                     < td  style ="PADDING-LEFT: 5px"  height ="25" >
                        版 &nbsp;&nbsp;&nbsp;&nbsp; 次： 
                         < asp:TextBox  class ="input_text"  id ="TxtBc"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                     < td  style ="PADDING-LEFT: 5px"  width ="47%"  height ="25" >
                        作 &nbsp;&nbsp;&nbsp;&nbsp; 者： 
                         < asp:TextBox  class ="input_text"  id ="TxtBookAuthor"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                 </ tr >
                 < tr  class ="tr1" >
                     < td  style ="PADDING-LEFT: 5px"  height ="25" >
                        架 位 号： 
                         < asp:TextBox  class ="input_text"  id ="TxtLocateCode"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                     < td  style ="PADDING-LEFT: 5px"  width ="47%"  height ="25" >
                        备 &nbsp;&nbsp;&nbsp;&nbsp; 注： 
                         < asp:TextBox  class ="input_text"  id ="TxtMemo"  runat ="server"  width ="200" ></ asp:TextBox >
                     </ td >
                 </ tr >
             </ tbody >
         </ table >
         < br  />
         < b > 图书信息查询 </ b >  
         < hr  size ="1"   />
         < table  class =""  id ="Table1"  cellspacing ="1"  cellpadding ="0"  width ="95%"  align ="center"  border ="0" >
             < tbody >
                 < tr  class ="tr2" >
                     < td  style ="PADDING-LEFT: 5px; height: 25px;" >
                        检索条件： 
                         < asp:DropDownList  class ="input_text"  id ="DdlQuery_tj"  runat ="server"  width ="97" >
                             < asp:ListItem  Value ="name"  Selected ="True" > 图书名称 </ asp:ListItem >
                             < asp:ListItem  Value ="ISBN" > 图书ISBN </ asp:ListItem >
                             < asp:ListItem  Value ="author" > 作者 </ asp:ListItem >
                             < asp:ListItem  Value ="abbrname" > 出版社名称 </ asp:ListItem >
                             < asp:ListItem  Value ="memo" > 备注 </ asp:ListItem >
                         </ asp:DropDownList >
                         &nbsp;&nbsp; 检索内容： 
                         < asp:TextBox  class ="input_text"  id ="TxtQuery_content"  runat ="server"  width ="120" ></ asp:TextBox >
                         &nbsp;&nbsp;  
                         < asp:button  class ="input_button"  id ="BtnQuery"  runat ="server"  text ="查  询"  CausesValidation ="False"  OnClick ="BtnQuery_Click" ></ asp:button >
                         < br  />
                         < b >< asp:Label  id ="LblMsg"  runat ="server"  forecolor ="Red" ></ asp:Label >< br  />
                             < asp:Button  ID ="BtnDelChecked"  runat ="server"  Height ="25px"  OnClick ="BtnDelChecked_Click"
                                Text ="删除选中"  Width ="100px"  CausesValidation ="false"  Visible ="false"   OnClientClick ="javascript:return ( checkSelect() && confirm('警告：删除将无法恢复！确认删除吗？') );"    />
                             &nbsp;
                             < asp:Button  ID ="BtnToExcel"  runat ="server"  CausesValidation ="False"  Height ="25px"
                                OnClick ="BtnToExcel_Click"  Text ="导出到Excel"  Width ="100px"   />
                             &nbsp;
                             < asp:Button  ID ="BtnToWord"  runat ="server"  CausesValidation ="False"  Height ="25px"
                                OnClick ="BtnToWord_Click"  Text ="导出到Word"  Width ="100px"   /></ b >
                             < input  type ="hidden"  id ="HiddenID"  name ="HiddenID"  runat ="server" />
                     </ td >
                 </ tr >
                 < tr >
                     < td  style ="PADDING-LEFT: 5px"  height ="25" >
                          < asp:GridView  ID ="gridViewPublishers"  runat ="server"  CellPadding ="4"
                            ForeColor ="#333333"  GridLines ="None"  AllowPaging ="True"  AllowSorting ="True"   AutoGenerateColumns ="False"  OnPageIndexChanging ="GridViewPublishersPageChanged"  OnSorting ="GridViewPublishersSorting"  OnRowDeleting ="GridViewRowDelete"  DataKeyNames ="code"  OnRowEditing ="GridViewRowEdit"  OnRowDataBound ="gridViewPublishers_RowDataBound" >
                             < FooterStyle  BackColor ="#1C5E55"  Font-Bold ="True"  ForeColor ="White"   />
                             < RowStyle  BackColor ="#E3EAEB"   />
                             < EditRowStyle  BackColor ="#7C6F57"   />
                             < SelectedRowStyle  BackColor ="#C5BBAF"  Font-Bold ="True"  ForeColor ="#333333"   />
                             < PagerStyle  BackColor ="#666666"  ForeColor ="White"  HorizontalAlign ="Right"   />
                             < HeaderStyle  BackColor ="#1C5E55"  Font-Bold ="True"  ForeColor ="White"   />
                             < AlternatingRowStyle  BackColor ="White"   />
                              < Columns >
                                  < asp:TemplateField >
                                     < ItemTemplate >
                                         < input  id ="check"  runat ="server"  type ="checkbox"  value ='<%#  Eval("Code") % > ' />
                                     </ ItemTemplate >
                                     < HeaderTemplate >
                                         < input  id ="checkAll"  runat ="server"  type ="checkbox"  onclick ="javascript:CheckAll(this);" />
                                     </ HeaderTemplate >
                                  </ asp:TemplateField >
                                  < asp:BoundField  DataField ="ID"  DataFormatString ="{0:d}"  HeaderText ="序號"   />
                                  < asp:BoundField  DataField ="code"  HeaderText ="圖書編號"  SortExpression ="code"   />
                                  < asp:BoundField  DataField ="name"  HeaderText ="圖書名稱 "  SortExpression ="name"   />
                                  < asp:BoundField  DataField ="abbrname"  HeaderText ="版別"  SortExpression ="abbrname"   />
                                  < asp:BoundField  DataField ="price"  DataFormatString ="{0:C}"  HtmlEncode ="false"  HeaderText ="定價"  SortExpression ="price"   />
                                  < asp:BoundField  DataField ="Author"  HeaderText ="作者"  SortExpression ="author"   />
                                  < asp:BoundField  DataField ="version"  HeaderText ="版次"  SortExpression ="version"   />
                                  < asp:BoundField  DataField ="storeamountz"  HeaderText ="總店庫存"  SortExpression ="storeamountz"   />
                                  < asp:BoundField  DataField ="storeamounta"  HeaderText ="門市庫存"  SortExpression ="storeamounta"   />
                                  < asp:TemplateField >
                                     < ItemTemplate >
                                         < asp:Button  ID ="del"  runat ="server"  CausesValidation ="false"  Text ="删除"  CommandName ="Delete"  OnClientClick ="return confirm('您确认删除该记录吗?');"  CommandArgument ='<%#  Eval("Code") % > '> </ asp:Button >
                                     </ ItemTemplate >
                                     < HeaderTemplate >
                                        删除
                                     </ HeaderTemplate >
                                  </ asp:TemplateField >
                                  < asp:CommandField  ButtonType ="Button"  CausesValidation ="False"  HeaderText ="編輯"  ShowEditButton ="True"   />
                              </ Columns >
                         </ asp:GridView >
                     </ td >
                 </ tr >
             </ tbody >
         </ table >
     </ form >
</ body >
</ html >

后台代码

BookInfo.aspx.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.Data.SqlClient;

public   partial   class  ZDWH_BookInfo : System.Web.UI.Page
... {

    Page_Load()#region Page_Load()
    /**//// <summary>
    /// Page_Load事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Page_Load(object sender, EventArgs e)
    ...{
        
        if (!IsPostBack)
        ...{
            //判断用户登陆状态
            //if (Session.Count = 0)
            //{
            //    Response.Redirect("../index.aspx");
            //}
            //else
            //{
            //    if (Session["CurrUser"].ToString == "")
            //        Response.Redirect("../index.aspx");
            //}
            DdlSelPressDataBind();
            GridViewPublishersDataBind();
            
        }
        //i = 1;
        //GridViewPublishersDataBind();
    }
    #endregion

    GetAlertScript(string str)#region GetAlertScript(string str)
    /**//// <summary>
    /// 返回javascript字符串,str：彈出對話框所呈現的文字
    /// </summary>
    /// <param name="str"></param>
    /// <returns></returns>
    protected string GetAlertScript(string str)
    ...{
        string theScript;
        theScript = "<script language='javascript'>alert('" + str + "')</script>";
        return theScript;
    }
    #endregion

    綁定出版社#region 綁定出版社
    /**//// <summary>
    /// 綁定出版社Ddl
    /// </summary>
    protected void DdlSelPressDataBind()
    ...{
        SqlConnection cnn;
        SqlDataAdapter dataA;
        string sql;
        DataSet ds;
        using (cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["BookStoreConnectionString"].ToString()))
        ...{
            sql = "SELECT Code,FullName FROM PressInfoSheet ORDER BY FullName";
            dataA = new SqlDataAdapter(sql, cnn);
            ds = new DataSet();
            try
            ...{
                dataA.Fill(ds, "PressInfoSheet");
                DdlSelPress.DataSource = ds.Tables["PressInfoSheet"].DefaultView;
                DdlSelPress.DataTextField = "FullName";
                DdlSelPress.DataValueField = "Code";
                DdlSelPress.DataBind();
            }
            catch(SqlException ex)
            ...{
                Response.Write("數據讀取錯:" + ex.Message);
            }
        }
    }
    #endregion

    属性#region 属性
    /**////
    private string GridViewSortDirection
    ...{
        get ...{ return ViewState["SortDirection"] as string ?? "ASC"; }
        set ...{ ViewState["SortDirection"] = value; }
    }

    private string GridViewSortExpression
    ...{
        get ...{ return ViewState["SortExpression"] as string ?? string.Empty; }
        set ...{ ViewState["SortExpression"] = value; }
    }
    #endregion

    方法#region 方法
    /**////
    ///
    private方法#region private方法
    /**//// <summary>
    /// GetDirection(),GetDataTable(SqlConnection cnn, string sql),ClearTextBox()
    /// SetBackColor(GridView gridView,DataTable dt,string colName,int col,System.Drawing.Color color)
    /// </summary>
    /// <returns></returns>
    private string GetDirection()
    ...{
        switch(GridViewSortDirection)
        ...{
            case"ASC":
                GridViewSortDirection = "DESC";
                break;
            case"DESC":
                GridViewSortDirection = "ASC";
                break;
        }

        return GridViewSortDirection;
    }

    获取DataTable#region 获取DataTable
    /**//// <summary>
    /// cnn,连接对象；sql,连接字符串;
    /// </summary>
    /// <param name="cnn"></param>
    /// <param name="sql"></param>
    /// <returns></returns>
    private DataTable GetDataTable(SqlConnection cnn, string sql)
    ...{
        SqlDataAdapter dataA;
        DataTable dt;
        dataA = new SqlDataAdapter(sql, cnn);
        dt = new DataTable("gridViewPublishers");
        dt.Columns.Add("ID");
        //dt.Columns["ID"].AutoIncrement = true;
        //dt.Columns["ID"].AutoIncrementSeed = 1;
        //dt.Columns["ID"].AutoIncrementStep = 1;
        ViewState["dataSource"] = dt;
        dataA.Fill(dt);
        return dt;
    }
    #endregion
    //
    设置突出显示#region 设置突出显示
    /**//// <summary>
    ///突出显示：gridView 要设置的GridView,dt 源DataTable,colName 列名,col 列序号 color 颜色
    /// </summary>
    //
    private void SetBackColor(GridView gridView,DataTable dt,string colName,int col,System.Drawing.Color color)
    ...{
        for (int i = 0; i <= gridView.Rows.Count - 1; i++)//设置突出显示单元格
        ...{
            //DataRowView mydrv = dt.DefaultView;
            if (Convert.ToInt32(gridView.Rows.Cells[col].Text) < 10)//当此列小于10时改变背景色
            ...{
                gridView.Rows.Cells[col].ForeColor = color;
                gridView.Rows.Cells[col].Font.Bold = true;
                gridView.Rows.Cells[col].Font.Size = 14;
                //gridView.Rows.Cells[col].BorderColor = System.Drawing.Color.White;
                //gridView.Rows.Cells[col].BorderWidth = 2;
            }
            //LblMsg.Text = mydrv[colName].ToString();
        }
    }
    #endregion

    清空输入区域#region 清空输入区域
    /**//// <summary>
    /// 
    /// </summary>
    private void ClearTextBox()
    ...{
        TxtISBN.Text = "";
        TxtName.Text = "";
        TxtBookPrice.Text = "";
        TxtSourceCode.Text = "";
        TxtBookSelfCode.Text = "";
        TxtCbyYear.Text = "";
        TxtBc.Text = "";
        TxtBookAuthor.Text = "";
        TxtLocateCode.Text = "";
        TxtMemo.Text = "";
        DdlSelPress.SelectedIndex = -1;
        BtnClear.Text = "清  空";
        BtnSave.Text = "保  存";
        LblCode.Text = "";
        LblCode.Visible = false;
        LblCode1.Visible = false;
    }

    #endregion

    批量删除#region 批量删除
    /**////删除tableName表中索引为sID的行
    ///
    private void DeleteRows(string tableName, string sID)
    ...{

        sID = sID.Trim();
        sID=sID.Substring(0,sID.Length-1);//去掉末尾","
        System.Text.StringBuilder code = new System.Text.StringBuilder(sID);
        code=code.Replace(",", " or code=");
        using (SqlConnection cnn = GetSqlConnection())
        ...{
            string sql = "delete from "+tableName+" where code="+code.ToString();
            
            using (SqlCommand cmd = new SqlCommand(sql, cnn))
            ...{
                try
                ...{
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                ...{
                    Response.Write("删除失败：" + ex.Message);
                }
            }
            CloseSqlConnection(cnn);
        }
    }
    #endregion

    #endregion

    
    protected方法#region protected方法
    /**//// <summary>
    /// 为转 excel 出问题而加上的过程(一定加！！）
    /// </summary>
    /// <param name="control"></param>
    public override void VerifyRenderingInServerForm(Control control)
    ...{
        // 为转 excel 出问题而加上的过程(一定加！！）// Confirms that an HtmlForm control is rendered for
    }

    /**//// <summary>
    /// fileName文件名称；fileType文件类型，只能为word/excel任一个
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="fileType"></param>
    protected void GridViewExport(string fileName, string fileType)
    ...{
        Response.ClearContent();
        Response.Buffer = true;

        if (fileType.ToLower()== "excel")
        ...{
            Response.AddHeader("content-disposition", "attachment; filename="" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls"");
            Response.ContentType = "application/vnd.ms-excel";
        }
        else if (fileType.ToLower() == "word")
        ...{
            Response.AddHeader("content-disposition", "attachment; filename="" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".doc"");
            Response.ContentType = "application/vnd.ms-word";
        }
        else
        ...{
            return ;
        }
        Response.Charset = "GB2312";
        //GetEncoding("GB2312")容易引起乱码，所以建议实用UTF7/UTF8格式
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentEncoding = System.Text.Encoding.UTF7;

        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

        gridViewPublishers.AllowPaging = false;//取消分页
        gridViewPublishers.AllowSorting = false;//取消排序
        GridViewPublishersDataBind();

        //隐藏不要的列
        gridViewPublishers.Columns[0].Visible = false;
        gridViewPublishers.Columns[10].Visible = false;
        gridViewPublishers.Columns[11].Visible = false;

        gridViewPublishers.RenderControl(htmlWrite);
        
        Response.Write(stringWrite.ToString());
        Response.Flush();
        Response.End();


        gridViewPublishers.AllowPaging = true;//恢复分页
        gridViewPublishers.AllowSorting = true;//恢复排序
        GridViewPublishersDataBind();

        //显示隐藏列
        gridViewPublishers.Columns[0].Visible = true;
        gridViewPublishers.Columns[10].Visible = true;
        gridViewPublishers.Columns[11].Visible = true;
    }
    #endregion

    #endregion

    数据库联接操作#region 数据库联接操作
    /**////
    //获取数据库联接对象
    private SqlConnection GetSqlConnection()
    ...{
        try
        ...{
            SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["BookStoreConnectionString"].ToString());
            cnn.Open();
            return cnn;
        }
        catch (SqlException e)
        ...{
            Response.Write("数据库连接出错：" + e.Message);
            return null;
        }
    }
    //关闭数据库联接对象
    private void CloseSqlConnection(SqlConnection cnn)
    ...{
        try
        ...{
            cnn.Close();
        }
        catch (SqlException e)
        ...{
            Response.Write("数据库连接不存在或已经关闭:" + e.Message);
        }
    }
    #endregion

    GridView操作#region GridView操作
    /**////

    綁定GridView#region 綁定GridView
    /**//// <summary>
    /// 綁定GridViewPublishers
    /// </summary>
    protected void GridViewPublishersDataBind()
    ...{
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataReader dataR;
        //SqlDataAdapter dataA;
        DataTable dt;
        //DataSet ds;
        //DataColumn dc;
        string sql;
        int maxPage;//最大頁數
        cnn = GetSqlConnection();

        sql = "SELECT COUNT(*) as nums,SUM(storeamounta) as mskc,SUM(storeamountz) as zdkc,SUM(price*storeamounta) as msmy,SUM(price*storeamountz) as zdmy FROM BookInfoSheet,PressInfoSheet,BookStoreSheetA,BookStoreSheetZ where BookInfoSheet.presscode=PressInfoSheet.code and BookInfoSheet.code=BookStoreSheetA.bookcode and BookInfoSheet.code=BookStoreSheetZ.bookcode ";
        if (TxtQuery_content.Text.Trim() != "")
            sql += " AND " + DdlQuery_tj.SelectedItem.Value + " LIKE '%" + TxtQuery_content.Text.Trim() + "%'";
        cmd = new SqlCommand(sql, cnn);
        try
        ...{
            dataR = cmd.ExecuteReader();
            if (dataR.Read())
            ...{
                if (dataR["nums"].ToString() == "0")
                    LblMsg.Text = "";
                else
                    LblMsg.Text = "合計：記錄數 " + dataR["nums"].ToString() + " 總店庫存 " + dataR["zdkc"].ToString() + " 門市庫存 " + dataR["mskc"].ToString() + " 總店碼洋 " + dataR["zdmy"].ToString() + " 門市碼洋 " + dataR["msmy"].ToString();
            }
            dataR.Close();
        }
        catch (SqlException ex)
        ...{
            Response.Write("數據讀取出錯：" + ex.Message);
        }
        /**////取表头信息结束
        ///綁定gridViewPublishers
        sql = "SELECT BookInfoSheet.*,abbrname,storeamounta,storeamountz FROM BookInfoSheet,PressInfoSheet,BookStoreSheetA,BookStoreSheetZ where BookInfoSheet.presscode=PressInfoSheet.code and BookInfoSheet.code=BookStoreSheetA.bookcode and BookInfoSheet.code=BookStoreSheetZ.bookcode ";
        if (TxtQuery_content.Text.Trim() != "")
            sql += " AND " + DdlQuery_tj.SelectedItem.Value + " LIKE '%" + TxtQuery_content.Text.Trim() + "%'";

        sql += " ORDER BY BookInfoSheet.name DESC";
        //dataA = new SqlDataAdapter(sql, cnn);
        //dt = new DataTable("gridViewPublishers");
        //dt.Columns.Add("ID");
        //dt.Columns["ID"].AutoIncrement = true;
        dt = GetDataTable(cnn, sql);
        try
        ...{
            //dataA.Fill(dt);

            if (dt.DefaultView.Count % gridViewPublishers.PageSize > 0)
            ...{
                maxPage = dt.DefaultView.Count / gridViewPublishers.PageSize + 1;
            }
            else
            ...{
                maxPage = dt.DefaultView.Count / gridViewPublishers.PageSize;
            }
            while (gridViewPublishers.PageIndex > 0)
            ...{
                if (maxPage < gridViewPublishers.PageIndex + 1)
                ...{
                    gridViewPublishers.PageIndex = gridViewPublishers.PageIndex - 1;
                }
                else
                ...{
                    break;
                }
            }
            gridViewPublishers.DataSource = dt;
            gridViewPublishers.DataBind();
            CloseSqlConnection(cnn);
            SetBackColor(gridViewPublishers, dt, "storeamounta", 9, System.Drawing.Color.Orange);
            SetBackColor(gridViewPublishers, dt, "storeamountz", 8, System.Drawing.Color.OrangeRed);
            BtnDelChecked.Visible = true;
        }
        catch (SqlException ex)
        ...{
            Response.Write("數據讀取錯：" + ex.Message);
        }
        CloseSqlConnection(cnn);
    }
    #endregion

    排序#region  排序
    /**////
    ///
    ///
    protected void GridViewPublishersSorting(Object sender, GridViewSortEventArgs e)
    ...{

        GridViewSortExpression = e.SortExpression;
        int pageIndex = gridViewPublishers.PageIndex;
        //if (dt != null)
            //LblMsg.Text = e.SortExpression;
        gridViewPublishers.DataSource = SortDataTable(ViewState["dataSource"] as DataTable, false);
        gridViewPublishers.DataBind();
        SetBackColor(gridViewPublishers, ViewState["dataSource"] as DataTable, "storeamountz",8, System.Drawing.Color.OrangeRed);
        SetBackColor(gridViewPublishers, ViewState["dataSource"] as DataTable, "storeamounta",9,System.Drawing.Color.Orange);
        gridViewPublishers.PageIndex = pageIndex;

    }

    //对DataTable排序操作
    private DataView SortDataTable(DataTable dataTable, bool isPageIndexChanging)
    ...{
        if (dataTable != null)
        ...{
            DataView dataView = new DataView(dataTable);
            if (GridViewSortExpression != string.Empty)
            ...{
                if (isPageIndexChanging)
                ...{
                    dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection);
                }
                else
                ...{
                    dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GetDirection());
                }
            }
            return dataView;
        }
        else
        ...{
            return new DataView();
        }
    }
    #endregion

    删除#region 删除
    /**////
    ///
    ///
    //
    protected void GridViewRowDelete(Object sender, GridViewDeleteEventArgs e)
    ...{
        int strCode = Convert.ToInt32(gridViewPublishers.DataKeys[e.RowIndex].Value);
        //string strCode=delete
        int zdkc = Convert.ToInt32(gridViewPublishers.Rows[e.RowIndex].Cells[8].Text);
        int mskc = Convert.ToInt32(gridViewPublishers.Rows[e.RowIndex].Cells[9].Text);
        if (zdkc != 0 || mskc != 0)
        ...{
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", GetAlertScript("该书存在库存，无法删除！"));
        }
        else
        ...{
            using (SqlConnection cnn = GetSqlConnection())
            ...{
                string sql = "delete from bookinfosheet where code=@code";
                using (SqlCommand cmd = new SqlCommand(sql, cnn))
                ...{
                    SqlParameter ParaCode = new SqlParameter("code", SqlDbType.Int);
                    ParaCode.Value = strCode;
                    cmd.Parameters.Add(ParaCode);
                    //gridViewPublishers.DeleteRow(e.RowIndex);
                    try
                    ...{
                        cmd.ExecuteNonQuery();
                        Page.ClientScript.RegisterStartupScript(this.GetType(),"success",GetAlertScript("删除成功！"));
                    }
                    catch (SqlException ex)
                    ...{
                        Response.Write("删除失败：" + ex.Message);
                    }
                }
                
            }
            GridViewPublishersDataBind();
            ClearTextBox();
        }
    }
    #endregion

    编辑#region 编辑
    /**////
    ///
    protected void GridViewRowEdit(Object sender, GridViewEditEventArgs e)
    ...{
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataReader dataR;
        string sql;
        LblCode.Text = gridViewPublishers.Rows[e.NewEditIndex].Cells[2].Text;//保存Code值
        LblCode.Visible = true;
        LblCode1.Visible = true;
        BtnSave.Text = "保存修改";
        BtnClear.Text = "取消";
        //gridViewPublishers.Rows[e.NewEditIndex].Enabled = false;
        using (cnn = GetSqlConnection())
        ...{
            sql = "SELECT * FROM BookInfoSheet WHERE Code=@Code";
            using (cmd = new SqlCommand(sql, cnn))
            ...{
                SqlParameter ParaCode = new SqlParameter("Code", SqlDbType.Int);
                ParaCode.Value = LblCode.Text;
                cmd.Parameters.Add(ParaCode);
                try
                ...{
                    dataR = cmd.ExecuteReader();
                    while (dataR.Read())
                    ...{
                        TxtISBN.Text = dataR["ISBN"].ToString();
                        TxtName.Text = dataR["Name"].ToString();
                        TxtBookPrice.Text = dataR["Price"].ToString();
                        TxtSourceCode.Text = dataR["SourceCode"].ToString();
                        TxtBookSelfCode.Text = dataR["SelfCode"].ToString();
                        TxtCbyYear.Text = dataR["YYear"].ToString();
                        TxtBc.Text = dataR["Version"].ToString();
                        TxtBookAuthor.Text = dataR["Author"].ToString();
                        TxtLocateCode.Text = dataR["LocCode"].ToString();
                        TxtMemo.Text = dataR["Memo"].ToString();
                        DdlSelPress.SelectedIndex = DdlSelPress.Items.IndexOf(DdlSelPress.Items.FindByValue(dataR["PressCode"].ToString()));
                    }
                }
                catch (SqlException ex)
                ...{
                    Response.Write("数据读取错：" + ex.Message);
                }
            }
        }
        
    }
    #endregion

    翻頁#region 翻頁
    protected void GridViewPublishersPageChanged(Object sender, GridViewPageEventArgs e)
    ...{
        gridViewPublishers.PageIndex = e.NewPageIndex;
        GridViewPublishersDataBind();
    }
    #endregion

    RowDataBound事件#region RowDataBound事件
    /**//// <summary>
    /// 鼠标移动时改变背景样式,和自增列
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gridViewPublishers_RowDataBound(object sender, GridViewRowEventArgs e)
    ...{
        //判断是否数据行
        if (e.Row.RowType == DataControlRowType.DataRow)
        ...{
            //当鼠标停留时更改背景色

            e.Row.Attributes.Add("onmouseover", "c=this.style.backgroundColor;this.style.backgroundColor='#6699cc'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=c");

            //
            System.Web.UI.HtmlControls.HtmlInputCheckBox check = (System.Web.UI.HtmlControls.HtmlInputCheckBox)e.Row.FindControl("check");
            string sid = check.Value;
            if (e.Row.RowIndex % 2 != 0)
                e.Row.Attributes.Add("onclick", "GetRowIndex('" + sid + "'),tog(this,'#EFF3FB')");
            else
                e.Row.Attributes.Add("onclick", "GetRowIndex('" + sid + "'),tog(this,'#ffffff')");
        }

        //设置序号列
        if (e.Row.RowIndex != -1)
        ...{
            int id = (e.Row.RowIndex + 1)+gridViewPublishers.PageSize*gridViewPublishers.PageIndex;
            e.Row.Cells[1].Text = id.ToString();
        }
    }
    #endregion

    #endregion

    事件#region 事件

    清空输入#region 清空输入
    /**//// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtnClear_Click(object sender, EventArgs e)
    ...{
        ClearTextBox();
    }

    #endregion

    查詢事件#region 查詢事件
    /**//// <summary>
    /// 查詢事件BtnQuery_Click()
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtnQuery_Click(object sender, EventArgs e)
    ...{
        GridViewPublishersDataBind();
    }
    #endregion

    保存用戶輸入#region 保存用戶輸入
    /**//// <summary>
    /// BtnSave事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtnSave_Click(object sender, EventArgs e)
    ...{

        string bookCode, sql;
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataReader dataR;
        try
        ...{
            cnn = GetSqlConnection();
            if (LblCode.Text == "")
            添加状态#region 添加状态
            ...{
                sql = "SELECT Code FROM BookInfoSheet WHERE PressCode=@PressCode ORDER BY Code DESC";
                //计算新的Code值
                using (cmd = new SqlCommand(sql, cnn))
                ...{
                    SqlParameter ParaPressCode1 = new SqlParameter("PressCode", SqlDbType.Char, 3);
                    ParaPressCode1.Value = DdlSelPress.SelectedValue;
                    cmd.Parameters.Add(ParaPressCode1);


                    dataR = cmd.ExecuteReader();
                    if (dataR.Read())
                        bookCode = DdlSelPress.SelectedValue + String.Format("0000", (Convert.ToInt32(dataR["Code"].ToString().Substring(dataR["Code"].ToString().Length - 4)) + 1));
                    else
                        bookCode = DdlSelPress.SelectedValue + "0001";
                    dataR.Close();
                }
                sql = "INSERT INTO BookInfoSheet(Code,Name,SourceCode,SelfCode,ISBN,YYear,Version,Author,Price,PressCode,LocCode,Memo) VALUES(@Code,@Name,@SourceCode,@SelfCode,@ISBN,@YYear,@Version,@Author,@Price,@PressCode,@LocCode,@Memo)";
                //sql = "UPDATE BookInfoSheet Set Name=@Name,SourceCode=@SourceCode,SelfCode=@SelfCode,ISBN=@ISBN,YYear=@YYear,Version=@Version,Price=@Price,PressCode=@PressCode,LocCode=@LocCode,Memo=@Memo WHERE Code=" + LblCode.Text;
                using (cmd = new SqlCommand(sql, cnn))
                ...{
                    SqlParameter ParaCode, ParaName, ParaSourceCode, ParaSelfCode, ParaISBN, ParaYYear, ParaVersion, ParaAuthor, ParaPrice, ParaPressCode, ParaLocCode, ParaMemo;
                    ParaCode = new SqlParameter("Code", bookCode);
                    ParaName = new SqlParameter("Name", TxtName.Text);
                    ParaSourceCode = new SqlParameter("SourceCode", TxtSourceCode.Text);
                    ParaSelfCode = new SqlParameter("SelfCode", TxtBookSelfCode.Text);
                    ParaISBN = new SqlParameter("ISBN", TxtISBN.Text);
                    ParaYYear = new SqlParameter("YYear", TxtCbyYear.Text);
                    ParaVersion = new SqlParameter("Version", TxtBc.Text);
                    ParaAuthor = new SqlParameter("Author", TxtBookAuthor.Text);
                    ParaPrice = new SqlParameter("Price", TxtBookPrice.Text);
                    ParaPressCode = new SqlParameter("PressCode", DdlSelPress.SelectedValue);
                    ParaLocCode = new SqlParameter("LocCode", TxtLocateCode.Text);
                    ParaMemo = new SqlParameter("Memo", TxtMemo.Text);
                    cmd.Parameters.Add(ParaCode);
                    cmd.Parameters.Add(ParaAuthor);
                    cmd.Parameters.Add(ParaISBN);
                    cmd.Parameters.Add(ParaLocCode);
                    cmd.Parameters.Add(ParaMemo);
                    cmd.Parameters.Add(ParaName);
                    cmd.Parameters.Add(ParaPressCode);
                    cmd.Parameters.Add(ParaPrice);
                    cmd.Parameters.Add(ParaSelfCode);
                    cmd.Parameters.Add(ParaSourceCode);
                    cmd.Parameters.Add(ParaVersion);
                    cmd.Parameters.Add(ParaYYear);
                    try
                    ...{
                        cmd.ExecuteNonQuery();
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", GetAlertScript("保存成功！"));
                        GridViewPublishersDataBind();
                    }
                    catch (Exception ex)
                    ...{
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", GetAlertScript("保存失敗！" + ex.Message));
                        Response.Write("數據插入錯：" + ex.Message);
                    }
                }
            }
            #endregion
            else
            修改状态#region 修改状态
            ...{
                sql = "UPDATE BookInfoSheet Set Name=@Name,SourceCode=@SourceCode,SelfCode=@SelfCode,ISBN=@ISBN,YYear=@YYear,Version=@Version,Price=@Price,PressCode=@PressCode,LocCode=@LocCode,Memo=@Memo WHERE Code=" + LblCode.Text;
                using (cmd = new SqlCommand(sql, cnn))
                ...{
                    SqlParameter ParaName, ParaSourceCode, ParaSelfCode, ParaISBN, ParaYYear, ParaVersion, ParaAuthor, ParaPrice, ParaPressCode, ParaLocCode, ParaMemo;

                    ParaName = new SqlParameter("Name", TxtName.Text);
                    ParaSourceCode = new SqlParameter("SourceCode", TxtSourceCode.Text);
                    ParaSelfCode = new SqlParameter("SelfCode", TxtBookSelfCode.Text);
                    ParaISBN = new SqlParameter("ISBN", TxtISBN.Text);
                    ParaYYear = new SqlParameter("YYear", TxtCbyYear.Text);
                    ParaVersion = new SqlParameter("Version", TxtBc.Text);
                    ParaAuthor = new SqlParameter("Author", TxtBookAuthor.Text);
                    ParaPrice = new SqlParameter("Price", TxtBookPrice.Text);
                    ParaPressCode = new SqlParameter("PressCode", DdlSelPress.SelectedValue);
                    ParaLocCode = new SqlParameter("LocCode", TxtLocateCode.Text);
                    ParaMemo = new SqlParameter("Memo", TxtMemo.Text);

                    cmd.Parameters.Add(ParaAuthor);
                    cmd.Parameters.Add(ParaISBN);
                    cmd.Parameters.Add(ParaLocCode);
                    cmd.Parameters.Add(ParaMemo);
                    cmd.Parameters.Add(ParaName);
                    cmd.Parameters.Add(ParaPressCode);
                    cmd.Parameters.Add(ParaPrice);
                    cmd.Parameters.Add(ParaSelfCode);
                    cmd.Parameters.Add(ParaSourceCode);
                    cmd.Parameters.Add(ParaVersion);
                    cmd.Parameters.Add(ParaYYear);
                    try
                    ...{
                        cmd.ExecuteNonQuery();
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", GetAlertScript("修改成功！"));
                        GridViewPublishersDataBind();
                    }
                    catch (Exception ex)
                    ...{
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", GetAlertScript("修改失敗！" + ex.Message));
                        Response.Write("數據修改錯：" + ex.Message);
                    }
                }
                cnn.Close();
            }
            #endregion
            CloseSqlConnection(cnn);
            ClearTextBox();
            GridViewPublishersDataBind();
        }
        catch (Exception ex)
        ...{
            ClearTextBox();
            Response.Write("連接數據庫錯：" + ex.Message);
        }
    }
    #endregion

    全选#region 全选
    /**//// <summary>
    /// 全选事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void checkAll_ServerChange(object sender, EventArgs e)
    ...{
        int zdkc, mskc;

        foreach (GridViewRow gvr in gridViewPublishers.Rows)
        ...{
            zdkc = Convert.ToInt32(gvr.Cells[8].Text);
            mskc = Convert.ToInt32(gvr.Cells[9].Text);
            if(zdkc==0&&mskc==0)
            ((CheckBox)gvr.Cells[0].FindControl("check")).Checked = ((CheckBox)sender).Checked;
        }
    }
    #endregion

    批量删除#region 批量删除
    /**//// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtnDelChecked_Click(object sender, EventArgs e)
    ...{
        int zdkc, mskc;
        bool isChecked;
        HtmlInputCheckBox check;
        GridViewRow row;
        string sID="";
       
        for (int i = 0; i < gridViewPublishers.Rows.Count; i++)
        ...{
            row = gridViewPublishers.Rows;
            zdkc = Convert.ToInt32(row.Cells[8].Text);
            mskc = Convert.ToInt32(row.Cells[9].Text);
            check =(HtmlInputCheckBox)row.FindControl("check");

            if (zdkc == 0 && mskc == 0 && check.Checked)
                sID += check.Value + ",";
        }
        
        if (sID.Length > 0)
        ...{
            DeleteRows("BookInfoSheet", sID);
            GridViewPublishersDataBind();
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", GetAlertScript("批量删除完成!"));
        }
        else
        ...{
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", GetAlertScript("请选择要删除的记录!"));
        }
        
    }
    #endregion

    导出为Excel#region 导出为Excel
    /**//// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    /// 
    protected void BtnToExcel_Click(object sender, EventArgs e)
    ...{
        GridViewExport("图书信息", "excel");
    }
    #endregion

    导出为Word#region 导出为Word
    /**//// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtnToWord_Click(object sender, EventArgs e)
    ...{
        GridViewExport("图书信息", "word");
    }
    #endregion

    #endregion

}