Demonstrate the feasibility of DataReader Paging

I remember that it was one afternoon in. I was visiting the Internet and suddenly saw a piece of code, similar to the code above the landlord. The code was paged through DataReader. At that time, I was shocked. Is such code useless for a slightly larger system? As I understood at the time, while (dr. read (). If my system has millions of data records, it would take a long time for this while, and it would be too fast to transmit data. However, after my tests, the performance was very good, at least not as slow as we thought.

At that time, I used a 200-plus-W statistical table in our system for testing, just a simple select * from table, and then while traversing in the program, finally, we bound it to the GridView, which worked very well. I have a deep memory. During the day, in the company, the previous pages run well, and the page numbers that follow them, including the last pages, are of course the last pages. I am afraid that the system performance will be affected. When I got home at night, I tried it in the middle of the night, but it was not much different from the previous pages. At that time, I just wanted to test whether it was feasible or not, and I didn't use the notebook, but it should be returned within 5 seconds. In the preceding example, it should also look like 3, 4 seconds. It surprised me too much.

However, because the system framework uses the stored procedure and runs well, it has never been changed. That is to say, this paging solution is not actually used in projects under the real large data volume, but is often used in small projects.

For general systems, this generic paging solution is enough. For larger tables, you can use other methods, such as table sharding or other methods, to satisfy general applications.

Think about sending the code to the homepage, and it takes some time to supplement it.

The following is my test code:

Page: simple.

 
 

  
  
    <asp:GridView ID="GridView1" runat="server"> 
  
  
    </asp:GridView> 
  
  
     
  
  
<lcs:Pager ID="Pager1" runat="server" onpagechanged="Pager1_PageChanged" AlwaysShow="true" 
  
  
    CurrentPageButtonPosition="Center"> 
  
  
</lcs:Pager>  
 
 

Background code: it is also simple.

 
 

  
  
private void BindRpt()  
  
  
{  
  
  
   int totalCount;  
  
  
   double beg = DateTime.Now.Ticks;  
  
  
   if (isDatareader)  
  
  
   {  
  
  
      GridView1.DataSource = LCS.Data.DbHelper.GetPager(  
  
  
         Pager1.PageSize, Pager1.CurrentPageIndex, "Statistic", "*", "StatisticID", false, out totalCount, null, null); ;  
  
  
   }  
  
  
   else  
  
  
   {  
  
  
      totalCount = LCS.Data.DbHelper.GetCount("Statistic", "");  
  
  
      GridView1.DataSource = LCS.Data.DbHelper.GetPager(  
  
  
         Pager1.PageSize, Pager1.CurrentPageIndex, "Statistic", "*", "StatisticID", false, null);  
  
  
   }  
  
  
   Response.Write(" 
  
    
  
  
   GridView1.DataBind();  
  
  
   Pager1.RecordCount = totalCount;  
  
  
}  
 
 

The method implementation in my DbHelper is attached:

First, use datareader's

 
 

  
  
Public static DataTable GetPager (int pageSize, int pageIndex,
  
  
String tblName, string fldName, string fldSort, bool isDesc,
  
  
Out int totalCount, string condition, params object [] parmsValues
  
  
)
  
  
{
  
  
// Select * from talble where 11 = 1 order by limit desc
  
  
// It is a standard SQL statement and does not need to be distinguished separately
  
  
String SQL = "select" + fldName + "from" + tblName. ToString ()
  
  
+ (String. IsNullOrEmpty (condition ))? String. Empty: ("where 11 = 1" + condition ))
  
  
+ "Order by" + fldSort. ToString () + (isDesc? "DESC": "ASC ");
  
  
Using (DbDataReader reader = ExecuteReader (SQL, parmsValues ))
  
  
{
  
  
DataTable dt = new DataTable ();
  
  
Int fieldCount = reader. FieldCount;
  
  
For (int I = 0; I <fieldCount; I ++)
  
  
{
  
  
DataColumn col = new DataColumn ();
  
  
Col. ColumnName = reader. GetName (I );
  
  
Col. DataType = reader. GetFieldType (I );
  
  
Dt. Columns. Add (col );
  
  
}
  
  
TotalCount = 0;
  
  
Int first = (pageIndex-1) * pageSize + 1;
  
  
Int last = pageIndex * pageSize;
  
  
While (reader. Read ())
  
  
{
  
  
TotalCount ++;
  
  
If (totalCount> = first & last> = totalCount)
  
  
{
  
  
DataRow r = dt. NewRow ();
  
  
For (int I = 0; I <fieldCount; I ++)
  
  
{
  
  
R [I] = reader [I];
  
  
}
  
  
Dt. Rows. Add (r );
  
  
}
  
  
}
  
  
Return dt;
  
  
}
  
  
}
 
 

Let's look at the general:

 
 

  
  
Public static DbDataReader GetPager (int pageSize, int pageIndex,
  
  
String tblName, string fldName, string fldSort, bool isDesc, string condition)
  
  
{
  
  
Return ExecuteReader (Provider. GetPagerSql (pageSize, pageIndex, tblName, fldName, fldSort, isDesc, condition ));
  
  
}
  
  
 
  
  
// I used an SQL string parameter formatting process internally, so there is a transit here.
  
  
 
  
  
Public static DbDataReader ExecuteReader (string format, params object [] parameterValues)
  
  
{
  
  
If (format = null | format. Length = 0) throw new ArgumentNullException ("commandText ");
  
  
If (parameterValues! = Null) & (parameterValues. Length> 0 ))
  
  
{
  
  
// If a parameter exists, format the Parameter
  
  
SQlParameterFormatter formatter = new SQlParameterFormatter ();
  
  
Formatter. Provider = Provider;
  
  
Formatter. Format (format, parameterValues );
  
  
Return ExecuteReader (CommandType. Text, formatter. SQL, formatter. Parameters );
  
  
}
  
  
Else // directly out of use if no parameter exists
  
  
{
  
  
Return ExecuteReader (CommandType. Text, format, (DbParameter []) null );
  
  
}
  
  
}
 
 

