The two paging stored procedures used in the previous section can be referenced by friends who need them.
The two paging stored procedures used in the previous section can be referenced by friends who need them.
First, get the total number of data rows
The Code is as follows:
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go
Alter procedure [sq8reyoung]. [fenye_num]
(
@ TableNames NVARCHAR (200 ),
@ Filter nvarchar (200 ))
AS
IF @ Filter =''
SET @ Filter = 'where 1 = 1'
ELSE
SET @ Filter = 'where' + @ Filter
EXECUTE ('select count (*) from '+ @ TableNames + ''+ @ Filter)
Second, retrieve paging data
The Code is as follows:
Set ANSI_NULLS ON
Set QUOTED_IDENTIFIER ON
Go
Alter procedure [sq8reyoung]. [fenye]
@ TableNames VARCHAR (200), -- table name, which can be multiple tables but cannot be aliases
@ PrimaryKey VARCHAR (100), -- primary key, which can be null, but this value cannot be blank when @ Order is null
@ Fields VARCHAR (200), -- the field to be retrieved. It can be a field of multiple tables. It can be null. If it is null, it indicates select *
@ PageSize INT, -- number of records per page
@ CurrentPage INT, -- current page, 0 indicates page 1st
@ Filter VARCHAR (200) = '', -- condition, which can be null. Do not enter where
@ Group VARCHAR (200) = '', -- group basis. It can be empty. You do not need to enter Group
@ Order VARCHAR (200) = ''-- sort. It can be null. If it is null, It is sorted by the primary key in ascending order by default. Order by is not required.
AS
BEGIN
DECLARE @ SortColumn VARCHAR (200)
DECLARE @ Operator CHAR (2)
DECLARE @ SortTable VARCHAR (200)
DECLARE @ SortName VARCHAR (200)
IF @ Fields =''
SET @ Fields = '*'
IF @ Filter =''
SET @ Filter = 'where 1 = 1'
ELSE
SET @ Filter = 'where' + @ Filter
IF @ Group <>''
SET @ Group = 'group by' + @ GROUP
IF @ Order <>''
BEGIN
DECLARE @ pos1 INT, @ pos2 INT
SET @ Order = REPLACE (@ Order, 'asc ', 'asc'), 'desc', 'desc ')
If charindex ('desc', @ Order)> 0
If charindex ('asc ', @ Order)> 0
BEGIN
If charindex ('desc', @ Order) <CHARINDEX ('asc ', @ Order)
SET @ Operator = '<='
ELSE
SET @ Operator = '> ='
END
ELSE
SET @ Operator = '<='
ELSE
SET @ Operator = '> ='
SET @ SortColumn = REPLACE (@ Order, 'asc ', ''), 'desc ',''),'','')
SET @ pos1 = CHARINDEX (',', @ SortColumn)
IF @ pos1> 0
SET @ SortColumn = SUBSTRING (@sortcolumn, 1, @ pos1-1)
SET @ pos2 = CHARINDEX ('.', @ SortColumn)
IF @ pos2> 0
BEGIN
SET @ SortTable = SUBSTRING (@sortcolumn, 1, @ pos2-1)
IF @ pos1> 0
SET @ SortName = SUBSTRING (@ SortColumn, @ pos2 + 1, @ pos1-@ pos2-1)
ELSE
SET @ SortName = SUBSTRING (@ SortColumn, @ pos2 + 1, LEN (@ SortColumn)-@ pos2)
END
ELSE
BEGIN
SET @ SortTable = @ TableNames
SET @ SortName = @ SortColumn
END
END
ELSE
BEGIN
SET @ SortColumn = @ PrimaryKey
SET @ SortTable = @ TableNames
SET @ SortName = @ SortColumn
SET @ Order = @ SortColumn
SET @ Operator = '> ='
END
DECLARE @ type varchar (50)
DECLARE @ prec int
SELECT @ type = t. name, @ prec = c. prec
FROM sysobjects o
JOIN syscolumns c on o. id = c. id
JOIN policypes t on c. xusertype = t. xusertype
WHERE o. name = @ SortTable AND c. name = @ SortName
If charindex ('Char ', @ type)> 0
SET @ type = @ type + '(' + CAST (@ prec AS varchar) + ')'
DECLARE @ TopRows INT
SET @ TopRows = @ PageSize * @ CurrentPage + 1
PRINT @ type
DECLARE @ SQL NVARCHAR (4000)
SET @ SQL = 'Clare @ SortColumnBegin '+ @ type +'
Set rowcount '+ Cast (@ TopRows as VARCHAR (10) + 'select @ SortColumnBegin =' +
@ SortColumn + 'from' + @ TableNames + ''+ @ Filter +'' + @ Group + 'ORDER BY' + @ ORDER +'
Set rowcount '+ CAST (@ PageSize as varchar (10) +'
SELECT '+ @ Fields + 'from' + @ TableNames + ''+ @ Filter + 'and' + @ SortColumn +'' + @ Operator +' @ SortColumnBegin '+ ISNULL (@ group, '') + 'ORDER BY' + @ ORDER +''
-- Print (@ SQL)
Exec (@ SQL)
END
And the data operation class that implements this method
The Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Data. SqlClient;
Using System. Data;
Using System. Configuration;
Using Wuqi. Webdiyer;
Using Models;
Namespace DAL
{
Public class DBHelper
{
Public static readonly string CONN_STRING = ConfigurationManager. ConnectionStrings ["RyMedicalConnectionString"]. ConnectionString;
Public static SqlDataReader GetReader (string safeSql)
{
SqlConnection conn = new SqlConnection (CONN_STRING );
SqlCommand cmd = new SqlCommand (safeSql, conn );
SqlDataReader reader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Reader. Close ();
Return reader;
}
Public static SqlDataReader GetReader (string SQL, params SqlParameter [] values)
{
SqlConnection conn = new SqlConnection (CONN_STRING );
SqlCommand cmd = new SqlCommand (SQL, conn );
Conn. Open ();
Cmd. Parameters. AddRange (values );
SqlDataReader reader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Reader. Close ();
Conn. Close ();
Return reader;
}
Public static DataTable GetDataSet (string safeSql)
{
SqlConnection conn = new SqlConnection (CONN_STRING );
DataSet ds = new DataSet ();
SqlCommand cmd = new SqlCommand (safeSql, conn );
Conn. Open ();
SqlDataAdapter da = new SqlDataAdapter (cmd );
Da. Fill (ds );
Conn. Close ();
Return ds. Tables [0];
}
Public static DataTable GetDataSet (CommandType parameter type, string parameter text, params SqlParameter [] parameter parms)
{
SqlConnection conn = new SqlConnection (CONN_STRING );
DataSet ds = new DataSet ();
SqlCommand cmd = new SqlCommand (plain text, conn );
Conn. Open ();
PrepareCommand (cmd, conn, null, partition type, plain text, plain parms );
SqlDataAdapter da = new SqlDataAdapter (cmd );
Da. Fill (ds );
Conn. Close ();
Return ds. Tables [0];
}
Public static SqlDataReader ExecuteReader (CommandType primitive type, string plain text, params SqlParameter [] partition parms)
{
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (CONN_STRING );
Conn. Open ();
PrepareCommand (cmd, conn, null, partition type, plain text, plain parms );
SqlDataReader rdr = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Cmd. Parameters. Clear ();
Rdr. Close ();
Conn. Close ();
Return rdr;
}
Public static object ExecuteScalar (CommandType parameter type, string parameter text, params SqlParameter [] parameter parms)
{
SqlCommand cmd = new SqlCommand ();
Using (SqlConnection conn = new SqlConnection (CONN_STRING ))
{
Conn. Open ();
PrepareCommand (cmd, conn, null, partition type, plain text, plain parms );
Object val = cmd. ExecuteScalar ();
Cmd. Parameters. Clear ();
Conn. Close ();
Return val;
}
}
Public static object ExecuteScalar (SqlConnection conn, CommandType limit type, string limit text, params SqlParameter [] limit parms)
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (cmd, conn, null, partition type, plain text, plain parms );
Object val = cmd. ExecuteScalar ();
Cmd. Parameters. Clear ();
Return val;
}
Private static void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType primitive type, string plain text, SqlParameter [] partial parms)
{
If (conn. State! = ConnectionState. Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd. CommandText = plain text;
If (trans! = Null)
Cmd. Transaction = trans;
Cmd. CommandType = primitive type;
If (partition parms! = Null)
{
Foreach (SqlParameter parm in milliseconds parms)
Cmd. Parameters. Add (parm );
}
}
Public static void ExecuteNonQuery (string SQL)
{
SqlConnection conn = new SqlConnection (CONN_STRING );
SqlCommand cmd = new SqlCommand (SQL, conn );
Conn. Open ();
Cmd. ExecuteNonQuery ();
Conn. Close ();
}
///
/// Input an SQL statement and return an int
///
///
///
Public static int ExcuteCommand (string SQL)
{
SqlConnection conn = new SqlConnection (CONN_STRING );
SqlCommand cmd = new SqlCommand (SQL, conn );
Conn. Open ();
Int result = cmd. ExecuteNonQuery ();
Conn. Close ();
Return result;
}
///
///
///
/// Pagination is required.
/// Primary Key name
/// The field to be retrieved. If it is left empty, it is *
/// Number of records per page
/// Current page
/// Condition, which can be null. Do not enter where
/// Group basis. It can be null. You do not need to enter group
/// Sorting. It can be null. If it is null, It is sorted by the primary key in ascending order by default. order by is not required.
///
Public static DataTable Pagedlist (string Name, string pk, string fields, int pagesize, int CurrentPage, string Filter, string Group, string order)
{
CurrentPage = CurrentPage-1;
DataTable dt = GetDataSet (CommandType. StoredProcedure, "fenye ",
New SqlParameter ("@ TableNames", Name ),
New SqlParameter ("@ PrimaryKey", pk ),
New SqlParameter ("@ Fields", fields ),
New SqlParameter ("@ PageSize", pagesize ),
New SqlParameter ("@ CurrentPage", CurrentPage ),
New SqlParameter ("@ Filter", Filter ),
New SqlParameter ("@ Group", Group ),
New SqlParameter ("@ Order", order)
);
Return dt;
}
Public static int fenye_num (string Name, string Filter)
{
Return (int) ExecuteScalar (CommandType. StoredProcedure, "fenye_num ",
New SqlParameter ("@ TableNames", Name ),
New SqlParameter ("@ Filter", Filter ));
}
///
///
///
/// Pagination is required.
/// Primary Key name
/// The field to be retrieved. If it is left empty, it is *
/// Number of records per page
/// Current page
/// Condition, which can be null. Do not enter where
/// Group basis. It can be null. You do not need to enter group
/// Sorting. It can be null. If it is null, It is sorted by the primary key in ascending order by default. order by is not required.
/// Pass the aspnetpager Control
///
Public static DataTable Paged (string Name, string pk, string fields, int pagesize, int CurrentPage, string Filter, string Group, string order, AspNetPager objanp)
{
CurrentPage = CurrentPage-1;
DataTable dt = GetDataSet (CommandType. StoredProcedure, "fenye ",
New SqlParameter ("@ TableNames", Name ),
New SqlParameter ("@ PrimaryKey", pk ),
New SqlParameter ("@ Fields", fields ),
New SqlParameter ("@ PageSize", pagesize ),
New SqlParameter ("@ CurrentPage", CurrentPage ),
New SqlParameter ("@ Filter", Filter ),
New SqlParameter ("@ Group", Group ),
New SqlParameter ("@ Order", order)
);
Objanp. RecordCount = fenye_num (Name, Filter );
Return dt;
}
}
}
Page call Method
The Code is as follows:
Using System;
Using System. Collections;
Using System. Configuration;
Using System. Data;
Using System. Linq;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. HtmlControls;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Xml. Linq;
Using Insus. NET;
Using DAL;
Using System. Data. SqlClient;
Public partial class news_newlist: System. Web. UI. Page
{
Int nid;
Int totalOrders;
Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
If (Request. QueryString ["typeid"]! = Null)
{
Nid = Convert. ToInt16 (Request. QueryString ["typeid"]. ToString ());
Binddata (1 );
}
Else
{
Response. Redirect ("~ /Default. aspx ");
}
}
}
Private void binddata (int page)
{
DataTable dt = DBHelper. paged ("M_NewInfoAll", "New_Id", "", AspNetPager1.PageSize, page, "New_TypeId =" + nid. toString () + "", "", "New_PubDate desc", AspNetPager1 );
This. Repeater1.DataSource = dt;
This. Repeater1.DataBind ();
DataRow dr = dt. Rows [0];
This. Label1.Text = dr ["New_TypeName"]. ToString ();
// This. Literal1.Text = dr ["new_typeName"]. ToString ();
Page. Title = Label1.Text. Trim () + "-New Rural Cooperative Medical Network ";
}
Protected void AspNetPager1_PageChanged (object sender, EventArgs e)
{
If (Request. QueryString ["page"]! = Null)
{
Binddata (Convert. ToInt32 (Request. QueryString ["page"]. ToString ()));
}
}
}
In this way, you can implement () by page. In any project, you only need to copy two stored procedures and one data operation class. Alternatively, you can make the data class DLL, only one line of code is required to input parameters during page calling.
