Excellent post - Thanks!
Here's my newbie ASP.NET variation for SQL Server using Session variables (instead of ViewState) and support for paging, sorting, data filtering and selecting:
web.config:
<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=<SERVER>;Initial Catalog=<DATABASE>;Persist Security Info=True;User ID=<USER>;Password=<PASSWORD>" providerName="System.Data.SqlClient"/>
</connectionStrings>
app_code (DatabaseConnection.vb):
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class DatabaseConnection
Public Shared connection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
End Class
app_code (Utils.vb):
Imports Microsoft.VisualBasic
Imports System.Web.HttpContext
Imports System.Data
Imports System.Data.SqlClient
Imports DatabaseConnection
Public Class Utils
Public Shared Function GetCompareStatement(ByVal firstValue As String, ByVal secondValue As String, ByVal operation As String) As String
Select Case operation
Case "Contains"
Return "LIKE '%" + firstValue + "%' "
Case "Begins with"
Return "LIKE '" + firstValue + "%' "
Case "Ends with"
Return "LIKE '%" + firstValue + "' "
Case "Equals"
Return "= '" + firstValue + "' "
Case "Date Equals"
Return "= CONVERT(DATETIME, '" + firstValue + "', 101) "
Case "After"
Return "> CONVERT(DATETIME, '" + firstValue + "', 101) "
Case "Before"
Return "< CONVERT(DATETIME, '" + firstValue + "', 101) "
Case "Between"
Return "BETWEEN CONVERT(DATETIME, '" + firstValue + "', 101) AND CONVERT(DATETIME, '" + secondValue + "', 101) "
Case Else
Return ""
End Select
End Function
Public Shared Sub BindPagingSortingGridView(ByVal query As String, ByVal gridViewControl As GridView)
'Binds Paging/Sorting GridView with data from the specified query
Dim sqlComm As SqlCommand
Dim sqlReader As SqlDataReader
Dim dataTableControl As New DataTable()
Dim dataTableRowCount As Integer
sqlComm = New SqlCommand(query, connection)
sqlReader = sqlComm.ExecuteReader()
dataTableControl.Load(sqlReader)
dataTableRowCount = dataTableControl.Rows.Count
If dataTableRowCount > 0 Then
gridViewControl.DataSource = dataTableControl
gridViewControl.DataBind()
End If
sqlReader.Close()
End Sub
Public Shared Function SortDataTable(ByVal dataTable As DataTable, ByVal isPageIndexChanging As Boolean) As DataView
If Not dataTable Is Nothing Then
Dim dataView As New DataView(dataTable)
If GridViewSortExpression <> String.Empty Then
If isPageIndexChanging Then
dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection)
Else
dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GetSortDirection())
End If
End If
Return dataView
Else
Return New DataView()
End If
End Function
Public Shared Property GridViewSortBLOCKED EXPRESSION As String
Get
Return IIf(Current.Session("SortBLOCKED EXPRESSION = Nothing, String.Empty, Current.Session("SortBLOCKED EXPRESSION)
End Get
Set(ByVal value As String)
Current.Session("SortBLOCKED EXPRESSION = value
End Set
End Property
Private Shared Function GetSortDirection() As String
Select Case GridViewSortDirection
Case "ASC"
GridViewSortDirection = "DESC"
Case "DESC"
GridViewSortDirection = "ASC"
End Select
Return GridViewSortDirection
End Function
Public Shared Property GridViewSortDirection() As String
Get
Return IIf(Current.Session("SortDirection") = Nothing, "ASC", Current.Session("SortDirection"))
End Get
Set(ByVal value As String)
Current.Session("SortDirection") = value
End Set
End Property
End Class
aspx page:
<asp:GridView ID="SupplierGridView" CssClass="datatable" Width="100%" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
EmptyDataText="No records found" PageSize="15" runat="server">
<Columns>
<asp:ButtonField ButtonType="Link" Text="Select" CommandName="SELECT" />
<asp:BoundField DataField="ENT_SUPPLIER_NBR" HeaderText="ESD Number" SortExpression="ENT_SUPPLIER_NBR" />
<asp:BoundField DataField="SUP_NAME1" HeaderText="Name" SortExpression="SUP_NAME1" />
<asp:BoundField DataField="STREET" HeaderText="Street" SortExpression="STREET" />
<asp:BoundField DataField="CITY" HeaderText="City" SortExpression="CITY" />
<asp:BoundField DataField="STATE_REGION" HeaderText="State" SortExpression="STATE_REGION" />
<asp:BoundField DataField="ACTIVE" HeaderText="ACTIVE" SortExpression="ACTIVE" />
</Columns>
</asp:GridView>
vb page:
Imports System.Data
Imports System.Data.SqlClient
Imports Utils
Imports DatabaseConnection
...
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
' Clear user paging/sort
Session("CurrentPage") = 0
Session("SortBLOCKED EXPRESSION = Nothing
GridViewSortDirection = "ASC"
' Set current query
Session("CurrentQuery") = "SELECT ENT_SUPPLIER_NBR, SUP_NAME1, STREET, CITY, STATE_REGION, Active FROM INFO_SUPPLIERS WHERE ENT_SUPPLIER_NBR IS NOT NULL AND ACTIVE = 'Active' ORDER BY SUP_NAME1 ASC"
End If
' Bind GridView to current query
connection.Open()
BindPagingSortingGridView(Session("CurrentQuery"), SupplierGridView)
connection.Close()
End Sub
Protected Sub ApplyFilterButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ApplyFilterButton.Click
' Filter GridView contents
Dim tempQuery As String = "SELECT ENT_SUPPLIER_NBR, SUP_NAME1, STREET, CITY, STATE_REGION, Active FROM INFO_Suppliers WHERE ENT_SUPPLIER_NBR IS NOT NULL"
' Filter ESD Number
If Len(EsdNumberTextBox.Text) > 0 Then
tempQuery += " AND ENT_SUPPLIER_NBR " & GetCompareStatement(EsdNumberTextBox.Text, Nothing, EsdNumberDropDownList.SelectedValue)
End If
' Filter Supplier Name
If Len(SupplierNameTextBox.Text) > 0 Then
tempQuery += " AND SUP_NAME1 " & GetCompareStatement(SupplierNameTextBox.Text, Nothing, SupplierNameDropDownList.SelectedValue)
End If
' Filter Supplier City
If Len(SupplierCityTextBox.Text) > 0 Then
tempQuery += " AND CITY " & GetCompareStatement(SupplierCityTextBox.Text, Nothing, SupplierCityDropDownList.SelectedValue)
End If
' Filter Supplier State
If Len(SupplierStateTextBox.Text) > 0 Then
tempQuery += " AND STATE_REGION " & GetCompareStatement(SupplierStateTextBox.Text, Nothing, SupplierStateDropDownList.SelectedValue)
End If
' Filter Active
If ActiveDropDownList.SelectedIndex > 0 Then
tempQuery += " AND Active = '" & ActiveDropDownList.SelectedValue & "'"
End If
' Clear user paging/sort
Session("CurrentPage") = 0
Session("SortBLOCKED EXPRESSION = Nothing
GridViewSortDirection = "ASC"
' Update current query
Session("CurrentQuery") = Trim(tempQuery) & " ORDER BY SUP_NAME1 ASC"
' Bind GridView to current query
connection.Open()
BindPagingSortingGridView(Session("CurrentQuery"), SupplierGridView)
connection.Close()
End Sub
Protected Sub ShowAllButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ShowAllButton.Click
' Show all suppliers
' Clear filter options
EsdNumberDropDownList.SelectedIndex = 0
SupplierNameDropDownList.SelectedIndex = 0
SupplierCityDropDownList.SelectedIndex = 0
SupplierStateDropDownList.SelectedIndex = 0
ActiveDropDownList.SelectedIndex = 0
EsdNumberTextBox.Text = ""
SupplierNameTextBox.Text = ""
SupplierCityTextBox.Text = ""
SupplierStateTextBox.Text = ""
' Clear user paging/sort
Session("CurrentPage") = 0
Session("SortDirection") = Nothing
' Update current query
Session("CurrentQuery") = "SELECT ENT_SUPPLIER_NBR, SUP_NAME1, STREET, CITY, STATE_REGION, Active FROM INFO_Suppliers WHERE ENT_SUPPLIER_NBR IS NOT NULL ORDER BY SUP_NAME1 ASC"
' Bind GridView to current query
connection.Open()
BindPagingSortingGridView(Session("CurrentQuery"), SupplierGridView)
connection.Close()
End Sub
Protected Sub SupplierGridView_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles SupplierGridView.SelectedIndexChanged
' Bind current data
SupplierGridView.DataSource = SortDataTable(SupplierGridView.DataSource, True)
SupplierGridView.PageIndex = Session("CurrentPage")
SupplierGridView.DataBind()
' <This is the row the user selected -- do with it what you want>
supplierNumber.Text = SupplierGridView.SelectedRow.Cells(1).Text
supplierName.Text = SupplierGridView.SelectedRow.Cells(2).Text
supplierStreet.Text = SupplierGridView.SelectedRow.Cells(3).Text
...
' Clear session variables
Session("CurrentPage") = Nothing
Session("SortBLOCKED EXPRESSION = Nothing
Session("CurrentQuery") = Nothing
End Sub
Protected Sub SupplierGridView_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles SupplierGridView.Sorting
GridViewSortExpression = e.SortExpression
Dim pageIndex As Integer = SupplierGridView.PageIndex
If SupplierGridView.DataSource.GetType().ToString = "System.Data.DataTable" Then
SupplierGridView.DataSource = SortDataTable(SupplierGridView.DataSource, False)
SupplierGridView.PageIndex = Session("CurrentPage")
SupplierGridView.DataBind()
End If
End Sub
Protected Sub SupplierGridView_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles SupplierGridView.PageIndexChanging
If SupplierGridView.DataSource.GetType().ToString = "System.Data.DataTable" Then
SupplierGridView.DataSource = SortDataTable(SupplierGridView.DataSource, True)
SupplierGridView.PageIndex = e.NewPageIndex
Session("CurrentPage") = e.NewPageIndex
SupplierGridView.DataBind()
End If
End Sub
Hopefully this helps someone out there as much as this forum posting helped me!