asp.net通用查询模块设计
前言
自从上次狂喷了devexpress for asp.net面向互联网的app的各种不合理，好像骂的dev无处容身了，不过说实话，dev在做互联网的app时，生成的代码确实很多，在服务器解析方面，效率肯定不是很高的，这是使用过dev控件的伙计们都有目共睹之处。难道一款被微软官方推荐的dev真他妈的就这么烂吗？我看未必，好钢你得用到刀刃上，你想杀只老母鸡吃，但是你要用棒槌把鸡打死了再煮，估计鸡死了也就成了肉酱了，那这只鸡杀的就太没水准了，你得用锋利的小刀割了老母鸡喉咙便是。使用dev做内网的一些应用，那才叫高效率，样式丰富，统一，大方，美观，是不可多得的选择。
功能介绍
今天闲暇，做一点学习笔记，针对asp.net的通用查询做了一个查询模块，代码可能略有粗糙，见谅。这里我要讲讲为什么在asp.net里为什么要用通用查询，这里我是有原因的。
- 查询字段比较多时，为了在页面不占用太大空间
- 查询字段容易变更，为了维护方便，不用更改主程序代码
- 主表子表孙表以及以下节点的字段都可能需要查询，提供更灵活的支持
以上是预览图片，左边下拉是字段，右边依次是比较符，比较值，增加条件(and)，或条件(or)，取消，清空当前选择的字段比较符信息，清楚所有条件
中间显示的是每次操作增加，或，清楚后的条件数据绑定，这里用的是Repeater
查询：将条件输出到页面
这里的查询字段根据业务需要归纳为以下比较类型
- formNo:用于单号等字段比较，对应sql: where FormNo=’’
- string:用于字符串模糊查询，对应sql: where ClientName like ‘%凡客%’
- date:用于日期比较，对应>=,<=,=
- select:用于状态等类似字段比较，其实就是=，只不过是用下拉框显示
有了以上基础，当前端操作条件组合时，后台产生对应的sql拼接
设计
具体问题，具体分析，怎么样才能从面向对象的角度更清晰的设计出模块功能呢？首先我们来分解：
一个查询字段包含如下信息：
- fieldName:字段名，如ClientName
- caption:前端显示名，如客户名称
- dataType:业务数据类型，如like
- groupSeq:组别序号，如1，这里指的是表的级别，可以自定义，例如客户表是主表，那么客户下面可能有对应的产品，产品下面又对应很多参数，这里的组别ID根据需要自定义
那么我们可以把这些条件信息用以下xml来表示
例如：
字段配置
<fields> <field name="Req_No" caption="申请单号" dataType="formNo" groupSeq="1" /> <field name="Operate_Time" caption="送检日期" dataType="date" groupSeq="1" /> <field name="Send_CustName" caption="委托单位" dataType="string" groupSeq="1" /> <field name="NULL" caption="区域" dataType="select" groupSeq="1" > <items> <item value="Local_Name = 'BJ'" text="北京" /> <item value="Local_Name = 'CQ'" text="重庆" /> <item value="Local_Name = 'SH'" text="上海" /> </items> </field> </fields>
ok，字段属性就这么多了，比较符也很简单
比较符配置
<operators dataType="formNo"> <operator value="equal" text="等于" dbValue="=" selected="true" /> </operators> <operators dataType="select"> <operator value="equal" text="等于" dbValue="=" selected="true" /> </operators> <operators dataType="number"> <operator value="equal" text="等于" dbValue="=" selected="true" /> </operators> <operators dataType="string"> <operator value="equal" text="等于" dbValue="=" selected="false" /> <operator value="contains" text="包含" dbValue="like" selected="true" /> <operator value="notContains" text="不包含" dbValue="not like" selected="false" /> </operators> <operators dataType="date"> <operator value="equal" text="等于" dbValue="=" selected="false" /> <operator value="more_equal" text="大于等于" dbValue=">=" selected="true" /> <operator value="less_equal" text="小于等于" dbValue="<=" selected="false" /> </operators>
以上是配置文件的设计，前端借助dev的华丽丽的样式，也毫不费功夫
.aspx
<asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <table> <tr> <td> <dx:ASPxComboBox ID="cbField" runat="server" AutoPostBack="True" OnSelectedIndexChanged="cbField_SelectedIndexChanged"> </dx:ASPxComboBox> </td> <td> <dx:ASPxComboBox ID="cbOperator" runat="server" ValueType="System.String" Width="80px" OnSelectedIndexChanged="cbOperator_SelectedIndexChanged"> </dx:ASPxComboBox> </td> <td> <dx:ASPxTextBox ID="txtValue" runat="server" Width="170px"> </dx:ASPxTextBox> <dx:ASPxComboBox ID="cbValue" runat="server" Visible="false" ValueType="System.String"> </dx:ASPxComboBox> <dx:ASPxDateEdit ID="dtValue" runat="server" Visible="false"> </dx:ASPxDateEdit> </td> <td> <dx:ASPxButton ID="btnAdd" runat="server" Text="增加" Width="60" ClientInstanceName="btn_Cdt_Add" OnClick="btnAdd_Click"> </dx:ASPxButton> </td> <td> <dx:ASPxButton ID="btnOr" runat="server" Text="或" Width="60" ClientInstanceName="btn_Cdt_Or" OnClick="btnOr_Click"> </dx:ASPxButton> </td> <td> <dx:ASPxButton ID="btnCancel" runat="server" Text="取消" Width="60" ClientInstanceName="btn_Cdt_Cancel" OnClick="btnCancel_Click"> </dx:ASPxButton> </td> <td> <dx:ASPxButton ID="btnClear" runat="server" Text="清除" Width="60" ClientInstanceName="btn_Cdt_Clear" OnClick="btnClear_Click"> </dx:ASPxButton> </td> </tr> <tr> <td colspan="3"> <asp:Repeater ID="rpConditionList" runat="server"> <ItemTemplate> <table> <tr> <td> <dx:ASPxLabel ID="ASPxLabel3" runat="server" Text='<%# Eval("Index") %>' Visible="false" /> <dx:ASPxLabel ID="lblRelation" runat="server" Text='<%# Eval("Relation") %>' /> <dx:ASPxLabel ID="lblCondition" runat="server" Text='<%# Eval("Caption") %>' /> <dx:ASPxLabel ID="ASPxLabel1" runat="server" Text='<%# Eval("Operator.Text") %>' /> <dx:ASPxLabel ID="ASPxLabel2" runat="server" Text='<%# Eval("Value") %>' /> </td> <td> <dx:ASPxButton ID="btnDelete" runat="server" OnClick="btnDelete_Click" CommandArgument='<%# Eval("Index") %>' Text="×" Width="20" Height="20" /> </td> </tr> </table> </ItemTemplate> </asp:Repeater> </td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> </table> </ContentTemplate> </asp:UpdatePanel>
以上字段控件只需要3个，TextBox,ComboBox,DateEdit
Repeater用来显示组合后的条件
ok，配置和前端搞定，我们来写后台代码
字段实体和操作符实体类，以及用到的枚举
Entity&Enum
public class CdtEntity { public CdtEntity() { } public CdtEntity(string field, string caption, string dataType, int groupSeq, List<Operator> operatorList, object value) { this.Field = field; this.Caption = caption; this.DataType = dataType; this.GroupSeq = groupSeq; this.OperatorList = operatorList; this.Value = value; } public CdtEntity(int index, string field, string caption, string dataType, int groupSeq, Operator _operator, object input, string relation) { this.Index = index; this.Field = field; this.Caption = caption; this.DataType = dataType; this.GroupSeq = groupSeq; this.Operator = _operator; this.Value = input; this.Relation = relation; } public CdtEntity Copy(CdtEntity cdtEntity) { CdtEntity model = new CdtEntity(); model.Field = cdtEntity.Field; model.Caption = cdtEntity.Caption; model.DataType = cdtEntity.DataType; model.Operator = cdtEntity.Operator; model.OperatorList = cdtEntity.OperatorList; model.Value = cdtEntity.Value; model.Relation = cdtEntity.Relation; model.Index = cdtEntity.Index; model.GroupSeq = cdtEntity.GroupSeq; return model; } public string Field { get; set; } public string Caption { get; set; } public string DataType { get; set; } public Operator Operator { get; set; } public List<Operator> OperatorList { get; set; } public object Value { get; set; } /// <summary> /// 条件关联符号：and or /// </summary> public string Relation { get; set; } /// <summary> /// 索引 /// </summary> public int Index { get; set; } /// <summary> /// 组序号 /// </summary> public int GroupSeq { get; set; } } /// <summary> /// 操作符 /// </summary> public class Operator { public Operator() { } public Operator(string value, string text, string dbValue, bool selected) { this.Value = value; this.Text = text; this.DbValue = dbValue; this.Selected = selected; } public string Value { get; set; } public string Text { get; set; } public string DbValue { get; set; } public bool Selected { get; set; } } /// <summary> /// 下拉框数据实体 /// </summary> public class Item { public string Value { get; set; } public string Text { get; set; } } /// <summary> /// 操作符枚举 /// </summary> public enum Enum_Operator { 等于 = 1, 不等于 = 2, 大于 = 3, 大于等于 = 4, 小于 = 5, 小于等于 = 6 } /// <summary> /// 数据类型 /// </summary> public enum DataType { FormNo = 1, String = 2, Number = 3, Date = 4, Select = 5 }
因为这里用的dev服务器控件，所以在字段选择改变后，要执行后台事件，要做的事情就是将该字段配置所属的数据类型下的比较符加载出来，并根据数据类型改变填写值的控件
点击增加按钮时将当前字段的信息存入CdtEntity实体对象，并添加到List<CdtEntity>集合，然后拼接成SQL字符串，这里加List集合的原因是为了处理更方便，因为加的字段条件也要进行增删操作
以下将核心代码都贴出来，当然有的地方写的有点冗余了，后面改改吧，主要是为了分享这种思想，刚接触asp.net的童鞋们可以看看，大牛们可以绕道了。
业务代码
#region 输入参数 public string ConfigPath { get { return Convert.ToString(ViewState["ConfigPath"]); } set { ViewState["ConfigPath"] = value; } } #endregion #region 输出参数 public string StrWhere { get { return Convert.ToString(ViewState["StrWhere"]); } set { ViewState["StrWhere"] = value; } } #endregion #region 属性 /// <summary> /// 输出类型对应的比较符集合 /// string:dataType /// List<Operator>:比较符集合 /// </summary> private Dictionary<string, List<Operator>> Operators { get { return ViewState["Operators"] as Dictionary<string, List<Operator>>; } set { ViewState["Operators"] = value; } } /// <summary> /// 初始化字段信息 /// </summary> private List<CdtEntity> CdtEntityList { get { return ViewState["CdtEntityList"] as List<CdtEntity>; } set { ViewState["CdtEntityList"] = value; } } /// <summary> /// 当前选择字段信息 /// </summary> private CdtEntity CurrentCdtEntity { get { return ViewState["CurrentCdtEntity"] as CdtEntity; } set { ViewState["CurrentCdtEntity"] = value; } } /// <summary> /// 当前比较符 /// </summary> private Operator CurrentOperator { get { return ViewState["CurrentOperator"] as Operator; } set { ViewState["CurrentOperator"] = value; } } /// <summary> /// 构造的查询条件集合 /// </summary> private List<CdtEntity> StrWhereList { get { return ViewState["StrWhereList"] as List<CdtEntity>; } set { ViewState["StrWhereList"] = value; } } #endregion #region 页面事件 /// <summary> /// 字段选择索引改变 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void cbField_SelectedIndexChanged(object sender, EventArgs e) { this.cbOperator.Items.Clear(); string selectedValue = (sender as ASPxComboBox).Value.ToString(); CdtEntity entity = CdtEntityList.Where(c => c.Field == selectedValue).FirstOrDefault(); CurrentCdtEntity = entity; ListEditItem editItem; int selectedIndex = 0; foreach (var item in entity.OperatorList) { editItem = new ListEditItem(item.Text, item.Value); this.cbOperator.Items.Add(editItem); if (item.Selected) { cbOperator.SelectedIndex = selectedIndex; CurrentOperator = new Operator(cbOperator.Value.ToString(), cbOperator.Text, item.DbValue, false); } selectedIndex++; } switch (entity.DataType) { case "formNo": case "number": case "string": txtValue.Visible = true; dtValue.Visible = false; cbValue.Visible = false; break; case "date": txtValue.Visible = false; dtValue.Visible = true; cbValue.Visible = false; break; case "select": txtValue.Visible = false; dtValue.Visible = false; cbValue.Visible = true; cbValue.Items.Clear(); foreach (Item item in (List<Item>)entity.Value) { cbValue.Items.Add(item.Text, item.Value); } break; default: break; } txtValue.Text = ""; cbValue.SelectedIndex = -1; } /// <summary> /// 操作符改变 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void cbOperator_SelectedIndexChanged(object sender, EventArgs e) { string dbValue = ""; switch (cbOperator.Value.ToString()) { case "equal": dbValue=" = "; break; case "contains": dbValue = " like "; break; case "notContains": dbValue = " not like "; break; case "more_equal": dbValue=" >= "; break; case "less_equal": dbValue=" <= "; break; default: break; } CurrentOperator = new Operator(cbOperator.Value.ToString() ,cbOperator.Text,dbValue,false); } /// <summary> /// 增加 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnAdd_Click(object sender, EventArgs e) { CreateCondition(" and "); } /// <summary> /// 或 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnOr_Click(object sender, EventArgs e) { CreateCondition(" or "); } /// <summary> /// 取消 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnCancel_Click(object sender, EventArgs e) { cbValue.SelectedIndex = -1; cbField.SelectedIndex = 0; txtValue.Text = ""; cbOperator.SelectedIndex = -1; CurrentCdtEntity = null; CurrentOperator = null; } /// <summary> /// 清除所有条件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnClear_Click(object sender, EventArgs e) { StrWhereList.Clear(); JoinCondition(); //绑定到显示控件 BindRepeater(); } /// <summary> /// 删除单个条件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDelete_Click(object sender, EventArgs e) { int index = int.Parse((sender as ASPxButton).CommandArgument); StrWhereList.RemoveAt(index); List<CdtEntity> tmpList = StrWhereList.ToList(); for (int i = 0; i < StrWhereList.Count; i++) { tmpList[i].Index = i; } StrWhereList = tmpList; JoinCondition(); //绑定到显示控件 BindRepeater(); } #endregion #region 方法 private string ValidateInput() { string errorMsg = ""; if (cbField.Value == null || cbField.Value.ToString() == "") { errorMsg += "字段不能为空 "; } if (cbOperator.Value == null || cbOperator.Value.ToString() == "") { errorMsg += "比较符不能为空 "; } if (CurrentCdtEntity != null) { switch (CurrentCdtEntity.DataType) { case "formNo": if (txtValue.Text == "") { errorMsg += "条件不能为空 "; } break; case "string": if (txtValue.Text == "") { errorMsg += "条件不能为空 "; } break; case "date": if (dtValue.Date == null || dtValue.Date.ToString() == "0001/1/1 0:00:00") { errorMsg += "条件不能为空 "; } break; case "select": if (cbValue.Value.ToString() == "") { errorMsg += "条件不能为空 "; } break; default: break; } } return errorMsg; } /// <summary> /// 初始化操作符 /// </summary> private void InitOperators() { Dictionary<string, List<Operator>> dic = new Dictionary<string, List<Operator>>(); XmlDocument doc = new XmlDocument(); doc.Load(ConfigPath); Operator entity; List<Operator> kvList = null; XmlNodeList xmlNodeList = doc.SelectNodes("/configuration/operators"); foreach (XmlNode xmlNode in xmlNodeList) { string dataType = xmlNode.Attributes["dataType"].Value; if (xmlNode.HasChildNodes) { kvList = new List<Operator>(); foreach (XmlNode xmlNode2 in xmlNode.ChildNodes) { entity = new Operator(xmlNode2.Attributes["value"].Value, xmlNode2.Attributes["text"].Value, xmlNode2.Attributes["dbValue"].Value, Convert.ToBoolean(xmlNode2.Attributes["selected"].Value)); kvList.Add(entity); } } dic.Add(dataType, kvList); } Operators = dic; } /// <summary> /// 初始化数据，在页面里调用 /// </summary> public void InitData() { if (ConfigPath == null) { throw new Exception("未指定配置文件路径"); } InitOperators(); CdtEntityList = new List<CdtEntity>(); object value = null; ListEditItem item = null; CdtEntity entity = null; List<Operator> operatorList = null; XmlDocument doc = new XmlDocument(); doc.Load(ConfigPath); XmlNode xmlNode = doc.SelectSingleNode("/configuration/fields"); string dataType = ""; foreach (XmlNode node in xmlNode.ChildNodes) { dataType = node.Attributes["dataType"].Value; if (dataType != "") { operatorList = Operators[dataType]; if (node.HasChildNodes && node.FirstChild.Name == "items" && node.FirstChild.HasChildNodes) { List<Item> itemList = new List<Item>();//select数据类型的数据集合 Item item2 = null; foreach (XmlNode node2 in node.FirstChild.ChildNodes) { item2 = new Item() { Value = node2.Attributes["value"].Value, Text = node2.Attributes["text"].Value }; itemList.Add(item2); } value = itemList; } entity = new CdtEntity(node.Attributes["name"].Value, node.Attributes["caption"].Value, dataType, int.Parse(node.Attributes["groupSeq"].Value), operatorList, value); CdtEntityList.Add(entity); } item = new ListEditItem(node.Attributes["caption"].Value, node.Attributes["name"].Value); this.cbField.Items.Add(item); } cbField.SelectedIndex = 0; } /// <summary> /// 生成条件 /// </summary> /// <param name="relation"></param> private void CreateCondition(string relation) { string errorMsg = ValidateInput(); if (errorMsg != "") { ScriptManager.RegisterStartupScript(this.UpdatePanel1, GetType(), Guid.NewGuid().ToString(), "alert('" + errorMsg + "')", true); return; } if (StrWhereList == null) { StrWhereList = new List<CdtEntity>(); } CdtEntity entity = null; switch (CurrentCdtEntity.DataType) { case "formNo": case "string": case "number": entity = new CdtEntity(StrWhereList.Count, CurrentCdtEntity.Field, CurrentCdtEntity.Caption, CurrentCdtEntity.DataType,CurrentCdtEntity.GroupSeq, CurrentOperator, txtValue.Text, relation); break; case "date": entity = new CdtEntity(StrWhereList.Count, CurrentCdtEntity.Field, CurrentCdtEntity.Caption, CurrentCdtEntity.DataType, CurrentCdtEntity.GroupSeq, CurrentOperator, dtValue.Date.ToString("yyyy-MM-dd"), relation); break; case "select": entity = new CdtEntity(StrWhereList.Count, CurrentCdtEntity.Field, CurrentCdtEntity.Caption, CurrentCdtEntity.DataType, CurrentCdtEntity.GroupSeq, CurrentOperator, new Item() { Value = cbValue.Value.ToString(), Text = cbValue.Text }, relation); break; default: break; } StrWhereList.Add(entity); JoinCondition(); //绑定到显示控件 BindRepeater(); } /// <summary> /// 绑定条件到控件 /// </summary> private void BindRepeater() { List<CdtEntity> ShowList = new List<CdtEntity>(); CdtEntity cModel = null; foreach (CdtEntity item in StrWhereList) { cModel = item.Copy(item); ShowList.Add(cModel); } foreach (CdtEntity item in ShowList) { if (item.DataType == "select") { item.Value = ((Item)item.Value).Text; } } this.rpConditionList.DataSource = ShowList; this.rpConditionList.DataBind(); } /// <summary> /// 生成sql字符串 /// </summary> private void JoinCondition() { StringBuilder sb = new StringBuilder(); int index = 0; foreach (CdtEntity item in StrWhereList) { //第一个条件 if (index == 0) { //如果有包含关系 if (item.Operator.Value == "contains" || item.Operator.Value == "notContains") { if (item.DataType == "select") { sb.AppendFormat("[{0}:" + item.Field + " " + item.Operator.DbValue + " {1} ]", item.GroupSeq, "''%" + ((Item)item.Value).Value + "%''"); } else { sb.AppendFormat("[{0}:" + item.Field + " " + item.Operator.DbValue + " {1} ]", item.GroupSeq, "''%" + item.Value.ToString() + "%''"); } } else//除“包含”相关的以外关系 { //值为下拉类型 if (item.DataType == "select") { //字段为空的情况 if (item.Field.Contains("NULL")) { sb.AppendFormat("[{0}:{1} ]", item.GroupSeq, ((Item)item.Value).Value); } else { sb.AppendFormat("[{0}:" + item.Field + " " + item.Operator.DbValue + " " + "''" + ((Item)item.Value).Value + "''" + " ]", item.GroupSeq); } } else { sb.AppendFormat("[{0}:" + item.Field + " " + item.Operator.DbValue + " " + "''" + item.Value.ToString() + "''" + " ]", item.GroupSeq); } } } else { if (item.Operator.Value == "contains" || item.Operator.Value == "notContains") { if (item.DataType == "select") { sb.AppendFormat(item.Relation + "[{0}:" + " " + item.Field + " " + item.Operator.DbValue + " {1} ]", item.GroupSeq, "''%" + ((Item)item.Value).Value + "%''"); } else { sb.AppendFormat(item.Relation + "[{0}:" + " " + item.Field + " " + item.Operator.DbValue + " {1} ]", item.GroupSeq, "''%" + item.Value.ToString() + "%''"); } } else { //值为下拉类型 if (item.DataType == "select") { if (item.Field.Contains("NULL")) { sb.AppendFormat(item.Relation + "[{0}:{1} ]", item.GroupSeq, ((Item)item.Value).Value); } else { sb.AppendFormat(item.Relation + "[{0}:" + " " + item.Field + " " + item.Operator.DbValue + " " + "''" + ((Item)item.Value).Value + "''" + " ]", item.GroupSeq); } } else { sb.AppendFormat(item.Relation + "[{0}:" + " " + item.Field + " " + item.Operator.DbValue + " " + "''" + item.Value.ToString() + "''" + " ]", item.GroupSeq); } } } index++; } StrWhere = sb.ToString(); } #endregion }
以上控件的代码都有了，页面调用就简单多了
使用
aspx:
<cdt:Condition ID="Condition1" runat="server" />
初始化配置文件：
Condition1.ConfigPath = AppDomain.CurrentDomain.BaseDirectory + "\DetectSelect\Cost.config"; Condition1.InitData();
获取查询条件：
string where = Condition1.StrWhere;
这里得到的条件类似[1:Operate_Time >= ''2013-6-1''] and [1:Send_CustName like '%北京XX有限公司%']
这里是由于我业务上的特殊需要，后台数据实在是太复杂了，不能用一张表或者一个视图来查询数据，条件字段不能从一张表里去取，所以分组查询逐级获取主表主键字段的值存放到临时表再去获取数据。
如果是一般的查询，只要不是很复杂的，业务逻辑可以稍微改的简单一些，这里字符串的拼接操作可不少哦，别看晕了，事实上还是蛮简单的。
效果预览
写完收工。