// Finally, let's look at the method for generating paging SQL strings.

 
 

  
  
public string GetPagerSql( int pageSize, int pageIndex,  
  
  
string tblName,string fldName,string fldSort, bool isDesc,string condition)  
  
  
{  
  
  
   string strSort = isDesc ? " DESC" : " ASC";  
  
  
   if (pageIndex == 1)  
  
  
   {  
  
  
      return "select top " + pageSize.ToString() + " " + fldName + " from " + tblName.ToString()  
  
  
      + ((string.IsNullOrEmpty(condition)) ? string.Empty : (" where " + condition))  
  
  
      + " order by " + fldSort.ToString() + strSort;  
  
  
   }  
  
  
   else  
  
  
   {  
  
  
      System.Text.StringBuilder strSql = new System.Text.StringBuilder();  
  
  
      strSql.AppendFormat("select top {0} {1} from {2} ", pageSize,fldName, tblName);  
  
  
      strSql.AppendFormat(" where {1} not in (select top {0} {1} from {2} ", pageSize * (pageIndex - 1),  
  
  
      (fldSort.Substring(fldSort.LastIndexOf(',') + 1, fldSort.Length - fldSort.LastIndexOf(',') - 1)), tblName);  
  
  
      if (!string.IsNullOrEmpty(condition))  
  
  
      {  
  
  
         strSql.AppendFormat(" where {0} order by {1}{2}) and {0}", condition, fldSort, strSort);  
  
  
      }  
  
  
      else  
  
  
      {  
  
  
         strSql.AppendFormat(" order by {0}{1}) ", fldSort, strSort);  
  
  
      }  
  
  
      strSql.AppendFormat(" order by {0}{1}", fldSort, strSort);  
  
  
      return strSql.ToString();  
  
  
   }  
  
  
} 
 
 

Finally, for a connection to directly look at the results: http://jyt.dai8.net: 89/test_cb.aspx

Don't kill my computer.

After my tests, the conventional method is faster than that of datareader. If we look at the value alone, the gap is quite large, with a big difference of more than 10 times, A small one is 3 or 4 times worse, but it is enough for practicality. Most of the time, users do not feel it, especially those clients or internal use of the enterprise. Basically, there are no concurrent projects.