Girdview custom page

This article describes how to use objectdatasource to customize paging and sorting. You will find that objectdatasource is highly scalable ,. Whether it's a beginner or experienced user, objectdatasource can always provide you with functions that meet your requirements.
In data paging, the most simple is to use the pagination and sorting functions of the gridview. This function should not be practically unnecessary to write code, but can be paged and sorted with a slight check. However, when there is a small amount of data, this method can indeed reduce the burden on programmers, but when there are a lot of data, such as hundreds of thousands or even hundreds of thousands of records, using the system's built-in paging will lead to a large amount of data recovery, therefore, using custom pages is more effective.

In summary, the daily summary of Custom Data paging mainly includes four methods:
1) use temporary tables-this method is widely used in open source code such as forum communityserver and blog
2) Using Stored Procedures-This method seems to have originally come from an article on csdn. You can view an article in the blog circle at the following URL.
Http://genson.cnblogs.com/archive/2006/01/17/318882.html
3) use SQL statements to select the limited data paging. I have used this method and I feel that there are some problems that need further confirmation.
4) You can use the client-to-server callback of girdview to obtain data on the new page. This is a new feature of ASP. net2.0.

This article mainly introduces the first method of using temporary tables for paging. Other methods will be introduced later

The following is a change to the above article. The Code is as follows:

 

Public list <product> loadallproduct (INT startindex, int maxrows, string sortedby)

{

List <Product> products = new List <Product> ();

SqlConnection conn = new SqlConnection (ConfigurationManager. ConnectionStrings ["ConnectionString"]. ConnectionString );

String commandText = @"

-- Create a temporary table for the page

Create table # TempPageTable

(

IndexId int IDENTITY (0, 1) not null,

Id int

)

 

-- Read data and insert a temporary table

Insert into # TempPageTable

(

[ID]

)

Select

[Productid]

From products ";

 

 

 

If (sortedby! = "")

{

Commandtext + = "order by" + sortedby;

}

 

Commandtext + = @"

Set @ totalrecords = @ rowcount

 

SELECT

Src. [ProductID],

Src. [ProductName],

Src. [CategoryID],

Src. [Price],

Src. [InStore],

Src. [Description]

FROM Products src, # TempPageTable p

WHERE

Src. [productid] = p. [id] AND

P. IndexId >=@ StartIndex AND p. IndexId <(@ startIndex + @ maxRows )";

If (sortedby! = ""){

Commandtext + = "order by" + sortedby;

}

 

Sqlcommand command = new sqlcommand (commandtext, Conn );

Command. Parameters. Add (New sqlparameter ("@ startindex", startindex ));

Command. Parameters. Add (New sqlparameter ("@ maxrows", maxrows ));

Command. Parameters. Add (New sqlparameter ("@ totalrecords", sqldbtype. INT ));

Command. Parameters ["@ totalRecords"]. Direction = ParameterDirection. Output;

 

Conn. Open ();

SqlDataReader dr = command. ExecuteReader ();

While (dr. Read ()){

Product prod = new Product ();

 

Prod. ProductID = (int) dr ["ProductID"];

Prod. ProductName = (string) dr ["ProductName"];

Prod. CategoryID = (int) dr ["CategoryID"];

Prod. Price = (decimal) dr ["price"];

Prod. InStore = (Int16) dr ["InStore"];

Prod. Description = (String) dr ["Description"];

Products. Add (prod );

}

 

Dr. Close ();

Conn. Close ();

 

_ Count = (int) command. Parameters ["@ totalRecords"]. Value;

 

Return products;

}

 

 

Public int CountAll ()

{

Return _ count;

}

A simple explanation is as follows:
1) A temporary table # TempPageTable is defined here. "#" is required for the definition of a temporary table. The advantage of a temporary table is that it is automatically created and destroyed when it is not needed. For more information, see the Help System of SQL.
2) In the temporary table, I created an index print column IndexId and id column. If you look at the design of the Producst table in my database, you can see that the table contains a ProductID column, which is an auto-incrementing ID seed, why do you need to create an IndexId column?
This is because the ProductID column of the Product table is an auto-incrementing form, so the sequence number may be disrupted during editing. For example, the original Product record is, 5. After you delete a record, for example, 5, when you add another record, the new serial number starts from 6 instead of 5.
To enable auto-increment of index numbers, a custom temporary IndexId table is used.
3) The id column in the temporary table corresponds to ProductID. As you can see, the data inserted in this id column actually comes from the ProductID column in the Products table.

The source code used on the page is as follows:

<Asp: ObjectDataSource ID = "objectperformance1" runat = "server" SelectMethod = "LoadAllProduct" TypeName = "ProductBLL" DataObjectTypeName = "Product"

EnablePaging = "True" MaximumRowsParameterName = "maxRows" StartRowIndexParameterName = "startIndex" SelectCountMethod = "CountAll" SortParameterName = "sortedBy"

> </Asp: ObjectDataSource>

& Nbsp; <asp: GridView ID = "GridView1" runat = "server" CellPadding = "4" Font-Names = "Verdana"

Font-Size = "X-Small" ForeColor = "#333333" GridLines = "None" performanceid = "objectperformance1" AllowPaging = "True" AllowSorting = "True">

<FooterStyle BackColor = "#1C5E55" Font-Bold = "True" ForeColor = "White"/>

Please study the specific explanations.