ASP. NET paging stored procedures and calls, asp.net stored procedures

Source: Internet
Author: User

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...



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.