For example, I now have some news information, which includes these fields; News ID, News Name, News ShortIntro, News Detail, News PublishTime. I'm going to store it in the database now and then take it out of the database and put it in the GridView to display it paginatedly.
I am now simulating this implementation process step by step in a common way of thinking that I have seen.
Step 1: Create a news data sheet。
At this step, many people will directly create a News table, including the fields mentioned above.
Step 2: Query the data.
Write a method to take out the data that meets the query conditions in the News table and put it in the DataSet (DataTable) as the data source.
Step 3: Bind to GridView.
Set the paging properties of GridView, bind the data source obtained from the above query to the GridView, and realize the pagination display of data in the GridView.
The above is what we commonly practice.
My approach will be like this:
Step 1: Create a news data master-slave table。
During the system development process, we will find that in a complete data information, many list items are not used in many cases. When we analyze News information, we can initially use ID, Name, ShortIntro, and PublishTime as the main information. We will concentrate this information and create a new table News (ID, Name, ShortIntro, PublishTime), and place another field Detail in another table from the table. Create a new table NewsDetail (ID, Detail, NewsID). What are the benefits of doing this? First, we lowered the "weight" of the watch. We simplify the most important and most commonly used information into a main table, so that during use, we only need to get the data we need from this table, without traversing all fields like the first method, which reduces the time for database queries and improves performance. The principle of establishing a master-slave table is to separate the most important and most commonly used as the master table, and to use those descriptive and huge content as the slave table.
Step 2: Write appropriate SQL statements.
We should write appropriate SQL statements for different functional implementations. In the above method, all data information is queried in one method, which meets the data requirements of all occasions. However, we do not need so much data content, and the excess data content consumes a lot of time and space. We often only need some of the content, such as the main information. This also confirms why we need to establish a master-slave table above. After we have established the master-slave table, we can write the following methods to meet various occasions: GetNews(int? ID, string Name)//Query the data that meets the conditions from the main table, GetNewsDetail(int? ID, string Name)//Query the data that meets the conditions from the main table and the slave table. The first method provides the main news information, and the second method provides comprehensive information. Both methods can basically meet all scenarios without causing excessive data redundancy. It should be pointed out here that some people like to write GetNewsByID(int?ID) and GetNewsByName(string Name) in this way. This way is very flexible and targeted, but it is completely unnecessary to write it like this.
Step 3: Pagination binding.
The above method is to take out all the data at once and let the control paginate it by itself, which is convenient and trouble-free. But there will be several problems:
(1) Large amount of data. Because all data that meets the conditions is retrieved at once, the amount of data is relatively large, and these data need to be placed in memory, which will affect system performance. And there will be some cards when loading for the first time, giving people the feeling that the system loads unevenly.
(2) We don’t need so much data. Why do I say that? By studying the user's usage habits, we will find that in most cases, users will not browse data page by page, and users often focus on the first few items of the previous pages. Therefore, the retrieved data is often not viewed by users.
So here, it is more appropriate to use pagination query. Only one page of data is queried from the database at a time, so that the system load is small, the page load is smooth, and it can fully meet the user's usage requirements. Some people may ask, if you do this, you will not increase the number of database IO times. What I want to say is that you can obtain a large amount of redundant data at one time and bear the lasting impact of redundancy. Compared with these much smaller accesses that are theoretically increased by IO times (the user will not view it page by page, so it will not generate so many paging queries).