一个Ajax.NET的查询实例

因為項目中只能用.net 1.1,所以atlas是用不了了，只能玩玩ajaxprofession.net, 下面是一個查詢的案例。 在頁面上放置一個datagrid1,各種查詢結果均放入其中，在client端的js中將sql語句傳給server端的方法，執行一個查詢，並可以控制雙擊行是否展開一個新的查詢。可將datagrid1的內容導出到excel中、模擬gmail的ajax的loading運用

using system;
using system.collections;
using system.componentmodel;
using system.data;
using system.drawing;
using system.web;
using system.web.sessionstate;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.htmlcontrols;
using system.data.sqlclient;
using system.io;
namespace eol
{
    /**//// <summary>
    /// index 的摘要描述。
    /// </summary>
    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 system.data.sqlclient.sqlconnection conn=new sqlconnection();
        protected system.web.ui.webcontrols.textbox eformsn;
        protected system.web.ui.webcontrols.label label1;
        protected system.web.ui.webcontrols.datagrid datagrid1=new datagrid();
        protected system.web.ui.webcontrols.button button2;
        protected system.web.ui.htmlcontrols.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;
        }

        web form 設計工具產生的程式碼#region web form 設計工具產生的程式碼
        override protected void oninit(eventargs e)
        {
            //
            // codegen: 此為 asp.net web form 設計工具所需的呼叫。
            //
            initializecomponent();
            base.oninit(e);
        }
       
        /**//// <summary>
        /// 此為設計工具支援所必須的方法 - 請勿使用程式碼編輯器修改
        /// 這個方法的內容。
        /// </summary>
        private void initializecomponent()
        {   
            this.conn = new system.data.sqlclient.sqlconnection();
            this.button2.click += new system.eventhandler(this.button2_click);
            this.datagrid1.itemcreated += new system.web.ui.webcontrols.datagriditemeventhandler(this.getpageindexstyle);
            this.load += new system.eventhandler(this.page_load);

        }
        #endregion

        private void getpageindexstyle(object sender, system.web.ui.webcontrols.datagriditemeventargs e)
        {
           
            if(e.item.itemtype == listitemtype.pager)
            {
                e.item.cells[0].text="total row:"+this.datagrid1.items.count.tostring();
            }
        }
       
        [ajaxpro.ajaxmethod]
        public string ajaxdata(string ssql, bool clientevent)
        {
            if (clientevent)
            {
                datagrid1.itemdatabound+=new datagriditemeventhandler(datagrid1_itemdatabound);
            }
//            datagrid1.allowpaging=true;
//            datagrid1.pagerstyle.position=pagerposition.top;
//            datagrid1.pagerstyle.mode=pagermode.numericpages;
//            datagrid1.itemcreated+=new datagriditemeventhandler(getpageindexstyle);
            datagrid1.datasource=db("query",ssql);
            datagrid1.headerstyle.backcolor=system.drawing.color.blue;
            datagrid1.width=unit.percentage(100);
            datagrid1.headerstyle.forecolor=system.drawing.color.white;
            datagrid1.databind();
           
           
            system.text.stringbuilder sb=new system.text.stringbuilder();
            system.io.stringwriter sw=new system.io.stringwriter(sb);
            system.web.ui.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 參數表示作為附件下載，您可以改成online在線打開
            //filename=fileflow.xls 指定輸出文件的名稱，注意其擴展名和指定文件類型相符，可以為：.doc 　　 .xls 　　 .txt 　.htm　　
            response.appendheader("content-disposition","attachment;filename=eol_"+eformsn.text+".xls");
            response.contentencoding=system.text.encoding.getencoding("big5");　
            //response.contenttype指定文件類型 可以為application/ms-excel,application/ms-word ,application/ms-txt,application/ms-html或其他瀏覽器可直接支持文檔　
            response.contenttype = "application/ms-excel";

            response.write(this.toexcel);
            response.write(session["txtexcel"]);
            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=''");
            }
        }

    }
}
client端的js:
<script language="javascript">       
var obj=document.all;
var frm=document.forms[0];
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();

