Implement the function of automatically loading data when the page scrolls to the end based on jquery_jquery

Now, Weibo, WeChat or other applications that we often use have asynchronous loading functions. In short, when we browse Weibo or WeChat, after moving to the top or bottom of the interface, the program passes asynchronous loading This method speeds up the loading of data because it only loads a part of the data each time. When we have a large amount of data but cannot display all of it, we can consider using the asynchronous method to load the data.

Asynchronous loading of data can occur automatically when the user clicks the "View More" button or the scroll bar scrolls to the bottom of the window; in the next blog post, we will introduce how to implement the function of automatically loading more.

Figure 1 Weibo loads more functions

Text

Suppose that the user's message data is stored in our database. Now, we need to open the API interface in the form of Web Service for the client to call. Of course, we can also use a general handler (ASHX file) to let the client call ( Please refer here for details).

Datasheet
First, we create the data table T_Paginate in the database, which contains three fields ID, Name and Message, where ID is an auto-increment value.

CREATE TABLE [dbo].[T_Paginate](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](60) COLLATE Chinese_PRC_CI_AS NULL,
  [Message] [text] COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_T_Paginate] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Figure 2 Data table T_Paginate

Data Object Model
We define the data object model Message based on the data table T_Paginate, which contains three fields: Id, Name and Comment. The specific definitions are as follows:

/// <summary>
/// The message data object.
/// </summary>
[Serializable]
public class Message
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Comment { get; set; }
}

Web Service Method
Now, we need to implement the method GetListMessages(), which obtains the corresponding paging data based on the number of paging passed by the client and returns it to the client in JSON format. Before implementing the GetListMessages() method, we First, we will introduce the method of data paging query.

In the Mysql database, we can use the limit function to implement data paging query, but there is no similar function provided in SQL Server. Then, we can use our subjective initiative - implement one ourselves. The specific implementation is as follows:

Declare @Start AS INT
Declare @Offset AS INT
;WITH Results_CTE AS (
  SELECT ID, Name, Message, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum 
FROM T_Paginate WITH(NOLOCK))
SELECT * FROM Results_CTE WHERE RowNum BETWEEN @Start AND @Offset

Above we defined the common table expression Results_CTE, which obtains the data in the T_Paginate table and sorts it according to the ID value from small to large, and then assigns ROW_NUMBER values ​​​​according to this order, where @Start and @Offset are the data range to be queried .

Next, let us implement the method GetListMessages(), the specific implementation is as follows:

/// <summary>
/// Get the user message.
/// </summary>
/// <param name="groupNumber">the pagination number</param>
/// <returns>the pagination data</returns>
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string GetListMessages(int groupNumber)
{
  string query = string.Format("WITH Results_CTE AS (SELECT ID, Name, Message, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum " +
                 "FROM T_Paginate WITH(NOLOCK)) " +
                 "SELECT * FROM Results_CTE WHERE RowNum BETWEEN '{0}' AND '{1}';",
(groupNumber - 1) * Offset + 1, Offset * groupNumber);
  var messages = new List<Message>();
  using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["RadditConn"].ToString()))
  using (var com = new SqlCommand(query, con))
  {
    con.Open();
    using (var reader = com.ExecuteReader(CommandBehavior.CloseConnection))
    {
      while (reader.Read())
      {
        var message = new Message
          {
            Id = (int)reader["ID"],
            Name = (string)reader["Name"],
            Comment = (string)reader["Message"]
          };
        messages.Add(message);
      }
    }

    // Returns json data.
    return new JavaScriptSerializer().Serialize(messages);
  }
}

Above, we defined the GetListMessages() method. For the sake of simplicity, we wrote the database operation directly on the Web Service. Please forgive me. It obtains the paging data by calling the common table expression Results_CTE. Finally, we create A JavaScriptSerializer object serializes data into JSON format and returns it to the client.

Javascript
Since we are calling the local Web Service API, we send a same-origin request to call the API interface (cross-origin request example). The specific implementation is as follows:

$.getData = function(options) {

  var opts = $.extend(true, {}, $.fn.loadMore.defaults, options);

  $.ajax({
    url: opts.url,
    type: "POST",
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    data: "{groupNumber:" + opts.groupNumber + "}",
    success: function(data, textStatus, xhr) {
      if (data.d) {
        // We need to convert JSON string to object, then
        // iterate thru the JSON object array.
        $.each($.parseJSON(data.d), function() {
          $("#result").append('<li id="">' +
              this.Id + ' - ' + '<strong>' +
              this.Name + '</strong>' + ' —&#63;' + '<span class="page_message">' +
              this.Comment + '</span></li>');
        });
        $('.animation_image').hide();
        options.groupNumber++;
        options.loading = false;
      }
    },
    error: function(xmlHttpRequest, textStatus, errorThrown) {
      options.loading = true;
      console.log(errorThrown.toString());
    }
  });
};

Above, we defined the getData() method, which uses the jQuery.ajax() method to send a same-origin request to call the GetListMessages interface. When the data is successfully loaded and displayed in the result div, the paging number (groupNumber) is increased by one.

Now, we have implemented the getData() method. Whenever the user drags the scroll bar to the bottom, the getData() method is called to obtain the data. Then, we need to combine the getData() method with the scroll bar event. Binding, the specific implementation is as follows:

// The scroll event.
$(window).scroll(function() {
  // When scroll at bottom, invoked getData() function.
  if ($(window).scrollTop() + $(window).height() == $(document).height()) {
    if (trackLoad.groupNumber <= totalGroups && !trackLoad.loading) {
      trackLoad.loading = true;   // Blocks other loading data again.
      $('.animation_image').show();
      $.getData(trackLoad);
    }
  }
});

Above, we implemented jQuery's scroll event. When the scroll bar scrolls to the bottom, the getData() method is called to obtain the data in the server.

CSS style
Next, we add CSS styles to the program, which are specifically defined as follows:

@import url("reset.css");
body,td,th {font-family: 'Microsoft Yahei', Georgia, Times New Roman, Times, serif;font-size: 15px;}
.animation_image {background: #F9FFFF;border: 1px solid #E1FFFF;padding: 10px;width: 500px;margin-right: auto;margin-left: auto;}
#result{width: 500px;margin-right: auto;margin-left: auto;}
#result ol{margin: 0px;padding: 0px;}
#result li{margin-top: 20px;border-top: 1px dotted #E1FFFF;padding-top: 20px;}

Picture 3 Loading more programs

Above, we implemented jQuery to automatically load more programs and send an asynchronous request to obtain data from the server whenever the scroll bar reaches the bottom.

We introduced asynchronous loading of data through jQuery through a Demo program. Of course, it also involves page query of data. Here we use a custom common table expression Results_CTE to obtain paginated data, and then, through The $.ajax() method sends a same-origin request to call the Web Service API; when the data is obtained successfully, the data is returned in JSON format. Finally, we display the data on the page.

The above is the entire content of this article, I hope it will be helpful to everyone’s study.