display records in asp.Net Web Application page wise

Here is a tip trick I prepared, but forgot to post :-O
I have posted it now, but it takes a while to be generally available.


Every now and then, I need to add a log reader to an ASP.NET application, and it is a pain when the log gets large, because if I let the GridView do the paging and attach it to the database table then it reads all the records from the table and throws away those it doesn't need for the current page. This quickly gets slow...
Or, I have to write code in the codebehind which handles the paging for me, and then filter the records from the DB myself. I have to provide a page number, first, next, previous and last buttons, and make them work too. I still have to format the data that comes from the DB, since my enum values are just integers as far as the DB is concerned, and I may not want all fields displayed, or displayed in the default format.

It's quite simple to make the GridView do all the work for you, with a small amount of (largely) generic code.

For example, my Log entry DB record holds:

iD             : int
loggedAt       : DateTime
message        : string
cause          : enum, stored as int
infoType       : enum, stored as int
additionalInfo : string or byte[] (depending on infoType)

What I actually want to display is a page of twenty log entries, with shorter "hints" as to the message and additional information:

10236	06/01/2011 10:13:47	EmailSystem  To:xxx@xxx.com b...                String	System.Net.Mail.SmtpFailedR...
10237	06/01/2011 16:29:48	Unknown	     System.Web.HttpUnhandledExc...	String	mon.DbDataAdapter.Fill(Data...
10238	06/01/2011 16:32:31	Unknown	     System.Web.HttpUnhandledExc...	None	No additional info

You can do all this pretty easily by tying an ObjectDataSource to your gridview, and providing a custom class to do the ObjectDataSource work.

GridView HTML:

<asp:GridView ID="gridViewPagedLog"
    runat="server"
    AllowPaging="True"
    AllowSorting="True"
    CellPadding="4"
    DataSourceID="dataLogViewer"
    ForeColor="#333333"
    GridLines="None"
    PageSize="20"
    >
    <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
    <RowStyle BackColor="#E3EAEB" />
    <EditRowStyle BackColor="#7C6F57" />
    <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
    <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
    <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
    <AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:ObjectDataSource ID="dataLogViewer"
    runat="server"
    EnablePaging="true"
    TypeName="LogViewer"
    SelectMethod="ShowPagedLogs"
    SelectCountMethod="GetRowCount"
    StartRowIndexParameterName="startRow"
    MaximumRowsParameterName="pageSize"
    SortParameterName="SortColumns"
    >
</asp:ObjectDataSource>

Ignoring the obvious, lets look at these:

AllowPaging="True"                : Causes the GridView to do paging
AllowSorting="True"               : Causes sorting to be permitted (we need this) 
DataSourceID="dataLogViewer"      : Names the ObjectDataSource for thr Gridview 
PageSize="20"                     : Sets the rows per page - defaults to 10

EnablePaging="true"                      : Causes the data to be paged
TypeName="LogViewer"                     : Names the custom class to fetch the data
SelectMethod="ShowPagedLogs"             : Names the method in the custom class to get the page of data
SelectCountMethod="GetRowCount"          : Names the method in the custom class to get the total rows count 
StartRowIndexParameterName="startRow"    : Names the parameter passed as the index to the page method in the custom class  
MaximumRowsParameterName="pageSize"      : Names the parameter passed as the size to the page method in the custom class
SortParameterName="SortColumns"          : Names the parameter passed as the column to sort on to the page method in the custom class

You can ignore a lot of this stuff here, basically it requires you to provide a couple of methods in your custom class:

public DataSet ShowPagedLogs(int startRow, int pageSize, string sortColumns)
public int GetRowCount()

Provide those, and you are done.
Create a new class in your project - it should go into the "App_code" folder. (If you don't have one, VS will prompt and create it for you.) Call it LogViewer (or match with the "TypeName" in the ObjectDataSource definition):

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using SMDAL;
using SMWebSiteUtils;

/// <summary>
/// Provides a data source for viewing the log data.
/// </summary>
public class LogViewer
    {
    #region Constants
    /// <summary>
    /// Name of connection string for log
    /// </summary>
    private const string dbConName = "LoggingDatabase";
    /// <summary>
    /// Prefix string for Sql Paging encapsulation
    /// </summary>
    private const string sqlPagedFront = "SELECT * FROM (SELECT ";
    /// <summary>
    /// Prefix for the RowNumber function
    /// </summary>
    private const string sqlPagedRowNumberPrefix = ", ROW_NUMBER() OVER (ORDER BY ";
    /// <summary>
    /// Suffix to end the Row Number function
    /// </summary>
    private const string sqlPagedRowNumberSuffix = ") AS ResultSetRowNumber FROM ";
    /// <summary>
    /// Suffix string for Sql Paging encapsulation
    /// </summary>
    private const string sqlPagedEndFormat = ") AS PagedResults WHERE ResultSetRowNumber > {0} AND ResultSetRowNumber <= {1}";
    #endregion

    #region Property bases
    /// <summary>
    /// Columns to sort the data for when paging
    /// </summary>
    private string sortColumns = string.Empty;
    #endregion

    #region Properties
    /// <summary>
    /// Gets and sets the column to sort on when paging
    /// NOTE: Use this in the SortParameterName="SortColumns"
    /// attribute of the ObjectDataSource
    /// </summary>
    public string SortColumns
        {
        get { return sortColumns; }
        set { sortColumns = value; }
        }
    #endregion

    #region Constructors
    /// <summary>
    /// Default constructor
    /// </summary>
    public LogViewer()
        {
        }
    #endregion

    #region Public Methods
    /// <summary>
    /// Return a page of the log.
    /// </summary>
    /// <returns></returns>
    public DataSet ShowPagedLogs(int startRow, int pageSize, string sortColumns)
        {
        if (!String.IsNullOrEmpty(sortColumns))
            {
            // Specified sort column
            SortColumns = sortColumns;
            }
        else
            {
            // Sort by date
            SortColumns = "loggedAt";
            }
        string dbCon = ConfigurationManager.ConnectionStrings[dbConName].ConnectionString;
        DataSet ds = new DataSet("Log entries");
        DataTable dt = ds.Tables.Add("Logs");
        dt.Columns.Add("iD");
        dt.Columns.Add("Logged at");
        dt.Columns.Add("Cause");
        dt.Columns.Add("Message");
        dt.Columns.Add("AI type");
        dt.Columns.Add("Additional Information");
        try
            {
            using (SqlConnection con = new SqlConnection(dbCon))
                {
                con.Open();
                using (SqlCommand com = new SqlCommand(SqlPage("*", SortColumns, "SMAdmin.Log", "", startRow / pageSize, pageSize), con))
                    {
                    SqlDataReader r = com.ExecuteReader();
                    while (r.Read())
                        {
                        int id = Convert.ToInt32(r["iD"]);
                        DateTime t = (DateTime) r["loggedAt"];
                        string m = (string) r["message"];
                        AdditionalInfoType at = (AdditionalInfoType) Convert.ToInt32(r["infoType"]);
                        ErrorLogCause c = (ErrorLogCause) Convert.ToInt32(r["cause"]);
                        byte[] add = (byte[]) r["additionalInfo"];
                        string additionalInfo;
                        switch (at)
                            {
                            case AdditionalInfoType.None:
                                additionalInfo = "No additional info";
                                break;
                            case AdditionalInfoType.ByteArray:
                                additionalInfo = Summarise(add);
                                break;
                            case AdditionalInfoType.String:
                                additionalInfo = System.Text.Encoding.ASCII.GetString(add);
                                break;
                            case AdditionalInfoType.Unknown:
                                additionalInfo = "Unknown additional info";
                                break;
                            default:
                                additionalInfo = "Error: Unknown type of additional info";
                                break;
                            }
                        dt.Rows.Add(id, t, c.ToString(), Summarise(m), at.ToString(), Summarise(additionalInfo));
                        }
                    }
                }
            }
        catch
            {
            // Anonymous catch: Error in error reporting system
            // Any attempt to record the problem will most likely makes things worse
            // as the whole system is to c**k anyway.
            }
        return ds;
        }
    /// <summary>
    /// Get the number of rows in the table
    /// </summary>
    /// <returns>Rows in table</returns>
    public int GetRowCount()
        {
        string dbCon = ConfigurationManager.ConnectionStrings[dbConName].ConnectionString;
        using (SqlConnection conn = new SqlConnection(dbCon))
            {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM SMAdmin.Log", conn))
                {
                return (int) cmd.ExecuteScalar();
                }
            }
        }

    #endregion

    #region Public static methods
        /// <summary>
        /// Summarise a stream of bytes into a string
        /// </summary>
        /// <param name="ab">Bytes to summarize</param>
       public static string Summarise(byte[] ab)
            {
            return Summarise(ab, 16, 1, false);
            }
        /// <summary>
        /// Summarise a stream of bytes into a string
        /// </summary>
        /// <param name="ab">Bytes to summarize</param>
        /// <param name="bytesPerLine">Number of bytes per line of summary</param>
        /// <param name="lines">number of lines of bytes to show</param>
        /// <param name="showOffset">If true, show the offset from the start of data with each line</param>
        /// <returns>Summary of the bytes in human readable form</returns>
        public static string Summarise(byte[] ab, int bytesPerLine, int lines, bool showOffset)
            {
            int count = bytesPerLine * lines;
            string formatShowOffset = "X" + GetMaxHexDigits(count).ToString();
            StringBuilder sb = new StringBuilder(count * 3 + 2);
            int bytes = Math.Min(count, ab.Length);
            int current = 0;
            string linePrefix = "";
            for (int i = 0; i < lines; i++)
                {
                sb.Append(linePrefix);
                linePrefix = "\n";
                if (showOffset)
                    {
                    sb.Append(current.ToString(formatShowOffset) + ": ");
                    }
                for (int j = 0; j < bytesPerLine; j++)
                    {
                    sb.Append(ab[current++].ToString("X2") + " ");
                    }
                }
            return sb.ToString();
            }
        /// <summary>
        /// Summarise a string as a shorter string (just the hint rather than full text)
        /// </summary>
        /// <param name="s">String to summarize</param>
        /// <returns>Shorter string if required</returns>
        public static string Summarise(string s)
            {
            return Summarise(s, 30);
            }
        /// <summary>
        /// Summarise a string as a shorter string (just the hint rather than full text)
        /// </summary>
        /// <param name="s">String to summarize</param>
        /// <param name="maxLength">Max characters in output string</param>
        /// <returns>Shorter string if required</returns>
        public static string Summarise(string s, int maxLength)
            {
            if (s.Length <= maxLength)
                {
                return s;
                }
            return s.Substring(0, maxLength - 3) + "...";
            }
       /// <summary>
        /// Encapsulate an Sql statement for paging
        /// </summary>
        /// <example>
        ///        string paged = SMUtils.SqlPage("iD, loggedAt, message, cause, infoType, additionalInfo, ",
        ///                                       "loggedAt",
        ///                                       "SMAdmin.Log",
        ///                                       "WHERE cause=0",
        ///                                       pageNo,
        ///                                       10);
        /// </example>
        /// <param name="fields">Fields to select</param>
        /// <param name="orderField">Field to order by</param>
        /// <param name="table">Table to query from</param>
        /// <param name="additionalClauses">Addituiona WHERE etc. clauses</param>
        /// <param name="pageNo">Page number to display</param>
        /// <param name="linesPerPage">Number of liens per page</param>
        /// <returns>An Sql Select string suitable for a paged request</returns>
        public static string SqlPage(string fields, string orderField, string table, string additionalClauses, int pageNo, int linesPerPage)
            {
            fields = fields.Trim(", ".ToCharArray());
            int messageLengthEstimate = sqlPagedFront.Length +
                                        fields.Length +
                                        sqlPagedRowNumberPrefix.Length +
                                        orderField.Length +
                                        sqlPagedRowNumberSuffix.Length +
                                        table.Length +
                                        1 +                             // A gap to separate them
                                        additionalClauses.Length +
                                        sqlPagedEndFormat.Length +
                                        (2 * 10);                       // 2 integers @ 2Gig
            StringBuilder sb = new StringBuilder(messageLengthEstimate);
            int pageStart = pageNo * linesPerPage;
            sb.Append(sqlPagedFront);
            sb.Append(fields);
            sb.Append(sqlPagedRowNumberPrefix);
            sb.Append(orderField);
            sb.Append(sqlPagedRowNumberSuffix);
            sb.Append(table);
            sb.Append(" ");
            sb.Append(additionalClauses);
            sb.Append(string.Format(sqlPagedEndFormat, pageStart, pageStart + linesPerPage));
            return sb.ToString();
            }
    #endregion
    }

The paging itself works by encapsulating a regular SQL SELECT statement and including a function to number the rows. It then only returns the rows which are within range.
For example: if the SQL statement to select the fields from the Grid view was:

SELECT iD, loggedAt, message, cause, infoType, additionalInfo FROM SMAdmin.Log WHERE cause=0

then after encapsulation it would become:

SELECT * FROM (SELECT iD, 
                      loggedAt, 
                      message, 
                      cause, 
                      infoType, 
                      additionalInfo, 
                      ROW_NUMBER() OVER (ORDER BY loggedAt) AS ResultSetRowNumber 
                      FROM SMAdmin.Log WHERE cause=0) AS PagedResults 
                      WHERE ResultSetRowNumber > 20 AND ResultSetRowNumber <= 40

What does this do?
The original Select cluase is changed to add

ROW_NUMBER() OVER (ORDER BY loggedAt) AS ResultSetRowNumber

which adds a field to teh resulted results: the ordinal number of the row in the dataset, in order of log date.
The returned fields are then processed through a second SELECT where only the relevant rows are returned.
The beauty of this is that all the unwanted data stays in the database server, only the wanted rows are returned each time. This is MUCH faster than selecting the rows from the complete table, especially when the table size gets large.