DIV CSS 佈局教程網

您现在的位置: DIV+CSS佈局教程網 >> 網頁腳本 >  >> JavaScript入門知識 >> AJAX入門 >> AJAX詳解
Ajax.NET 查詢功能的完整實例
編輯:AJAX詳解     

專案只能用 .NET 1.1,Atlas 用不上,於是選擇 AjaxPro.NET。以下是一個查詢功能的實作:頁面上放一個 DataGrid 承載查詢結果,客戶端把查詢條件送到伺服器端方法執行,還可以雙擊列展開明細,並把 DataGrid 的內容匯出成 Excel,同時模擬 Gmail 風格的 loading 提示。

伺服器端程式碼

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace eol
{
    public class index : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.TextBox TextBox1;
        protected WebControlLibrary.InputCalendar startTime;
        protected WebControlLibrary.InputCalendar endTime;
        protected WebControlLibrary.InputCalendar pStartTime;
        protected WebControlLibrary.InputCalendar pEndTime;
        protected SqlConnection Conn = new SqlConnection();
        protected System.Web.UI.WebControls.TextBox eformsn;
        protected System.Web.UI.WebControls.Label Label1;
        protected DataGrid DataGrid1 = new DataGrid();
        protected System.Web.UI.WebControls.Button Button2;
        protected HtmlInputText applicant;
        public string toExcel;

        private void Page_Load(object sender, System.EventArgs e)
        {
            AjaxPro.Utility.RegisterTypeForAjax(typeof(eol.index));
        }

        public DataSet DB(string dataName, string sqlCmd)
        {
            string connStr = "server=172.16.0.120;uid=msikdb;pwd=dbmsik;database=" + dataName;
            Conn.ConnectionString = connStr;
            try
            {
                Conn.Open();
            }
            catch (Exception ex)
            {
                Conn.Close();
                Response.Write(ex.Message);
            }
            SqlCommand cmd = new SqlCommand(sqlCmd, Conn);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            Conn.Close();
            return ds;
        }

        [AjaxPro.AjaxMethod]
        public string AjaxData(string sSql, bool clientEvent)
        {
            if (clientEvent)
            {
                DataGrid1.ItemDataBound += new DataGridItemEventHandler(DataGrid1_ItemDataBound);
            }
            DataGrid1.DataSource = DB("query", sSql);
            DataGrid1.HeaderStyle.BackColor = System.Drawing.Color.Blue;
            DataGrid1.HeaderStyle.ForeColor = System.Drawing.Color.White;
            DataGrid1.Width = Unit.Percentage(100);
            DataGrid1.DataBind();

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            System.IO.StringWriter sw = new System.IO.StringWriter(sb);
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            this.DataGrid1.RenderControl(htw);
            string s = sb.ToString().Replace(" 上午 12:00:00", "");
            this.toExcel = s;
            Session["txtExcel"] = s;
            return s;
        }

        public void setToExcel()
        {
            // attachment 表示作為附件下載,可改成 inline 直接開啟
            Response.AppendHeader("Content-Disposition", "attachment;filename=EOL_" + eformsn.Text + ".xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("big5");
            Response.ContentType = "application/ms-Excel";
            Response.Write(this.toExcel);
            Session.Abandon();
            Response.End();
        }

        private void Button2_Click(object sender, System.EventArgs e)
        {
            setToExcel();
        }

        private void DataGrid1_ItemDataBound(object sender, DataGridItemEventArgs e)
        {
            ListItemType itemtype = (ListItemType)e.Item.ItemType;
            if (itemtype != ListItemType.Header && itemtype != ListItemType.Footer
                && itemtype != ListItemType.Separator)
            {
                e.Item.Attributes.Add("ondblclick", "showDetail(" + e.Item.Cells[1].Text + ");");
                e.Item.Attributes.Add("onmouseover", "this.className='mouseOn'");
                e.Item.Attributes.Add("onmouseout", "this.className=''");
            }
        }
    }
}

客戶端 JavaScript

<script language="javascript">
var box  = document.getElementById("detail");
var list = document.getElementById("datagrid");

if (typeof ASP == "undefined") ASP = {};
ASP.LoadingDemo_class = function() {};
Object.extend(ASP.LoadingDemo_class.prototype, Object.extend(new AjaxPro.AjaxClass(), {
    LongOperation: function() {
        return this.invoke("LongOperation", {}, this.LongOperation.getArguments().slice(0));
    },
    url: '/ajaxpro/ASP.LoadingDemo,App_Web_t_mu_tej.ashx'
}));
ASP.LoadingDemo = new ASP.LoadingDemo_class();

