Home » Ajax » ASP.Net » extjs » Grid Pagging » extjs: Grid paging & Asp.Net 資料分頁技術

extjs: Grid paging & Asp.Net 資料分頁技術

in , , , - on 2:37 下午 - No comments

花了兩天的時間，找了資料分頁的相關技術資料 當DataGrid遇見100萬筆資料
再將 extjs grid paging 的 sample 好好看了一遍並測試
分頁展示的部份終於完成了

在進入程式說明之前，我們先來看一下整體運作的架構
首先我們讓User去查詢 po_finished.aspx 將已完成簽核的單據給撈出來
並以分頁的方式來呈現，當然Ajax及分頁的處理都交給 pgfinished.js
這個以 extjs library所撰寫的 javascript




image

由於之前提到我們是以 JsonStore 來擷取後端資料並以Json String的方式傳輸到前端
所以我們定義了一個 qryfinished2.aspx 來當作 pgfinished.js 跟 qryfinished.asmx 這個 Web Service 中間的橋樑

資料分頁的部份，除了前端操作介面傳遞 Page Number 與 Page Size 外
我們在資料庫的部份是以 Store Procedure 來接收前端傳過來的這兩個參數值

依據這樣的架構，我們就可以很簡單的享有資料分頁的功能

接下來我們進入程式說明的部份

qryMasterPage.master





<%@ Master CodeFile="qryMasterPage.master.vb" Inherits="qryMasterPage" Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd>
<html xmlns="http://www.w3.org/1999/xhtml>
<meta content="300" http-equiv="refresh" />
<head id="Head1" runat="server">
<title>採購簽核系統</title>
<link href="js/ext-2.0/resources/css/ext-all.css" rel="stylesheet" type="text/css" />
<link href="js/ext-2.0/resources/css/xtheme-gray.css" rel="stylesheet" type="text/css" />

<script src="js/ext-2.0/adapter/ext/ext-base.js" type="text/javascript"></script>

<script src="js/ext-2.0/ext-all.js" type="text/javascript"></script>

<link href="menus.css" rel="stylesheet" type="text/css" />
<link href="forms.css" rel="stylesheet" type="text/css" />
<!-- Common Styles for the examples -->
<link href="js/ext-2.0/examples/examples.css" rel="stylesheet" type="text/css" />
<style type="text/css">
html, body {
font:normal 12px verdana;
margin:0;
padding:0;
border:0 none;
overflow:hidden;
height:100%;
}
</style>

<script type="text/javascript">
function doSign(unid,uid,po_no,version,s){
s=(typeof(s)=="undefined") ? s="":s;
if (s=="finished")
window.open("po_read.aspx?unid="+unid+"&isApproval=0&stage="+s+"&po_no="+po_no+"&version="+version+"&uid="+uid,"PO","");
else
window.open("po_check.aspx?unid="+unid+"&stage="+s+"&po_no="+po_no+"&version="+version+"&uid="+uid,"PO","");
}

</script>

</head>
<body class="x-aero">

<script src="js/ext-2.0/examples/examples.js" type="text/javascript"></script>

<br />
<table align="center" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<div id="form-ct"> <!-- extjs Form Panel -->
<form id="form1" class="x-form">
<asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">
</asp:ContentPlaceHolder>
</form>
</div>
</td>
</tr>
</table>
</body>
</html>


po_finished.aspx
因為有多個畫面都共用一個 Master Page，所以將個別使用的部份寫在
ContentPlaceHolder 裡面


<%@ Page AutoEventWireup="false" CodeFile="po_finished.aspx.vb" Inherits="po_finished"
Language="VB" MasterPageFile="~/qryMasterPage.master" %>

<asp:Content ID="Content1" runat="Server" ContentPlaceHolderID="ContentPlaceHolder1">

<script type="text/javascript">
var WF_UserID="<%=WF_UserID %>";
var poTitle="已完成簽核查詢";
</script>

<script src="combos.js" type="text/javascript"></script>

<link href="combos.css" rel="stylesheet" type="text/css" />

<script src="pgfinished.js" type="text/javascript"></script> <!-- extjs Library -->

