A Custom Paging DataGrid

[cesark]

Hi,

I created a Custom Paging DataGrid in which the user can navigate forward and backward through the results without having to load in server memory every time a lot of database records only to display some of them. So, every time the page is post back to display the next or previous 10 results, the database query only returns 10 results every time.

My code works, I only want to know some experienced opinion or suggestion if there is something to improve.

This is the code and the logic:

<%@ Page Language="VB" Debug="true" %>


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



<script language="VB" runat="server">
Dim strConnection As New SqlConnection(ConfigurationSettings.AppSettings("myConn"))

Sub Page_Load(sender As Object, e As EventArgs)
 If Not IsPostBack Then 

  Dim CurrentPageNumber As Int32
  CurrentPageNumber = 1 
  display_offers(CurrentPageNumber)
 
 End If
End Sub




Sub display_offers(CurrentPageNumber)

  Dim CmdOffers As New SqlCommand("offers_list", strConnection)
  CmdOffers.CommandType = CommandType.StoredProcedure

  CmdOffers.Parameters.Add(New SqlParameter("@CurrentPage", SqlDbType.int))
  CmdOffers.Parameters("@CurrentPage").Value = CurrentPageNumber
  
  CmdOffers.Parameters.Add(New SqlParameter("@PageSize", SqlDbType.int))
  CmdOffers.Parameters("@PageSize").Value = datagrid_offers.PageSize

  CmdOffers.Parameters.Add(New SqlParameter("@TotalRecords", SqlDbType.int))
  CmdOffers.Parameters("@TotalRecords").Direction = ParameterDirection.Output 
  
  strConnection.Open()
   datagrid_offers.DataSource = CmdOffers.ExecuteReader()
   datagrid_offers.DataBind() 
  strConnection.Close() 


  CurrentPage.Text() = CurrentPageNumber.ToString()
  Dim total_Pages As Double
  total_Pages = 1  
  
  Dim total_Records As Int32
  total_Records = CmdOffers.Parameters("@TotalRecords").Value 
  total_Pages = System.Math.Ceiling(total_Records/datagrid_offers.PageSize)
  Total_P.Text() = total_Pages.ToString()
  Total_R.Text() = total_Records.ToString()


  If CurrentPageNumber = 1 Then
   Previous_Page.Enabled = False
   Previous_Page.Visible = False
   
    If total_Pages > 1 Then
	 Next_Page.Enabled = True
	 Next_Page.Visible = True
	Else
	 Next_Page.Enabled = False
	 Next_Page.Visible = False
	End If
   
  Else
   Previous_Page.Enabled = True
   Previous_Page.Visible = True
	
    If CurrentPageNumber = total_Pages Then
	 Next_Page.Enabled = False
	 Next_Page.Visible = False
	Else
	 Next_Page.Enabled = True
	 Next_Page.Visible = True
	End If
   
  End If 


End Sub




Sub Navigate(Sender As Object, e As CommandEventArgs)

 Dim CurrentPageNumber As Int32 

 Select Case(e.CommandName) 
  case "Next"
   CurrentPageNumber = Int32.Parse(CurrentPage.Text) + 1
  case "Previous"
   CurrentPageNumber = Int32.Parse(CurrentPage.Text) - 1
 End Select 
    
 display_offers(CurrentPageNumber)	

End Sub
</Script>

The DataGrid:

<asp:datagrid ID="datagrid_offers" AutoGenerateColumns="false"
	   AllowPaging="true" AllowCustomPaging="true" PageSize="10" PagerStyle-Visible="false" CssClass="letter1" 
	   HeaderStyle-CssClass="letter3" HeaderStyle-BackColor="#FFFFFF" AlternatingItemStyle-BackColor="#FFFFFF" 
	   BackColor="#ECF1E2" ItemStyle-Height="30" 
	   GridLines="None" runat="server">

<Columns>
	<asp:BoundColumn DataField="Date" HeaderText="Date" SortExpression="Date" DataFormatString="{0:d}" ItemStyle-Width="75">
	</asp:BoundColumn>
	<asp:BoundColumn DataField="Company_name" HeaderText="Company" ItemStyle-Width="250">
	</asp:BoundColumn>
    <asp:BoundColumn DataField="Title" HeaderText="Offer Title" ItemStyle-Width="425">
	</asp:BoundColumn>
	<asp:BoundColumn DataField="City_name" HeaderText=”City" ItemStyle-Width="125">
	</asp:BoundColumn>	
</Columns>

</asp:datagrid>

The navigation link buttons:

<asp:linkbutton ID="Previous_Page" Text="Previous Page" OnCommand="Navigate" CommandName="Previous" CssClass="Link1" runat="server"></asp:linkbutton>
      
<asp:linkbutton ID="Next_Page" Text="Next Page" OnCommand="Navigate" CommandName="Next" CssClass="Link1" runat="server"></asp:linkbutton>

And the labels:

Current Page:<asp:label ID="CurrentPage" CssClass="letter1" runat="server"></asp:label>
      
Total Pages:<asp:label ID="Total_P" CssClass="letter1" runat="server"></asp:label><br>
		
Total Records:<asp:label ID="Total_R" CssClass="letter1" runat="server"></asp:label>

Thank you