Asp.net paging Stored Procedure

 

Queryadrecordforadmin queryadrecord = new queryadrecordforadmin
{
Adname = txttitle. value,
Pageindex = aspnetpager1.currentpageindex,
Pagesize = aspnetpager1.pagesize,
Promotiontype = valueconverter. parse <int> (droppromotiontype. selectedvalue ),
Approvedstate = valueconverter. parse <int> (dropapprovedstate. selectedvalue ),
Username = txtusername. value,
Imagewidth = valueconverter. parse <int> (splict [0]),
Imageheight = valueconverter. parse <int> (splict [1])

};
Datetime startdate;
If (datetime. tryparse (txtcreatedate. value. Trim (), Out startdate ))
Queryadrecord. createdate = startdate;
Datetime enddate;
If (datetime. tryparse (txtdateend. value. Trim (), Out enddate ))
Queryadrecord. publishenderson date = enddate. adddays (1 );

Ilist <adrecordinfo> List = adrecorbll. getadrecordmananger (queryadrecord );
Aspnetpager1.recordcount = queryadrecord. recordcount;
Repeater1.datasource = List;
Repeater1.databind ();

1 public static dataset getpagerlist (INT pagesize, int pageindex, string tablename, string fieldname, string where, string order, bool iscount, out int totalrecord)
{
Totalrecord = 0;
Sqlparameter totalpageparm = new sqlparameter ("@ totalpage", sqldbtype. INT );
Totalpageparm. Direction = parameterdirection. output;
Sqlparameter totalrecordparm = new sqlparameter ("@ totalrecord", sqldbtype. INT );
Totalrecordparm. Direction = parameterdirection. output;

Sqlparameter [] parameters = {
New sqlparameter ("@ tablename", sqldbtype. varchar, 50 ),
New sqlparameter ("@ fields", sqldbtype. varchar, 5000 ),
New sqlparameter ("@ orderfield", sqldbtype. varchar, 5000 ),
New sqlparameter ("@ sqlwhere", sqldbtype. varchar, 8000 ),
New sqlparameter ("@ pagesize", sqldbtype. INT ),
New sqlparameter ("@ pageindex", sqldbtype. INT ),
New sqlparameter ("@ iscount", sqldbtype. Bit ),
Totalpageparm, totalrecordparm
};
Parameters [0]. value = tablename;
Parameters [1]. value = fieldname;
Parameters [2]. value = order;
Parameters [3]. value = where;
Parameters [4]. value = pagesize;
Parameters [5]. value = pageindex;
Parameters [6]. value = iscount;
Dataset DS = dbhelpersql. runprocedure ("pager2005", parameters, "pagertable ");
If (iscount)
{
Int. tryparse (totalrecordparm. value. tostring (), Out totalrecord );
}
Return Ds;
}

2

Public static datatable getpagerlist (INT startindex, int endindex, string tablename, string where, string order)
{< br> string SQL = "select row_number () over (order by "+ order +") row_id, * from "+ tablename +" where "+ where;
If (startindex> 0 & endindex> 0)
SQL = "select * from (" + SQL + ") TB where row_id between" + startindex. tostring () + "and" + endindex. tostring ();
return dbhelpersql. query (SQL ). tables [0];
}

<Webdiyer: aspnetpager id = "aspnetpager1" runat = "server" onpagechanged = "aspnetpager1_pagechanged"
Custominfohtml = "Page % currentpageindex %/% pagecount %, % pagesize % per page, % recordcount %"
Firstpagetext = "Homepage" lastpagetext = "last page" nextpagetext = "next page" alwaysshow = "true" cssclass = "paipai"
Currentpagebuttonclass = "CPB" pageindexboxtype = "textbox" prevpagetext = "Previous Page" showcustominfosection = "left"
Showpageindexbox = "always" submitbuttontext = "go" textafterpageindexbox = "page" pagesize = "4"
Textbeforepageindexbox = "go to" Height = "25px" numericbuttoncount = "5" width = "98%"
Centercurrentpagebutton = "true" custominfotextalign = "Left" horizontalalign = "right">
</Webdiyer: aspnetpager>

Int totalrecord = 0;

Strwere = "userid =" + model. userid + "and ispass =" + state. tostring ();

If (! String. isnullorempty (txtkeyword. Text. Trim ()))
Strwere + = "and subject like '%" + text. cleansqlstring (txtkeyword. Text. Trim () + "% '";

If (! String. isnullorempty (txtstartdate. value. Trim ())&&! String. isnullorempty (txtenddate. value. Trim ()))
Strwere + = "and datecreated between '" + text. cleansqlstring (txtstartdate. value. trim () + "'and'" + datetime. parse (text. cleansqlstring (txtenddate. value. trim ())). adddays (1 ). tostring () + "'";

Dataset DS = commonbll. getpagerlist (aspnetpager1.pagesize, aspnetpager1.currentpageindex, "view_users ",
"*",
Strwere, ddlorderby. selectedvalue, true, out totalrecord );
Aspnetpager1.recordcount = totalrecord;
Repeater1.datasource = Ds;
Repeater1.databind ();