// 校验关系,注意可能抛出NoMatch异常 public static int VerifyRelation(ref string strMatch, ref string strRelation, ref string strDataType) { if (strDataType == "number") { if (strMatch == "left" || strMatch == "right" || strMatch == "middle") { strMatch = "exact"; //修改有两种:1.将left换成exact;2.将dataType设为string,我们先按认为dataType优先,将match改为exact NoMatchException ex = new NoMatchException("匹配方式'" + strMatch + "'与数据类型" + strDataType + "不匹配"); throw (ex); } } if (strDataType == "string") { if (strMatch == "left" || strMatch == "right" || strMatch == "middle") { if (strRelation != "=") { //也可以将left或right改为exact,但意义不大‘ strRelation = "="; NoMatchException ex = new NoMatchException("关系操作符'" + strRelation + "'与数据类型" + strDataType + "和匹配方式'" + strMatch + "'不匹配"); throw (ex); } } } return(0); }
// 得到关于key的条件 // parameter: // searchItem 检索信息对象 // nodeCovertQuery 处理检索词的配置节点 // 可能会抛出的异常:NoMatchException(检索方式与数据类型) private int GetKeyCondition(SearchItem searchItem, XmlNode nodeConvertQueryString, XmlNode nodeConvertQueryNumber, out string strKeyCondition, out string strError) { strError = ""; strKeyCondition = ""; QueryUtil.VerifyRelation(ref searchItem.Match,//strMatch, ref searchItem.Relation,//strRelation, ref searchItem.DataType); //strDataType); int nRet; KeysCfg keysCfg = null; nRet = this.GetKeysCfg(out keysCfg, out strError); if (nRet == -1) return -1; string strKeyValue = searchItem.Word.Trim(); if (searchItem.DataType == "string") { if (nodeConvertQueryString != null && keysCfg != null) { List<string> keys = null; nRet = keysCfg.ConvertKeyWithStringNode( null, //dataDom strKeyValue, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { // strError = "检索词配置不合法,不应变成多个。"; strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strKeyValue = keys[0]; } } else if (searchItem.DataType == "number") { if (nodeConvertQueryNumber != null && keysCfg != null) { string strMyKey; nRet = keysCfg.ConvertKeyWithNumberNode( null, strKeyValue, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1 || nRet == 1) return -1; strKeyValue = strMyKey; } } strKeyValue = strKeyValue.Trim(); // 4. 如果strMatch为空,则按"左方一致" if (searchItem.Match == "left" || searchItem.Match == "") { //判断选择的数据类型 if (searchItem.DataType != "string") { NoMatchException ex = new NoMatchException("在匹配方式为left时或为空时,数据类型不匹配,应该为string"); throw (ex); } //这句是保险的,因为上面已抛出异常 int nLength = searchItem.Word.Trim().Length; strKeyCondition = " (substring(keystring,1," + Convert.ToString(nLength) + ")='" + strKeyValue + "') "; } // TODO: 为什么没有支持中间一致?2007/10/9 if (searchItem.Match == "middle") { strError = "对于文件方式的数据库 '"+this.GetCaption("zh")+"',不支持中间一致(middle)匹配方式"; return -1; } //右方一致 if (searchItem.Match == "right") { if (searchItem.DataType != "string") { NoMatchException ex = new NoMatchException("在匹配方式为right时,数据类型不匹配,应该为string"); throw (ex); } //注意这里要改成右方一致 int nLength = searchItem.Word.Trim().Length; strKeyCondition = " (substring(keystring,1," + Convert.ToString(nLength) + ")='" + strKeyValue + "') "; } //精确一致 if (searchItem.Match == "exact") { //从词中汲取,较复杂,注意 if (searchItem.Relation == "draw" || searchItem.Relation == "range") { string strStartText; string strEndText; bool bRet = StringUtil.SplitRangeEx(searchItem.Word, out strStartText, out strEndText); //先按"-"算 if (bRet == true) { if (searchItem.DataType == "string") { if (nodeConvertQueryString != null && keysCfg != null) { // 加工首 List<string> keys = null; nRet = keysCfg.ConvertKeyWithStringNode(null,//dataDom strStartText, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strStartText = keys[0]; // 加工尾 nRet = keysCfg.ConvertKeyWithStringNode(null,//dataDom strEndText, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strEndText = keys[0]; } strKeyCondition = " keystring >= '" + strStartText + "' and keystring<= '" + strEndText + "'"; } else if (searchItem.DataType == "number") { if (nodeConvertQueryNumber != null && keysCfg != null) { // 首 string strMyKey; nRet = keysCfg.ConvertKeyWithNumberNode( null, strStartText, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1 || nRet == 1) return -1; strStartText = strMyKey; // 尾 nRet = keysCfg.ConvertKeyWithNumberNode( null, strEndText, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1 || nRet == 1) return -1; strEndText = strMyKey; } strKeyCondition = " keystringnum >= " + strStartText + " and keystringnum<= " + strEndText + ""; } } else // 再算 比较符号 { string strOperator; string strRealText; StringUtil.GetPartCondition(searchItem.Word, out strOperator, out strRealText); //SQL与Xpath比较运算符的差别 if (strOperator == "<>") strOperator = "!="; if (searchItem.DataType == "string") { if (nodeConvertQueryString != null && keysCfg != null) { List<string> keys = null; nRet = keysCfg.ConvertKeyWithStringNode(null,//dataDom strRealText, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strRealText = keys[0]; } strKeyCondition = "keystring" + strOperator + "'" + strRealText + "'"; } else if (searchItem.DataType == "number") { if (nodeConvertQueryNumber != null && keysCfg != null) { string strMyKey; nRet = keysCfg.ConvertKeyWithNumberNode( null, strRealText, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1 || nRet == 1) return -1; strRealText = strMyKey; } strKeyCondition = "keystringnum" + strOperator + strRealText + " and keystringnum!=-1"; } } } else { // 当关系操作符为空为,按等于算 if (searchItem.Relation == "") searchItem.Relation = "="; if (searchItem.Relation == "<>") searchItem.Relation = "!="; if (searchItem.DataType == "string") { strKeyCondition = " keystring " + searchItem.Relation + "'" + strKeyValue + "'"; } else if (searchItem.DataType == "number") { strKeyCondition = "keystringnum" + searchItem.Relation + "" + strKeyValue + ""; } } } return 0; }
// 得到检索条件,私有函数,被SearchByUnion()函数调 // 可能会抛出的异常:NoMatchException(检索方式与数据类型) // parameters: // searchItem SearchItem对象 // nodeConvertQueryString 字符串型检索词的处理信息节点 // nodeConvertQueryNumber 数值型检索词的处理信息节点 // strPostfix Sql命令参数名称后缀,以便多个参数合在一起时区分 // aParameter 参数数组 // strKeyCondition out参数,返回Sql检索式条件部分 // strError out参数,返回出错信息 // return: // -1 出错 // 0 成功 // 线:不安全 // ???该函数抛出异常的处理不太顺 private int GetKeyCondition(SearchItem searchItem, XmlNode nodeConvertQueryString, XmlNode nodeConvertQueryNumber, string strPostfix, ref List<SqlParameter> aSqlParameter, out string strKeyCondition, out string strError) { strKeyCondition = ""; strError = ""; //检索三项是否有矛盾,该函数可能会抛出NoMatchException异常 QueryUtil.VerifyRelation(ref searchItem.Match, ref searchItem.Relation, ref searchItem.DataType); int nRet = 0; KeysCfg keysCfg = null; nRet = this.GetKeysCfg(out keysCfg, out strError); if (nRet == -1) return -1; //3.根据数据类型,对检索词进行加工 string strKeyValue = searchItem.Word.Trim(); if (searchItem.DataType == "string") //字符类型调字符的配置,对检索词进行加工 { if (nodeConvertQueryString != null && keysCfg != null) { List<string> keys = null; nRet = keysCfg.ConvertKeyWithStringNode( null,//dataDom strKeyValue, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strKeyValue = keys[0]; } } else if (searchItem.DataType == "number") //数字型调数字格式的配置,对检索词进行加工 { if (nodeConvertQueryNumber != null && keysCfg != null) { string strMyKey; nRet = KeysCfg.ConvertKeyWithNumberNode( strKeyValue, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1) return -1; strKeyValue = strMyKey; } } string strParameterName; //4.根据match的值,分别得到不同的检索表达式 if (searchItem.Match == "left" || searchItem.Match == "") //如果strMatch为空,则按"左方一致" { //其实一开始就已经检查了三顶是否矛盾,如果有矛盾抛出抛异,这里重复检查无害,更严格 if (searchItem.DataType != "string") { NoMatchException ex = new NoMatchException("在匹配方式值为left或空时,与数据类型值" + searchItem.DataType + "矛盾,数据类型应该为string"); throw (ex); } strParameterName = "@keyValue" + strPostfix; strKeyCondition = "keystring LIKE " + strParameterName + " "; SqlParameter temp = new SqlParameter(strParameterName, SqlDbType.NVarChar); temp.Value = strKeyValue + "%"; aSqlParameter.Add(temp); } else if (searchItem.Match == "middle") { //其实一开始就已经检查了三顶是否矛盾,如果有矛盾抛出抛异,这里重复检查无害,更严格 if (searchItem.DataType != "string") { NoMatchException ex = new NoMatchException("在匹配方式值为middle或空时,与数据类型值" + searchItem.DataType + "矛盾,数据类型应该为string"); throw (ex); } strParameterName = "@keyValue" + strPostfix; strKeyCondition = "keystring LIKE " + strParameterName + " "; //N'%" + strKeyValue + "'"; SqlParameter temp = new SqlParameter(strParameterName, SqlDbType.NVarChar); temp.Value = "%" + strKeyValue + "%"; aSqlParameter.Add(temp); } else if (searchItem.Match == "right") { //其实一开始就已经检查了三顶是否矛盾,如果有矛盾抛出抛异,这里重复检查无害,更严格 if (searchItem.DataType != "string") { NoMatchException ex = new NoMatchException("在匹配方式值为left或空时,与数据类型值" + searchItem.DataType + "矛盾,数据类型应该为string"); throw (ex); } strParameterName = "@keyValue" + strPostfix; strKeyCondition = "keystring LIKE " + strParameterName + " "; //N'%" + strKeyValue + "'"; SqlParameter temp = new SqlParameter(strParameterName, SqlDbType.NVarChar); temp.Value = "%" + strKeyValue; aSqlParameter.Add(temp); } else if (searchItem.Match == "exact") //先看match,再看relation,最后看dataType { //从词中汲取,较复杂,注意 if (searchItem.Relation == "draw") { int nPosition; nPosition = searchItem.Word.IndexOf("-"); //应按"-"算 if (nPosition >= 0) { string strStartText; string strEndText; StringUtil.SplitRange(searchItem.Word, out strStartText, out strEndText); if (searchItem.DataType == "string") { if (nodeConvertQueryString != null && keysCfg != null) { // 加工首 List<string> keys = null; nRet = keysCfg.ConvertKeyWithStringNode( null,//dataDom strStartText, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strStartText = keys[0]; // 加工尾 nRet = keysCfg.ConvertKeyWithStringNode( null,//dataDom strEndText, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strEndText = keys[0]; } string strParameterMinName = "@keyValueMin" + strPostfix; string strParameterManName = "@keyValueMan" + strPostfix; strKeyCondition = " " + strParameterMinName + " <=keystring and keystring<= " + strParameterManName + " "; SqlParameter temp = new SqlParameter(strParameterMinName, SqlDbType.NVarChar); temp.Value = strStartText; aSqlParameter.Add(temp); temp = new SqlParameter(strParameterManName, SqlDbType.NVarChar); temp.Value = strEndText; aSqlParameter.Add(temp); } else if (searchItem.DataType == "number") { if (nodeConvertQueryNumber != null && keysCfg != null) { // 首 string strMyKey; nRet = KeysCfg.ConvertKeyWithNumberNode( strStartText, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1) return -1; strStartText = strMyKey; // 尾 nRet = KeysCfg.ConvertKeyWithNumberNode( strEndText, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1) return -1; strEndText = strMyKey; } strKeyCondition = strStartText + " <= keystringnum and keystringnum <= " + strEndText + " and keystringnum <> -1"; } } else { string strOperator; string strRealText; //如果词中没有包含关系符,则按=号算 StringUtil.GetPartCondition(searchItem.Word, out strOperator, out strRealText); if (strOperator == "!=") strOperator = "<>"; if (searchItem.DataType == "string") { if (nodeConvertQueryString != null && keysCfg != null) { List<string> keys = null; nRet = keysCfg.ConvertKeyWithStringNode( null,//dataDom strRealText, nodeConvertQueryString, out keys, out strError); if (nRet == -1) return -1; if (keys.Count != 1) { strError = "不支持把检索词通过'split'样式加工成多个."; return -1; } strRealText = keys[0]; } strParameterName = "@keyValue" + strPostfix; strKeyCondition = " keystring" + strOperator + " " + strParameterName + " "; SqlParameter temp = new SqlParameter(strParameterName, SqlDbType.NVarChar); temp.Value = strRealText; aSqlParameter.Add(temp); } else if (searchItem.DataType == "number") { if (nodeConvertQueryNumber != null && keysCfg != null) { string strMyKey; nRet = KeysCfg.ConvertKeyWithNumberNode( strRealText, nodeConvertQueryNumber, out strMyKey, out strError); if (nRet == -1) return -1; strRealText = strMyKey; } strKeyCondition = " keystringnum" + strOperator + strRealText + " and keystringnum <> -1"; } } } else //普通的关系操作符 { //当关系操作符为空为,按等于算 if (searchItem.Relation == "") searchItem.Relation = "="; if (searchItem.Relation == "!=") searchItem.Relation = "<>"; if (searchItem.DataType == "string") { strParameterName = "@keyValue" + strPostfix; strKeyCondition = " keystring " + searchItem.Relation + " " + strParameterName + " "; SqlParameter temp = new SqlParameter(strParameterName, SqlDbType.NVarChar); temp.Value = strKeyValue; aSqlParameter.Add(temp); } else if (searchItem.DataType == "number") { strKeyCondition = " keystringnum " + searchItem.Relation + strKeyValue + " and keystringnum <> -1"; } } } return 0; }
// 校验关系,注意可能抛出NoMatch异常 public static int VerifyRelation(ref string strMatch, ref string strRelation, ref string strDataType) { if (strDataType == "number") { if (strMatch == "left" || strMatch == "right" || strMatch == "middle") { NoMatchException ex = new NoMatchException("匹配方式'" + strMatch + "'与数据类型" + strDataType + "不匹配"); strMatch = "exact"; //修改有两种:1.将left换成exact;2.将dataType设为string,我们先按认为dataType优先,将match改为exact throw (ex); } } if (strDataType == "string") { if (strMatch == "left" || strMatch == "right" || strMatch == "middle") { if (strRelation != "=") { NoMatchException ex = new NoMatchException("关系操作符'" + strRelation + "'与数据类型" + strDataType + "和匹配方式'" + strMatch + "'不匹配"); //也可以将left或right改为exact,但意义不大‘ strRelation = "="; throw (ex); } } } return 0; }