// 模擬 Gmail 的 loading 提示
var counter = 0;
AjaxPro.onLoading = function(b) {
    counter++;
    window.status = counter;
    var l = document.getElementById("loadinfo");
    l.style.visibility = b ? "visible" : "hidden";
    l.style.right = 0;
    l.style.pixelTop = document.body.scrollTop;
};

function showDetail(n) {
    counter = 0;
    ASP.LoadingDemo.LongOperation(new Function('getEolByeFormsn(' + n + ')'));
}

function getDataGrid(sql, clientEvent) {
    if (list.style.display == 'none') {
        list.style.display = '';
    }
    box.style.display = '';
    var p = eol.index.AjaxData(sql, clientEvent).value;
    list.innerHTML = p;
}

function getEolByeFormsn(n) {
    if (box.style.display == "none") {
        box.style.display = "";
        box.innerHTML = "<div id='toolbar'><div id='btnGoback'>GO BACK</div></div>";
    }
    list.style.display = "none";
    var p = eol.index.AjaxData("select * from msi_eol_list_line where eformsn=" + n, false).value;
    box.innerHTML += p;
}

window.onload = function() {
    box.style.display = "none";
    list.style.display = "none";
    document.getElementById("btnQuery").onclick = whenQuery;
    document.getElementById("btnToExcel").style.visibility = "hidden";
};

function goBack() {
    box.style.display = 'none';
    list.style.display = '';
}

function whenQuery() {
    var type = document.getElementById("types");
    var err = '', sql = '', param = '';
    var baseSql = "select eFormsn, ApplyDate, Applicant, LOB, PendingDate, Status from ... where 1=1 ";

    switch (type.value) {
        case "eformsn":
            if (frm.eformsn.value != '') {
                param = " and e.eformsn = '" + frm.eformsn.value + "'";
            } else {
                err = "請輸入申請單號!";
            }
            break;
        case "applicant":
            if (frm.applicant.value != "") {
                param = " and e.applicant = '" + frm.applicant.value + "' order by w.ApplyDate";
            } else {
                err = "請輸入申請者名稱";
            }
            break;
        case "lob":
            if (frm.lob.value != "") {
                param = " and w.lob = '" + frm.lob.value + "'";
            } else {
                err = "請輸入 LOB";
            }
            break;
        default:
            err = "不支援的查詢類型";
            break;
    }

    if (err == '') {
        sql = baseSql + param;
        counter = 0;
        ASP.LoadingDemo.LongOperation(new Function('getDataGrid("' + sql + '",true)'));
    } else {
        alert(err);
    }
}

var oPopup = window.createPopup();
function getHelp() {
    var oPopupBody = oPopup.document.body;
    oPopupBody.innerHTML = helpbox.innerHTML;
    oPopup.show(screen.width - 150, document.body.scrollTop, 280, document.body.offsetHeight, document.body);
    document.body.onmouseup = new Function("oPopup.hide();");
}
</script>

設計說明

AjaxData 方法把 DataGrid 轉成 HTML 字串回傳給客戶端,客戶端只要把字串塞進容器的 innerHTML,畫面就更新了。ItemDataBound 事件裡動態掛上 ondblclick,讓使用者雙擊某一列就能載入該筆的明細;onmouseover 與 onmouseout 切換 CSS 類別,做出列高亮的視覺回饋。

匯出 Excel 的部分靠 setToExcel():以 Content-Disposition 宣告附件檔名、Content-Type 指定 application/ms-excel,再把先前存進 Session 的 HTML 輸出,Excel 就能開啟這個檔案。

資安提醒

這個範例有兩個必須修正的問題,正式使用前請務必處理:

  • SQL Injection:查詢字串完全由客戶端組裝並傳回伺服器執行,等於把資料庫查詢權交給使用者。請改為由伺服器依「查詢類型」決定 SQL,條件值一律用 SqlParameter 綁定,並限制可查詢的欄位與資料範圍。
  • 憑證外洩:連線字串含資料庫帳號密碼且寫死在程式碼中,應移到 web.config 並加密;同時改用整合式驗證或最小權限帳號。

此外,以 innerHTML 寫入伺服器回傳內容時,若其中含有使用者輸入的資料,應先做 HTML 編碼,避免 XSS。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved