ASPNET：DataGrid+存储过程的分页编辑代码[原创]

2022-07-15 80酷酷网 80kuku.com

　　datagrid|存储过程|分页|原创

<% Import Namespace="System.Data.SqlClient" %>
<% Import Namespace="System.Data" %>

Dim conNorthwind As SqlConnection
Dim strSql As String
Dim strSelect As String
Dim intStartIndex As Integer
Dim intEndIndex As Integer
Dim intRecordCount As Integer
Dim cmdSql As SqlCommand

Sub Page_Load
btnFirst.Text = "首页"
btnPrev.Text = "上一页"
btnNext.Text = "下一页"
btnLast.Text = "末页"
conNorthwind = New SqlConnection( "Server=192.168.4.1;UID=sa;PWD=111111;Database=yourDBname" )
If Not IsPostBack Then
BindDataGrid
End If
End Sub

Sub BindDataGrid
Dim cmdSelect As SqlCommand
Dim dtrRecordCount As SqlDataReader

intEndIndex = dgrdProducts.PageSize
cmdSelect = New SqlCommand( "newsPaged", conNorthwind )
cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add( "PageIndex", intStartIndex )
cmdSelect.Parameters.Add( "PageSize ", intEndIndex )
conNorthwind.Open()
dtrRecordCount = cmdSelect.ExecuteReader()
While dtrRecordCount.read()
intRecordCount=dtrRecordCount(0)
End While
dgrdProducts.VirtualItemCount = (intRecordCount / dgrdProducts.PageSize)
dtrRecordCount.NextResult()
dgrdProducts.DataSource = dtrRecordCount
dgrdProducts.DataBind()
conNorthwind.Close()
End Sub

Sub dgrdProducts_PageIndexChanged( s As Object, e As DataGridPageChangedEventArgs )
intStartIndex = e.NewPageIndex
dgrdProducts.CurrentPageIndex = e.NewPageIndex
BindDataGrid
End Sub
Sub PagerButtonClick(ByVal sender As Object, ByVal e As EventArgs)
Dim arg As String = sender.CommandArgument
Select Case arg
      Case "next"
        If (dgrdProducts.CurrentPageIndex < (dgrdProducts.PageCount - 1)) Then
           dgrdProducts.CurrentPageIndex += 1
        End If
      Case "prev"
        If (dgrdProducts.CurrentPageIndex > 0) Then
           dgrdProducts.CurrentPageIndex -= 1
        End If
      Case "last"
           dgrdProducts.CurrentPageIndex = (dgrdProducts.PageCount - 1)
      Case Else
        'page number
        dgrdProducts.CurrentPageIndex = System.Convert.ToInt32(arg)
End Select
intStartIndex=dgrdProducts.CurrentPageIndex
BindDataGrid
End Sub

Sub dgrdProducts_EditCommand( s As Object, e As DataGridCommandEventArgs )
dgrdProducts.EditItemIndex = e.Item.ItemIndex
intStartIndex = dgrdProducts.CurrentPageIndex
BindDataGrid
End Sub

Sub dgrdProducts_UpdateCommand( s As Object, e As DataGridCommandEventArgs )
Dim intArticleID As Integer
Dim txtTopic As TextBox
Dim txtEditor As TextBox
Dim strTopic As String
Dim strEditor As String

intArticleID = dgrdProducts.DataKeys( e.Item.ItemIndex )
txtTopic = e.Item.Cells( 1 ).Controls( 0 )
txtEditor = e.Item.Cells( 2 ).Controls( 0 )
strTopic = txtTopic.Text
strEditor = txtEditor.Text
strSql = "Update Tb_Article Set Topic=Topic, " _
& "Editor=Editor Where ArticleID=ArticleID"
cmdSql = New SqlCommand( strSql, conNorthwind )
cmdSql.Parameters.Add( "Topic", strTopic )
cmdSql.Parameters.Add( "Editor", strEditor )
cmdSql.Parameters.Add( "ArticleID", intArticleID )
conNorthwind.Open()
cmdSql.ExecuteNonQuery()
conNorthwind.Close()
dgrdProducts.EditItemIndex = -1
BindDataGrid
End Sub

Sub dgrdProducts_CancelCommand( s As Object, e As DataGridCommandEventArgs )
dgrdProducts.EditItemIndex = -1
BindDataGrid
End Sub
</Script>

<html>
<head><title>DataGridCustomPaging.aspx</title></head>
<body>
<form Runat="Server">

<asp:DataGrid Runat="Server"
ID="dgrdProducts"
OnEditCommand="dgrdProducts_EditCommand"
OnUpdateCommand="dgrdProducts_UpdateCommand"
OnCancelCommand="dgrdProducts_CancelCommand"
DataKeyField="A_ArticleID"
AutoGenerateColumns="false"
showheader="true"
AllowPaging="True"
AllowCustomPaging="True"
HeaderStyle-BackColor="Salmon"
PageSize="10"
OnPageIndexChanged="dgrdProducts_PageIndexChanged"
PagerStyle-Mode="NumericPages"
AlternatingItemStyle-BackColor="#eeaaee"
Font-Size="10pt"
Font-Name="Verdana"
CellSpacing="0"
CellPadding="3"
GridLines="Both"
BorderWidth="1"
BorderColor="black"
PagerStyle-HorizontalAlign="Right">
<AlternatingItemStyle BackColor="#EEEEEE"></AlternatingItemStyle>
<Columns>
    <asp:BoundColumn
      HeaderText="序列号"
      DataField="ArticleID"
      ReadOnly="True" />
    <asp:BoundColumn
      HeaderText="标题"
      DataField="Topic" />
    <asp:BoundColumn
      HeaderText="编辑者"
      DataField="Editor" />
    <asp:EditCommandColumn
      EditText="Edit!"
      UpdateText="Update!"
      CancelText="Cancel!" />
<asp:HyperLinkColumn
   HeaderText="编辑"
   DataNavigateUrlField="ArticleID"
   DataNavigateUrlFormatString="Details.aspx?id={0}"
   Text="编辑"/>
</columns>
</asp:datagrid>
<asp:linkbutton id="btnFirst" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="0"></asp:linkbutton>
<asp:linkbutton id="btnPrev" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="prev"></asp:linkbutton>
<asp:linkbutton id="btnNext" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="next"></asp:linkbutton>
<asp:linkbutton id="btnLast" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="last"></asp:linkbutton>
</form>
</html>
下面是存储过程：
CREATE PROCEDURE newsPaged
(
    PageIndex int,
    PageSize int
)
AS
BEGIN
DECLARE PageLowerBound int
DECLARE PageUpperBound int
DECLARE RowsToReturn int

-- First set the rowcount
SET RowsToReturn = PageSize * (PageIndex + 1)
SET ROWCOUNT RowsToReturn

-- Set the page bounds
SET PageLowerBound = PageSize * PageIndex
SET PageUpperBound = PageLowerBound + PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
ArticleID int,
)

-- Insert into the temp table
INSERT INTO #PageIndex (ArticleID)
SELECT
    ArticleID
FROM
    tablename
ORDER BY
    ArticleID DESC

-- Return total count
SELECT COUNT(ArticleID) FROM tablename
-- Return paged results
SELECT
    O.ArticleID,O.Topic,Editor
FROM
    tablename O,
    #PageIndex PageIndex
WHERE
    O.ArticleID = PageIndex.ArticleID AND
    PageIndex.IndexID > PageLowerBound AND
    PageIndex.IndexID < PageUpperBound
ORDER BY
    PageIndex.IndexID

END