ASP.NET分页存储过程自定义用户控件
生活随笔
收集整理的這篇文章主要介紹了
ASP.NET分页存储过程自定义用户控件
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
網上有很多分頁存儲過程，但是基本上都是提供一個單純的存儲過程，沒有具體的怎樣去實現。最近做一個項目用戶的數數據相當大（一百萬以上的數據），如果用.NET自帶的分頁基本上是跑不動了，不是提示超時就是死在那里。于是就想到用存儲過程分頁來實現，去網上逛了一大圈終于找了幾個比較好的存儲過程。接下去就開始做測試等等，最后就干脆把它做成用戶控件算了，以后用直接拖到頁面上，傳幾個屬性進去就可以實現分頁，免得每次都重復同樣的code。
??????????@tblName????????????varchar(255),????????????????--???表名?
??????????@RetColumns?????????varchar(1000)?=?'*',?????????--???需要返回的列，默認為全部???
??????????@Orderfld???????????varchar(255),????????????????--???排序字段名???
??????????@PageSize???????????int??=??10,??????????????????--???頁尺寸???
??????????@PageIndex??????????int??=??1,???????????????????--???頁碼???
??????????@IsCount????????????bit??=??0,???????????????????--???返回記錄總數,???非???0???值則返回???
??????????@OrderType??????????varchar(50)??=??'asc',???????--???設置排序類型,???非???asc???值則降序???
??????????@strWhere???????????varchar(1000)??=??''?????????--???查詢條件???(注意:???不要加???where)???
??AS
??????
??declare???@strSQL???????varchar(1000)??????????--???主語句???
??declare???@strTmp???????varchar(300)???????????--???臨時變量???
??declare???@strOrder?????varchar(400)???????????--???排序類型???
????
??if??@IsCount??!=???0????--執行總數統計
??
????begin
??????if?@strWhere?!=?''???
??????????set???@strSQL???=???"select???count(*)???as???Total???from???["???+???@tblName???+???"]??where?"?+?@strWhere?
??????else
??????????set???@strSQL???=???"select???count(*)???as???Total???from???["???+???@tblName???+???"]"
????end
????
??else????????????????????--執行查詢操作
??
??begin
????????????
????if??@OrderType??!=???'asc'???
????????begin???
????????????????set???@strTmp???=???"<(select???min"???
????????????????set???@strOrder???=???"???order???by???["???+???@Orderfld???+"]???desc"???
????????end???
????else???
????????begin???
????????????????set??@strTmp???=???">(select???max"???
????????????????set??@strOrder???=???"???order???by???["???+???@Orderfld???+"]???asc"???
????????end???
????????
????set??@strSQL???=???"select???top???"???+???str(@PageSize)???+???"???"?+?@RetColumns?+?"????from???["???
????????????+???@tblName???+???"]???where???["???+???@Orderfld???+???"]"???+???@strTmp???+???"(["???
????????????+???@Orderfld???+???"])???from???(select???top???"???+???str((@PageIndex-1)*@PageSize)???+???"???["???
????????????+???@Orderfld???+???"]???from???["???+???@tblName???+???"]"???+???@strOrder???+???")???as???tblTmp)"???
????????????+???@strOrder???
????????
????if??@strWhere??!=???''???
????????????set???@strSQL???=???"select???top???"???+???str(@PageSize)???+???"???"?+?@RetColumns?+?"???from???["???
????????????????????+???@tblName???+???"]???where???["???+???@Orderfld???+???"]"???+???@strTmp???+???"(["???
????????????????????+???@Orderfld???+???"])???from???(select???top???"???+???str((@PageIndex-1)*@PageSize)???+???"???["???
????????????????????+???@Orderfld???+???"]???from???["???+???@tblName???+???"]???where???("???+???@strWhere???+???")???"???
????????????????????+???@strOrder???+???")???as???tblTmp)???and???("???+???@strWhere???+???")???"???+???@strOrder???
????????
????if??@PageIndex???=???1???
????????begin???
????????????????set???@strTmp???=???""???
????????????????if???@strWhere???!=???''???
????????????????????????set???@strTmp???=???"???where???("???+???@strWhere???+???")"???
????????????
????????????????set???@strSQL???=???"select???top???"???+???str(@PageSize)???+???"???"?+?@RetColumns?+?"???from???["??????????
????????????????????????+???@tblName???+???"]"???+???@strTmp???+???"???"???+???@strOrder???
????????end
?end
????
exec??(@strSQL)
<asp:label?id="Label2"?runat="server"?Font-Size="9pt">共</asp:label><FONT?face="宋體"> </FONT></FONT><asp:label?id="lbl_RecordCnt"?runat="server"?Font-Size="9pt"></asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label3"?runat="server"?Font-Size="9pt">項</asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label4"?runat="server"?Font-Size="9pt"?ForeColor="Black">|</asp:label><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbFirst"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="First">首頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbPre"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="Pre">上一頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbNext"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="Next">下一頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbLast"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="Last">末頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:label?id="Label5"?runat="server"?Font-Size="9pt"?ForeColor="Black">|</asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label6"?runat="server"?Font-Size="9pt">轉</asp:label><asp:textbox?id="txt_CurrentPage"?runat="server"?Enabled="False"?Width="35px"?Height="18px"?AutoPostBack="True"></asp:textbox><FONT?face="宋體"></FONT>
<asp:label?id="Label8"?runat="server"?Font-Size="9pt"?ForeColor="Black">/</asp:label><FONT?face="宋體"> </FONT><asp:label?id="lbl_PageCnt"?runat="server"?Font-Size="9pt"></asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label9"?runat="server"?Font-Size="9pt">頁</asp:label>
...{
????using?System;
????using?System.Data;
????using?System.Drawing;
????using?System.Web;
????using?System.Web.UI.WebControls;
????using?System.Web.UI.HtmlControls;
????using?System.Data.SqlClient;
????/**////?<summary>
????///?配合存儲過程分頁自定義控件(Sql?Server)
??? ///?By?Cherish58
????///?</summary>
????public?class?GetPagerForSql?:?System.Web.UI.UserControl
????...{
????????protected?System.Web.UI.WebControls.Label?Label1;
????????protected?System.Web.UI.WebControls.Label?Label9;
????????protected?System.Web.UI.WebControls.Label?lbl_PageCnt;
????????protected?System.Web.UI.WebControls.Label?Label6;
????????protected?System.Web.UI.WebControls.LinkButton?lkbLast;
????????protected?System.Web.UI.WebControls.LinkButton?lkbNext;
????????protected?System.Web.UI.WebControls.LinkButton?lkbPre;
????????protected?System.Web.UI.WebControls.LinkButton?lkbFirst;
????????protected?System.Web.UI.WebControls.Label?Label3;
????????protected?System.Web.UI.WebControls.Label?lbl_RecordCnt;
????????protected?System.Web.UI.WebControls.Label?Label2;
????????protected?System.Web.UI.WebControls.Label?Label4;
????????protected?System.Web.UI.WebControls.Label?Label5;
????????protected?System.Web.UI.WebControls.Label?Label7;
????????protected?System.Web.UI.WebControls.Label?Label8;
????????protected?System.Web.UI.WebControls.TextBox?txt_CurrentPage;
????????全局變量#region?全局變量
????????/**////?<summary>
????????///?獲得數據庫連接字符
????????///?</summary>
????????protected?string?strconn?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString()?;
????????/**////?<summary>
????????///?初始登陸時是否綁定數據（是為true，否為false），默認為false
????????///?</summary>
????????public?bool?InitBindData?=?false?;
????????#endregion
????????屬性#region?屬性
????????/**////?<summary>
????????///?表名，必須賦初值
????????///?</summary>
????????public?string?TableName
????????...{
????????????get...{return?ViewState["TableName"].ToString();}
????????????set...{ViewState["TableName"]?=?value;}
????????}
????????/**////?<summary>
????????///?返回的列名，默認為全部
????????///?</summary>
????????public?string?RetColumns
????????...{
????????????get...{return?ViewState["RetColumns"].ToString();}
????????????set...{ViewState["RetColumns"]?=?value;}
????????}
????????/**////?<summary>
????????///?查詢條件字符串，默認為空
????????///?</summary>
????????public?string?SqlWhere
????????...{
????????????get...{return?ViewState["SqlWhere"].ToString();}
????????????set...{ViewState["SqlWhere"]?=?value;}
????????}
????????/**////?<summary>
????????///?排序字段，必須賦初值
????????///?</summary>
????????public?string?OrderField
????????...{
????????????get...{return?ViewState["OrderField"].ToString();}
????????????set...{ViewState["OrderField"]?=?value;}
????????}
????????/**////?<summary>
????????///?排序類型（升序為asc，降序為desc），默認為升序
????????///?</summary>
????????public?string?OrderType
????????...{
????????????get...{return?ViewState["OrderType"].ToString();}
????????????set...{ViewState["OrderType"]?=?value;}
????????}
????????/**////?<summary>
????????///?每頁顯示記錄數，默認為10條
????????///?</summary>
????????public?int?PageSize
????????...{
????????????get...{return?int.Parse(ViewState["PageSize"].ToString());}
????????????set...{ViewState["PageSize"]?=?value;}
????????}
????????/**////?<summary>
????????///?初始顯示為第幾頁，默認為第1頁
????????///?</summary>
????????public?int?CurrentPage
????????...{
????????????get...{return?int.Parse(ViewState["CurrentPage"].ToString());}
????????????set...{ViewState["CurrentPage"]?=?value;}
????????}
????????/**////?<summary>
????????///?數據列表控件名稱，必須賦初值
????????///?</summary>
????????public?string?DataControlName
????????...{
????????????get...{return?ViewState["DataControlName"].ToString();}
????????????set...{ViewState["DataControlName"]?=?value;}
????????}
????????#endregion
????????Page_Load#region?Page_Load
????????private?void?Page_Load(object?sender,?System.EventArgs?e)
????????...{
????????????if(!IsPostBack)
????????????...{
????????????????if(this.InitBindData)
????????????????...{
????????????????????//默認顯示為第幾頁
????????????????????ViewState["CurrentPage"]?=?ViewState["CurrentPage"]?==?null?||?ViewState["CurrentPage"].ToString()?==?""???"1"?:?ViewState["CurrentPage"].ToString()?;
????????????????????//每頁顯示記錄總數
????????????????????ViewState["PageSize"]?=?ViewState["PageSize"]?==?null?||?ViewState["PageSize"].ToString()?==?""???10?:?int.Parse(ViewState["PageSize"].ToString())?;
????????????????????this.BindGridData()?;
????????????????}
????????????}
????????}
????????#endregion
????????Web?窗體設計器生成的代碼#region?Web?窗體設計器生成的代碼
????????override?protected?void?OnInit(EventArgs?e)
????????...{
????????????//
????????????//?CODEGEN:?該調用是?ASP.NET?Web?窗體設計器所必需的。
????????????//
????????????InitializeComponent();
????????????base.OnInit(e);
????????}
????????
????????/**////?<summary>
????????///????????設計器支持所需的方法?-?不要使用代碼編輯器
????????///????????修改此方法的內容。
????????///?</summary>
????????private?void?InitializeComponent()
????????...{
????????????this.lkbFirst.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.lkbPre.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.lkbNext.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.lkbLast.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.txt_CurrentPage.TextChanged?+=?new?System.EventHandler(this.txt_CurrentPage_TextChanged);
????????????this.Load?+=?new?System.EventHandler(this.Page_Load);
????????}
????????#endregion
????????分頁?ChangePage#region?分頁?ChangePage
????????private?void?ChangePage(object?sender,?System.Web.UI.WebControls.CommandEventArgs?e)
????????...{
????????????int?PageCount?=?this.GetPageCount()?;???
????????????int?CurrentPage?=?int.Parse(ViewState["CurrentPage"].ToString())?;
???
????????????string?Change?=?e.CommandArgument.ToString()?;
????????????if(Change?==?"Pre")?//上一頁???
????????????...{?????????
????????????????if(CurrentPage?<=?1)???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?1;????
????????????????}???
????????????????else???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?CurrentPage?-?1?;?????
????????????????}???
????????????}???
????????????else?if(Change?==?"Next")?//下一頁??
????????????...{?????????
????????????????if(CurrentPage?>=?PageCount)???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?PageCount?;?????
????????????????}???
????????????????else???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?CurrentPage?+?1?;????
????????????????}??????
????????????}???
????????????else?if(Change?==?"First")?//首頁
????????????...{???
????????????????ViewState["CurrentPage"]?=?1?;???????
????????????}???
????????????else?//末頁
????????????...{???
????????????????ViewState["CurrentPage"]?=?PageCount?;???
????????????}
???
????????????//顯示當前頁
????????????this.txt_CurrentPage.Text?=?ViewState["CurrentPage"].ToString()?;??
?
????????????this.ProData()?;???
????????}
????????#endregion
????????綁定數據#region?綁定數據
????????/**////?<summary>
????????///?設置分頁相關的參數
????????///?</summary>
????????private?void?BindGridData()
????????...{
????????????//記錄總數
????????????this.lbl_RecordCnt.Text?=?this.GetRecordCount().ToString()?;?
????????????//總頁數
????????????this.lbl_PageCnt.Text?=?this.GetPageCount().ToString()?;
????????????if(this.lbl_PageCnt.Text?!=?"0")
????????????...{
????????????????//當前頁
????????????????this.txt_CurrentPage.Text?=?ViewState["CurrentPage"].ToString()?;?
????????????}
????????????else
????????????????this.txt_CurrentPage.Text?=?"0"?;?
????????????//避免翻頁后再查詢出現列表沒記錄的情況
????????????if(int.Parse(this.lbl_RecordCnt.Text)?<=?int.Parse(ViewState["PageSize"].ToString()))
????????????...{
????????????????ViewState["CurrentPage"]?=?1?;
????????????????this.txt_CurrentPage.Text?=?"1"?;
????????????}
????????????//綁定數據
????????????this.ProData()?;
????????}
????????#endregion
????????處理數據集#region?處理數據集
????????/**////?<summary>
????????///?處理數據集
????????///?</summary>
????????///?<returns></returns>
????????private?void?ProData()
????????...{???
????????????SqlConnection?conn?=?new?SqlConnection(strconn);???
????????????SqlCommand?cmd?=?new?SqlCommand("GetRecordFromPage",conn);???
????????????conn.Open()?;?
?
????????????cmd.CommandType?=?CommandType.StoredProcedure?;???
????????????cmd.Parameters.Add("@tblName",""+ViewState["TableName"].ToString()+"")?;
????????????string?retcolumns?=?ViewState["RetColumns"]?==?null?||?ViewState["RetColumns"].ToString()?==?""???"*"?:?ViewState["RetColumns"].ToString()?;
????????????cmd.Parameters.Add("@RetColumns",retcolumns)?;???
????????????string?sqlwhere?=?ViewState["SqlWhere"]?==?null?||?ViewState["SqlWhere"].ToString()?==?""???""?:?ViewState["SqlWhere"].ToString()?;
????????????cmd.Parameters.Add("@strWhere",sqlwhere)?;???
????????????cmd.Parameters.Add("@Orderfld",""+ViewState["OrderField"].ToString()+"")?;
????????????cmd.Parameters.Add("@PageIndex",int.Parse(ViewState["CurrentPage"].ToString()))?;?
????????????cmd.Parameters.Add("@PageSize",""+int.Parse(ViewState["PageSize"].ToString())+"")?;
????????????string?ordertype?=?ViewState["OrderType"]?==?null?||?ViewState["OrderType"].ToString()?==?""???"asc"?:?ViewState["OrderType"].ToString()?;
????????????cmd.Parameters.Add("@OrderType",ordertype)?;
????
????????????SqlDataAdapter?da?=?new?SqlDataAdapter()?;???
????????????da.SelectCommand?=?cmd?;???
????
????????????DataSet?ds?=?new?DataSet()?;???
????????????da.Fill(ds)?;
????????????//找到父頁面控件并綁定（這里只對DataGrid控件綁定）
????????????DataGrid?dg?=?(DataGrid)this.Page.FindControl(""+ViewState["DataControlName"].ToString()+"")?;
????????????dg.DataSource?=?ds?;
????????????dg.DataBind()?;
????????????da.Dispose()?;
????????????cmd.Dispose()?;
????????????conn.Close()?;
????????????//控制分頁按扭狀態
????????????this.StatsLinkButton()?;
????????}
????????#endregion
????????控制分頁按扭狀態#region?控制分頁按扭狀態
????????private?void?StatsLinkButton()
????????...{
????????????int?CurrentPage?=?int.Parse(ViewState["CurrentPage"].ToString())?;???
????????????int?PageCount?=?this.GetPageCount()?;?
????????????if(PageCount?>?0)
????????????????this.txt_CurrentPage.Enabled?=?true?;
????????????else
????????????????this.txt_CurrentPage.Enabled?=?false?;
????????????//若當前頁為第一頁
????????????if(CurrentPage?<=1?)???
????????????...{?????????
????????????????this.lkbFirst.Enabled?=?false?;???
????????????????this.lkbPre.Enabled?=?false?;???????
????????????}
????????????else
????????????...{
????????????????this.lkbFirst.Enabled?=?true?;???
????????????????this.lkbPre.Enabled?=?true?;???????
????????????}
????????????//若當前頁為最后頁?
????????????if(CurrentPage?>=?PageCount)??
????????????...{?????????
????????????????this.lkbLast.Enabled?=?false?;???
????????????????this.lkbNext.Enabled?=?false?;???
????????????}
????????????else
????????????...{
????????????????this.lkbLast.Enabled?=?true?;???
????????????????this.lkbNext.Enabled?=?true?;???
????????????}
????????}
????????#endregion
????????得到記錄總數、總頁數#region?得到記錄總數、總頁數
????????//記錄總數
????????private?int?GetRecordCount()???
????????...{???
????????????int?RecordCount?=?0?;
???
????????????string?sql?=?"select?count(*)?from?"+ViewState["TableName"].ToString()+"?where?1=1"?;
????????????if(ViewState["SqlWhere"]?!=?null?&&?ViewState["SqlWhere"].ToString()?!=?"")
????????????????sql?=?sql?+?"?and?"+ViewState["SqlWhere"].ToString()+""?;
????????????SqlConnection?conn?=?new?SqlConnection(strconn)?;???
????????????SqlCommand?cmd?=?new?SqlCommand(sql,conn)?;???
????????????conn.Open()?;
????????????RecordCount?=?int.Parse(cmd.ExecuteScalar().ToString())?;
????????????cmd.Dispose()?;
????????????conn.Close()?;
?????
????????????return?RecordCount?;???????
????????}?
????????//總頁數
????????private?int?GetPageCount()???
????????...{???
????????????int?RecordCount?=?0?;???
????????????int?YeShu?=?0?;
????????????int?psize?=?int.Parse(ViewState["PageSize"].ToString())?;
????????????string?sql?=?"select?count(*)?from?"+ViewState["TableName"].ToString()+"?where?1=1"?;
????????????if(ViewState["SqlWhere"]?!=?null?&&?ViewState["SqlWhere"].ToString()?!=?"")
????????????????sql?=?sql?+?"?and?"+ViewState["SqlWhere"].ToString()+""?;
????????????SqlConnection?conn?=?new?SqlConnection(strconn)?;???
????????????SqlCommand?cmd?=?new?SqlCommand(sql,conn)?;???
????????????conn.Open()?;
????????????RecordCount?=?int.Parse(cmd.ExecuteScalar().ToString())?;
????????????cmd.Dispose()?;
????????????conn.Close()?;
?????
????????????YeShu?=?RecordCount?%?psize?;???
????
????????????if(YeShu?==?0)???
????????????...{???
????????????????return?RecordCount/psize?;???????
????????????}???
????????????else???
????????????...{???
????????????????return?RecordCount/psize?+?1?;???
????????????}????
????????}?
??
????????#endregion
????????跳轉#region?跳轉
????????private?void?txt_CurrentPage_TextChanged(object?sender,?System.EventArgs?e)
????????...{
????????????try
????????????...{
????????????????int?num?=?Convert.ToInt32(this.txt_CurrentPage.Text)?;
????????????????if(num?>?this.GetPageCount())
????????????????...{
????????????????????Page.RegisterStartupScript("","<script>alert('輸入的頁數已超出總頁數，請重新輸入！')</script>")?;
????????????????????return?;
????????????????}
????????????????ViewState["CurrentPage"]?=?num?;??
?
????????????????this.ProData()?;???
????????????}
????????????catch(Exception?ee)
????????????...{
????????????????Page.RegisterStartupScript("","<script>alert('請輸入正確的頁數！')</script>")?;
????????????????return?;
????????????}????????
????????}
????????#endregion
????????傳值后再綁定，用于有條件查詢（前臺調用）#region?傳值后再綁定，用于有條件查詢（前臺調用）
????????/**////?<summary>
????????///?傳值后再綁定，用于有條件查詢
????????///?</summary>
????????public?void?GetDataByCond()
????????...{
????????????//默認顯示為第1頁
????????????ViewState["CurrentPage"]?=?ViewState["CurrentPage"]?==?null?||?ViewState["CurrentPage"].ToString()?==?""???"1"?:?ViewState["CurrentPage"].ToString()?;
????????????//每頁顯示記錄總數
????????????ViewState["PageSize"]?=?ViewState["PageSize"]?==?null?||?ViewState["PageSize"].ToString()?==?""???10?:?int.Parse(ViewState["PageSize"].ToString())?;
????????????this.BindGridData()?;
????????}
????????#endregion
????}
}
????????protected?GetPagerForSql?GetPagerForSql1?;
????
????????Page_Load#region?Page_Load
????????private?void?Page_Load(object?sender,?System.EventArgs?e)
????????{
????????????if(!IsPostBack)
????????????{
????????????????ViewState["sqlcond"]?=?""?;
????????????????ViewState["key"]?=?""?;
????????????????this.BindGrid(true)?;
????????????}
????????}
????????#endregion????????
????????綁定列表
??????? 經本人測試，對于Sqlserver的效率相當快，而對于Oracle的效率（按某個字段倒序排）不是很理想，如果不排序效率很理想，這點沒有深入研究（Oracle為什么按倒序排速度很慢，在PL/SQL里也一樣）。
?????? 先發布SqlServer版的分頁自定義存儲過程???????下載代碼
????
?????? 存儲過程（該存儲過程為網上下載）：
??????????@tblName????????????varchar(255),????????????????--???表名?
??????????@RetColumns?????????varchar(1000)?=?'*',?????????--???需要返回的列，默認為全部???
??????????@Orderfld???????????varchar(255),????????????????--???排序字段名???
??????????@PageSize???????????int??=??10,??????????????????--???頁尺寸???
??????????@PageIndex??????????int??=??1,???????????????????--???頁碼???
??????????@IsCount????????????bit??=??0,???????????????????--???返回記錄總數,???非???0???值則返回???
??????????@OrderType??????????varchar(50)??=??'asc',???????--???設置排序類型,???非???asc???值則降序???
??????????@strWhere???????????varchar(1000)??=??''?????????--???查詢條件???(注意:???不要加???where)???
??AS
??????
??declare???@strSQL???????varchar(1000)??????????--???主語句???
??declare???@strTmp???????varchar(300)???????????--???臨時變量???
??declare???@strOrder?????varchar(400)???????????--???排序類型???
????
??if??@IsCount??!=???0????--執行總數統計
??
????begin
??????if?@strWhere?!=?''???
??????????set???@strSQL???=???"select???count(*)???as???Total???from???["???+???@tblName???+???"]??where?"?+?@strWhere?
??????else
??????????set???@strSQL???=???"select???count(*)???as???Total???from???["???+???@tblName???+???"]"
????end
????
??else????????????????????--執行查詢操作
??
??begin
????????????
????if??@OrderType??!=???'asc'???
????????begin???
????????????????set???@strTmp???=???"<(select???min"???
????????????????set???@strOrder???=???"???order???by???["???+???@Orderfld???+"]???desc"???
????????end???
????else???
????????begin???
????????????????set??@strTmp???=???">(select???max"???
????????????????set??@strOrder???=???"???order???by???["???+???@Orderfld???+"]???asc"???
????????end???
????????
????set??@strSQL???=???"select???top???"???+???str(@PageSize)???+???"???"?+?@RetColumns?+?"????from???["???
????????????+???@tblName???+???"]???where???["???+???@Orderfld???+???"]"???+???@strTmp???+???"(["???
????????????+???@Orderfld???+???"])???from???(select???top???"???+???str((@PageIndex-1)*@PageSize)???+???"???["???
????????????+???@Orderfld???+???"]???from???["???+???@tblName???+???"]"???+???@strOrder???+???")???as???tblTmp)"???
????????????+???@strOrder???
????????
????if??@strWhere??!=???''???
????????????set???@strSQL???=???"select???top???"???+???str(@PageSize)???+???"???"?+?@RetColumns?+?"???from???["???
????????????????????+???@tblName???+???"]???where???["???+???@Orderfld???+???"]"???+???@strTmp???+???"(["???
????????????????????+???@Orderfld???+???"])???from???(select???top???"???+???str((@PageIndex-1)*@PageSize)???+???"???["???
????????????????????+???@Orderfld???+???"]???from???["???+???@tblName???+???"]???where???("???+???@strWhere???+???")???"???
????????????????????+???@strOrder???+???")???as???tblTmp)???and???("???+???@strWhere???+???")???"???+???@strOrder???
????????
????if??@PageIndex???=???1???
????????begin???
????????????????set???@strTmp???=???""???
????????????????if???@strWhere???!=???''???
????????????????????????set???@strTmp???=???"???where???("???+???@strWhere???+???")"???
????????????
????????????????set???@strSQL???=???"select???top???"???+???str(@PageSize)???+???"???"?+?@RetColumns?+?"???from???["??????????
????????????????????????+???@tblName???+???"]"???+???@strTmp???+???"???"???+???@strOrder???
????????end
?end
????
exec??(@strSQL)
下面為用戶控件前臺html代碼：
<%@?Control?Language="c#"?AutoEventWireup="false"?Codebehind="GetPagerForSql.ascx.cs"?Inherits="doHope.GetPagerForSql"?TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%><asp:label?id="Label2"?runat="server"?Font-Size="9pt">共</asp:label><FONT?face="宋體"> </FONT></FONT><asp:label?id="lbl_RecordCnt"?runat="server"?Font-Size="9pt"></asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label3"?runat="server"?Font-Size="9pt">項</asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label4"?runat="server"?Font-Size="9pt"?ForeColor="Black">|</asp:label><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbFirst"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="First">首頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbPre"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="Pre">上一頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbNext"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="Next">下一頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:linkbutton?id="lkbLast"?runat="server"?Font-Size="9pt"?Enabled="False"?ForeColor="Black"?CommandArgument="Last">末頁</asp:linkbutton><FONT?face="宋體"> </FONT><asp:label?id="Label5"?runat="server"?Font-Size="9pt"?ForeColor="Black">|</asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label6"?runat="server"?Font-Size="9pt">轉</asp:label><asp:textbox?id="txt_CurrentPage"?runat="server"?Enabled="False"?Width="35px"?Height="18px"?AutoPostBack="True"></asp:textbox><FONT?face="宋體"></FONT>
<asp:label?id="Label8"?runat="server"?Font-Size="9pt"?ForeColor="Black">/</asp:label><FONT?face="宋體"> </FONT><asp:label?id="lbl_PageCnt"?runat="server"?Font-Size="9pt"></asp:label><FONT?face="宋體"> </FONT><asp:label?id="Label9"?runat="server"?Font-Size="9pt">頁</asp:label>
下面為后臺代碼：
namespace?doHope...{
????using?System;
????using?System.Data;
????using?System.Drawing;
????using?System.Web;
????using?System.Web.UI.WebControls;
????using?System.Web.UI.HtmlControls;
????using?System.Data.SqlClient;
????/**////?<summary>
????///?配合存儲過程分頁自定義控件(Sql?Server)
??? ///?By?Cherish58
????///?</summary>
????public?class?GetPagerForSql?:?System.Web.UI.UserControl
????...{
????????protected?System.Web.UI.WebControls.Label?Label1;
????????protected?System.Web.UI.WebControls.Label?Label9;
????????protected?System.Web.UI.WebControls.Label?lbl_PageCnt;
????????protected?System.Web.UI.WebControls.Label?Label6;
????????protected?System.Web.UI.WebControls.LinkButton?lkbLast;
????????protected?System.Web.UI.WebControls.LinkButton?lkbNext;
????????protected?System.Web.UI.WebControls.LinkButton?lkbPre;
????????protected?System.Web.UI.WebControls.LinkButton?lkbFirst;
????????protected?System.Web.UI.WebControls.Label?Label3;
????????protected?System.Web.UI.WebControls.Label?lbl_RecordCnt;
????????protected?System.Web.UI.WebControls.Label?Label2;
????????protected?System.Web.UI.WebControls.Label?Label4;
????????protected?System.Web.UI.WebControls.Label?Label5;
????????protected?System.Web.UI.WebControls.Label?Label7;
????????protected?System.Web.UI.WebControls.Label?Label8;
????????protected?System.Web.UI.WebControls.TextBox?txt_CurrentPage;
????????全局變量#region?全局變量
????????/**////?<summary>
????????///?獲得數據庫連接字符
????????///?</summary>
????????protected?string?strconn?=?System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString()?;
????????/**////?<summary>
????????///?初始登陸時是否綁定數據（是為true，否為false），默認為false
????????///?</summary>
????????public?bool?InitBindData?=?false?;
????????#endregion
????????屬性#region?屬性
????????/**////?<summary>
????????///?表名，必須賦初值
????????///?</summary>
????????public?string?TableName
????????...{
????????????get...{return?ViewState["TableName"].ToString();}
????????????set...{ViewState["TableName"]?=?value;}
????????}
????????/**////?<summary>
????????///?返回的列名，默認為全部
????????///?</summary>
????????public?string?RetColumns
????????...{
????????????get...{return?ViewState["RetColumns"].ToString();}
????????????set...{ViewState["RetColumns"]?=?value;}
????????}
????????/**////?<summary>
????????///?查詢條件字符串，默認為空
????????///?</summary>
????????public?string?SqlWhere
????????...{
????????????get...{return?ViewState["SqlWhere"].ToString();}
????????????set...{ViewState["SqlWhere"]?=?value;}
????????}
????????/**////?<summary>
????????///?排序字段，必須賦初值
????????///?</summary>
????????public?string?OrderField
????????...{
????????????get...{return?ViewState["OrderField"].ToString();}
????????????set...{ViewState["OrderField"]?=?value;}
????????}
????????/**////?<summary>
????????///?排序類型（升序為asc，降序為desc），默認為升序
????????///?</summary>
????????public?string?OrderType
????????...{
????????????get...{return?ViewState["OrderType"].ToString();}
????????????set...{ViewState["OrderType"]?=?value;}
????????}
????????/**////?<summary>
????????///?每頁顯示記錄數，默認為10條
????????///?</summary>
????????public?int?PageSize
????????...{
????????????get...{return?int.Parse(ViewState["PageSize"].ToString());}
????????????set...{ViewState["PageSize"]?=?value;}
????????}
????????/**////?<summary>
????????///?初始顯示為第幾頁，默認為第1頁
????????///?</summary>
????????public?int?CurrentPage
????????...{
????????????get...{return?int.Parse(ViewState["CurrentPage"].ToString());}
????????????set...{ViewState["CurrentPage"]?=?value;}
????????}
????????/**////?<summary>
????????///?數據列表控件名稱，必須賦初值
????????///?</summary>
????????public?string?DataControlName
????????...{
????????????get...{return?ViewState["DataControlName"].ToString();}
????????????set...{ViewState["DataControlName"]?=?value;}
????????}
????????#endregion
????????Page_Load#region?Page_Load
????????private?void?Page_Load(object?sender,?System.EventArgs?e)
????????...{
????????????if(!IsPostBack)
????????????...{
????????????????if(this.InitBindData)
????????????????...{
????????????????????//默認顯示為第幾頁
????????????????????ViewState["CurrentPage"]?=?ViewState["CurrentPage"]?==?null?||?ViewState["CurrentPage"].ToString()?==?""???"1"?:?ViewState["CurrentPage"].ToString()?;
????????????????????//每頁顯示記錄總數
????????????????????ViewState["PageSize"]?=?ViewState["PageSize"]?==?null?||?ViewState["PageSize"].ToString()?==?""???10?:?int.Parse(ViewState["PageSize"].ToString())?;
????????????????????this.BindGridData()?;
????????????????}
????????????}
????????}
????????#endregion
????????Web?窗體設計器生成的代碼#region?Web?窗體設計器生成的代碼
????????override?protected?void?OnInit(EventArgs?e)
????????...{
????????????//
????????????//?CODEGEN:?該調用是?ASP.NET?Web?窗體設計器所必需的。
????????????//
????????????InitializeComponent();
????????????base.OnInit(e);
????????}
????????
????????/**////?<summary>
????????///????????設計器支持所需的方法?-?不要使用代碼編輯器
????????///????????修改此方法的內容。
????????///?</summary>
????????private?void?InitializeComponent()
????????...{
????????????this.lkbFirst.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.lkbPre.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.lkbNext.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.lkbLast.Command?+=?new?System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
????????????this.txt_CurrentPage.TextChanged?+=?new?System.EventHandler(this.txt_CurrentPage_TextChanged);
????????????this.Load?+=?new?System.EventHandler(this.Page_Load);
????????}
????????#endregion
????????分頁?ChangePage#region?分頁?ChangePage
????????private?void?ChangePage(object?sender,?System.Web.UI.WebControls.CommandEventArgs?e)
????????...{
????????????int?PageCount?=?this.GetPageCount()?;???
????????????int?CurrentPage?=?int.Parse(ViewState["CurrentPage"].ToString())?;
???
????????????string?Change?=?e.CommandArgument.ToString()?;
????????????if(Change?==?"Pre")?//上一頁???
????????????...{?????????
????????????????if(CurrentPage?<=?1)???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?1;????
????????????????}???
????????????????else???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?CurrentPage?-?1?;?????
????????????????}???
????????????}???
????????????else?if(Change?==?"Next")?//下一頁??
????????????...{?????????
????????????????if(CurrentPage?>=?PageCount)???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?PageCount?;?????
????????????????}???
????????????????else???
????????????????...{???
????????????????????ViewState["CurrentPage"]?=?CurrentPage?+?1?;????
????????????????}??????
????????????}???
????????????else?if(Change?==?"First")?//首頁
????????????...{???
????????????????ViewState["CurrentPage"]?=?1?;???????
????????????}???
????????????else?//末頁
????????????...{???
????????????????ViewState["CurrentPage"]?=?PageCount?;???
????????????}
???
????????????//顯示當前頁
????????????this.txt_CurrentPage.Text?=?ViewState["CurrentPage"].ToString()?;??
?
????????????this.ProData()?;???
????????}
????????#endregion
????????綁定數據#region?綁定數據
????????/**////?<summary>
????????///?設置分頁相關的參數
????????///?</summary>
????????private?void?BindGridData()
????????...{
????????????//記錄總數
????????????this.lbl_RecordCnt.Text?=?this.GetRecordCount().ToString()?;?
????????????//總頁數
????????????this.lbl_PageCnt.Text?=?this.GetPageCount().ToString()?;
????????????if(this.lbl_PageCnt.Text?!=?"0")
????????????...{
????????????????//當前頁
????????????????this.txt_CurrentPage.Text?=?ViewState["CurrentPage"].ToString()?;?
????????????}
????????????else
????????????????this.txt_CurrentPage.Text?=?"0"?;?
????????????//避免翻頁后再查詢出現列表沒記錄的情況
????????????if(int.Parse(this.lbl_RecordCnt.Text)?<=?int.Parse(ViewState["PageSize"].ToString()))
????????????...{
????????????????ViewState["CurrentPage"]?=?1?;
????????????????this.txt_CurrentPage.Text?=?"1"?;
????????????}
????????????//綁定數據
????????????this.ProData()?;
????????}
????????#endregion
????????處理數據集#region?處理數據集
????????/**////?<summary>
????????///?處理數據集
????????///?</summary>
????????///?<returns></returns>
????????private?void?ProData()
????????...{???
????????????SqlConnection?conn?=?new?SqlConnection(strconn);???
????????????SqlCommand?cmd?=?new?SqlCommand("GetRecordFromPage",conn);???
????????????conn.Open()?;?
?
????????????cmd.CommandType?=?CommandType.StoredProcedure?;???
????????????cmd.Parameters.Add("@tblName",""+ViewState["TableName"].ToString()+"")?;
????????????string?retcolumns?=?ViewState["RetColumns"]?==?null?||?ViewState["RetColumns"].ToString()?==?""???"*"?:?ViewState["RetColumns"].ToString()?;
????????????cmd.Parameters.Add("@RetColumns",retcolumns)?;???
????????????string?sqlwhere?=?ViewState["SqlWhere"]?==?null?||?ViewState["SqlWhere"].ToString()?==?""???""?:?ViewState["SqlWhere"].ToString()?;
????????????cmd.Parameters.Add("@strWhere",sqlwhere)?;???
????????????cmd.Parameters.Add("@Orderfld",""+ViewState["OrderField"].ToString()+"")?;
????????????cmd.Parameters.Add("@PageIndex",int.Parse(ViewState["CurrentPage"].ToString()))?;?
????????????cmd.Parameters.Add("@PageSize",""+int.Parse(ViewState["PageSize"].ToString())+"")?;
????????????string?ordertype?=?ViewState["OrderType"]?==?null?||?ViewState["OrderType"].ToString()?==?""???"asc"?:?ViewState["OrderType"].ToString()?;
????????????cmd.Parameters.Add("@OrderType",ordertype)?;
????
????????????SqlDataAdapter?da?=?new?SqlDataAdapter()?;???
????????????da.SelectCommand?=?cmd?;???
????
????????????DataSet?ds?=?new?DataSet()?;???
????????????da.Fill(ds)?;
????????????//找到父頁面控件并綁定（這里只對DataGrid控件綁定）
????????????DataGrid?dg?=?(DataGrid)this.Page.FindControl(""+ViewState["DataControlName"].ToString()+"")?;
????????????dg.DataSource?=?ds?;
????????????dg.DataBind()?;
????????????da.Dispose()?;
????????????cmd.Dispose()?;
????????????conn.Close()?;
????????????//控制分頁按扭狀態
????????????this.StatsLinkButton()?;
????????}
????????#endregion
????????控制分頁按扭狀態#region?控制分頁按扭狀態
????????private?void?StatsLinkButton()
????????...{
????????????int?CurrentPage?=?int.Parse(ViewState["CurrentPage"].ToString())?;???
????????????int?PageCount?=?this.GetPageCount()?;?
????????????if(PageCount?>?0)
????????????????this.txt_CurrentPage.Enabled?=?true?;
????????????else
????????????????this.txt_CurrentPage.Enabled?=?false?;
????????????//若當前頁為第一頁
????????????if(CurrentPage?<=1?)???
????????????...{?????????
????????????????this.lkbFirst.Enabled?=?false?;???
????????????????this.lkbPre.Enabled?=?false?;???????
????????????}
????????????else
????????????...{
????????????????this.lkbFirst.Enabled?=?true?;???
????????????????this.lkbPre.Enabled?=?true?;???????
????????????}
????????????//若當前頁為最后頁?
????????????if(CurrentPage?>=?PageCount)??
????????????...{?????????
????????????????this.lkbLast.Enabled?=?false?;???
????????????????this.lkbNext.Enabled?=?false?;???
????????????}
????????????else
????????????...{
????????????????this.lkbLast.Enabled?=?true?;???
????????????????this.lkbNext.Enabled?=?true?;???
????????????}
????????}
????????#endregion
????????得到記錄總數、總頁數#region?得到記錄總數、總頁數
????????//記錄總數
????????private?int?GetRecordCount()???
????????...{???
????????????int?RecordCount?=?0?;
???
????????????string?sql?=?"select?count(*)?from?"+ViewState["TableName"].ToString()+"?where?1=1"?;
????????????if(ViewState["SqlWhere"]?!=?null?&&?ViewState["SqlWhere"].ToString()?!=?"")
????????????????sql?=?sql?+?"?and?"+ViewState["SqlWhere"].ToString()+""?;
????????????SqlConnection?conn?=?new?SqlConnection(strconn)?;???
????????????SqlCommand?cmd?=?new?SqlCommand(sql,conn)?;???
????????????conn.Open()?;
????????????RecordCount?=?int.Parse(cmd.ExecuteScalar().ToString())?;
????????????cmd.Dispose()?;
????????????conn.Close()?;
?????
????????????return?RecordCount?;???????
????????}?
????????//總頁數
????????private?int?GetPageCount()???
????????...{???
????????????int?RecordCount?=?0?;???
????????????int?YeShu?=?0?;
????????????int?psize?=?int.Parse(ViewState["PageSize"].ToString())?;
????????????string?sql?=?"select?count(*)?from?"+ViewState["TableName"].ToString()+"?where?1=1"?;
????????????if(ViewState["SqlWhere"]?!=?null?&&?ViewState["SqlWhere"].ToString()?!=?"")
????????????????sql?=?sql?+?"?and?"+ViewState["SqlWhere"].ToString()+""?;
????????????SqlConnection?conn?=?new?SqlConnection(strconn)?;???
????????????SqlCommand?cmd?=?new?SqlCommand(sql,conn)?;???
????????????conn.Open()?;
????????????RecordCount?=?int.Parse(cmd.ExecuteScalar().ToString())?;
????????????cmd.Dispose()?;
????????????conn.Close()?;
?????
????????????YeShu?=?RecordCount?%?psize?;???
????
????????????if(YeShu?==?0)???
????????????...{???
????????????????return?RecordCount/psize?;???????
????????????}???
????????????else???
????????????...{???
????????????????return?RecordCount/psize?+?1?;???
????????????}????
????????}?
??
????????#endregion
????????跳轉#region?跳轉
????????private?void?txt_CurrentPage_TextChanged(object?sender,?System.EventArgs?e)
????????...{
????????????try
????????????...{
????????????????int?num?=?Convert.ToInt32(this.txt_CurrentPage.Text)?;
????????????????if(num?>?this.GetPageCount())
????????????????...{
????????????????????Page.RegisterStartupScript("","<script>alert('輸入的頁數已超出總頁數，請重新輸入！')</script>")?;
????????????????????return?;
????????????????}
????????????????ViewState["CurrentPage"]?=?num?;??
?
????????????????this.ProData()?;???
????????????}
????????????catch(Exception?ee)
????????????...{
????????????????Page.RegisterStartupScript("","<script>alert('請輸入正確的頁數！')</script>")?;
????????????????return?;
????????????}????????
????????}
????????#endregion
????????傳值后再綁定，用于有條件查詢（前臺調用）#region?傳值后再綁定，用于有條件查詢（前臺調用）
????????/**////?<summary>
????????///?傳值后再綁定，用于有條件查詢
????????///?</summary>
????????public?void?GetDataByCond()
????????...{
????????????//默認顯示為第1頁
????????????ViewState["CurrentPage"]?=?ViewState["CurrentPage"]?==?null?||?ViewState["CurrentPage"].ToString()?==?""???"1"?:?ViewState["CurrentPage"].ToString()?;
????????????//每頁顯示記錄總數
????????????ViewState["PageSize"]?=?ViewState["PageSize"]?==?null?||?ViewState["PageSize"].ToString()?==?""???10?:?int.Parse(ViewState["PageSize"].ToString())?;
????????????this.BindGridData()?;
????????}
????????#endregion
????}
}
使用時，只需傳幾個必須賦初值的屬性即可：TableName為表或視圖名，OrderField為排序字段（該存儲過程只對一個字段進行排序），DataControlName為數據列表控件名稱（這里默認是DataGrid控件，根據需要自己修改）。
里面有個InitBindData屬性：初始登陸時是否綁定數據（是為true，否為false），默認為false。
簡單示例：
????????protected?GetPagerForSql?GetPagerForSql1?;
????
????????Page_Load#region?Page_Load
????????private?void?Page_Load(object?sender,?System.EventArgs?e)
????????{
????????????if(!IsPostBack)
????????????{
????????????????ViewState["sqlcond"]?=?""?;
????????????????ViewState["key"]?=?""?;
????????????????this.BindGrid(true)?;
????????????}
????????}
????????#endregion????????
????????綁定列表
轉載于:https://www.cnblogs.com/hzuIT/articles/741446.html
《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀總結
以上是生活随笔為你收集整理的ASP.NET分页存储过程自定义用户控件的全部內容，希望文章能夠幫你解決所遇到的問題。
