I wanted to add some custom pages to my admin console that managed some custom data in the database. Normally this would be a rather complicated process of reading data from the database, formatting it into a grid and displaying it, all the while managing pagination if the dataset was large. ASP.Net actually will do this all for you, if you know how to wire it all up. Herein I'll show you how I did this.

Please note that there is a slightly strange behavior with doing this with the other libraries that ASPDNSF needs to run. However, the excellent support staff at Vortex helped me to correct for the strange default behavior. If you follow the directions herein you'll not run in to any of the strangeness that I did when using the default configuration.

The first step is to create the ASP page. This page will display a button (that does nothing but initiate a post-back), a text field (for holding a filter string), and a GridView (for holding the records from the database). Also on the page is an ObjectDataSource that will actually fill the GridView with data and otherwise manage it.

Code:
    <asp:Literal ID="Literal3" runat="server">Log:</asp:Literal>
    <br />
    <asp:TextBox ID="txtLogFilter" runat="server"></asp:TextBox>
    <asp:Button ID="btnFilterLog" runat="server" Text="Filter History" />
    <br />
    <asp:GridView ID="gvHistory" runat="server" EnableViewState="False" AllowPaging="True" DataSourceID="dsHistory" AllowSorting="True">
    </asp:GridView>
    <asp:ObjectDataSource ID="dsHistory" runat="server" SelectMethod="GetHistoryRows" SelectCountMethod="GetHistoryCount" EnablePaging="true" TypeName="WynPartsImport.ROIImportHistory, App_Code">
        <SelectParameters>
            <asp:ControlParameter DefaultValue="%" ControlID="txtLogFilter" Name="filter" Type="String" />
        </SelectParameters>
    </asp:ObjectDataSource>
Here is where the gotchya comes in. Please note that the TypeName for the ObjectDataSource is WynPartsImport.ROIImportHistory, App_Code. The default second parameter for the TypeName is App_Code, however, because of the way that ASPDNSF works, the expected default is not App_Code, thus we must specify App_Code here. The TypeName itself is the fully-qualified class that will actually provide the data for the GridView, which we'll get to in a bit.

  • Also, please note that ViewState on the GridView isn't needed because the data is repopulated every time. If you were doing other processing on the data such that you weren't refreshing it from the ObjectDataSource everytime, then you would need to enable ViewState, but that should be a rare thing.
  • The button does nothing but cause a post-back. When everything is posted back the value of the filter text box is also posted back and is available to the ObjectDataSource to read and forward on to the underlying data-providing class to actually accomplish the filtering. The button doesn't actually need to do anything, the ObjectDataSource does it all for us.


The second part of this is the code-behind, which is actually really simple.

Code:
Imports AspDotNetStorefrontCore
Imports AspDotNetStorefrontAdmin

Partial Class localadmin_ROI
	Inherits AdminPageBase

	Protected Sub Page_InitComplete(sender As Object, e As System.EventArgs) Handles Me.InitComplete

		dsQueue.ConnectionString = DB.GetDBConn

	End Sub

End Class
Since the connection string must be set before the page starts processing, we have to set it in the InitComplete event. Since the connections string is coming from a class in ASPDNSF, we can't set it at design-time.

Finally, the class that drives all of this. The class must be (for this example) in the App_Code folder. I just created a new file and put this in it:

Code:
Imports System.Data.SqlClient
Imports System.Data
Imports AspDotNetStorefrontCore
Imports System.Collections.Generic

Namespace WynPartsImport

    Public Class ROIImportHistory

        Public Shared Function GetHistoryCount(filter As String) As Integer
            Dim filterParameter As New SqlParameter("Filter", filter)
            Return DB.GetSqlN("SELECT COUNT(*) AS N FROM WR_ROIImportLog WHERE Item LIKE @Filter OR TimeStamp LIKE @Filter OR Message LIKE @Filter OR Description LIKE @Filter", New SqlParameter() {filterParameter})

        End Function

        Public Shared Function GetHistoryRows(maximumRows As Long, startRowIndex As Long, filter As String) As DataTable

            Dim localDatatable As New DataTable

            Using conn As New SqlConnection(DB.GetDBConn)
                conn.Open()

                Dim sql As String = "SELECT Item, TimeStamp AS 'Time Stamp', Message, Description FROM WR_ROIImportLog WITH (NOLOCK) WHERE Item LIKE @Filter OR TimeStamp LIKE @Filter OR Message LIKE @Filter OR Description LIKE @Filter ORDER BY TimeStamp DESC OFFSET @RowStart ROWS FETCH NEXT @MaxRows ROWS ONLY"

                Using cmd As New SqlCommand(sql, conn)
                    cmd.Parameters.AddWithValue("Filter", filter)
                    cmd.Parameters.AddWithValue("RowStart", startRowIndex)
                    cmd.Parameters.AddWithValue("MaxRows", maximumRows)

                    localDatatable.Load(cmd.ExecuteReader)

                End Using

            End Using

            Return localDatatable

        End Function

    End Class

End Namespace
As you can see from the Namespace and the Class name, there is a match between this and the TypeName specified earlier. In this way the ObjectDataSource can find which object it should pull data from. In this class there are then two functions, one to return the total count of records, and the other to actually return the records that should be put in the GridView.

The function that returns the count of the records is necessary for the pagination to work correctly. This function must also be able to work with filters so that the correct number of records is returned even when working with a filter. Earlier, in the ASP page, I specified that the default for the 'filter' field be '%', in this way I can use the SQL LIKE operator to match everything, even if the 'filter' field was left blank on the web form.

The function that returns the actual data returns a DataTable. By doing so we don't have to manually specify the names of the columns in the ASP page. We can if we want, but otherwise the GridView will just grab the columns in the order they appear in the DataTable with the names for the columns that are returned with the SQL query. It is also worth noting that I used SQL 2008 syntax here to get pagination to be really low-effort/low-impact as far as the database is concerned. If you don't understand the syntax, please look it up online as it is quite powerful and really speeds processing quite a bit. Not to mention, it is a lot easier than other procedure-based solutions I've seen on the internet.

So, basically what happens here is:
  1. You browse to the page
  2. The page initializes
  3. The page sets the DB connection string
  4. The ObjectDataSource looks for the object to get data from by looking up the TypeName
  5. The ObjectDataSource looks for the functions on the object to call for the count of records and the actual data itself
  6. The object (in this case a class) executes the functions and returns the requested data to the ObjectDataSource
  7. The ObjectDataSource reads the data from the object (an int, not long (long will not work), of the count of the records, and a DataTable of the records to display)
  8. The ObjectDataSource sends the data to the GridView which then renders it to the form
  9. The user sees nicely formatted data from the database that they can filter and paginate with excellent performance and very little effort on the developers part