发布一个原创的基于Ajax的通用(组合)查询

简单介绍:
1.完全无刷实现,客户端主要实现生成条件选择或录入控件,并进行录入控制;

2.将字段信息存放于XML文件,便于数据移植

3.通过js脚本,ajax框架引擎获取字段信息

4.通过js脚本生成组合条件;
 
5.运行效果:

下面是代码和实现效果:
1.AjaxSeachMethod.cs

using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

namespace WebUI.AjaxSearchSystem
{
    
/// <summary>
    
/// SearchMethod 的摘要说明。
    
/// </summary>

    public class AjaxSearchMethod
    
{
        
private string[] CHAR_OP    = new string[]{"等于|=","不等于|<>","相似于|Like","不相似于|Not Like"};
        
private string[] NUM_OP        = new string[]{"大于|>","大于或等于|>=","等于|=","不等于|<>","小于|<","小于或等于|<="};    
        
private string[] DATE_OP    = new string[]{"等于|=","不等于|<>","先于|<","后于|>"};    

        
public AjaxSearchMethod()
        
{}

        
/// <summary>
        
/// 根据表名获取包含的字段
        
/// </summary>
        
/// <param name="table">表名</param>
        
/// <returns></returns>

        [AjaxPro.AjaxMethod]
        
public string[] GetFields(string table)
        
{
            
string[] strArrFields = null;

            
try
            
{
                XmlDocument xd 
= LoadXml(@"\AjaxSearchDataCollection.xml");
                XmlElement    xe  
=xd.DocumentElement;
                XmlNode        xn 
= xe.SelectSingleNode("descendant::Table[@value='"+table+"']");
                XmlNodeList xnl 
= xn.ChildNodes;
                
int iLen = xnl.Count;
                strArrFields 
= new string[iLen];

                
int iIndex = 0;

                
foreach(XmlNode xnField in xnl)
                
{
                    strArrFields[iIndex] 
= xnField.Attributes["Name"].InnerText + "|" + xnField.Attributes["Value"].InnerText;
                    iIndex 
++;
                }

            }

            
catch(Exception ee)
            
{
                
throw new ArgumentOutOfRangeException(@"AjaxSearchDataCollection.xml","/Table[@value='"+table+"']:指定的接点不存在,请重新定义XML文档!");
            }

            
return strArrFields;
        }


        
/// <summary>
        
/// 根据字段获取对应的操作符列表
        
/// </summary>
        
/// <param name="field"></param>
        
/// <returns></returns>

        [AjaxPro.AjaxMethod]
        
public string[] GetOpts(string table,string field)
        
{
            
string strTypeofField = GetTypeofField(table,field);

            
switch(strTypeofField)
            
{
                
case "char":
                    
return CHAR_OP;
                    
break;
                
case "int":
                    
if(GetEnumField(table,field)==null)
                    
{
                        
return NUM_OP;
                    }

                    
else
                    
{
                        
return (new string[]{"是|=","不是|<>"});
                    }

                    
                    
break;
                
case "decimal":
                    
return NUM_OP;
                    
break;
                
case "datetime":
                    
return DATE_OP;
                    
break;
                
default:
                    
return (new string[]{"等于|="});
                    
break;
            }

        }


        
/// <summary>
        
/// 根据字段获取对应的可能存在的枚举值列表
        
/// </summary>
        
/// <param name="field"></param>
        
/// <returns></returns>