<div id="pb_master">
<form id="qryFinished" class="x-form">
<table align="center" border="0" cellpadding="0" cellspacing="2" width="100%">
<tr>
<td>
採購單號：</td>
<td>
<div>
<input id="po_no" size="20" type="text" />
</div>
</td>
<td>
料號：</td>
<td>
<div>
<input id="part_no" size="20" type="text" />
</div>
</td>
<td>
供應商：</td>
<td>
<div>
<input id="vendor" size="20" type="text" />
</div>
</td>
</tr>
<tr>
<td>
部門：
</td>
<td>
<div>
<input id="cboDept" size="20" type="text" />
</div>
</td>
<td>
產品線：
</td>
<td>
<div>
<input id="cboBrand" size="20" type="text" />
</div>
</td>
<td>
PM：</td>
<td>
<div>
<input id="cboPM" size="20" type="text" />
</div>
</td>
</tr>
<tr>
<td>
採購日期：</td>
<td>
<div>
<input id="pod1" size="20" type="text" /></div>
</td>
<td>
~</td>
<td>
<div>
<input id="pod2" size="20" type="text" />
</div>
</td>
<td>
&nbsp;
</td>
<td>
&nbsp;
</td>
</tr>
<tr>
<td>
URD：</td>
<td>
<div>
<input id="urd1" size="20" type="text" /></div>
</td>
<td>
~</td>
<td>
<div>
<input id="urd2" size="20" type="text" />
</div>
</td>
<td>
&nbsp;
</td>
<td>
<div id="btnQuery">
</div>
</td>
</tr>
<tr>
<td>
VSD：</td>
<td>
<div>
<input id="vsd1" size="20" type="text" /></div>
</td>
<td>
~</td>
<td>
<div>
<input id="vsd2" size="20" type="text" />
</div>
</td>
<td>
&nbsp;
</td>
<td>
&nbsp;
</td>
</tr>
</table>
</form>
</div>
</asp:Content>


pgfinished.js





/*
* Ext JS Library 2.0
* Copyright(c) 2006-2007, Ext JS, LLC.
* licensing@extjs.com
*
* http://extjs.com/license
*/

Ext.onReady(function(){

Ext.QuickTips.init();
// turn on validation errors beside the field globally
Ext.form.Field.prototype.msgTarget = 'side';
// create the Data Store
var store = new Ext.data.JsonStore({
url: 'http://xxx/PO/qryfinished2.aspx?uid=,
root: 'data',
totalProperty: 'totalCount',
id: 'ReferenceNo',
fields: [
'sno','ReferenceNo', 'Version', 'UserName', 'Actions', 'StartTime', 'SignTime', 'Diff'
]
});

// pluggable renders
function renderReferenceNo(value, p, record){
return String.format(
'<a href="javascript:void(0);" onclick="doSign(\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'finished\');" title="點此開啟採購單">{2}</a>',
record.id, WF_UserID, value, record.data['Version']);
}
function renderDiff(value, p, record){
var dd, hh, mm, ss;
var t;

t=record.data.SignTime-record.data.StartTime;
dd = t.getDay();
hh = t.getHours() - dd * 24
mm = t.getMinutes() - dd * 24 * 60 - hh * 60
ss = t - dd * 24 * 60 * 60 - hh * 60 * 60 - mm * 60
return String.format('{0}天又{1}小時{2}分{3}秒', dd, hh, mm, ss);
}

var fs = new Ext.FormPanel({
id: 'frmFinished',
collapsible:true,
frame: true,
title:'採購簽核-'+poTitle,
labelAlign: 'right',
labelWidth: 85,
width:800,
waitMsgTarget: true,
tbar: [''],
items: [new Ext.form.FieldSet({
title: '查詢條件',
collapsible: true,
animCollapse: true,
autoHeight: true,
defaultType: 'textfield',
contentEl: 'pb_master'
}),new Ext.grid.GridPanel({
id: 'grdFinished',
store: store,
frame: true,
//autoHeight: true,
autoScroll: true,
loadMask: true,
sm: new Ext.grid.RowSelectionModel({singleSelect:true}),
stripeRows: true,
columns: [
{ header: '', dataIndex: 'sno', width: 30, align: 'right' },
{ id: 'ReferenceNo', header: '採購單號', dataIndex: 'ReferenceNo', width: 80, renderer: renderReferenceNo, align: 'center', sortable: true },
{ header: '申請者', dataIndex: 'UserName', width: 50, align: 'center', sortable: true },
{ header: '簽核結果', dataIndex: 'Actions', width: 70, align: 'center', sortable: true },
{ header: '申請時間', dataIndex: 'StartTime', width: 150, align: 'center', sortable: true },
{ header: '完成時間', dataIndex: 'SignTime', width: 150, align: 'center', sortable: true },
{ header: '簽核歷時', dataIndex: 'Diff', width: 150, sortable: true }
],
width: 788,
height: 350,
bbar: new Ext.PagingToolbar({
pageSize: 25,
store: store,
displayInfo: true,
displayMsg: '{0} - {1} of {2}',
emptyMsg: "查無資料"
})
})]
});

fs.render('form-ct');

store.load({params:{start:0, limit:25}}); // 必要參數

fs.getTopToolbar().add('->', {
text: '',
tooltip: '重新整理',
iconCls: 'breload',
handler: function(){
location.reload();
}
});

});


