A SQL Server2000 and 2005 can be used for paging method

2010-04-07  来源:本站原创  分类:DotNet  人气:219 

(1) Stored Procedure (SQL2000 and 2005 are available under):

USE [Northwind]
GO
/******  Object  :  StoredProcedure [dbo].[p_GetOrders]     Script date  : 03/29/2010 23:00:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[p_GetOrders]
@strWhere   varchar(1500),   --  Query criteria   ( Note  :  Do not add   where)
@PageSize    int = 50,          --  Page size
@PageIndex   int = 1,           --  Page numbers
@PageCount   int out,           -- The total number of pages
@RecordCount int out            -- The total number of records
AS
BEGIN
 declare @strSQL   varchar(5000)       --  The main statement
 declare @strTmp   varchar(110)        --  Temporary variables
 declare @strOrder varchar(400)        --  The sort type
 select @RecordCount=count(*) from SaleInvoices
 select @PageCount=ceiling(@RecordCount/@PageSize)
 set @strTmp = '<(select min'
 set @strOrder = ' order by OrderID desc'
 if @PageIndex = 1
 begin
     if @strWhere != ''
     set @strSQL = 'select top ' + str(@PageSize) +' *  from Orders where ' + @strWhere + ' ' + @strOrder
     else
     set @strSQL = 'select top ' + str(@PageSize) +'  *   from Orders '+ @strOrder
     -- If this is the first page you want to execute the code above, this will speed up the execution speed
 end
 else
 begin
 -- The following code gives  @strSQL To really executed SQL code
 set @strSQL = 'select top ' + str(@PageSize) +'  *  from Orders
 where OrderID' + @strTmp + '(OrderID) from (select top ' + str((@PageIndex-1)*@PageSize) + ' OrderID from Orders' + @strOrder + ') as tblTmp)'+ @strOrder
 if @strWhere != ''
     set @strSQL = 'select top ' + str(@PageSize) +'  * from Orders
      where OrderID' + @strTmp + '(OrderID) from
    (select top ' + str((@PageIndex-1)*@PageSize) + ' OrderID from Orders where ' + @strWhere + ' '
         + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
 end
 --print (@strSQL)
 exec (@strSQL)
END

Use: Orders will be stored in the process of query data to their table to be the name, OrderID to his own identity field with the table (with unique values can be)
Stored Procedure Test:

declare @PageCount int
declare @RecordCount int
exec p_GetPaging '[no] like "%RB%"',10,3,@PageCount,@RecordCount

(2) front page:

......
        <asp:TextBox runat="server"></asp:TextBox>
        <asp:Button runat="server" Text=" Search  " />
        <asp:GridView runat="server">
        </asp:GridView>
        <asp:LinkButton runat="server" Text="1">1</asp:LinkButton>
        <asp:LinkButton  runat="server" Text="2">2</asp:LinkButton>
......

Note: The button text is set to "1" and "2"
(3) page background:

private int PageSize = 5;  // Global variables
        private int PageIndex = 1;  // Global variables
        // Gets the data method
        protected DataTable getPageDataSource(string sqlWhere, int pageIndex, int pageSize)
        {
            string connStr = ConfigurationManager.AppSettings["MsSql2nd"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            string sql = "p_GetPaging";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@strWhere",SqlDbType.VarChar);
            cmd.Parameters["@strWhere"].Value = sqlWhere;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
            cmd.Parameters["@PageIndex"].Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int);
            cmd.Parameters["@PageSize"].Value = pageSize;
            cmd.Parameters.Add("@RecordCount",SqlDbType.Int);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@PageCount", SqlDbType.Int);
            cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
            SqlDataReader sdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(sdr);
            sdr.Dispose();
            conn.Close();
            return dt;
        }
        // Gets the total number of pages
        protected int getPageCount(string sqlWhere, int pageIndex, int pageSize)
        {
            string connStr = ConfigurationManager.AppSettings["MsSql2nd"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            string sql = "p_GetPaging";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@strWhere",SqlDbType.VarChar);
            cmd.Parameters["@strWhere"].Value = sqlWhere;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
            cmd.Parameters["@PageIndex"].Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int);
            cmd.Parameters["@PageSize"].Value = pageSize;
            cmd.Parameters.Add("@RecordCount",SqlDbType.Int);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@PageCount", SqlDbType.Int);
            cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
            SqlDataReader sdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(sdr);
            sdr.Dispose();
            conn.Close();
            //return dt;
            int pageCount = (int)cmd.Parameters["@PageCount"].Value;
            return pageCount;
        }
        // Search button event
        protected void btnAllSearch_Click(object sender, EventArgs e)
        {
            string sestr = this.txtAllSearch.Text.Trim();
            string sqlWhere = "";
            if (sestr == "")
            {
                sqlWhere = "";
            }
            else
            {
                sqlWhere = "[no] like \"%" + sestr + "%\""; // Note the use of the escape character
            }
            DataTable dt = getPageDataSource(sqlWhere, PageIndex, PageSize);
            this.GvSAll.DataSource = dt; ;
            this.GvSAll.DataBind();
        }
        // Previous page event
        protected void lbtnPre_Click(object sender, EventArgs e)
        {
            string sestr = this.txtAllSearch.Text.Trim();
            string sqlWhere = "";
            if (sestr == "")
            {
                sqlWhere = "";
            }
            else
            {
                sqlWhere = "[no] like \"%" + sestr + "%\"";
            }
            PageIndex = Convert.ToInt16(this.lbtnPre.Text);
            PageIndex -= 1;
            if (PageIndex<=1)
            {
                PageIndex = 1;
            }
            DataTable dt = getPageDataSource(sqlWhere, PageIndex, PageSize);
            this.lbtnPre.Text = PageIndex.ToString();
            this.lbtnNext.Text = (PageIndex + 1).ToString();
            this.GvSAll.DataSource = dt; ;
            this.GvSAll.DataBind();
        }
        // Next event
        protected void lbtnNext_Click(object sender, EventArgs e)
        {
            string sestr = this.txtAllSearch.Text.Trim();
            string sqlWhere = "";
            if (sestr == "")
            {
                sqlWhere = "";
            }
            else
            {
                sqlWhere = "[no] like \"%" + sestr + "%\"";
            }
            PageIndex=Convert.ToInt16(this.lbtnNext.Text);
            PageIndex += 1;
            int PageCount = getPageCount("", 1, 5);
            if (PageIndex>PageCount+1)
            {
                PageIndex = PageCount+1;
            }
            DataTable dt = getPageDataSource(sqlWhere, PageIndex, PageSize);
            this.lbtnNext.Text = PageIndex.ToString();
            this.lbtnPre.Text = (PageIndex - 1).ToString();
            this.GvSAll.DataSource = dt; ;
            this.GvSAll.DataBind();
        }

Note: The use of two flip cases, two buttons to save the text value of their parameters, and phase effects

相关文章
  • A SQL Server2000 and 2005 can be used for paging method 2010-04-07

    (1) Stored Procedure (SQL2000 and 2005 are available under): USE [Northwind] GO /****** Object : StoredProcedure [dbo].[p_GetOrders] Script date : 03/29/2010 23:00:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[p_G

  • sql server2000 installation and jdbc connection 2010-03-28

    Freshman in contact with sql server2000, today, to install, actually a lot of problems, rummaging through the search only to get rid of, depressed. . . 1. Install sql server2000: Direct installation of Personal Edition, choose mixed authentication, s

  • Export data to SQL server2000 oracle10g Precautions 2010-04-30

    Using DTS to export data to SQL server2000 oracle10g the attention of: Implementation of the selected table object, you need to aim the source of the user name and double quotes are removed, while also click on the editor to create change in SQL, the

  • Install SQL Server2000 with SP4 patch hang solution 2010-05-12

    Today, the computer at home, a heavy equipment, install sql server2000 O'clock in the emergence of a mistake, said that the past Mouge program hangs, the installation program to restart your computer before, this issue surprising, has faced many time

  • mdf file into sql server2000 (Additional database) 2010-06-13

    First, import the mdf There are two ways: (need two files mdf and ldf) 1: In the Enterprise Manager menu, inside the operation - "All Tasks -" Additional database which, based on demand or you have completed. 2: execute the following statement i

  • sql server2000 import mdf file (attached database) 2010-06-13

    First, import the mdf There are two ways: (need two files mdf and ldf) 1: In the Enterprise Manager menu, inside the operation - "All Tasks -" Additional database which, based on demand or you have completed. 2: execute the following statement i

  • sql server2000 set the IP segment access 2010-08-16

    sql server2000 does not have this feature. But in an open 1433 port, you can set the access port of the IP segment. The following diagram:

  • [Reprinted] Soybean boys article: SQL Server 2000/2005 Page SQL - single SQL statement 2010-09-03

    Reprinted articles from soybean boys SQL Server 2000/2005 Page SQL - single SQL statement Method 1: Applies to SQL Server 2000/2005 SELECT TOP Page size * FROM table1 WHERE id NOT IN ( SELECT TOP Page size *( Number of pages -1) id FROM table1 ORDER

  • Some practical operation sql server2000 2010-10-25

    REVIEW: Continue to the database sql server2000 First, data backup and preservation PS: backup is relatively simple, as long as the designated backup location, and the suffix (bak); also need to pay attention to the principles of PS: 1, "forcing a re

  • SQL Server2000 installation 2010-10-27

    Open Enterprise Manager, SQL Server2000 after, "SQL Server Group" under the there is no "local (window NT)" Operating system may be a problem, it could be the process of installing SQL Server2000 a problem. Solution: In the Enterprise

  • SQL Server2000 uninstall steps 2010-11-28

    Uninstall SQL Server2000 step one, the purpose of SqlServer unloading must be thoroughly, otherwise the next install SqlServer will not be properly installed. The document is SqlServer2000 completely uninstall completely uninstall SqlServer steps. Se

  • Chapter VII of the Introduction and Application of SQL Server2000 2010-12-30

    Overview 7.1 SQL Server2000 7.1.1 SQL Server2000 version of the structure and 1.SQL Server structure 2.SQL Server2000 version Introduction 7.1.2 SQL Server2000 environment 1. The system directory purposes described 2. Automatically create the databas

  • Paging SQL Server 2000/2005 SQL - single SQL statement 2011-07-20

    Paging SQL Server 2000/2005 SQL - SQL statements on a single page of information a lot of SQL, and some use of stored procedures, and some use the cursor. I do not like to use a cursor, I think it cost and low efficiency; using stored procedures is a

  • SQL Server2000 installation problems 2010-10-27

    Open Enterprise Manager, SQL Server2000 after, "SQL Server Group" under the there is no "local (window NT)" Operating system may be a problem, it may be the process of installing SQL Server2000 a problem. Solution: In Enterprise Manage

  • Introduction and Chapter VII apply SQL Server2000 2010-12-30

    7.1 SQL Server2000 Overview 7.1.1 SQL Server2000 version of the structure and 1.SQL Server structure 2.SQL Server2000 version Introduction 7.1.2 SQL Server2000 environment 1 Introduction uses the system directory (2) automatically create database SQL

  • sql server2000 in use convert to get the datetime data type style (all) 2011-02-14

    sql server2000 in use convert to get the datetime data type style (all) Date data format processing, two examples: CONVERT (varchar (16), time-, 20) results: 2007--02--01 08:02 / * time is generally getdate () function or data table fields * / CONVER

  • Using oracle sql develop data as sql server2000 migrated to oracle10g 2011-09-02

    migrate tool: oracle sql develop 3.0.0.4 sources database: sql server2000 target database: oracle10g migrate data large: 70w migrate time: Migration support: 1, support the continued move by the query: There is no query that can support the continued

  • 文本.Excel.Access数据导入SQL Server2000的方法 2014-02-23

    昨天参加一个项目的维护,因为各种原因,数据在不同服务器上不一致,所以需要重新将数据库清空一次,并将整理后的数据重新导入.需导入的数据源为文本文件,记录以行存储,","逗号分割各字段,每个字段值用""双引号封闭. 如下所示: "AWU","102300","ZX240-3","2609997000","2609997000","3016924000"

  • 在程序中压缩sql server2000的数据库备份文件的代码 2014-02-25

    怎样压缩sql server2000的数据库备份文件,像rar一样?小弟有一7m的sql server2000 数据库备份文件,在程序中怎样压缩啊? procedure TForm1.Button2Click(Sender: TObject); var SHExecInfo: SHELLEXECUTEINFO; begin SHExecInfo.cbSize := sizeof(SHELLEXECUTEINFO); SHExecInfo.fMask := SEE_MASK_NOCLOSEPROC

  • 删除SQL Server2000数据库表中重复记录 2014-02-17

    最近由于要给旧系统的表中增加主键(SQL Server2000的表),由于旧表中存在重复记录所以导致增加不上,所以需要写一段SQL语句来删除所有的重复记录(就是必须保留重复记录中的一条,维持数据记录的唯一性),我知道园子里大虾多,所以在这里集思广益,看看大家都有什么好的办法: 方法一: 1 为了保证完整性,首先启动一个事务 2 声明一个表变量(在这里使用表变量主要是考虑重复的数据不是很多,同时为了获得更好的性能:当然如果重复的数据特别多,使用临时表是更 好的选择,因为表变量的数据都是存在内存中的