ASP. NET paging stored procedures and calls, asp.net stored procedures
Let's talk about code and processes.
1. Create a stored procedure
<Span style = "font-size: 18px;"> create procedure [dbo]. [sp_PagingTabs] @ TableName nvarchar (200),/* Table name */@ FieldName nvarchar (500),/* field to be queried */@ where nvarchar (500 ), /* query condition */@ OrderField nvarchar (500),/* sort the specified field */@ Order nvarchar (50 ), /* the sorting can only be asc desc */@ PageIdORField nvarchar (50),/* specify the field for pagination */@ PageSize int, /* Number of pages per page */@ PageIndex int/* Current page number */asbegin/* first clear spaces around the string */set @ TableName = LTRIM (rtrim (@ TableName )); set @ FieldName = LTRIM (RTRIM (@ FieldName); set @ where = LTRIM (RTRIM (@ where); set @ Order = LTRIM (RTRIM (@ Order )); set @ OrderField = LTRIM (RTRIM (@ OrderField); set @ PageIdORField = LTRIM (rtrim (@ PageIdORField )); /* Then judge the non-empty pass value */if ISNULL (@ TableName, '') = ''return; if ISNULL (@ FieldName ,'') = ''set @ FieldName = '*'; if ISNULL (@ Order, '') = ''set @ Order = 'desc '; /* query paging data */declare @ SQL nvarchar (1000) set @ SQL = 'select top' + cast (@ PageSize as nvarchar (50 )) + ''+ @ FieldName + 'from' + @ TableName + 'where 1 = 1' + @ where + 'and' + @ PageIdORField + 'not in (select top '+ cast (@ PageSize * (@ PageIndex-1 )) as nvarchar (50 )) + @ PageIdORField + 'from' + @ TableName + 'where 1 = 1' + @ where + 'ORDER BY' + @ OrderField + ''+ @ order + ') order by '+ @ OrderField + ''+ @ Order;/* Total Number of pages */set @ SQL + = 'select count (*) from '+ @ TableName + 'where 1 = 1' + @ where exec (@ SQL) end </span>
2. Create a method class
/// <Summary> /// paging Stored Procedure /// </summary> /// <param name = "TableName"> table name </param> /// <param name = "FieldName"> Field name </param> // <param name = "wheres"> where condition </param> /// <param name = "order"> only desc or asc </param> // <param name = "PageSize"> Number of entries per page </param> /// <param name = "PageIndex"> current page number </param> /// <param name = "TotalCount"> total page number </param> /// <param name = "PageIdORField"> specify fields for pagination </param> /// <param name = "OrderFi Eld "> sort specified fields </param> public static DataSet LinkProce (string TableName, string FieldName, string wheres, string order, string PageIdORField, string OrderField, ref int PageSize, ref int PageIndex) {using (SqlConnection conn = new SqlConnection (Conn. connString) {conn. open (); using (SqlCommand cmd = conn. createCommand () {cmd. commandText = "sp_PagingTabs"; cmd. commandType = CommandType. storedProcedu Re; cmd. Parameters. AddWithValue ("@ TableName", (TableName. Trim () = "User "? "[User]": TableName); cmd. parameters. addWithValue ("@ FieldName", FieldName); cmd. parameters. addWithValue ("@ where", wheres); cmd. parameters. addWithValue ("@ Order", order); cmd. parameters. addWithValue ("@ OrderField", OrderField); cmd. parameters. addWithValue ("@ PageIdORField", PageIdORField); cmd. parameters. addWithValue ("@ PageSize", PageSize); cmd. parameters. addWithValue ("@ PageIndex", PageIndex); SqlDataAdapter da = new SqlDataAdapter (); da. selectCommand = cmd; DataSet ds = new DataSet (); try {da. fill (ds); return ds;} catch {return null;} finally {ds. dispose (); conn. close ();}}}}
3. Call Method
public DataSet LinkServer(string TableName, string FieldName, string wheres, string order,string PageIdORField,string OrderField, ref int PageSize, ref int PageIndex) { return SQLHelperMe.LinkProce(TableName, FieldName, wheres, order, PageIdORField, OrderField,ref PageSize,ref PageIndex); }
4. Official Use
namespace Web.WebForm1{ public partial class WebForm1 : System.Web.UI.Page { private int PageIndex = 0; public DataTable list; public string DivPager = ""; protected void Page_Load(object sender, EventArgs e) { GetParams(); showData(); } private void GetParams() { if (!String.IsNullOrEmpty(Request["page"])) { PageIndex = Convert.ToInt32(Request["Page"]); } else { PageIndex = 1; } } private void showData() { list = new DataTable(); int PageSize = 10; Pager pager = new Pager(PageIndex); DivPager = pager.GetDivPager("", pager.LinkServer(" dbo.ze_user ", "*",""," desc "," Id "," Id ",ref PageSize,ref PageIndex), out list); } }}
<Strong> <span style = "font-size: 24px;"> 5. </span> <span style = "font-size: 18px; font-family: Arial, Helvetica, sans-serif;"> GetDivPager concatenation string </span> </strong>
<span style="font-family: Arial, Helvetica, sans-serif;"></span>
<Span style = "font-family: Arial, Helvetica, sans-serif;"> if you need to add parameters to the URL of queryString, for example, & charset = UTF-8 </span>
<Span style = "font-family: Arial, Helvetica, sans-serif;"> ds acquires a dataset from a stored procedure </span>
<Span style = "font-family: Arial, Helvetica, sans-serif;"> dt returns list data </span>
<Span style = "font-family: Arial, Helvetica, sans-serif;"> return value HTML tag </span>
<Span style = "font-family: Arial, Helvetica, sans-serif;"> </span> <span style = "font-family: Arial, Helvetica, sans-serif; "> </span> <pre name =" code "class =" csharp "> public string GetDivPager (string queryString, DataSet ds, out DataTable dt) {StringBuilder sp = new StringBuilder (); int PageSize = 10; if (ds! = Null & ds. tables. count> 0) {dt = ds. tables [0]; int TotalCount = Convert. toInt32 (ds. tables [1]. rows [0] [0]. toString (); int rowCount = (TotalCount % PageSize! = 0 )? TotalCount/PageSize + 1: TotalCount/PageSize; if (dt! = Null & dt. rows. count> 0) {sp. appendFormat ("<p class = \" fl \ "> total records: <span id = \" sum \ "> {0} </span>", TotalCount); sp. appendFormat ("page number: <em> <B id = \ "current \"> {0} </B>/<span id = \ "count \"> {1} </span> </ em> ", pageIndex, rowCount); sp. appendFormat ("per page: <span id = \" eachPage \ ">{0} </span> </p>", PageSize); sp. appendFormat ("<div class = \" pagination fr \ ">"); sp. appendFormat ("<a class = \" disabled \ "href = '{0}'> homepage </A> ","? Page = 1 "+ queryString); if (PageIndex> 1) {sp. AppendFormat (" <a href = '{0}'> <previous page </a> ","? Page = "+ (PageIndex-1) + queryString);} int temp = 0; int loopc = rowCount> 10? 10: rowCount; for (int I = 0; I <loopc; I ++) {temp = I + 1; if (PageIndex> 10) {temp = (PageIndex-10) + I + 1;} sp. appendFormat ("<a class = \" {0} \ "href = '{1}'> {2} </a>", PageIndex = temp? "Active ":"","? Page = "+ temp + queryString, temp);} if (PageIndex! = RowCount) {sp. AppendFormat ("<a href = '{0}'> next page> </a> ","? Page = "+ (PageIndex + 1) + queryString);} sp. AppendFormat (" <a href = '{0}'> last page </a> ","? Page = "+ rowCount + queryString); sp. AppendFormat (" </div> ") ;}} else {dt = null;} return sp. ToString ();}
6. Page display
<Span style = "white-space: pre "> </span> <table width =" 100% "> <tr> <th> NO. </th> <th> User Name </th> <th> password </ th> <th> creation time </th> </tr> <% if (list! = Null & list. rows. count> 0) {int abc = 1; foreach (System. data. dataRow item in list. rows) {%> <tr> <td> <input type = "checkbox" name = "checkbox2" value = "<% = item [" ID "] %>"/> </ td> <% = abc ++ %> </td> <% = item ["UserName"] %> </td> <% = item ["PASSWORD"] %> </td> <% = Convert. toDateTime (item ["CreateTime"]). toString ("yyyy-MM-dd hh: mm: ss") %> </td> </tr> <% }%> </table> <! -- Pagination --> <div runat = "server" id = "runPageDiv" class = "page"> </div>
OK! This is my success! Go and browse your page...