Preface:
The task of the company's project development last week was to manage basic data. The aspx page of asp.net embedded in Sharepoint2010 encountered a variety of amazing problems, because I only had some knowledge about sharepoint before, but did not design specific programming work, this is the first contact. Some of the basic data has a large amount of data, which is roughly 10 million. Because of the maintenance of basic data, you also need to list and display the data, add, delete, modify, and query the data, we all know that the GridView in Asp.net has its own page, but that page is good for a small amount of data. For such a data volume of 100,000, this paging method is a disaster. There are many items on the Internet about the efficient paging of the GridView. I found a new one and changed it myself. It works well. Let's share it with you.
This is paging
Next, let's talk about the specific implementation. First, I declare that this is not my original one, but it is modified and improved on this basis. I hope you will be inspired.
I. Foreground Layout
Copy codeThe Code is as follows: <div>
<Div id = "main">
<Div id = "search">
<Table>
<Tr>
<Td>
<Asp: Label ID = "lb" runat = "server" Text = "name"> </asp: Label> </td>
<Td>
<Asp: TextBox ID = "SearchName" runat = "server"> </asp: TextBox>
</Td>
<Td>
<Asp: Button ID = "btnSearch" runat = "server" Text = "query" onclick = "PagerBtnCommand_OnClick" CommandName = "search"/>
</Td>
<Td>
<Asp: Button ID = "btnReset" runat = "server" Text = "reset" onclick = "btnReset_Click"/>
</Td>
</Tr>
</Table>
</Div>
<Div id = "gridView">
<Asp: GridView ID = "UserGridView" runat = "server" AutoGenerateColumns = "false">
<Columns>
<Asp: TemplateField HeaderText = "User Name">
<ItemTemplate>
<Asp: Label ID = "UserName" runat = "server" Text = '<% # Eval ("username") %>'> </asp: Label>
</ItemTemplate>
</Asp: TemplateField>
<Asp: TemplateField HeaderText = "unit name">
<ItemTemplate>
<Asp: Label ID = "DisplayName" runat = "server" Text = '<% # Eval ("displayname") %>'> </asp: Label>
</ItemTemplate>
</Asp: TemplateField>
<Asp: TemplateField HeaderText = "Organization Code">
<ItemTemplate>
<Asp: Label ID = "OrgCode" runat = "server" Text = '<% # Eval ("orgcode") %>'> </asp: Label>
</ItemTemplate>
</Asp: TemplateField>
<Asp: TemplateField HeaderText = "organization name">
<ItemTemplate>
<Asp: Label ID = "OrgName" runat = "server" Text = '<% # Eval ("orgname") %>'> </asp: Label>
</ItemTemplate>
</Asp: TemplateField>
</Columns>
</Asp: GridView>
</Div>
<Div id = "page">
<Table>
<Tr>
<Td>
<Asp: Label ID = "lbcurrentpage1" runat = "server" Text = "Current page:"> </asp: Label>
<Asp: Label ID = "lbCurrentPage" runat = "server" Text = ""> </asp: Label>
<Asp: Label ID = "lbFenGe" runat = "server" Text = "/"> </asp: Label>
<Asp: Label ID = "lbPageCount" runat = "server" Text = ""> </asp: Label>
</Td>
<Td>
<Asp: Label ID = "recordscount" runat = "server" Text = "Total number of entries:"> </asp: Label>
<Asp: Label ID = "lbRecordCount" runat = "server" Text = ""> </asp: Label>
</Td>
<Td>
<Asp: Button ID = "Fistpage" runat = "server" CommandName = "" Text = "Homepage" OnClick = "PagerBtnCommand_OnClick"/>
<Asp: Button ID = "Prevpage" runat = "server" CommandName = "prev" Text = "Previous Page"
OnClick = "PagerBtnCommand_OnClick"/>
<Asp: Button ID = "Nextpage" runat = "server" CommandName = "next" Text = "next" OnClick = "PagerBtnCommand_OnClick"/>
<Asp: Button ID = "Lastpage" runat = "server" CommandName = "last" Text = "last page"
Key = "last" OnClick = "PagerBtnCommand_OnClick"/>
</Td>
<Td>
<Asp: Label ID = "lbjumppage" runat = "server" Text = ""> </asp: Label>
<Asp: TextBox ID = "GotoPage" runat = "server" Width = "25px"> </asp: TextBox>
<Asp: Label ID = "lbye" runat = "server" Text = "page"> </asp: Label>
<Asp: Button ID = "Jump" runat = "server" Text = "jump" CommandName = "Jump" OnClick = "PagerBtnCommand_OnClick"/>
</Td>
</Tr>
</Table>
</Div>
</Div>
</Div>
The layout effect is as follows:
Ii. Background code implementation
I will explain the paging principle to you.
Members: It mainly defines several global variables, which are used to record the number of information, the number of pages, and the current page.
Copy codeThe Code is as follows: # region Members
Const int PAGESIZE = 10; // number of information displayed per page
Int PagesCount, RecordsCount; // the total number of pages and total number of information records.
Int CurrentPage, Pages, JumpPage; // the current page, the total number of Pages of information (used to control the button failure), jump to the page number
Const string COUNT_ SQL = "select count (*) from p_user ";
# Endregion
Methods:
1. GetRecordsCount: This method is mainly used to obtain the total number of current information. It has a sqlSearch parameter, default value: default, that is, the total number of all information queried during page initialization, when a user enters the user name to be searched for retrieval, the total number of information items that meet the user's search criteria is obtained.
Copy codeThe Code is as follows: // <summary>
/// Total number of retrieved information
/// </Summary>
/// <Param name = "sqlSearch"> </param>
/// <Returns> </returns>
Public static int GetRecordsCount (string sqlRecordsCount)
{
String sqlQuery;
If (sqlRecordsCount = "default ")
{
SqlQuery = COUNT_ SQL;
}
Else
{
SqlQuery = sqlRecordsCount;
}
Int RecordCount = 0;
SqlCommand cmd = new SqlCommand (sqlQuery, Conn ());
RecordCount = Convert. ToInt32 (cmd. ExecuteScalar ());
Cmd. Connection. Close ();
Return RecordCount;
}
2. OverPage
Copy codeThe Code is as follows: // <summary>
/// Computing remainder page
/// </Summary>
/// <Returns> </returns>
Public int OverPage ()
{
Int pages = 0;
If (RecordsCount % PAGESIZE! = 0)
Pages = 1;
Else
Pages = 0;
Return pages;
}
3. ModPage: This method is also used to calculate the remainder page, which is mainly used to prevent SQL Execution overflow.
Copy codeThe Code is as follows: // <summary>
/// Calculate the remaining page to prevent overflow of the query range during SQL statement execution
/// </Summary>
/// <Returns> </returns>
Public int ModPage ()
{
Int pages = 0;
If (RecordsCount % PAGESIZE = 0 & RecordsCount! = 0)
Pages = 1;
Else
Pages = 0;
Return pages;
}
4. Conn: This method is used to create a data connection object. You only need to change it to your own database name.
Copy codeThe Code is as follows: // <summary>
/// Data connection object
/// </Summary>
/// <Returns> </returns>
Public static SqlConnection Conn ()
{
SqlConnection conn = new SqlConnection ("data source =.; initial catalog = DB_GSL_ZCW; Integrated Security = true ");
Conn. Open ();
Return conn;
}
5. GridViewDataBind: This method is mainly used for data binding. If the input parameter is default, all data is bound by default. Otherwise, filtered data is bound.
Copy codeThe Code is as follows: // <summary>
/// You can bind the GridView data to perform different queries based on the input parameters,
/// </Summary>
/// <Param name = "sqlSearch"> </param>
Private void GridViewDataBind (string sqlSearch)
{
CurrentPage = (int) ViewState ["PageIndex"];
// Read the page number value from ViewState and save it to the CurrentPage variable for button Failure Calculation
Pages = (int) ViewState ["PagesCount"];
// Read the total page parameters from ViewState for button invalidation Calculation
// Determine the status of the four buttons (Home Page, Previous Page, next page, and last page)
If (CurrentPage + 1> 1) // whether the current page is the homepage
{
Fistpage. Enabled = true;
Prevpage. Enabled = true;
}
Else
{
Fistpage. Enabled = false;
Prevpage. Enabled = false;
}
If (CurrentPage = Pages) // whether the current page is the last page
{
Nextpage. Enabled = false;
Lastpage. Enabled = false;
}
Else
{
Nextpage. Enabled = true;
Lastpage. Enabled = true;
}
DataSet ds = new DataSet ();
String sqlResult;
// It is determined based on the input parameter sqlSearch. If it is set to default, it is the default paging query. Otherwise, it is the paging query with the filter conditions added.
If (sqlSearch = "default ")
{
SqlResult = "Select Top" + PAGESIZE + "user_serialid, username, displayname, orgcode, orgname from p_user where user_serialid not in (select top "+ PAGESIZE * CurrentPage +" user_serialid from p_user order by user_serialid asc) order by user_serialid asc ";
}
Else
{
SqlResult = sqlSearch;
}
SqlDataAdapter sqlAdapter = new SqlDataAdapter (sqlResult, Conn ());
SqlAdapter. Fill (ds, "Result ");
UserGridView. DataSource = ds. Tables ["Result"]. DefaultView;
UserGridView. DataBind ();
// Display the status of the Label control lbCurrentPaget and the text box control GotoPage
LbCurrentPage. Text = (CurrentPage + 1). ToString ();
GotoPage. Text = (CurrentPage + 1). ToString ();
SqlAdapter. Dispose ();
}
6. Page_Load: page loading function. It initializes the page for the first time and obtains all information by default.
Copy codeThe Code is as follows: protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack) // when the page is initialized for the first time
{
RecordsCount = GetRecordsCount ("default"); // The total number of default information
PagesCount = RecordsCount/PAGESIZE + OverPage (); // default total number of pages
ViewState ["PagesCount"] = RecordsCount/PAGESIZE-ModPage (); // save the last page index, 1 smaller than the total number of pages
ViewState ["PageIndex"] = 0; // Save the initial index of the page from 0
ViewState ["JumpPages"] = PagesCount;
// Saves the total number of pages. When jumping off a page, you can determine whether the number of user input exceeds the page number range.
// Display the status of lbPageCount and lbRecordCount
LbPageCount. Text = PagesCount. ToString ();
LbRecordCount. Text = RecordsCount. ToString ();
// Determines whether the text box on the page is invalid.
If (RecordsCount <= 10)
{
GotoPage. Enabled = false;
}
GridViewDataBind ("default"); // call the data binding function TDataBind () for data binding.
}
}
7. PagerBtnCommand_OnClick: This method is mainly used to process the Click events of the "Homepage", "Next page", "Previous Page", "Last page", and "query" buttons on the design view page, the CommandName attribute of Different buttons is used for separate processing. You need to assign a value to the CommandName attribute of each button on the foreground. If the user clicks the "query" button, in this case, you need to rewrite the SQL statement to be queried and add filter conditions, that is, the query conditions entered by the user.
Copy codeThe Code is as follows: // <summary>
/// Page button Click Processing
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Protected void PagerBtnCommand_OnClick (object sender, EventArgs e)
{
CurrentPage = (int) ViewState ["PageIndex"];
// Read the page number value from ViewState and save it to the CurrentPage variable for Parameter Calculation
Pages = (int) ViewState ["PagesCount"]; // calculates the total page parameter read from ViewState.
Button btn = sender as Button;
String sqlResult = "default ";
If (btn! = Null)
{
String cmd = btn. CommandName;
Switch (cmd) // perform different operations based on different commandnames.
{
Case "next ":
CurrentPage ++;
Break;
Case "prev ":
CurrentPage --;
Break;
Case "last ":
CurrentPage = Pages;
Break;
Case "search ":
If (! String. IsNullOrEmpty (SearchName. Text ))
{
RecordsCount = GetRecordsCount ("select count (*) from p_user where username like '" + SearchName. Text + "%'"); // obtain the total number of records after filtering
PagesCount = RecordsCount/PAGESIZE + OverPage (); // This variable is the total number of pages.
ViewState ["PagesCount"] = RecordsCount/PAGESIZE-ModPage (); // The variable is the last index, which is 1 smaller than the total number of pages.
ViewState ["PageIndex"] = 0; // save a page index value of 0 to ViewState. The page index starts from 0.
ViewState ["JumpPages"] = PagesCount;
// Save PageCount to ViewState. When you skip the page, determine whether the number of user inputs exceeds the page number range.
// Display the status of lbPageCount and lbRecordCount
LbPageCount. Text = PagesCount. ToString ();
LbRecordCount. Text = RecordsCount. ToString ();
// Determines whether the text box on the page is invalid.
If (RecordsCount <= 10)
GotoPage. Enabled = false;
SqlResult = "Select Top" + PAGESIZE + "user_serialid, username, displayname, orgcode, orgname from p_user where user_serialid not in (select top "+ PAGESIZE * CurrentPage +" user_serialid from p_user order by user_serialid asc) and username like '"+ SearchName. text + "% 'order by user_serialid asc ";
}
Else
{
Response. Write ("enter the name of the user you want to search! ");
}
Break;
Case "jump ":
JumpPage = (int) ViewState ["JumpPages"];
// Read the Available page number value from ViewState and save it to the JumpPage variable
// Determine whether the user input value exceeds the Available page number range
If (Int32.Parse (GotoPage. Text)> JumpPage | Int32.Parse (GotoPage. Text) <= 0)
Response. Write ("<script> alert ('page number range exceeded! ') </Script> ");
Else
{
Int InputPage = Int32.Parse (GotoPage. Text. ToString ()-1;
// Convert the user input value and save it in the int-type InputPage variable
ViewState ["PageIndex"] = InputPage;
CurrentPage = InputPage;
// Write the InputPage value to ViewState ["PageIndex "]
SqlResult = "Select Top" + PAGESIZE + "user_serialid, username, displayname, orgcode, orgname from p_user where user_serialid not in (select top "+ PAGESIZE * CurrentPage +" user_serialid from p_user order by user_serialid asc) and username like '"+ SearchName. text + "% 'order by user_serialid asc ";
}
Break;
Default:
CurrentPage = 0;
Break;
}
ViewState ["PageIndex"] = CurrentPage;
// Save the computed CurrentPage variable to ViewState again
GridViewDataBind (sqlResult); // call the data binding function TDataBind ()
}
}
8. btn_Reset_Click: This method is mainly used for resetting. After you complete a query, You need to reset it to perform the next query operation.
Copy codeThe Code is as follows: protected void btnReset_Click (object sender, EventArgs e)
(
RecordsCount = GetRecordsCount ("default"); // The total number of default information
PagesCount = RecordsCount/PAGESIZE + OverPage (); // default total number of pages
ViewState ["PagesCount"] = RecordsCount/PAGESIZE-ModPage (); // save the last page index, 1 smaller than the total number of pages
ViewState ["PageIndex"] = 0; // Save the initial index of the page from 0
ViewState ["JumpPages"] = PagesCount;
// Saves the total number of pages. When jumping off a page, you can determine whether the number of user input exceeds the page number range.
// Display the status of lbPageCount and lbRecordCount
LbPageCount. Text = PagesCount. ToString ();
LbRecordCount. Text = RecordsCount. ToString ();
// Determines whether the text box on the page is invalid.
If (RecordsCount <= 10)
{
GotoPage. Enabled = false;
}
GridViewDataBind ("default"); // call the data binding function TDataBind () for data binding.
}
The efficient paging method here mainly uses select top 10 Id, Name from tb where Id not in (select top 10 * N from tb order by Id asc) order by Id asc
N in the example represents the number of pages. In the past, there were many discussions about efficient paging in the atom. I will not talk about it here. I personally think this paging statement works well, of course, there are also row_number () function paging, select Max () paging and other methods, which have been summarized before, if you are interested, you can take a look at the ListView and Repeater high-efficiency paging article I have previously written. The article also describes in detail, as long as you follow the drill step by step, it should not be a problem.
Here we need to explain why there is no data binding control to bind directly, because in the pivopoint2010 project, only XMLDataSource is supported by the data source control.
Now we are here today. I hope to help you! Please give me more advice!