在 pgfinished.js 裡面由上到下，我們先定義一個 Ext.data.JsonStore 將 qryfinished2.aspx 中所傳回的 Json String
存在 store 變數中，供 Grid Panel 使用

在 Form Panel 上我們定義了一個 Field Set 以及一個 Grid Panel，在 Grid Panel 中我們將 data store 指向我們剛定義的
store 變數，並在底下加上一個 PageToolBar (bbar 參數)，PageSize設為 25 即一頁顯示25筆 Record

Define 完 UI 部份，我們將 Form Panel render 在 qryMasterPage.master 中的 <DIV> tag "form-ct"
即 Form 的 Container，同時開始進行資料的擷取 store.load()

由於我們要做分頁，所以必須將參數傳遞給 store 中 define 的 url

qryfinished2.aspx


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="qryfinished2.aspx.vb" Inherits="qryfinished2" %>
<%=JSONString%>


qryfinished2.vb


Partial Class qryfinished2
Inherits System.Web.UI.Page
Public JSONString As String

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim myJSONObject As New qryfinished ' 建立 Web Service 物件
Dim uid As String = Request.Params("uid")
Dim start As Long = CLng(Request.Params("start")) '必須使用 Request.Params 來接參數
Dim limit As Integer = CInt(Request.Params("limit")) ' Request.QueryString 接不到
Dim pg As Integer

If start = 0 Then
pg = 1
Else
pg = (start / limit) + 1 '由於 extjs 中 start 指的是 開始的列數, 所以要透過計算轉換成頁數 (重要!!)
End If

JSONString = myJSONObject.ToJSONString(uid, pg, limit) ' 將 JSONString 傳至前端
End Sub
End Class


接下來我們來看 qryfinished.asmx的程式


Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports Newtonsoft.Json
Imports CommonModule
Imports Audits

