extjs: 使用JsonStore & .Net Web Service讀取資料庫

extjs 在 2.0 之後針對資料擷取的部份，做了大幅度的改善
透過很直覺的定義方式，可以很快的建構出以Grid為base的資料呈現方式

底下要示範的是，如何透過.Net的Web Service以及Ext.data.JsonStore將資料庫中的已完成簽核紀錄快速讀取出來

首先我們先建構.Net的Web Service
qryfinished.asmx 他會在 App_Code下產生一個 qryfinished.aspx
在這邊我們還會需要使用一個 Audits class 來建構我們所需的 Json Array

Imports Microsoft.VisualBasic Public Class Audits Public sno As Integer Public InstanceID As String Public ReferenceNo As String Public Version As String Public FTime As String Public StartTime As String Public SignTime As String Public UserID As String Public UserName As String Public ApproverID As String Public ApproverName As String Public Actions As String Public Diff As String Public vendor As String Public dept As String End Class

上述的class就是我們要產生的Json Array的格式

Imports System.Web Imports System.Web.Services Imports System.Web.Services.Protocols Imports Newtonsoft.Json Imports CommonModule Imports Audits ' define一個Json Array所需的 class <WebService(Namespace:="http://xxx/webservices> _ <WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _ <Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _ Public Class qryfinished Inherits System.Web.Services.WebService <WebMethod()> _ Public Function ToJSONString(ByVal uid As String) As String Dim strSQL As String Dim connWorkFlow1 As System.Data.SqlClient.SqlConnection = Nothing Dim sqlCommand As System.Data.SqlClient.SqlCommand, sqlCommand1 As System.Data.SqlClient.SqlCommand Dim drWorkflow As System.Data.SqlClient.SqlDataReader = Nothing, drWorkflow1 As System.Data.SqlClient.SqlDataReader = Nothing Dim cmdWMS As System.Data.SqlClient.SqlCommand Dim drWMS As System.Data.SqlClient.SqlDataReader = Nothing Dim iCount As Integer = 1 Dim WF_UserID As String = uid Dim starttime As String = "", signtime As String = Now().ToString Dim oAudits() As Audits ' define Json Array Dim pb_no As String = "" ReDim oAudits(0) Try strSQL = "select distinct SLIP_NO from CELL_TMP_TB where status='C' order by SLIP_NO" If connWMS Is Nothing Then Connection(False, True, False) End If If connWorkFlow Is Nothing Then Connection(False, False, True) End If If connWMS.State <> Data.ConnectionState.Open Then connWMS.Open() End If cmdWMS = New System.Data.SqlClient.SqlCommand(strSQL, connWMS) drWMS = cmdWMS.ExecuteReader() Do While drWMS.Read() pb_no = drWMS.GetString(drWMS.GetOrdinal("SLIP_NO")) strSQL = "select top 1 * from Audits NOLOCK where ReferenceNo='" + pb_no + "' order by StartTime" If connWorkFlow.State <> Data.ConnectionState.Open Then connWorkFlow.Open() End If sqlCommand = New System.Data.SqlClient.SqlCommand(strSQL, connWorkFlow) drWorkflow = sqlCommand.ExecuteReader() If drWorkflow.Read() Then 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 = WF_UserID oAudits(iCount - 1).StartTime = starttime oAudits(iCount - 1).UserName = drWorkflow.GetString(drWorkflow.GetOrdinal("UserName")) GetERP(oAudits(iCount - 1), pb_no) strSQL = "select top 1 * from Audits NOLOCK where ReferenceNo='" + pb_no + "' order by SignTime desc" connWorkFlow1 = getSqlConnection("cnnWorkFlow") sqlCommand1 = New System.Data.SqlClient.SqlCommand(strSQL, connWorkFlow1) connWorkFlow1.Open() drWorkflow1 = sqlCommand1.ExecuteReader() If drWorkflow1.Read() Then oAudits(iCount - 1).InstanceID = drWorkflow1.GetString(drWorkflow1.GetOrdinal("InstanceID")) oAudits(iCount - 1).ReferenceNo = drWorkflow1.GetString(drWorkflow1.GetOrdinal("ReferenceNo")) signtime = drWorkflow1.GetDateTime(drWorkflow1.GetOrdinal("SignTime")).ToString() oAudits(iCount - 1).SignTime = signtime oAudits(iCount - 1).Actions = drWorkflow1.GetString(drWorkflow1.GetOrdinal("Actions")) End If drWorkflow1.Close() connWorkFlow1.Close() oAudits(iCount - 1).Diff = getSignTime(CDate(starttime), CDate(signtime)) End If iCount += 1 drWorkflow.Close() Loop drWMS.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 If Not drWorkflow1 Is Nothing Then drWorkflow1 = Nothing If Not drWMS Is Nothing Then drWMS = Nothing If Not connWorkFlow1 Is Nothing Then connWorkFl ow1 = Nothing End Try ' 將 Json Array 透過 JavaScriptConvert.SerializeObject 轉換成序列化的String資料以利Internet傳輸 Return "{""totalCount"":" + CStr(iCount - 1) + ",""data"":" + JavaScriptConvert.SerializeObject(oAudits) + "}" End Function End Class

