目前用到的两个分页存储过程代码_mssql数据库教程-查字典教程网
目前用到的两个分页存储过程代码
目前用到的两个分页存储过程代码
发布时间:2016-12-29 来源:查字典编辑
摘要:第一个,取得数据总行数复制代码代码如下:setANSI_NULLSONsetQUOTED_IDENTIFIERONgoALTERPROCED...

第一个,取得数据总行数

复制代码 代码如下:

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)

第二个取得分页数据

复制代码 代码如下:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [sq8reyoung].[fenye]

@TableNames VARCHAR(200), --表名,可以是多个表,但不能用别名

@PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空

@Fields VARCHAR(200), --要取出的字段,可以是多个表的字段,可以为空,为空表示select *

@PageSize INT, --每页记录数

@CurrentPage INT, --当前页,0表示第1页

@Filter VARCHAR(200) = '', --条件,可以为空,不用填 where

@Group VARCHAR(200) = '', --分组依据,可以为空,不用填 group by

@Order VARCHAR(200) = '' --排序,可以为空,为空默认按主键升序排列,不用填 order by

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(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(REPLACE(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 systypes 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 = 'DECLARE @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

以及实现此方法的数据操作类

复制代码 代码如下:

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 cmdType, string cmdText, params SqlParameter[] cmdParms)

{

SqlConnection conn = new SqlConnection(CONN_STRING);

DataSet ds = new DataSet();

SqlCommand cmd = new SqlCommand(cmdText, conn);

conn.Open();

PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(ds);

conn.Close();

return ds.Tables[0];

}

public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)

{

SqlCommand cmd = new SqlCommand();

SqlConnection conn = new SqlConnection(CONN_STRING);

conn.Open();

PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();

rdr.Close();

conn.Close();

return rdr;

}

public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)

{

SqlCommand cmd = new SqlCommand();

using (SqlConnection conn = new SqlConnection(CONN_STRING))

{

conn.Open();

PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

conn.Close();

return val;

}

}

public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)

{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

if (trans != null)

cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)

{

foreach (SqlParameter parm in cmdParms)

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();

}

/// <summary>

/// 传入SQL语句,返回int

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

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;

}

/// <summary>

///

/// </summary>

/// <param name="Name">需要分页的表明</param>

/// <param name="pk">主键名</param>

/// <param name="fields">需要取出的字段,留空则为*</param>

/// <param name="pagesize">每页的记录数</param>

/// <param name="CurrentPage">当前页</param>

/// <param name="Filter">条件,可以为空,不用填 where</param>

/// <param name="Group">分组依据,可以为空,不用填 group by</param>

/// <param name="order">排序,可以为空,为空默认按主键升序排列,不用填 order by</param>

/// <returns></returns>

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));

}

/// <summary>

///

/// </summary>

/// <param name="Name">需要分页的表明</param>

/// <param name="pk">主键名</param>

/// <param name="fields">需要取出的字段,留空则为*</param>

/// <param name="pagesize">每页的记录数</param>

/// <param name="CurrentPage">当前页</param>

/// <param name="Filter">条件,可以为空,不用填 where</param>

/// <param name="Group">分组依据,可以为空,不用填 group by</param>

/// <param name="order">排序,可以为空,为空默认按主键升序排列,不用填 order by</param>

/// <param name="objanp">传递aspnetpager控件</param>

/// <returns></returns>

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;

}

}

}

以及页面调用方式

复制代码 代码如下:

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() + " - 新农合医药网";

}

protected void AspNetPager1_PageChanged(object sender, EventArgs e)

{

if (Request.QueryString["page"] != null)

{

binddata(Convert.ToInt32(Request.QueryString["page"].ToString()));

}

}

}

如此分页即可实现(下图),在任何项目中只需要COPY2个存储过程一个数据操作类,或者喜欢将数据类做成DLL也可以,在页面调用时传入参数只需一行代码即可.

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新mssql数据库学习
    热门mssql数据库学习
    编程开发子分类