        [AjaxPro.AjaxMethod]
        
public string[] GetEnums(string table,string field)
        
{
            
string strTypeofField = GetTypeofField(table,field);
            
string[] enums = GetEnumField(table,field);

            
int iLen = enums==null?0:enums.Length;

            
string[] result = new string[iLen+1];

            result[
0= strTypeofField;

            
if (iLen == 0)
            
{
                
return result;
            }


            
int iIndex = 1;

            
foreach(string str in enums)
            
{
                result[iIndex] 
= str;
                iIndex 
++;
            }


            
return result;

        }


        
内部支持脚本
    }

}

2.AjaxSearchDataCollection.xml 

<?xml version="1.0" encoding="utf-8" ?>
<Fields>
    
<Table Name="产品表" value="employee">
        
<Field Name="雇佣号" Value="emp_id" DataType="char">
        
</Field>
        
<Field Name="名字" Value="fname" DataType="char">
            
<Enum SqlSyntax="" TextField="" ValueField=""></Enum>
        
</Field>
        
<Field Name="工作号" Value="job_id" DataType="int">
            
<Enum SqlSyntax="SELECT job_id,job_desc FROM jobs" TextField="job_desc" ValueField="job_id"></Enum>
        
</Field>
        
<Field Name="工作**" Value="job_lvl" DataType="int">
        
</Field>
        
<Field Name="雇佣日期" Value="hire_date" DataType="datetime">
        
</Field>
    
</Table>
</Fields>

继续阅读
3.AjaxSearchClientScript.js
var tbPanel            = null;        //用于展示组合条件的表控件
var tableName        = null;        //记录查询的表
var fieldsInfo        = null;        //保存从服务端取得的对应表的字段信息
var no                = 0;        //为防止出现重复id保存的计数器
//
添加条件编辑行

function addTerm(tbPanelId)
{
    tbPanel 
= document.getElementById(tbPanelId);
    
if(tableName == null) tableName = document.getElementById("transValue").value;
    
    
// 添加一待填模板行
    var row    = tbPanel.insertRow();
    no
++;
    row.id 
= "row"+no;
    
    
//*****************************step 1:添加字段选择部分*****************************
    var cell            = row.insertCell();
    
var fieldInputId    = "field"+row.id;
    
var newElement        = document.createElement("<SELECT ID='"+fieldInputId+"' onchange='javascript:getOpts("+row.id+");getInput("+row.id+")'></SELECT>");
    
    cell.insertBefore(newElement);
    
    
//取出字段信息,首次从服务端取得
    if(fieldsInfo == null)
    
{
        fieldsInfo 
= WebUI.AjaxSearchSystem.AjaxSearchMethod.GetFields(tableName.toString()).value;
    }

    
    
//分解字段信息,格式为“字段中文名|字段名”

    
var fieldsInfoLen;
    
var i,index;
    
var fieldValue;
    
    fieldsInfoLen 
= fieldsInfo.length
    
    
for(i=0;i<fieldsInfoLen;i++)
    
{
        fieldValue 
= fieldsInfo[i];
        index 
= fieldValue.indexOf("|");
        newElement.options[newElement.options.length] 
= new Option(fieldValue.substring(0,index),fieldValue.substring(index+1,fieldValue.length));
    }

    
    
//*****************************step 2:提取默认(首个)字段对应的运算符*************
    cell = row.insertCell();
    
var optInputId = "opt" +row.id;
    newElement 
= document.createElement("<SELECT ID='"+optInputId+"' style='WIDTH: 88px'></SELECT>");
    cell.insertBefore(newElement);
    
    getOpts(row);
    
    
//*****************************step 3:设置字段条件值的录入(选择)框***************
    getInput(row);
    
    
//*****************************step 4:生成连接条件选择框***************************
    cell = row.insertCell();
    
var relateInputId = "relate"+row.id;
    newElement 
= document.createElement("<SELECT ID='"+relateInputId+"'></SELECT>");
    cell.insertBefore(newElement);
    newElement.options[newElement.options.length] 
= new Option("并且","And");
    newElement.options[newElement.options.length] 
= new Option("或者","Or");
    
    
//*****************************step 5:生成删除当前行按纽***************************
    cell = row.insertCell();
    
var deleteBtnId = "delete"+row.id;
    newElement 
= document.createElement("<INPUT ID='"+deleteBtnId+"' type='Button' class='redButtonCss' value='删除' onclick='javascript:DelRow("+row.id+")'>");
    cell.insertBefore(newElement);
    
    
//*****************************step 6:生成增加下一行按纽***************************
    cell = row.insertCell();
    
var addBtnId = "add"+row.id;
    newElement 
= document.createElement("<INPUT ID='"+addBtnId+"' type='Button' class='redButtonCss' value='新增' onclick='javascript:addRow("+row.id+")'>");
    cell.insertBefore(newElement);
}


//取得并设置运算符
function getOpts(row)
{
    
var rowId = row.id;
    
    
var fieldSelId = "field"+rowId;
    
var field = document.getElementById(fieldSelId);
    
    
var elemID = "opt"+rowId;
    
var element = document.getElementById(elemID);
    
    
var optInfo = WebUI.AjaxSearchSystem.AjaxSearchMethod.GetOpts(tableName.toString(),field.value).value;
    
var optValue;
    
var j,index;

    
//先移除原有项
    while (element.options.length > 0
    
{    
        element.options.remove(element.options.length
-1);
    }

    
    
for(j=0;j<optInfo.length;j++)
    
{
        optValue 
= optInfo[j];
        index 
= optValue.indexOf("|");
        element.options[element.options.length] 
= new Option(optValue.substring(0,index),optValue.substring(index+1,optValue.length));
    }

}


//设置值录入控件,取得可能存在的枚举值

function getInput(row)
{
    
var rowId = row.id;
    
    
var fieldSelId = "field"+rowId;
    
var field = document.getElementById(fieldSelId);
    
    
var inputId = "input"+rowId;
    
var inputElement = document.getElementById(inputId);
    
    
if (inputElement != null)
    
{
        row.deleteCell(
2);
    }

    
    
//获取可能存在的枚举值

    
var enums = WebUI.AjaxSearchSystem.AjaxSearchMethod.GetEnums(tableName.toString(),field.value).value;
    
var oCell;
    
    
if (enums.length <= 1)//不存在枚举值,使用text
    {
        
var elementSyntax = "<INPUT ID='"+elemID+"' type='text' style='WIDTH: 144px'>";
        oCell 
= row.insertCell(2);
        
var elemID = "input"+rowId;
        
        
switch(enums[0])
        
{
            
case "int":
                elementSyntax 
= "<INPUT ID='"+elemID+"' type='text' style='WIDTH: 144px' onKeyPress='javascript:return controlNumberKeyPress(this)' onKeyUp='return controlNumberOnKeyUp(this)' onfocus='this.select()' style='TEXT-ALIGN:right' onpaste='return !clipboardData.getData(\"text\").match(/\D/);'>"
                
break;
            
case "decimal":
                elementSyntax 
= "<INPUT ID='"+elemID+"' type='text' style='WIDTH: 144px' onKeyPress='javascript:return controlMoneyKeyPress(this)' onKeyUp='return controlMoneyOnKeyUp(this,10,4)' onfocus='this.select()' style='TEXT-ALIGN:right' onpaste='return !clipboardData.getData(\"text\").match(/\D/);'>"
                
break;
            
case "datetime":
                elementSyntax 
= "<INPUT ID='"+elemID+"' type='text' style='WIDTH: 144px' onfocus='setday(this)'onkeypress='return false' onselectstart='return false;' readonly='true' onpaste='return false;' >"
                
break;
            
default:
                elementSyntax 
= "<INPUT ID='"+elemID+"' type='text' style='WIDTH: 144px'>"
                
break;
        }

                
        
var oNewItem = document.createElement(elementSyntax);
        oCell.insertBefore(oNewItem);
    }

    
else //存在枚举值,使用select
    {
        oCell 
= row.insertCell(2);
        
var elemID = "input"+rowId;
        
var oNewItem = document.createElement("<SELECT ID='"+elemID+"' style='WIDTH: 144px'></SELECT>");
        oCell.insertBefore(oNewItem);
        
        
var enumValue;
        
var j,index;
        
var element = document.getElementById(elemID);
        
        
for(j=1;j<enums.length;j++)
        
{
            enumValue 
= enums[j];
            index 
= enumValue.indexOf("|");
            element.options[element.options.length] 
= new Option(enumValue.substring(0,index),enumValue.substring(index+1,enumValue.length));
        }

    }

    
    
//用于值的数据类型
    var fieldType= document.createElement("<INPUT id='type"+rowId+"'type='hidden'>");
    oCell.insertBefore(fieldType);
    document.getElementById(
"type"+rowId).value = enums[0];
}


//删除当前行

function DelRow(row)
{
    
var rowOfIndex = row.rowIndex;
    
    
if(tbPanel.rows.length == 1)
    
{
        
return
    }

    
else if (rowOfIndex == tbPanel.rows.length-1)
    
{
        
//删除最后一行时,保持增加按纽在最后一行

        
var cell = tbPanel.rows[rowOfIndex-1].insertCell();
        
var addBtnId = "add"+tbPanel.rows[rowOfIndex-1].id;
        newElement 
= document.createElement("<INPUT ID='"+addBtnId+"' type='Button' class='redButtonCss' value='新增' onclick='javascript:addRow("+tbPanel.rows[rowOfIndex-1].id+")'>");
        cell.insertBefore(newElement);
    }

    
    tbPanel.deleteRow(rowOfIndex);
}


//新增行

function addRow(row)
{
    row.deleteCell(
5);
    addTerm(tbPanel.id);
}


//得到组合条件
function getCombinTerm()
{
    
var lenOfRows = tbPanel.rows.length;
    
var i=0;
    
var combinTerm = "";
    
var field="",opt="",inputValue="",jointMode="",fieldType="";
    
    
for(i=0;i<lenOfRows;i++)
    
{
        field        
= tbPanel.rows[i].cells[0].childNodes[0].value;
        opt            
= tbPanel.rows[i].cells[1].childNodes[0].value;
        inputValue    
= tbPanel.rows[i].cells[2].childNodes[0].value;
        
        
//判断字段类型
        fieldType =tbPanel.rows[i].cells[2].childNodes[1].value;
        
switch(fieldType)
        
{
            
case "char":
                
if (inputValue == ""|| inputValue == null)
                
{
                    
if(opt.toLowerCase() == "like" || opt.toLowerCase() == "not like")
                    
{
                        inputValue 
= "'%%'";
                    }

                    
else
                    
{
                        inputValue 
= "''";
                    }

                }

                
else
                
{
                    
if(opt.toLowerCase() == "like" || opt.toLowerCase() == "not like")
                    
{
                        inputValue 
= "'%"+inputValue+"%'"
                    }

                    
else
                    
{
                        inputValue 
= "'"+inputValue+"'"
                    }

                    
                }

                
break;
            
case "int":
                
if (inputValue == ""|| inputValue == null) inputValue = 0;
                
break;
            
case "decimal":
                
if (inputValue == ""|| inputValue == null) inputValue = 0;
                
break;
            
case "datetime":
                
if (inputValue == ""|| inputValue == null)
                
{
                    inputValue 
= "''";
                }

                
else
                
{
                    inputValue 
= "'"+inputValue+"'"
                }

                
break;
            
default:
                
break;
        }

        
        
//拼接条件
        combinTerm += " "+tableName+"."+field + " " + opt + " " + inputValue;
        
        
if (i<lenOfRows-1)
        
{
            jointMode    
= tbPanel.rows[i].cells[3].childNodes[0].value;
            combinTerm 
+= " " + jointMode
        }

    }

    
    document.getElementById(
"transValue").value = combinTerm;
    
//return combinTerm;
}


//
var oldValue = "";
var MONEY_FLAG = "";
var THOUSAND_FLAG = ",";

function controlNumberKeyPress(textbox)
{
    oldValue 
= textbox.value.replace(MONEY_FLAG,'');
    
    
return /\d/.test(String.fromCharCode(event.keyCode))||(textbox.value.indexOf('+')<0?String.fromCharCode(event.keyCode)=="+":false);
}


//
function controlMoneyKeyPress(textbox)
{
    
var val = textbox.value.replace(MONEY_FLAG,'');

    
if(isNumber(event.keyCode))
    
{
        
return true;
    }

    
    
if (textbox.value.indexOf('+') < 0)
    
{
        
if ( String.fromCharCode(event.keyCode)=="+" && val.length < 1)
            
return true;
    }


    
if ( textbox.value.indexOf('.') < 0)
    
{
        
if (String.fromCharCode(event.keyCode)==".")
            
return true;
    }


    
return false;
}


//控制金额录入keyUp事件
function controlNumberOnKeyUp(textbox)
{
    
//处理"后退"键
    //alert(event.keyCode);
    if (event.keyCode == 8)
    
{
        
return true;
    }


    
if (String.fromCharCode(event.keyCode) != '\t' && event.keyCode != 13)
    
{
        
if!/\d/.test(String.fromCharCode(event.keyCode)) &&
            event.keyCode 
!= 190/* 如果录入"."则不执行 */ )
        
{
            
//textbox.value = "";
            textbox.value = oldValue;
            oldValue 
= textbox.value;
            
return false;
        }

    }

    
    
return true;
}




//控制金额录入keyUp事件
function controlMoneyOnKeyUp(textbox, intBit , dotBit)
{
    
//处理"后退"键

    
if (event.keyCode == 8)
    
{
        
if (textbox.value.indexOf(MONEY_FLAG) < 0)
        
{
            textbox.value 
= MONEY_FLAG + textbox.value;
        }

        
return true;
    }


    
if (String.fromCharCode(event.keyCode) != '\t' && event.keyCode != 13)
    
{
        
if! isNumber (event.keyCode) &&
            event.keyCode 
!= 190/* 如果录入"."则不执行 */ )
        
{
            
//textbox.value = "";
            textbox.value = MONEY_FLAG + oldValue;
            oldValue 
= textbox.value.replace(MONEY_FLAG,"");
            
return false;
        }


        textbox.value 
= MONEY_FLAG + textbox.value.replace(MONEY_FLAG,'');

        
var val = textbox.value;
        
var dotBehind = "";

        
        val 
= val.replace(MONEY_FLAG,'').replace(/\,/g,'');
        
var iIndex = val.indexOf('.');
        
if (iIndex > 0)
        
{
            
var valDot = val.substring(val.indexOf('.') + 1,val.length);
            
if (iIndex > intBit)
            
{
                val 
= val.substring(0,intBit);
            }

            
            
if (valDot.length > dotBit)
            
{
                textbox.value 
= addThousandFlag(val.substring(0,val.indexOf('.'))) + '.' + valDot.substring(0,dotBit);
                
return;
            }


            dotBehind 
= val.substring(val.indexOf('.'),val.length);
            val 
= val.substring(0,val.indexOf('.'));
        }

        
else if (val.length >= intBit)
        
{
            
if (val.length > intBit)
            
{
                val 
= val.substring(0,intBit);
                val 
= addThousandFlag(val);
            }

            textbox.value 
= val + '.';
            
return;
        }


        
//val = noOtherInfoNumber;
        textbox.value = addThousandFlag(val) + dotBehind;
    }

}



