分頁存儲過程; CREATE PROCEDURE [dbo].[sp_SystemQuotationRevisionPaging] ( @ClientID NVARCHAR(255), @ClientModel NVARCHAR(255), @Collection NVARCHAR(255), @startIndexpage int, @endIndexpage int, @Count INT OUTPUT ) As SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CreatedDate DESC) AS row,* FROM QuotationRevision WHERE UPPER(ClientID) LIKE @ClientID +'%' AND UPPER(ClientModel) LIKE @ClientModel +'%' AND (UPPER([Collection]) LIKE '%'+@Collection +'%' OR (@Collection = '' AND [Collection] is null)) ) QuotationRevision WHERE row BETWEEN (@endIndexpage-1)*@startIndexpage+1 AND @endIndexpage*@startIndexpage
SET @Count=(SELECT COUNT(*) FROM QuotationRevision WHERE UPPER(ClientID) LIKE @ClientID+'%' AND UPPER(ClientModel) LIKE @ClientModel+'%' AND (UPPER([Collection]) LIKE '%'+@Collection +'%' OR (@Collection = '' AND [Collection] is null)) ) RETURN GO GridView 數據綁定; Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click GenerateData() End Sub Private Sub GenerateData() Dim CurrentPage As Integer If Request.QueryString("CurrentPage") Is Nothing Then CurrentPage = 1 Else CurrentPage = CInt(Request.QueryString("CurrentPage")) End If Dim strENGCode As String = txtClientID.Text.Trim Dim strERPCode As String = txtClientModel.Text.Trim Dim strDescription As String = txtCollection.Text.Trim Dim pENGCode As New SqlClient.SqlParameter("@ClientID", SqlDbType.NVarChar) Dim pERPCode As New SqlClient.SqlParameter("@ClientModel", SqlDbType.NVarChar) Dim pDescription As New SqlClient.SqlParameter("@Collection", SqlDbType.NVarChar) Dim pPageSize As New SqlClient.SqlParameter("@startIndexpage", SqlDbType.Int) Dim pCurrentPage As New SqlClient.SqlParameter("@endIndexpage", SqlDbType.Int) Dim pCount As New SqlClient.SqlParameter("@Count", SqlDbType.Int) pENGCode.Value = strENGCode.ToUpper() pERPCode.Value = strERPCode.ToUpper() pDescription.Value = strDescription.ToUpper() pPageSize.Value = 10 pCurrentPage.Value = CurrentPage pCount.Direction = ParameterDirection.Output Dim da As New WMO.AppObjects.DataAccess Dim conn As SqlClient.SqlConnection = da.Conn Dim cmd As New SqlClient.SqlCommand("sp_SystemQuotationRevisionPaging", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(pENGCode) cmd.Parameters.Add(pERPCode) cmd.Parameters.Add(pDescription) cmd.Parameters.Add(pPageSize) cmd.Parameters.Add(pCurrentPage) cmd.Parameters.Add(pCount) conn.Open() Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader() GV_QuotationID.DataSource = reader GV_QuotationID.DataBind() reader.Close() conn.Close() Dim Count As Integer = CInt(cmd.Parameters("@Count").Value) GeneratePaging(CurrentPage, 10, 5, Count) End Sub Private Sub GeneratePaging(ByVal CurrentPage As Integer, ByVal PageSize As Integer, ByVal MaxPage As Integer, ByVal Count As Integer) Dim queryString As String If Request.QueryString.Count > 0 Then Dim indexCurentPage As Integer = Request.Url.AbsoluteUri.IndexOf("CurrentPage") If indexCurentPage <> -1 Then queryString = Request.Url.AbsoluteUri.Substring(0, indexCurentPage) Else queryString = Request.Url.AbsoluteUri + "&" End If Else queryString = Request.Url.AbsoluteUri + "?" End If Dim PageCount As Integer If Count Mod PageSize > 0 Then PageCount = Math.Floor(Count / PageSize) + 1 Else PageCount = Math.Floor(Count / PageSize) End If If PageCount > 1 Then Dim scopeNumber = Math.Floor((CurrentPage - 1) / MaxPage) Dim startNumber As Integer = scopeNumber * MaxPage + 1 Dim endNumber As Integer If ((scopeNumber + 1) * MaxPage) < PageCount Then endNumber = (scopeNumber + 1) * MaxPage Else endNumber = PageCount End If Dim table As New Table() Dim tr As New TableRow 'pre page If CurrentPage > 1 Then Dim td As New TableCell Dim href As New HyperLink href.Text = "上一頁" href.NavigateUrl = queryString + "CurrentPage=" + (CurrentPage - 1).ToString() td.Controls.Add(href) tr.Controls.Add(td) End If 'pre more If scopeNumber > 0 Then Dim td As New TableCell Dim href As New HyperLink href.Text = "..." href.NavigateUrl = queryString + "CurrentPage=" + ((scopeNumber - 1) * MaxPage + 1).ToString() td.Controls.Add(href) tr.Controls.Add(td) End If 'middle For i As Integer = startNumber To endNumber Dim td As New TableCell Dim href As New HyperLink href.Text = i If i <> CurrentPage Then href.NavigateUrl = queryString + "CurrentPage=" + i.ToString() Else href.CssClass = "currentPage" End If td.Controls.Add(href) tr.Controls.Add(td) Next 'last more If endNumber < PageCount Then Dim td As New TableCell Dim href As New HyperLink href.Text = "..." href.NavigateUrl = queryString + "CurrentPage=" + (endNumber + 1).ToString() td.Controls.Add(href) tr.Controls.Add(td) End If 'last Number If endNumber <> PageCount Then Dim td As New TableCell Dim href As New HyperLink href.Text = PageCount.ToString() href.NavigateUrl = queryString + "CurrentPage=" + PageCount.ToString() td.Controls.Add(href) tr.Controls.Add(td) End If 'last page If CurrentPage < PageCount Then Dim td As New TableCell Dim href As New HyperLink href.Text = "下一頁" href.NavigateUrl = queryString + "CurrentPage=" + (CurrentPage + 1).ToString() td.Controls.Add(href) tr.Controls.Add(td) End If 'go Dim html = "<a id='lnkPage' style='display:none;'></a>到第<input id='txtPageNo' size='1' onblur='SetPage()' />頁<input type='button' value='確定' onclick='GoPage()' />" Dim tdGO As New TableCell tdGO.Text = html tr.Controls.Add(tdGO) table.Controls.Add(tr) divPaging.Controls.Add(table) End If End Sub
http://dotnet.aspx.cc/Exam/GridViewMultiSelect.aspx代码
http://dotnet.aspx.cc/file/Cross-Pages-Checked-GridView.aspx
GridView1.PageIndex=e.SelectedPageIndex;
//重新绑定GridView1的数据源(比如DataTable)
CREATE PROCEDURE [dbo].[sp_SystemQuotationRevisionPaging]
(
@ClientID NVARCHAR(255),
@ClientModel NVARCHAR(255),
@Collection NVARCHAR(255),
@startIndexpage int,
@endIndexpage int,
@Count INT OUTPUT
)
As
SELECT
*
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY CreatedDate DESC) AS row,* FROM QuotationRevision
WHERE
UPPER(ClientID) LIKE @ClientID +'%'
AND UPPER(ClientModel) LIKE @ClientModel +'%'
AND (UPPER([Collection]) LIKE '%'+@Collection +'%'
OR
(@Collection = '' AND [Collection] is null))
) QuotationRevision
WHERE row BETWEEN (@endIndexpage-1)*@startIndexpage+1 AND @endIndexpage*@startIndexpage
SET @Count=(SELECT COUNT(*) FROM QuotationRevision
WHERE
UPPER(ClientID) LIKE @ClientID+'%'
AND UPPER(ClientModel) LIKE @ClientModel+'%'
AND (UPPER([Collection]) LIKE '%'+@Collection +'%'
OR
(@Collection = '' AND [Collection] is null))
)
RETURN
GO
GridView 數據綁定;
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
GenerateData()
End Sub Private Sub GenerateData()
Dim CurrentPage As Integer
If Request.QueryString("CurrentPage") Is Nothing Then
CurrentPage = 1
Else
CurrentPage = CInt(Request.QueryString("CurrentPage"))
End If Dim strENGCode As String = txtClientID.Text.Trim
Dim strERPCode As String = txtClientModel.Text.Trim
Dim strDescription As String = txtCollection.Text.Trim Dim pENGCode As New SqlClient.SqlParameter("@ClientID", SqlDbType.NVarChar)
Dim pERPCode As New SqlClient.SqlParameter("@ClientModel", SqlDbType.NVarChar)
Dim pDescription As New SqlClient.SqlParameter("@Collection", SqlDbType.NVarChar)
Dim pPageSize As New SqlClient.SqlParameter("@startIndexpage", SqlDbType.Int)
Dim pCurrentPage As New SqlClient.SqlParameter("@endIndexpage", SqlDbType.Int)
Dim pCount As New SqlClient.SqlParameter("@Count", SqlDbType.Int) pENGCode.Value = strENGCode.ToUpper()
pERPCode.Value = strERPCode.ToUpper()
pDescription.Value = strDescription.ToUpper()
pPageSize.Value = 10
pCurrentPage.Value = CurrentPage
pCount.Direction = ParameterDirection.Output Dim da As New WMO.AppObjects.DataAccess
Dim conn As SqlClient.SqlConnection = da.Conn
Dim cmd As New SqlClient.SqlCommand("sp_SystemQuotationRevisionPaging", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(pENGCode)
cmd.Parameters.Add(pERPCode)
cmd.Parameters.Add(pDescription)
cmd.Parameters.Add(pPageSize)
cmd.Parameters.Add(pCurrentPage)
cmd.Parameters.Add(pCount) conn.Open()
Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader() GV_QuotationID.DataSource = reader
GV_QuotationID.DataBind() reader.Close()
conn.Close() Dim Count As Integer = CInt(cmd.Parameters("@Count").Value)
GeneratePaging(CurrentPage, 10, 5, Count)
End Sub Private Sub GeneratePaging(ByVal CurrentPage As Integer, ByVal PageSize As Integer, ByVal MaxPage As Integer, ByVal Count As Integer)
Dim queryString As String
If Request.QueryString.Count > 0 Then
Dim indexCurentPage As Integer = Request.Url.AbsoluteUri.IndexOf("CurrentPage")
If indexCurentPage <> -1 Then
queryString = Request.Url.AbsoluteUri.Substring(0, indexCurentPage)
Else
queryString = Request.Url.AbsoluteUri + "&"
End If Else
queryString = Request.Url.AbsoluteUri + "?"
End If Dim PageCount As Integer
If Count Mod PageSize > 0 Then
PageCount = Math.Floor(Count / PageSize) + 1
Else
PageCount = Math.Floor(Count / PageSize)
End If If PageCount > 1 Then
Dim scopeNumber = Math.Floor((CurrentPage - 1) / MaxPage)
Dim startNumber As Integer = scopeNumber * MaxPage + 1
Dim endNumber As Integer
If ((scopeNumber + 1) * MaxPage) < PageCount Then
endNumber = (scopeNumber + 1) * MaxPage
Else
endNumber = PageCount
End If Dim table As New Table()
Dim tr As New TableRow 'pre page
If CurrentPage > 1 Then
Dim td As New TableCell
Dim href As New HyperLink href.Text = "上一頁"
href.NavigateUrl = queryString + "CurrentPage=" + (CurrentPage - 1).ToString() td.Controls.Add(href)
tr.Controls.Add(td)
End If 'pre more
If scopeNumber > 0 Then
Dim td As New TableCell
Dim href As New HyperLink href.Text = "..."
href.NavigateUrl = queryString + "CurrentPage=" + ((scopeNumber - 1) * MaxPage + 1).ToString()
td.Controls.Add(href)
tr.Controls.Add(td)
End If 'middle
For i As Integer = startNumber To endNumber
Dim td As New TableCell
Dim href As New HyperLink href.Text = i
If i <> CurrentPage Then
href.NavigateUrl = queryString + "CurrentPage=" + i.ToString()
Else
href.CssClass = "currentPage"
End If td.Controls.Add(href)
tr.Controls.Add(td)
Next 'last more
If endNumber < PageCount Then Dim td As New TableCell
Dim href As New HyperLink href.Text = "..."
href.NavigateUrl = queryString + "CurrentPage=" + (endNumber + 1).ToString() td.Controls.Add(href)
tr.Controls.Add(td)
End If 'last Number
If endNumber <> PageCount Then
Dim td As New TableCell
Dim href As New HyperLink href.Text = PageCount.ToString()
href.NavigateUrl = queryString + "CurrentPage=" + PageCount.ToString() td.Controls.Add(href)
tr.Controls.Add(td)
End If 'last page
If CurrentPage < PageCount Then
Dim td As New TableCell
Dim href As New HyperLink href.Text = "下一頁"
href.NavigateUrl = queryString + "CurrentPage=" + (CurrentPage + 1).ToString() td.Controls.Add(href)
tr.Controls.Add(td)
End If 'go
Dim html = "<a id='lnkPage' style='display:none;'></a>到第<input id='txtPageNo' size='1' onblur='SetPage()' />頁<input type='button' value='確定' onclick='GoPage()' />"
Dim tdGO As New TableCell
tdGO.Text = html
tr.Controls.Add(tdGO) table.Controls.Add(tr)
divPaging.Controls.Add(table)
End If
End Sub
把查到的集合用缓存存下,很不错