// 校验关系，注意可能抛出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;
}