回傳的 Json String

{"totalCount":1,"data":[{"sno":1,"InstanceID":"7C038193C70EBC50482573C500328A3B","ReferenceNo":"XX -7C0358","Version":null,"FTime":null,"StartTime":"2008/1/3 下午 05:12:02","SignTime":"2008/1/3 下午 05:52:00","UserID":"nnnnnn","UserName":"AAA","ApproverID":null,"ApproverName":null,"Actions":"確認","Diff":"39分58秒","vendor":"XXXX","dept":"XXX"}]}

由於JasonStore要回傳的是一個格式化過的Json Array資料
所以我們要另外建構一個qryfinished2.aspx透過它來呼叫qryfinished這個Web Service
並透過Web Service中的ToJSONString將Json Array以序列化的方式回傳到前端網頁中

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.QueryString("uid") JSONString = myJSONObject.ToJSONString(uid) End Sub End Class

qryfinished2.aspx, 只呈現 Json String

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

建構好Web Service及呈現Json String的網頁後，接下來我們要建構 extjs 的UI部分

/* * 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 // 透過qryfinished2.aspx去讀取qryfinished.asmx這個Web Service抓出已簽核完成的資料 // 作為 Grid 的資料來源 var store = new Ext.data.JsonStore({ url: 'http://xxx/DateCode/qryfinished2.aspx?uid=, root: 'data', id: 'InstanceID', fields: [ 'sno','ReferenceNo', 'UserName', 'Actions', 'StartTime', 'SignTime', 'Diff', 'dept', 'vendor' ] }); // 呼叫 define 在 HTML 中的 Javascript 讓第一個欄位可以點擊後開啟資料頁 function renderReferenceNo(value, p, record){ return String.format( '<a href="javascript:void(0);" onclick="doSign(\'{0}\',\'{1}\',\'{2}\',\'worklist\');" title="點此開啟入庫單">{2}</a>', record.id, WF_UserID, value); } // define 一個 FormPanel 上面擺 Grid var fs = new Ext.FormPanel({ collapsible:true, frame: true, title:'Date Code 逾期簽核-'+dcTitle, labelAlign: 'right', labelWidth: 85, width:800, waitMsgTarget: true, tbar: [''], // 顯示在FormPanel上的元件 items: [new Ext.form.FieldSet({ title: '查詢條件', collapsible: true, animCollapse: true, autoHeight: true, defaultType: 'textfield'/*, contentEl: 'pb_master'*/ }),new Ext.grid.GridPanel({ 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: 'dept', width: 50, align: 'center', sortable: true }, { header: '申請者', dataIndex: 'UserName', width: 50, align: 'center', sortable: true }, { header: '廠商', dataIndex: 'vendor', width: 80, 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: 400 })] }); fs.render('form-ct'); // 將 FormPanel render 在 "form-ct" 這個 div tag 中 store.load(); // 讀取資料 fs.getTopToolbar().add('->', { text: '', tooltip: '重新整理', iconCls: 'breload', handler: function(){ location.reload(); } }); });

在給前端查詢的頁面中，我們只要將 define 好的 pgfinished.js include 進來即可

<%@ Page Language="VB" MasterPageFile="~/qryMasterPage.master" AutoEventWireup="false" CodeFile="dc_finished.aspx.vb" Inherits="dc_finished" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <script type="text/javascript"> var WF_UserID="<%=WF_UserID %>"; var dcTitle="已完成簽核查詢"; </script> <script src="pgfinished.js" type="text/javascript"></script> </asp:Content>

部份程式在Master Page 中

<%@ 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="30" http-equiv="refresh" />--> <head id="Head1" runat="server"> <title>Date Code逾期簽核系統</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,pb_no,s){ s=(typeof(s)=="undefined") ? s="":s; if (s=="finished") window.open("dc_read.aspx?unid="+unid+"&stage="+s+"&pb_no="+pb_no+"&uid="+uid,"DC",""); else window.open("dc_check.aspx?unid="+unid+"&stage="+s+"&pb_no="+pb_no+"&uid="+uid,"DC",""); } </script> </head> <body class="x-aero"> <script src="js/ext-2.0/examples/examples.js" type="text/javascript"></script> <br /> <form id="form1" class="x-form"> <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server"> </asp:ContentPlaceHolder> </form> <table align="center" border="0" cellpadding="0" cellspacing="0"> <tr> <td> <div id="form-ct"> <div id="pb_master"> </div> </div> </td> </tr> </table> </body> </html>

完成圖

建構 extjs 的 Application 也是符合 MVC 的規範
Model: Web Service
View: HTML
Controller: extjs
以這樣的方式去思考，就比較容易清楚掌握該如何撰寫 extjs 的 Application 了