几种办法:
一、采用嵌套的sql返回特定页的数据,类似这样:
select top 5 * from tb where id not in (select top 10 id from tb order by id) order by id
二、采用缓存主键的方法,就是每次取n条,记下n条中最大的id为OldId,下一页的时候,取id〉OldId的前n条,此法性能最好,但是不能自由挑转.
三、google式返回法,实际上是二的变形。
四、结合一、二、三的办法。
void SetGridItemCount()
{
// create the command and the connection
string connString = "server=(local);database=Northwind;uid=sa;pwd=;";
string sql = "SELECT COUNT(*) FROM Employees";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
// execute the command and use the return value for the grid's VirtualItemCount prop
Datagrid1.VirtualItemCount = (int)cmd.ExecuteScalar();
conn.Close();
}
void BindGrid()
{
// create the command and the connection
string connString = "server=(local);database=Northwind;uid=sa;pwd=;";
SqlConnection conn = new SqlConnection(connString);
// open the connection and get the Reader
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
// bind the reader to the DataList
Datagrid1.DataSource = reader;
Datagrid1.DataBind();
// close the reader and the connection
reader.Close();
conn.Close();
}
protected void Datagrid1_SelectionChanged(object sender, EventArgs e)
{
// create the command and the connection
string connString = "server=(local);database=Northwind;uid=sa;pwd=;";
int empID = (int)Datagrid1.DataKeys[Datagrid1.SelectedIndex];
string sql = "SELECT * FROM Employees WHERE EmployeeID = " + empID.ToString();
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
// open the connection and get the Reader
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
// show the results
StringBuilder str = new StringBuilder("");
reader.Read();
str.Append("<b>");
str.Append(reader["FirstName"].ToString());
str.Append(" ");
str.Append(reader["LastName"].ToString());
str.Append("<br/>");
str.Append(reader["Title"].ToString());
str.Append("<br/>");
str.Append(reader["Address"].ToString());
str.Append("<br/>");
str.Append(reader["City"].ToString());
str.Append(", ");
str.Append(reader["Region"].ToString());
str.Append("</b><br/>");
str.Append(reader["Notes"].ToString());
MoreInfo.Text = str.ToString();
// close the reader and the connection
reader.Close();
conn.Close();
// rebind the grid
BindGrid();
}
protected void Datagrid1_PageChanged(Object sender, DataGridPageChangedEventArgs e)
{
// deselect the currently selected row, if any
Datagrid1.SelectedIndex = -1;
MoreInfo.Text = "";
// change the current page and rebind
Datagrid1.CurrentPageIndex = e.NewPageIndex;
BindGrid();
}
</script>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetEmployeesByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetEmployeesByPage]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_GetEmployeesByPage
@PageNumber int,
@PageSize int
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #TempEmployees
(
ID int IDENTITY PRIMARY KEY,
EmployeeID int,
LastName nvarchar(20),
FirstName nvarchar(10),
Title nvarchar(30),
TitleOfCourtesy nvarchar(25),
Address nvarchar(60),
City nvarchar(15),
Region nvarchar(15),
Country nvarchar(15),
Notes ntext
)
-- fill the temp table with all the employees
INSERT INTO #TempEmployees
(
EmployeeID,
LastName,
FirstName,
Title,
TitleOfCourtesy,
Address,
City,
Region,
Country,
Notes
)
SELECT
EmployeeID,
LastName,
FirstName,
Title,
TitleOfCourtesy,
Address,
City,
Region,
Country,
Notes
FROM
Employees ORDER BY EmployeeID ASC
-- declare two variables to calculate the range of records to extract for the specified page
DECLARE @FromID int
DECLARE @ToID int
-- calculate the first and last ID of the range of records we need
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize
-- select the page of records
SELECT * FROM #TempEmployees WHERE ID >= @FromID AND ID <= @ToID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO