Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
I have created web page that retrives data returned by web service in XML format and display it to the user in well formatted manner. Data retrieved depend upon criterias selected by the user. But sometimes data retrieved is very large. I want to display records to the user page wise, i.e. 50 records on first page and next 50 records displayed when user clicks next button. This means only 100 records should be retrieved when user first select search criteria, next 50 records retrived when he clicks next button and so on, as to reduce data transferred from server to client. Please suggest me how to achieve this as soon as possible.
Thanks in advance.
 
[edit]Spurious code block removed - OriginalGriff[/edit]
Posted 7-Feb-11 23:09pm
Edited 7-Feb-11 23:14pm
v2
Comments
Manas Bhardwaj at 8-Feb-11 5:15am
   
any effort so far?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Here is a tip trick I prepared, but forgot to post Blush | :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*** 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.
  Permalink  
Comments
Dalek Dave at 8-Feb-11 5:43am
   
Excellent and Comprehensive answer.
Deserves more than the 5 I gave.
OriginalGriff at 8-Feb-11 5:47am
   
I think it qualifies as the longest answer I have ever given!
I wrote it as a reminder to me when I first had to do it, and then converted it to a Tip/Trick format later.
And then forgot to post it as a Tip/Trick... :blush:
Dalek Dave at 8-Feb-11 5:51am
   
Still, I hope others reward you for it too!
thatraja at 8-Feb-11 12:16pm
   
Excellent answer OG 5!
DaveAuld at 8-Feb-11 14:20pm
   
I think it would have been better to post the Tip/Trick and then give him the link!
Never the less +5!
OriginalGriff at 8-Feb-11 14:29pm
   
Only trouble with that is that it takes time to moderate the Tip/Trick, and I didn't want to look like I was saying "Well, I have a solution, but you can't see it yet..."
Might have looked mean and petty :laugh:
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

See this article/tutorial on ASP.net about the subject;
 
efficiently-paging-through-large-amounts-of-data[^]
  Permalink  
Comments
Dalek Dave at 8-Feb-11 5:43am
   
Good Link.

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 509
1 Nirav Prabtani 298
2 OriginalGriff 215
3 _Amy 165
4 Abhinav S 160
0 OriginalGriff 7,715
1 Sergey Alexandrovich Kryukov 6,911
2 Maciej Los 3,954
3 Peter Leow 3,698
4 CHill60 2,742


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 8 Feb 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100