存储过程 + AspNetPager 实现分页

最新推荐文章于 2021-02-05 03:50:50 发布

weixin_30265171

最新推荐文章于 2021-02-05 03:50:50 发布

阅读量65

点赞数

原文链接：http://www.cnblogs.com/sqlmlm/archive/2009/11/23/1608772.html

版权

/存储过程

/****** 对象: StoredProcedure [dbo].[sp_AllQuery] 脚本日期: 10/26/2009 11:27:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- [sp_AllQuery] '','','','','','',1,10,0

ALTER procedure [dbo].[sp_AllQuery]
(
@tableName nvarchar(500),
@keyField nvarchar(100),
@keyField_NO nvarchar(100),
@queryField nvarchar(1000),
@orderField nvarchar(200),
@strWhere nvarchar(500),
@startIndex int,
@endIndex int,
@docount bit
)
as
set nocount on
if(@docount=1)
begin
exec('select count(' + @keyField + ') from ' + @tableName + ' where '+ @strWhere +'')
end
else
begin
--declare @indextable table(id int identity(1,1),nid int)
create table #indextable (id int identity(1,1),nid int)
set rowcount @endIndex
exec('insert into #indextable(nid) select ' + @keyField + ' from ' + @tableName + ' where '+ @strWhere +' order by ' + @orderField +' ')

exec('select t.id as noiid,a.* from (select top 10000000 '+@queryField+' from '+@tableName+' where '+@strWhere+') a,#indextable t where a.' + @keyField_NO +'=t.nid and t.id between ' + @startIndex +' and ' + @endIndex + ' order by t.id ')

drop table #indextable
end

///实现代码///

ArrayList arr = new ArrayList();
        if (tableName == "")
        {
            tableName = " "; //表名或多表连接 table1 a left join table2 b on a.id=b.id
        }

        if (keyField == "")
        {
            keyField = " "; //a 表的主键 a.id
        }
        if (keyField_NO == "")
        {
            keyField_NO = " ";//a 表的主键 ,但写的时候去掉[a.]  ,直接写  id
        }
        if (queryField == "")
        {
            queryField = " "; //查询的字段 a.id,b.id,a......
        }
        if (orderField == "")
        {
            orderField = " "; //排序字段 a.addtime desc
        }

        if (strWhere == "")
        {
            strWhere = " "; //判断条件 1=1 and a.isok=1
        }
        arr.Insert(0, tableName);
        arr.Insert(1, keyField);
        arr.Insert(2, keyField_NO);
        arr.Insert(3, queryField);
        arr.Insert(4, orderField);
        arr.Insert(5, strWhere);

ViewState["QueryStr"] = arr;

        if (ShowPageCount)
        {
            AspNetPager1.CurrentPageIndex = 1;
        }

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DataBaseString"].ConnectionString))
        {
            SqlCommand myCmd = new SqlCommand("sp_AllQuery", connection);
            myCmd.CommandType = CommandType.StoredProcedure;

            SqlParameter spParamenter;
            spParamenter = myCmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 500);
            myCmd.Parameters["@tableName"].Value = tableName;

spParamenter = myCmd.Parameters.Add("@keyField", SqlDbType.NVarChar, 100);
myCmd.Parameters["@keyField"].Value = keyField;

spParamenter = myCmd.Parameters.Add("@keyField_NO", SqlDbType.NVarChar, 100);
myCmd.Parameters["@keyField_NO"].Value = keyField_NO;

spParamenter = myCmd.Parameters.Add("@queryField", SqlDbType.NVarChar, 1000);
myCmd.Parameters["@queryField"].Value = queryField;

spParamenter = myCmd.Parameters.Add("@orderField", SqlDbType.NVarChar, 200);
myCmd.Parameters["@orderField"].Value = orderField;

spParamenter = myCmd.Parameters.Add("@strWhere", SqlDbType.NVarChar, 500);
myCmd.Parameters["@strWhere"].Value = strWhere;

            spParamenter = myCmd.Parameters.Add("@startIndex", SqlDbType.Int);
            myCmd.Parameters["@startIndex"].Value = Convert.ToString((this.AspNetPager1.CurrentPageIndex - 1) * Convert.ToInt32(Cls_System.getWebListCount()) + 1);
            spParamenter = myCmd.Parameters.Add("@endIndex", SqlDbType.Int);
            myCmd.Parameters["@endIndex"].Value = Convert.ToString(this.AspNetPager1.CurrentPageIndex * Convert.ToInt32(Cls_System.getWebListCount()));
            spParamenter = myCmd.Parameters.Add("@docount", SqlDbType.Int);
            myCmd.Parameters["@docount"].Value = 1;

            try
            {
                connection.Open();

                if (ShowPageCount)
                {
                    int totalOrders = (int)myCmd.ExecuteScalar(); //得到总记录数
                    AspNetPager1.RecordCount = totalOrders;
                    AspNetPager1.CurrentPageIndex = 1;
                    AspNetPager1.PageSize = Convert.ToInt32(Cls_System.getWebListCount());//每页数量
                }
                myCmd.Parameters["@docount"].Value = 0;

                SqlDataReader dr = myCmd.ExecuteReader();
                this.rep_JobList.DataSource = dr;
                this.rep_JobList.DataBind();
                dr.Close();
            }
            catch
            {
                Page.ClientScript.RegisterStartupScript(this.Page.GetType(), "error1", "<script>alert('网络异常，请稍候刷新重试1！');</script>");
                return;
            }
            finally
            {
                connection.Close();
            }
        }

附件为 aspnetpager.dll

附件下载：
　　

aspnetpager.dll