//添加千分位号
function addThousandFlag(val)
{
    
var strTemp = MONEY_FLAG;
    
if (val.length < 4)
    
{
        
return MONEY_FLAG + val;
    }


    
var flag = false;
    
if (val.length % 3 != 0)
    
{
        strTemp 
+= val.substring(0,(val.length % 3));
    }

    
else
        flag 
= true;

    
for(var i = val.length; i > 0; i = i - 1)
    
{
        
if (i % 3 != 0)
        
{
            
continue;
        }

        
        
if (flag)
        
{
            strTemp 
+= val.substring(val.length - i,val.length - i + 3);
            flag 
= false;
        }

        
else
        
{
            strTemp 
+= THOUSAND_FLAG + val.substring(val.length - i,val.length - i + 3);
        }

    }


    
if (strTemp.indexOf(MONEY_FLAG) > 0)
    
{
        strTemp 
= MONEY_FLAG + strTemp.replace(//g,'');
    }


    
return strTemp;
}


//判断是否是数字

function isNumber(param)
{
    
return /\d/.test(String.fromCharCode(param)) ||  (param >= 96 && param <= 105);//加上小键盘处理

}
4.AjaxSeachPage.aspx
<%@ Page language="c#" Codebehind="AjaxSeachPage.aspx.cs" AutoEventWireup="false" Inherits="WebUI.AjaxSearchSystem.AjaxSeachPage" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
    
<HEAD>
        
<title>查询条件</title>
        
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
        
<meta content="C#" name="CODE_LANGUAGE">
        
<meta content="JavaScript" name="vs_defaultClientScript">
        
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
        
<base target="_self">
        
<LINK href="BasicStyle.css" type="text/css" rel="stylesheet">
        
<script language="javascript" src="AjaxSearchClientScript.js">
        
</script>
        
<script language="javascript" src="Calendar.js"></script>
    
</HEAD>
    
<body onload="javascript:addTerm('tbPanel');" MS_POSITIONING="GridLayout">
        
<form id="Form1" method="post" runat="server">
            
<TABLE id="Table1" style="Z-INDEX: 101; LEFT: 0px; POSITION: absolute; TOP: 0px" cellSpacing="0"
                cellPadding
="0" width="100%" border="0">
                
<TR>
                    
<TD style="HEIGHT: 23px" align="center"><asp:button id="btnOK" runat="server" CssClass="redButtonCss" Text="确定"></asp:button><INPUT class="redButtonCss" type="button" value="取消" onclick="javascript:window.close();">
                    
</TD>
                
</TR>
                
<TR>
                    
<TD align="center">
                        
<TABLE id="tbPanel" cellSpacing="0" borderColorDark="aliceblue" cellPadding="0" border="1">
                        
</TABLE>
                    
</TD>
                
</TR>
                
<TR>
                    
<TD align="center"></TD>
                
</TR>
            
</TABLE>
            
<INPUT id="transValue" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 64px" type="hidden"
                name
="Hidden1" runat="server">
        
</form>
    
</body>
</HTML>

AjaxSeachPage.aspx.cs
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.Text;

namespace WebUI.AjaxSearchSystem
{
    /// 
<summary>
    /// AjaxSeachPage 的摘要说明。
    /// 
</summary>
    public class AjaxSeachPage : System.Web.UI.Page
    {
        protected System.Web.UI.HtmlControls.HtmlInputHidden transValue;
        protected System.Web.UI.WebControls.Button btnOK;
        public const string SEARCH_WHERE_SESSION = "SearchWhere";
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            if (!IsPostBack)
            {
                try
                {
                    //tableName.Value = Request.QueryString["table"].Trim();
                    transValue.Value = "employee";
                }
                catch(NullReferenceException ee)
                {
                    throw new ArgumentOutOfRangeException("table","参数不正确,请传入预先定义的表名");
                }
            

                AjaxPro.Utility.RegisterTypeForAjax(typeof(AjaxSearchMethod));
            
                this.btnOK.Attributes.Add("OnClick","getCombinTerm()");
            }
        }

        #region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /// 
<summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// 
</summary>
        private void InitializeComponent()
        {    
            this.btnOK.Click += new System.EventHandler(this.btnOK_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        private void btnOK_Click(object sender, System.EventArgs e)
        {
            Session[SEARCH_WHERE_SESSION] = transValue.Value.Trim();

            Response.Write(ClosePage());
        }

        private string ClosePage()
        {
            StringBuilder js = new StringBuilder();
            js.Append("
<script language=\"JavaScript\">");
            js.Append(
"window.close();");
            js.Append(
"
</script>");
            return js.ToString();
        }
    }
}

运行效果:

下载源码 AjaxGeneralSearch.rar
请大家提出宝贵意见,在此感谢我的同事 命运有自己的梦!提供的帮助!感谢日历控件提供者meizz
原文地址:https://www.cnblogs.com/studio313/p/298924.html