<WebService(Namespace:="http://xxx/webservices> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class qryfinished
Inherits System.Web.Services.WebService

Dim connERP As System.Data.Odbc.OdbcConnection = Nothing
Dim connWorkFlow As System.Data.SqlClient.SqlConnection = Nothing
Dim connWorkFlow1 As System.Data.SqlClient.SqlConnection = Nothing

<WebMethod()> _
Public Function ToJSONString(ByVal uid As String, ByVal pg As Long, ByVal limit As Integer) As String
Dim strSQL As String
Dim sqlCommand As System.Data.SqlClient.SqlCommand
Dim drWorkflow As System.Data.SqlClient.SqlDataReader = Nothing
Dim iCount As Integer = 1, totalcount As Long
Dim starttime As String = "", signtime As String = Now().ToString
Dim oAudits() As Audits
Dim po_no As String, version As String

ReDim oAudits(0)

Try

Init_Connection()

strSQL = "select count(*) as n from vw_po_finished" ' 計算總筆數 store 中的 totalProperty 使用

sqlCommand = New System.Data.SqlClient.SqlCommand(strSQL, connWorkFlow)

drWorkflow = sqlCommand.ExecuteReader()

If drWorkflow.Read() Then
totalcount = CLng(drWorkflow.GetValue(drWorkflow.GetOrdinal("n")))
End If

drWorkflow.Close()

strSQL = "exec sp_po_finished @page=" + CStr(pg) + ",@limit=" + CStr(limit) + ",@userid='" + uid + "'"

sqlCommand = New System.Data.SqlClient.SqlCommand(strSQL, connWorkFlow)

drWorkflow = sqlCommand.ExecuteReader()

Do While drWorkflow.Read() '組織 Json Array
po_no = drWorkflow.GetString(drWorkflow.GetOrdinal("ReferenceNo"))
version = drWorkflow.GetString(drWorkflow.GetOrdinal("Version"))

ReDim Preserve oAudits(iCount - 1)

starttime = drWorkflow.GetDateTime(drWorkflow.GetOrdinal("StartTime")).ToString()

oAudits(iCount - 1) = New Audits()
oAudits(iCount - 1).sno = iCount
oAudits(iCount - 1).UserID = uid
oAudits(iCount - 1).StartTime = starttime
oAudits(iCount - 1).UserName = drWorkflow.GetString(drWorkflow.GetOrdinal("UserName"))
'oAudits(iCount - 1).InstanceID = drWorkflow.GetString(drWorkflow.GetOrdinal("InstanceID"))
oAudits(iCount - 1).ReferenceNo = po_no
oAudits(iCount - 1).Version = version
signtime = drWorkflow.GetDateTime(drWorkflow.GetOrdinal("SignTime")).ToString()
oAudits(iCount - 1).SignTime = signtime
oAudits(iCount - 1).Actions = drWorkflow.GetString(drWorkflow.GetOrdinal("Actions"))

oAudits(iCount - 1).Diff = getSignTime(CDate(starttime), CDate(signtime))
iCount += 1

Loop

drWorkflow.Close()
Catch ex As System.Data.Odbc.OdbcException
My.ErrorHandling.ExceptionHandler(ex, Resources.Settings.smtphost, Resources.Settings.From, Resources.Settings.CopyTo)
Finally
If Not drWorkflow Is Nothing Then drWorkflow = Nothing

Close_Connection()
End Try

Return "{""totalCount"":" + CStr(totalcount) + ",""data"":" + JavaScriptConvert.SerializeObject(oAudits) + "}" ' 回傳 Json String
End Function

Private Sub Init_Connection()
getConnectionString()

connWorkFlow = getSqlConnection(connWorkFlowString)
connWorkFlow.Open()

End Sub

Private Sub Close_Connection()
If connWorkFlow.State = Data.ConnectionState.Open Then connWorkFlow.Close()

If Not connWorkFlow Is Nothing Then connWorkFlow = Nothing
End Sub
End Class


最後我們再說明一下 SQL Server 2000 中如何透過 T-SQ來達到資料分頁的作法


CREATE PROCEDURE [dbo].[sp_po_finished]
@page as int,
@limit as int,
@userid as varchar(10)=''
AS
begin
declare @strSQL varchar(5000)

set @strSQL='SELECT TOP '+convert(varchar(10),@limit)+' * from vw_po_finished '
set @strSQL=@strSQL+'WHERE ReferenceNo+Version not in (SELECT TOP '+convert(varchar(10),(@page-1)*@limit)+' c.ReferenceNo+c.Version from vw_po_finished c '
set @strSQL=@strSQL+'ORDER BY c.SignTime desc, c.ReferenceNo, c.Version) '
set @strSQL=@strSQL+'ORDER BY SignTime desc, ReferenceNo, Version'

print @strSQL

exec (@strSQL)

end
GO


說穿了，就是透過兩個 select TOP statement 來完成，舉例說 我們要看 26~50 筆的記錄
就是 select TOP 25 ... NOT IN (select TOP (2-1)*25 ...)，因為 26~50 筆在第二頁 :)

完成圖

image

程式碼

po_finished.rar

標籤： , , ,

張貼留言