在asp.Net Web应用程序页面中明智地显示记录 [英] display records in asp.Net Web Application page wise
问题描述
我创建了一个网页，该网页以XML格式检索Web服务返回的数据，并以格式良好的方式将其显示给用户.检索到的数据取决于用户选择的标准.但是有时检索到的数据非常大.我想在用户页面上明智地显示记录，即第一页上有50条记录，当用户单击下一步"按钮时显示下50条记录.这意味着，当用户首先选择搜索条件时，仅应检索100条记录，而当他单击下一步"按钮时，将检索接下来的50条记录，以此类推，以减少从服务器到客户端的数据传输.请建议我如何尽快实现此目标.
提前谢谢.
[edit]删除了虚假代码块-OriginalGriff [/edit]
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]
推荐答案
这是我准备的技巧，但忘记发布了:-O
我已经发布了它，但是要花一些时间才能发布.
时不时地，我需要向ASP.NET应用程序中添加一个日志读取器，当日志变大时，这很痛苦，因为如果我让GridView进行分页并将其附加到数据库表中，那么它将读取所有内容.记录表中的记录，并丢弃当前页面不需要的记录.很快就会变慢...
或者，我必须在后面的代码中编写代码来为我处理分页，然后自己过滤数据库中的记录.我必须提供页码，第一个，下一个，上一个和最后一个按钮，并使它们也能正常工作.我仍然需要格式化来自数据库的数据，因为就数据库而言，我的枚举值只是整数，并且我可能不希望所有字段都以默认格式显示或以默认格式显示.
使用少量(大部分)通用代码，使GridView为您完成所有工作非常简单.
例如，我的日志条目数据库记录保存:
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
通过将ObjectDataSource绑定到gridview并提供一个自定义类来执行ObjectDataSource的工作，您可以轻松地完成所有这些操作.
GridView HTML:
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()
提供这些，您就完成了.
在您的项目中创建一个新类-它应该进入"App_code"文件夹. (如果没有，则VS会提示并为您创建它.)将其称为LogViewer(或与ObjectDataSource定义中的"TypeName"匹配):
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:
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:
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
What does this do?
The original Select cluase is changed to add
ROW_NUMBER() OVER (ORDER BY loggedAt) AS ResultSetRowNumberwhich 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.
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.
See this article/tutorial on ASP.net about the subject;
efficiently-paging-through-large-amounts-of-data[^]
See this article/tutorial on ASP.net about the subject;
efficiently-paging-through-large-amounts-of-data[^]
这篇关于在asp.Net Web应用程序页面中明智地显示记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
