Click here to Skip to main content
Click here to Skip to main content

Tagged as

Showing a page of information at a time in a GridView

, 8 Feb 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
When I add a log reader, the size of the log can be huge, and get very slow to view. This makes the GridView do all the work of paging the data for you.
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, let's 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*** 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 clause is changed to add
ROW_NUMBER() OVER (ORDER BY loggedAt) AS ResultSetRowNumber
which adds a field to the 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

OriginalGriff
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?
Follow on   Google+

Comments and Discussions

 
GeneralRe: Agree but my opinion is it's not fit for Tip/Trick because t... Pinmvpthatraja8-Feb-11 14:59 
GeneralReason for my vote of 3 not well constructed PinmemberAhamadmd14-Feb-11 5:34 
GeneralRe: Which? the layout of the tip, or the code itself? PinsubeditorIndivara15-Feb-11 20:17 
GeneralReason for my vote of 5 Good one OG but you might have poste... Pinmvpthatraja8-Feb-11 7:21 
GeneralRe: I don't think there is enough substance there to make an art... PinmvpOriginalGriff8-Feb-11 9:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411022.1 | Last Updated 8 Feb 2011
Article Copyright 2011 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid