MVC+Bootstrap+Drapper supports multiple query criteria paging using PagedList. Mvc

A few days ago, I made a small project, using MVC + Bootstrap. Before, I used to load some views of Mvc asynchronously, because this is a small project, just 1 point. 1-like list page, there are query conditions. Let's share the experience of using Drapper+PagedList. Mvc to support multiple query conditions paging.

In MVC, we are used to using strongly typed Model, and we build this Model by analyzing the display page of Orders.

1. Model for query parameters

public class OrderQueryParamModel
  {
    /// <summary>
    ///  Order number 
    /// </summary>
    public string OrderNo { get; set; }
    /// <summary>
    ///  Customer name 
    /// </summary>
    public string CustomerName { get; set; }
  }

2. Orders paged data Model

PagedList provides 1 StaticPagedList < T > Generic classes to encapsulate data. (Look at the source code of StaticPagedList, very convenient to use, the T type of data subset, pageNumber, pageSize, totalCount can be initialized.
)

public StaticPagedList(IEnumerable<T> subset, IPagedList metaData) : this(subset, metaData.PageNumber, metaData.PageSize, metaData.TotalItemCount)
    {
    }

3. Orders Show page overall Model

public class OrderViewModel
  {
    public OrderQueryParamModel QueryModel { get; set; }
    public PagedList.StaticPagedList<OrderModel> OrderList { get; set; } 
  }

OK, let's take a look at how to fill in data for OrderViewModel in Controller

 public ActionResult List(OrderViewModel orderViewModel, int page = 1)
    {
      var pagesize = 10;
      var count = 0;
      var orders = _orderService.GetOrders(page, pagesize, model.QueryModel, ref count);
      orderViewModel.OrderList = new StaticPagedList<OrderModel>(orders, page, pagesize, count);
      return View(orderViewModel);
    }

The code in Controller is very simple, receiving two parameters from POST, orderViewModel: the current page that contains the query parameter Model, page: PagedList definition.

By the way, take a look at GetOrders () this method, in order to save trouble too lazy to write stored procedures, directly used Drapper QueryMultiple, feeling very powerful ah.

 public List<OrderModel> GetOrders(int pageindex, int pagesize, OrderQueryParamModel query, ref int count)
    {
      var orders = new List<OrderModel>();
      var whereStr = string.Empty;
      if (query != null)
      {
        if (!string.IsNullOrEmpty(query.CustomerName))
        {
          whereStr += string.Format(" and CustomerName like '%{0}%' ", query.CustomerName);
        }
      }
      var cmd = string.Format(@"SELECT COUNT(*) FROM [Orders] WHERE 1=1 {0};
            SELECT * FROM (
            SELECT *, row_number() OVER (ORDER BY orderId DESC ) AS [row] 
                 FROM [Orders] WHERE 1=1 {0} )t
            WHERE t.row >@indexMin AND t.row<=@indexMax", whereStr);
      using (IDbConnection conn = BaseDBHelper.GetConn())
      {
        using (var multi = conn.QueryMultiple(cmd, 
          new { indexMin = (pageindex - 1) * pagesize, indexMax = pageindex * pagesize }))
        {
          count = multi.Read<int>().SingleOrDefault();
          orders = multi.Read<OrderModel>().ToList();
        }
      }
      return orders;
    }

It should be noted here that the order of multi. Read must be the same as that of the data set queried by Sql.

Well, the data is so pleasant to obtain. Finally, let's take a look at the key front-end data display.

1. Add a reference to View first

@using PagedList.Mvc;
@using PagedList;
@model Models.OrderViewModel

2. Create a form for the query

<div class="page-header">
  @using (Html.BeginForm("List", "Order", FormMethod.Post, new { id = "OrderForm", @class = "form-horizontal" }))
  {
    @Html.Raw(" Customer name: ") @Html.TextBoxFor(m => m.QueryModel.CustomerName)
    @Html.Raw(" Order No.: ") @Html.TextBoxFor(m => m.QueryModel.OrderNo)
    <button type="submit" class="btn btn-purple btn-sm"> Query </button>
    // Eye, what's this for? It will be explained later 
    <input type="hidden" name="page" value="1" />
  }
</div>

3. Bind data

<table class="table loading table-bordered margin-top-5 margin-bottom-5">
  <thead>
    <tr>
      <th> Order number </th>
      <th> Customer name </th>
      <th> Mobile phone number </th>      
      <th> Quantity of goods </th>
      <th> Order amount </th>
      <th> Ordering time </th>
    </tr>
  </thead>
  <tbody>
    @foreach (var item in Model.OrderList)
    {
      <tr>
        <td>@item.orderNo</td>
        <td>@item.customerName</td>
        <td>@item.customerMobile</td>
        <td>@item.productQuantity</td>
        <td>@item.orderAmount</td>
        <td>@item.orderCreateTime</td>
      </tr>
    }
  </tbody>
</table>

4. Bind paging plug-in data

@if (Model.OrderList != null&&Model.OrderList.Any())
{
  <div class="pagedList" style="margin:0 auto;text-align:center">
    @Html.PagedListPager(Model.OrderList, page => Url.Action("List", new { page }), PagedListRenderOptions.Classic)
  </div>
}

OK, 1 cut is done, you will find that the links generated by paging navigation are all in the form of "/Order/List/2", which cannot support us to pass other query parameters to Controller.

Let's change our thinking. Why don't we put the parameter page into the form form? Remember that we have an name=page in form, hidden input?

 $(function () {
    $(".pagination > li > a").click(function () {
      event.preventDefault();
      var index = $(this).html();
      if (index == '»') {
        index = parseInt($(".pagination > li[class=active] > a").html()) + 1;
      }
      if (index == '«') {
        index = parseInt($(".pagination > li[class=active] > a").html()) - 1;
      }
      if (index < 1) return;
      $("input[name=page]").val(index);
      $("#OrderForm").submit();
    });
  });

Through this section of JS, the original paged a label is directly invalidated, and his page value is put into form, and then submit () of form is directly triggered, thus meeting our general query business requirements.

The above is the site to introduce you MVC + Bootstrap + Drapper use PagedList. Mvc to support multiple query conditions paging, I hope to help you, if you have any questions welcome to leave us a message, this site will reply to you in time!