第四回 C#之SQL 实现分页查询

SQL:方法一创建存储过程实现分页查询

USE [Eric]
GO

/****** Object: StoredProcedure [dbo].[pageDemo] Script Date: 01/13/2016 13:55:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[pageDemo]
@pageSize int,--每页的笔数
@page int--(当前页的前一页*每页的笔数)
as
declare @temp int
set @temp=@pageSize*(@page - 1)

begin
SELECT TOP (select @pageSize) * FROM [Eric].[dbo].SYS_TB_SYSTEM_LIST WHERE
LISTID NOT IN(SELECT TOP (select @page) LISTID FROM [Eric].[dbo].SYS_TB_SYSTEM_LIST ORDER BY LISTID)
ORDER BY LISTID
end

GO

///////////////一下是C#创建代码

Form全部变量

DataSet dst = null;
int i = 0;

 

private void Search()//查询语句
{
ClassSQL SQLKAS = new ClassSQL();
string StrErr = default(string);
SQLKAS.SqlCnn = "Data Source=KSD443;Initial Catalog=;Integrated Security=True";
//string SqlStr = "select top (300)* from Sys_formes_Test.Sys_formes.SYS_TB_MAIL_LOG";
string SqlStr = "EXEC Eric.dbo.pageDemo 30," + (i * 30).ToString();

StrErr = SQLKAS.GetDataBase(out dst, SqlStr);
if ("" != StrErr)
{
MessageBox.Show(StrErr);
return;
}
else
{
ClassSQL.DisplayInfoToDataGridView(dst, dataGridView1);
}

}

private void button1_Click(object sender, EventArgs e)//下一页
{
i++;
Search();
}

private void button2_Click(object sender, EventArgs e)//上一页
{
i--;
Search();
}

private void Form1_Load(object sender, EventArgs e)//加载程序显示前30笔数据
{
Search();
}

/////////////C#就是直接写SQL语句(方法二)

string SqlStr=" SELECT TOP 30 * FROM [Eric].[dbo].SYS_TB_SYSTEM_LIST WHERE ";
SqlStr=SqlStr+"LISTID NOT IN(SELECT TOP " + (i*30).ToString() + " LISTID FROM [Eric].[dbo].SYS_TB_SYSTEM_LIST ORDER BY LISTID) ";
SqlStr = SqlStr + "ORDER BY LISTID";