ASP.NET结合存储过程写的通用搜索分页程序

技术2022-05-11 79

1、select.aspx <% @ Page Language = " C# " %> <% @ import Namespace = " System.Data " %> <% @ import Namespace = " System.Data.SqlClient " %> < script runat = " server " > protected void Page_Load(Object sender, EventArgs e) { int intPageNo,intPageSize,intPageCount; intPageSize = 25; if (Request["CurrentPage"]==null) { intPageNo = 1; } else { intPageNo = Int32.Parse(Request["CurrentPage"]); } SqlConnection mySqlConnection = new SqlConnection("server=(local);Database=test;user id=sa;password="); SqlCommand mySqlCommand = new SqlCommand("up_GetTopicList", mySqlConnection); mySqlCommand.CommandType = CommandType.StoredProcedure; SqlParameter workParm; //搜索表字段，以","号分隔 workParm = mySqlCommand.Parameters.Add("@a_TableList", SqlDbType.VarChar, 200); mySqlCommand.Parameters["@a_TableList"].Value = "OFFERID,type,offertime"; //搜索表名 workParm = mySqlCommand.Parameters.Add("@a_TableName", SqlDbType.VarChar, 30); mySqlCommand.Parameters["@a_TableName"].Value = "offer"; //搜索条件，如"select * from aa where a=1 and b=2 and c=3"则条件为"where a=1 and b=2 and c=3" workParm = mySqlCommand.Parameters.Add("@a_SelectWhere", SqlDbType.VarChar, 500); mySqlCommand.Parameters["@a_SelectWhere"].Value = "where type='idl'"; //表主键字段名，必须为INT类型 workParm = mySqlCommand.Parameters.Add("@a_SelectOrderId", SqlDbType.VarChar, 50); mySqlCommand.Parameters["@a_SelectOrderId"].Value = "offerid"; //排序，可以使用多字段排序但主键字段必需在最前面 workParm = mySqlCommand.Parameters.Add("@a_SelectOrder", SqlDbType.VarChar, 50); mySqlCommand.Parameters["@a_SelectOrder"].Value = "order by offerid desc"; //页号 workParm = mySqlCommand.Parameters.Add("@a_intPageNo", SqlDbType.Int); mySqlCommand.Parameters["@a_intPageNo"].Value = intPageNo; //每页显示数 workParm = mySqlCommand.Parameters.Add("@a_intPageSize", SqlDbType.Int); mySqlCommand.Parameters["@a_intPageSize"].Value = intPageSize; //总记录数(存储过程输出参数) workParm = mySqlCommand.Parameters.Add("@RecordCount", SqlDbType.Int); workParm.Direction = ParameterDirection.Output; //当前页记录数(存储过程返回值) workParm = mySqlCommand.Parameters.Add("RowCount", SqlDbType.Int); workParm.Direction = ParameterDirection.ReturnValue; mySqlConnection.Open(); Repeater.DataSource = mySqlCommand.ExecuteReader(); Repeater.DataBind(); mySqlConnection.Close(); Int32 RecordCount = (Int32)mySqlCommand.Parameters["@RecordCount"].Value; Int32 RowCount = (Int32)mySqlCommand.Parameters["RowCount"].Value; LabelRecord.Text = RecordCount.ToString(); LabelRow.Text = intPageNo.ToString(); intPageCount = RecordCount/intPageSize; if ((RecordCount%intPageSize)>0) intPageCount += 1; LabelPage.Text = intPageCount.ToString(); if (intPageNo>1) { HLFistPage.NavigateUrl = "select.aspx?CurrentPage=1"; HLPrevPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo-1); } else { HLFistPage.NavigateUrl = ""; HLPrevPage.NavigateUrl = ""; //HLFistPage.Enabled = false; //HLPrevPage.Enabled = false; } if (intPageNo<intPageCount) { HLNextPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo+1); HLEndPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageCount); } else { HLNextPage.NavigateUrl = ""; HLEndPage.NavigateUrl = ""; //HLNextPage.Enabled=false; //HLEndPage.Enabled=false; } } </ script > < html > < meta http - equiv = " Content-Type " content = " text/html; charset=gb2312 " > < head > < link href = " /style.css " rel = " stylesheet " /> < style type = " text/css " > .high { font-family: "宋体"; font-size: 9pt; line-height: 140%} .mid { font-size: 9pt; line-height: 12pt} .small { font-size: 9pt; line-height: normal} .TP10_5 { font-size: 14px; line-height: 140%;} </ style > < style type = " text/css " > A:link { COLOR: #cc6666} </ style > </ head > < body > < form runat = " server " > < span class = " high " > 第 < font color = " #CC0000 " >< asp:Label id = " LabelRow " runat = " server " /></ font > 页 | 共有 < asp:Label id = " LabelPage " runat = " server " /> 页 | < asp:Label id = " LabelRecord " runat = " server " /> 条信息 | < asp:HyperLink id = " HLFistPage " Text = " 首页 " runat = " server " /> | < asp:HyperLink id = " HLPrevPage " Text = " 上一页 " runat = " server " /> | < asp:HyperLink id = " HLNextPage " Text = " 下一页 " runat = " server " /> | < asp:HyperLink id = " HLEndPage " Text = " 尾页 " runat = " server " /></ span >< br > < asp:Repeater id = Repeater runat = " server " > < HeaderTemplate > < table width = " 583 " border = " 0 " cellspacing = " 0 " cellpadding = " 0 " > < tr > < td bgcolor = " #000000 " >< table width = " 100% " border = " 0 " cellpadding = " 4 " cellspacing = " 1 " class = " TP10_5 " > < tr bgcolor = " #999999 " > < td align = " center " > < strong >< font color = " #FFFFFF " > 订单号 </ font ></ strong ></ td > < td align = " center " > < strong >< font color = " #FFFFFF " > 服务项目 </ font ></ strong ></ td > < td align = " center " > < strong >< font color = " #FFFFFF " > 预订日期 </ font ></ strong ></ td > < td align = " center " > < strong >< font color = " #FFFFFF " > 操作人员 </ font ></ strong ></ td > < td align = " center " > < strong >< font color = " #FFFFFF " > 分配状态 </ font ></ strong ></ td > < td > < div align = " center " ></ div ></ td > </ tr > </ HeaderTemplate > < ItemTemplate > < tr align = " center " bgcolor = " #FFFFFF " class = " small " onMouseOver = ' this.style.background="#CCCCCC" ' onMouseOut = ' this.style.background="#FFFFFF" ' > < td ><% # DataBinder.Eval(Container.DataItem, " offerid " ) %></ td > < td ><% # DataBinder.Eval(Container.DataItem, " type " ) %></ td > < td ><% # DataBinder.Eval(Container.DataItem, " offertime " ) %></ td > < td > </ td > < td > </ td > < td >< a href = " javascript:void(window.open('info.asp?id=<%# DataBinder.Eval(Container.DataItem, " offerid " ) %>','订单分配','height=600,width=1000')) " > 订单详情 </ a ></ td > </ tr > </ ItemTemplate > < FooterTemplate > </ table ></ td > </ tr > </ table > </ FooterTemplate > </ asp:Repeater > </ form > </ body > </ html > 2、up_GetTopicList.sql CREATE proc up_GetTopicList @a_TableList Varchar ( 200 ), @a_TableName Varchar ( 30 ), @a_SelectWhere Varchar ( 500 ), @a_SelectOrderId Varchar ( 20 ), @a_SelectOrder Varchar ( 50 ), @a_intPageNo int , @a_intPageSize int , @RecordCount int OUTPUT as /*定义局部变量*/ declare @intBeginID int declare @intEndID int declare @intRootRecordCount int declare @intRowCount int declare @TmpSelect NVarchar ( 600 ) /*关闭计数*/ set nocount on /*求总共根贴数*/ select @TmpSelect = ' set nocount on;select @SPintRootRecordCount = count(*) from ' + @a_TableName + ' ' + @a_SelectWhere execute sp_executesql @TmpSelect , N ' @SPintRootRecordCount int OUTPUT ' , @SPintRootRecordCount = @intRootRecordCount OUTPUT select @RecordCount = @intRootRecordCount if ( @intRootRecordCount = 0 ) -- 如果没有贴子，则返回零 return 0 /*判断页数是否正确*/ if ( @a_intPageNo - 1 ) * @a_intPageSize > @intRootRecordCount return ( - 1 ) /*求开始rootID*/ set @intRowCount = ( @a_intPageNo - 1 ) * @a_intPageSize + 1 /*限制条数*/ select @TmpSelect = ' set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = ' + @a_SelectOrderId + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' ' + @a_SelectOrder execute sp_executesql @TmpSelect , N ' @SPintRowCount int,@SPintBeginID int OUTPUT ' , @SPintRowCount = @intRowCount , @SPintBeginID = @intBeginID OUTPUT /*结束rootID*/ set @intRowCount = @a_intPageNo * @a_intPageSize /*限制条数*/ select @TmpSelect = ' set nocount on;set rowcount @SPintRowCount;select @SPintEndID = ' + @a_SelectOrderId + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' ' + @a_SelectOrder execute sp_executesql @TmpSelect , N ' @SPintRowCount int,@SPintEndID int OUTPUT ' , @SPintRowCount = @intRowCount , @SPintEndID = @intEndID OUTPUT if @a_SelectWhere = '' or @a_SelectWhere IS NULL select @TmpSelect = ' set nocount off;set rowcount 0;select ' + @a_TableList + ' from ' + @a_TableName + ' where ' + @a_SelectOrderId + ' between ' else select @TmpSelect = ' set nocount off;set rowcount 0;select ' + @a_TableList + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' and ' + @a_SelectOrderId + ' between ' if @intEndID > @intBeginID select @TmpSelect = @TmpSelect + ' @SPintBeginID and @SPintEndID ' + ' ' + @a_SelectOrder else select @TmpSelect = @TmpSelect + ' @SPintEndID and @SPintBeginID ' + ' ' + @a_SelectOrder execute sp_executesql @TmpSelect , N ' @SPintEndID int,@SPintBeginID int ' , @SPintEndID = @intEndID , @SPintBeginID = @intBeginID return ( @@rowcount ) -- select @@rowcount GO

专利

最新回复(0)