var c = 0;
ajaxpro.onloading = function(b) {
    c++;
    window.status = c;
    var l = document.getelementbyid("loadinfo");
    l.style.visibility = b ? "visible" : "hidden";
       l.style.right=0;
    l.style.pixeltop=document.body.scrolltop;
}

function showdetail(n) {
    c = 0;
    asp.loadingdemo.longoperation(new function('geteolbyeformsn('+n+')'));
}

function getdatagrid(sql,event)
{
    if(list.style.display=='none')
    {
        list.style.display='';
    }
    box.style.display=='' ? box.style.display='none':void(0);
    var p=eol.index.ajaxdata(sql, event).value;
    list.innerhtml=p;
}

function geteolbyeformsn(n)
{
    if (box.style.display=="none")
    {
        box.style.display="";
        box.innerhtml="<div id='toolbar'><div id='btngoback' onclick='goback()'><img src=http://172.16.0.134/webflow/images/cch.gif>go back</div></div>"
    }
    list.style.display="none";
    var p=eol.index.ajaxdata("select * from msi_eol_list_line t 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";
    document.getelementbyid("btnhelp").onclick=gethelp;
}
function goback()
{
    box.style.display='none';
    list.style.display='';
}
function whenquery()
{
    var type=document.getelementbyid("types");
    var err=time=param='';
    var sql="select distinct (select count(eformsn) from query.dbo.msi_eol_list_line where eformsn=w.eformsn) line, w.* from (select eformsn, cast(apply_date as datetime) as applydate, applicant, lob, cast(p_date as datetime) as pendingdate, 'process' as status from webflow.dbo.tb_326_1 union select eformsn, cast(apply_date as datetime), applicant, lob, cast(p_date as datetime), 'over' as status from webflowdata.dbo.tb_326_1 where flowendstatus='e') w inner join query.dbo.msi_eol_list_line as e on w.eformsn=e.eformsn where (w.eformsn=e.eformsn) ";
    switch (type.value)
    {
        case "eformsn":
            if (frm.eformsn.value!='')
            {
                param="and e.eformsn='"+frm.eformsn.value+"'";
            }else{
                err="請輸入申請單號.!"
            }
            break;
        case "applytime":
            if (frm.starttime.value=='' && frm.endtime.value=='')
            {
                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;
        case "pendingtime":
            if (frm.pstarttime.value!="" && frm.pendtime.value!=""){
                param=" and w.pendingdate between cast('"+frm.pstarttime.value+"' as datetime) and cast('"+frm.pendtime.value+"' as datetime) order by w.pendingdate";
            }else{
                err="請輸入pending time";
            }
            break;
        case "model":
            if (frm.model.value!=''){
                frm.starttime.value=frm.endtime.value='';
                sql='';
                param="select distinct (select count(model) from query.dbo.msi_eol_list_line where model=e.model) total, eformsn, model, applicant, lob, cast(creation_date as datetime) as applydate, cast(pending_date as datetime) as pendingdate, (select status from (select eformsn, 'process' as status from webflow.dbo.tb_326_1 union select eformsn, 'over' as status from webflowdata.dbo.tb_326_1 where flowendstatus='e') as w where w.eformsn=e.eformsn) as status from query.dbo.msi_eol_list_line as e where e.model='"+frm.model.value+"' group by model, applicant, lob, creation_date, pending_date, eformsn";
            }else{
                err="請輸入model";
            }
            break;
    }
    if (err=='')
    {
        if (frm.starttime.value!='' && frm.endtime.value!='')
        {
            time="and applydate between cast('"+frm.starttime.value+"' as datetime) and cast('"+frm.endtime.value+"' as datetime) ";
        }

        sql+=time+param;
        //document.write(sql);
        c = 0;
        asp.loadingdemo.longoperation(new function('getdatagrid("'+sql+'",true)'));
    }else{
        alert(err);
    }
}

var opopup = window.createpopup();
function gethelp()
{
  var opopupbody = opopup.document.body;
  var lefter = screen.width-150;
  var topper = document.body.scrolltop;
  opopupbody.innerhtml = helpbox.innerhtml;
  opopup.show(lefter, topper, 280, document.body.offsetheight, document.body);
  document.body.onmouseup = new function("opopup.hide();");
}
</script>