نمایش جستجوی داده و صفحه بندی در گرید ویو در asp.net با استفاده از jquery
یکشنبه 19 بهمن 1393در این مقاله میخواهیم با استفاده از jquery درasp.net داده ها را در گرید نمایش بدیم و بتوانیم داده ای را سرچ کنیم و دارای صفحه بندی باشد
یک جدول در پایگاه داده خود با فیلد های زیر بسازید:
سپس تعدادی رکورد در جدول درج میکنیم:
سپس یک StoreProcedure به شکل زیر بنویسید:
CREATEPROCEDURE [dbo].[GetStudentDataWithSearch] ( @SearchTerm VARCHAR(100)='', @PageIndex INT= 1, @PageSize INT= 10, @RecordCount INTOUTPUT ) AS BEGIN SETNOCOUNTON; SELECTROW_NUMBER()OVER ( ORDERBY StudentID ASC )AS RowNumber ,StudentID ,Name ,Email ,Class,EnrollYear,City INTO #Results FROM Student WHERE [Name] LIKE'%'+ @SearchTerm +'%'OR @SearchTerm ='' SELECT*FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1)* @PageSize + 1 AND(((@PageIndex -1)* @PageSize + 1)+ @PageSize)- 1 SELECT @RecordCount =COUNT(*)FROM #Results DROPTABLE #Results END
حال صفحه aspx. خود را به شکل زیر ویرایش کنید:
Reader Level: Articles [ASP.NET Programming] Show Search Data and Paging in ASP.Net Grid View Using jQuery By Rahul Saxena on Feb 06, 2015 In this article I explain how to make a search application with paging in an ASP.NET Grid View using jQuery. Tweet inShare 0 1 1536 The following is my Data Table structure from which I am fetching data: table design Image 1. Data in my table: table Image 2. To do this I created the following Stored Procedure: store procedure Image 3. My Stored Procedure is: CREATEPROCEDURE [dbo].[GetStudentDataWithSearch] ( @SearchTerm VARCHAR(100)='', @PageIndex INT= 1, @PageSize INT= 10, @RecordCount INTOUTPUT ) AS BEGIN SETNOCOUNTON; SELECTROW_NUMBER()OVER ( ORDERBY StudentID ASC )AS RowNumber ,StudentID ,Name ,Email ,Class,EnrollYear,City INTO #Results FROM Student WHERE [Name] LIKE'%'+ @SearchTerm +'%'OR @SearchTerm ='' SELECT*FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1)* @PageSize + 1 AND(((@PageIndex -1)* @PageSize + 1)+ @PageSize)- 1 SELECT @RecordCount =COUNT(*)FROM #Results DROPTABLE #Results END The following is my aspx code: <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="ShowSearchPaging.aspx.cs"Inherits="ShowSearchAndPagingUsing_jQuery.ShowSearchPaging"%> <!DOCTYPEhtml> <htmlxmlns="http://www.w3.org/1999/xhtml"> <headrunat="server"> <title>jQuery: Show Data and Paging in ASP.NET Grid View using jQuery</title> <scripttype="text/javascript"src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script> <scriptsrc="jquery.pagination.min.js"type="text/javascript"></script> <scripttype="text/javascript"> $(function () { GetStudents(1); }); $("[id*=txtSearch]").live("keyup", function () { GetStudents(parseInt(1)); }); $(".Pagination .page").live("click", function () { GetStudents(parseInt($(this).attr('page'))); }); function SearchTerm() { return jQuery.trim($("[id*=txtSearch]").val()); }; function GetStudents(pageIndex) { $.ajax({ type: "POST", url: "ShowSearchPaging.aspx/GetStudents", data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}', contentType: "application/json; charset=utf-8", dataType: "json", success: OnSuccess, failure: function (response) { alert(response.d); }, error: function (response) { alert(response.d); } }); } var row; function OnSuccess(response) { var xmlDoc = $.parseXML(response.d); var xml = $(xmlDoc); var customers = xml.find("Student"); if (row == null) { row = $("[id*=GridViewStudent] tr:last-child").clone(true); } $("[id*=GridViewStudent] tr").not($("[id*=GridViewStudent] tr:first-child")).remove(); if (customers.length > 0) { $.each(customers, function () { var customer = $(this); $("td", row).eq(0).html($(this).find("Name").text()); $("td", row).eq(1).html($(this).find("Email").text()); $("td", row).eq(2).html($(this).find("Class").text()); $("td", row).eq(3).html($(this).find("EnrollYear").text()); $("td", row).eq(4).html($(this).find("City").text()); $("td", row).eq(5).html($(this).find("Country").text()); $("[id*=GridViewStudent]").append(row); row = $("[id*=GridViewStudent] tr:last-child").clone(true); }); var pager = xml.find("dtForPaging"); $(".Pagination").jQ_Pager({ ActiveCssClass: "current", PagerCssClass: "pager", PageIndex: parseInt(pager.find("PageIndex").text()), PageSize: parseInt(pager.find("PageSize").text()), RecordCount: parseInt(pager.find("RecordCount").text()) }); $(".Name").each(function () { var searchPattern = new RegExp('(' + SearchTerm() + ')', 'ig'); $(this).html($(this).text().replace(searchPattern, "<span style='background-color:red;color:white;'>" + SearchTerm() + "</span>")); }); } else { var empty_row = row.clone(true); $("td:first-child", empty_row).attr("colspan", $("td", row).length); $("td:first-child", empty_row).attr("align", "center"); $("td:first-child", empty_row).html("No records found for the search criteria."); $("td", empty_row).not($("td:first-child", empty_row)).remove(); $("[id*=GridViewStudent]").append(empty_row); } }; </script> </head> <body> <formid="form1"runat="server"> <div> <tablestyle="border: solid15pxblue; width: 100%; vertical-align: central;"> <tr> <tdstyle="padding-left: 20px; padding-top: 20px; padding-bottom: 20px; background-color: skyblue; text-align: center; font-family: Verdana; font-size: 20pt; color: red;">jQuery: Show Data and Paging in ASP.NET Grid View using jQuery</td> </tr> <tr> <tdstyle="padding-left: 100px; padding-top: 20px; padding-bottom: 20px; background-color: #EE9A4D; font-family: Arial; font-size: 15pt; color: #E41B17">Enter Name To Search #: <asp:TextBoxID="txtSearch"runat="server"/> </td> </tr> <tr> <td> <tablestyle="width: 80%; text-align: center; vertical-align: central;"> <tr> <tdstyle="text-align: left;"> <asp:GridViewID="GridViewStudent"runat="server"AutoGenerateColumns="False"Width="100%" BackColor="White"BorderColor="#3366CC"BorderStyle="None"BorderWidth="1px"CellPadding="4"GridLines="Both"> <Columns> <asp:BoundFieldDataField="Name"HeaderText="Student Name"HeaderStyle-HorizontalAlign="Left"ItemStyle-CssClass="Name"></asp:BoundField> <asp:BoundFieldDataField="Email"HeaderText="Email"HeaderStyle-HorizontalAlign="Left"/> <asp:BoundFieldDataField="Class"HeaderText="Class"HeaderStyle-HorizontalAlign="Left"/> <asp:BoundFieldDataField="EnrollYear"HeaderText="Enroll Year"HeaderStyle-HorizontalAlign="Left"/> <asp:BoundFieldDataField="City"HeaderText="City"HeaderStyle-HorizontalAlign="Left"/> <asp:BoundFieldDataField="Country"HeaderText="Country"HeaderStyle-HorizontalAlign="Left"/> </Columns> <FooterStyleBackColor="#99CCCC"ForeColor="#003399"/> <HeaderStyleBackColor="#003399"Font-Bold="True"ForeColor="#CCCCFF"/> <PagerStyleBackColor="#99CCCC"ForeColor="#003399"HorizontalAlign="Left"/> <RowStyleBackColor="White"ForeColor="#003399"/> <SelectedRowStyleBackColor="#009999"Font-Bold="True"ForeColor="#CCFF99"/> <SortedAscendingCellStyleBackColor="#EDF6F6"/> <SortedAscendingHeaderStyleBackColor="#0D4AC4"/> <SortedDescendingCellStyleBackColor="#D6DFDF"/> <SortedDescendingHeaderStyleBackColor="#002876"/> </asp:GridView> </td> </tr> <tr> <td> <divclass="Pagination"style="background-color: orange; font-family: Verdana; font-size: 10pt; height: 30px; text-align: center; vertical-align: central; padding-top: 20px; padding-bottom: 10px;"> </div> </td> </tr> </table> </td> </tr> </table> </div> </form> </body> </html>
و در صفحه aspx.cs کد زیر را بنویسید:
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.Services; using System.Web.UI; using System.Web.UI.WebControls; namespace ShowSearchAndPagingUsing_jQuery { publicpartialclassShowSearchPaging : System.Web.UI.Page { privatestaticint PageSize = 5; protectedvoid Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindGridViewHeader(); } } privatevoid BindGridViewHeader() { DataTable dtHeader = newDataTable(); dtHeader.Columns.Add("Name"); dtHeader.Columns.Add("Email"); dtHeader.Columns.Add("Class"); dtHeader.Columns.Add("EnrollYear"); dtHeader.Columns.Add("City"); dtHeader.Columns.Add("Country"); dtHeader.Rows.Add(); GridViewStudent.DataSource = dtHeader; GridViewStudent.DataBind(); } [WebMethod] publicstaticstring GetStudents(string searchTerm, int pageIndex) { string query = "[GetStudentDataWithSearch]"; SqlCommand cmd = newSqlCommand(query); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@SearchTerm", searchTerm); cmd.Parameters.AddWithValue("@PageIndex", pageIndex); cmd.Parameters.AddWithValue("@PageSize", PageSize); cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output; return GetData(cmd, pageIndex).GetXml(); } privatestaticDataSet GetData(SqlCommand cmd, int pageIndex) { SqlDataAdapter da; DataSet ds = newDataSet(); SqlConnection con = newSqlConnection(); ds = newDataSet(); con.ConnectionString = @"Data Source=MYPC\SqlServer2k8; Initial Catalog=SchoolManagement; Integrated Security=true;"; cmd.Connection = con; da = newSqlDataAdapter(cmd); da.Fill(ds, "Student"); con.Open(); cmd.ExecuteNonQuery(); con.Close(); DataTable dt = newDataTable("dtForPaging"); dt.Columns.Add("PageIndex"); dt.Columns.Add("PageSize"); dt.Columns.Add("RecordCount"); dt.Rows.Add(); dt.Rows[0]["PageIndex"] = pageIndex; dt.Rows[0]["PageSize"] = PageSize; dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value; ds.Tables.Add(dt); return ds; } } }
حالا برنامه رو اجرا کنید:
- ASP.net
- 3k بازدید
- 10 تشکر