Asp.net security, practical, and simple large-capacity stored procedure page 1/2

Basically, I will focus on how to use it. In fact, there are a lot of paging stored procedures on the Internet. If you are too reluctant to find it, you can directly use the following: I tested it and modified it, paging stored procedures with high rating on the Internet.

The main advantages of this page are as follows:
1. Data paging in large capacity. My test data is 520 million.
2. I combined the aspnetpager control to make it easier to use.
3. Three layers are used for clear structure.
4. You can use it safely. You can rest assured that SQL injection is a problem here. An article on the internet says that SQL Injection exists as long as the stored procedure is concatenated with SQL statements, and the injection is directly tested in the SQL query analyzer. In fact, it is not correct. The adoption of stored procedures and parameterized submission statements does not have the problem of SQL injection. Because it will be replaced when it enters the database.

Preparations:
1. directly use a database and a base class of the data access layer to return a dataset object. When using this class, we only need to use the following statements to return a dataset object.
Sosuo8.DBUtility. DbHelperSQL. RunProcedure ("pagination", parameter, "userinfo ");
Pagination is a stored procedure I found online. I modified it to add the total number of output records. The total number of records is also mentioned here. Generally, we use statements similar to the following:
Copy codeThe Code is as follows:
Sosuo8.DBUtility. DbHelperSQL. RunProcedure ("pagination", parameter, "userinfo ");

Pagination is a stored procedure I found online. I modified it to add the total number of output records. The total number of records is also mentioned here. Generally, we use statements similar to the following:
Copy codeThe Code is as follows:
Select count (*) from sosuo8data

Here I want to say two more words. Some online users say that using count (a column), for example, count (userName) is faster than count (*). If the column is not found, it is not faster than count. And count (*) will automatically help us find the column that can achieve the fastest statistics, but in fact, in use, it is generally our primary key id, count (id) is the fastest.
2. Create a database data_test and two tables:
(1) The userinfo table is used to store data.
Copy codeThe Code is as follows:
Create table [dbo]. [userinfo] (
[Id] [int] IDENTITY (1, 1) not null,
[UserName] [nchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[UserWebName] [nchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[CreateDate] [datetime] null constraint [DF_userinfo_createDate] DEFAULT (getdate ()),
CONSTRAINT [PK_userinfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

IDs are auto-incrementing and aggregate indexes. OK? Start adding million records to the database:
Copy codeThe Code is as follows:
Set identity_insert userinfo on -- ID that can be inserted into auto-incrementing Columns
Declare @ count int
Declare @ date datetime
Set @ count = 1
Set @ date = '2017-4-5 00:00:00'
While @ count <= 5200000
Begin
Insert into userinfo (id, userName, userWebName, createDate) values (@ count, 'ahui Nannan ', 'sosuo8. com', @ date)
Set @ count = @ count + 1
Set @ date = @ date + '00: 00: 01' -- add one second to avoid duplication. Otherwise, the page is inaccurate. Do not use too many duplicate values for sorting fields.
End
Set identity_insert userinfo off

If your computer is generally configured, do not try it; otherwise, it may take some time to insert so many records.
(2) tmp table for storageTotal number of records without search criteria. Here I want to explain why we need a table to store the total number of records. You can update the total number of records every time in the background and write the latest total number of records. Otherwise, it takes a lot of time to use count (id) for records each time.

The table creation statement is as follows:
Copy codeThe Code is as follows:
Create table [dbo]. [tmp] (
[Id] [int] IDENTITY (1, 1) not null,
[RowCount_tmp] [int] NULL,
[Table_tmp] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_tmp] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Here I have to mention why we only need to retrieve the total number of rows in the table, instead of calculating all the pages in the stored procedure. Because we will use the aspnetpager control, we only need to input three values for this control. The function definition is as follows:
Copy codeThe Code is as follows:
Public DataSet GetList (int PageIndex, string strWhere, ref int rowCount)

PageIndex: Current page, corresponding to CurrentPageIndex in aspnetpager
StrWhere: search criteria. This article will not focus on the Search section. Therefore, string. Empty is used in the Code. to search data in large capacity, you need to write an article to describe it.
RowCount: Total number of records returned by the stored procedure.