Asp.net mvc4 mysql creates simple paging components (partial views) and mvc4mysql

Asp.net mvc4 mysql creates simple paging components (partial views) and mvc4mysql

Before starting the mysql paging function component, you must set the reusability. First, right-click the Views folder in the project and choose "_ PaginationComponent. cshtml". Here, we use the bootstrap paging component for html and css. For details, refer to http://v3.bootcss.com/components /.

First, the project results generated are presented as follows:

The difference between mysql paging query and mssql paging query is that mysql supports the limit statement. The limit format is limit pageIndex * pageSize, pageSize, and pageIndex-the number of pages, pageSize -- contains the page data volume. For more information about limit usage, see the mysql manual. Then, we need to pre-define three volumes: pageSize, pageIndex, and pageCount (total number of pages. The default pageSize is 30, that is, int pageSize = 30.

First, we can get pageCount: the idea is to first retrieve all the data sets to be obtained from the database and obtain the total number of pages based on pageSize = 30. The problem is whether the number of data sets is a multiple of 30, solution:

MySqlCommand comm_1 = new MySqlCommand (sqlSearch, connection); MySqlDataAdapter da_1 = new MySqlDataAdapter (sqlSearch, connection); da_1.SelectCommand = comm_1; // initialize da. the fill command DataSet set_1 = new DataSet (); da_1.Fill (set_1); DataTable dt_1 = set_1.Tables [0]; // use the datatable command to load multiple tables, obtain the first table Count = (double) (dt_1.Rows.Count)/30; // the total number of pages if (Count> (int) (dt_1.Rows.Count)/30) {pageCount = (dt_1.Rows.Count)/30 + 1;} else if (Count = (dt_1.Rows.Count)/30) {pageCount = (dt_1.Rows.Count)/30 ;} else if (Count <(int) (dt_1.Rows.Count)/30) {pageCount = 1 ;}

Here we use the judgment. For pageCount greater than 30, we add 1 to the top, and for pageCount less than 30, it is 1.

The next step is to use pageIndex to retrieve the corresponding dataset. The idea is to use the limit statement features:

Public List <Model> GetDormitoryBottleRecycleListPagination (int pageIndex, int pageSize) {Get_Connection (); List <Model> list = null; string sqlPaginatioSearch = "SELECT * FROM table order by file_1 asc limit" + (pageIndex-1) * 30 + "," + pageSize + ""; // fill in the paging data MySqlCommand comm_2 = new MySqlCommand (sqlPaginatioSearch, connection); MySqlDataAdapter da_2 = new MySqlDataAdapter (sqlPaginatioSearch , Connection); da_2.SelectCommand = comm_2; // initialize da. the fill command DataSet set_2 = new DataSet (); da_2.Fill (set_2); DataTable dt_2 = set_2.Tables [0]; // use the datatable command to load multiple tables, and obtain the first table if (dt_2! = Null & dt_2.Rows.Count> 0) {list = new List <Model> (); foreach (DataRow row in dt_2.Rows) {Model entity = sqlhelper. createItem (row); list. add (entity) ;}} Close_Connection (); return list ;}

String sqlPaginatioSearch = "SELECT * FROM table order by file_1 asc limit" + (pageIndex-1) * 30 + "," + pageSize + ""; // fill in the data after pagination
This is the core SQL statement. The number of pages is imported through pageIndex, And the pageSize data is retrieved from (pageIndex-1) * 30.
The implementation in the Controller action is also the key:

Public ActionResult DormitoryBottleRecycleSort (int id = 1) {int pageIndex = id; // number of transferred pages int pageSize = 30; int pageCount = 0; List <BottleRecycleModel> list_1; list_1 = pbsAccess. getDormitoryBottleRecycleListPagination (pageIndex, pageSize, pageCount); // obtain the page list ViewBag. listCount = list_1.Count; BottleRecycleList viewBottleRecycleList = new BottleRecycleList (); viewBottleRecycleList. bottleRecycleList = New List <BottleRecycleModel> (); // It is very important to instantiate an object. // This is the function code for displaying paging data if (list_1! = Null) {foreach (var item in list_1) {BottleRecycleModel viewBottleRecycle = new BottleRecycleModel (); viewBottleRecycle. id = item. id; viewBottleRecycle. dormitoryNumber = item. dormitoryNumber; viewBottleRecycle. smallBottleNumber = item. smallBottleNumber; viewBottleRecycle. bigBottleNumber = item. bigBottleNumber; viewBottleRecycle. totalBottleNumber = item. totalBottleNumber; viewBottleRecycle. publishTime = item. publishTime; viewBottleRecycleList. bottleRecycleList. add (viewBottleRecycle);} ViewBag. dormitoryBottleRecycleSort = viewBottleRecycleList. bottleRecycleList;} else {ViewBag. dormitoryBottleRecycleSort = null;} ViewBag. pageCount = pbsAccess. getDormitoryBottlePaginationPageCount (); ViewBag. pageIndex = pageIndex; return View (ViewBag );}

Here, we use ViewBag to transmit values. getDormitoryBottlePaginationPageCount () Here is the method obtained from pageCount above. This is the background method.

Let's talk about how to use these values in _ PaginationComponent. cshtml.

@{ string Controllers = ViewContext.RouteData.Values["controller"].ToString(); string Actions = ViewContext.RouteData.Values["Action"].ToString();}<li><a href="#">«</a></li>@for (int i = 1; i < @ViewBag.pageCount + 1; i++){ <li><a href="/@Controllers/@Actions/@i">@i</a></li>}<li><a href="#">»</a></li>

ViewContext is used for Component reuse. routeData. values ["controller"]. toString () method. In this way, when referencing components on other pages, you can easily transplant them as follows: <a href = "/@ Controllers/@ Actions/@ I"> @ I </a>. Of course, the practical for loop is to display more pages, for example, 1, 2, 3, and 4. I only display one page because I have a small amount of data. Of course, there are other functions, such as skipping too many pages as ellipsis and disabling the current page, other JavaScript code is required. Here we only want to implement a simple paging component function.

The code on this page is not the source code of all pages. Here we only provide my personal solution ideas. If there are any errors, please correct them and be sure to be empty-minded.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.