/// <summary>
/// 子查询条件
/// </summary>
/// <param name="whereType"></param>
/// <param name="ColumnName"></param>
/// <param name="termType"></param>
/// <param name="Sql"></param>
public void WhereAddSql(DBWhereType whereType, object ColumnName, DBTermType termType, string Sql)
{
string strWhere = "{4} {0} {1} {2} {3}";
if (termType == DBTermType.None)
{
strWhere = "{4} {0} {3}";
}
WhereStr = string.Format(strWhere, whereType, ColumnName, GetOperatorStr(termType), Sql, WhereStr);
}
public void Where(DBWhereType whereType, object ColumnName, DBTermType termType, object StartValue, object EndValue)
{
object item = null;
if (ColumnName.GetType().Name == "QueryItem")
{
item = new object[] { ColumnName }
}
;
else
{
item = (object[])ColumnName;
}
Where(whereType, (object[])item, termType, StartValue, EndValue);
}
protected string GetOperatorStr(DBTermType terType)
{
string strOperator = "";
switch (terType)
{
case DBTermType.Equal: strOperator = "=";
break;
case DBTermType.NOEqual: strOperator = "<>";
break;
case DBTermType.IN: strOperator = " IN ";
break;
case DBTermType.LIKE: strOperator = " LIKE ";
break;
case DBTermType.NOLIKE: strOperator = " NOT LIKE ";
break;
case DBTermType.GreaterThan: strOperator = " > ";
break;
case DBTermType.GreaterThan_Equal: strOperator = " >= ";
break;
case DBTermType.LessThan: strOperator = " < ";
break;
case DBTermType.LessThan_Equal: strOperator = " <= ";
break;
default:
break;
}
return(strOperator);
}
public void Where(DBWhereType whereType, object[] ColumnName, DBTermType termType, object StartValue, object EndValue)
{
object Value = StartValue;
if (ColumnName.Length == 1)
{
string _ColumnName = ColumnName[0] + "";
string _KeyColumnName = ColumnName[0] + "" + listDara.Count;
if ((termType == DBTermType.Section || termType == DBTermType.Section_Equal))
{
string dtj = termType == DBTermType.Section ? ">" : ">=";
string xtj = termType == DBTermType.Section ? "<" : "<=";
string andStr = "";
if (!string.IsNullOrEmpty(StartValue + ""))
{
andStr += " AND " + _ColumnName + dtj + "@" + _KeyColumnName + "Start";
listDara.Add(new DataParameter("@" + _KeyColumnName + "Start", StartValue));
}
if (!string.IsNullOrEmpty(EndValue + ""))
{
andStr += " AND " + _ColumnName + xtj + "@" + _KeyColumnName + "End";
listDara.Add(new DataParameter("@" + _KeyColumnName + "End", EndValue));
}
WhereStr += " " + whereType + " (1=1" + andStr + ")";
}
else if (Value != null && Value + "" != "")
{
WhereStr += " " + whereType + " " + (termType == DBTermType.NOLIKE ? "ISNULL(" + _ColumnName + ",'')" : _ColumnName) + GetOperatorStr(termType) + "{0}";
object ColumnNameValue = "";
switch (termType)
{
case DBTermType.Equal: ColumnNameValue = "@" + _KeyColumnName;
listDara.Add(new DataParameter("@" + _KeyColumnName, Value));
break;
case DBTermType.NOEqual: ColumnNameValue = "@" + _KeyColumnName;
listDara.Add(new DataParameter("@" + _KeyColumnName, Value));
break;
case DBTermType.IN:
ColumnNameValue = "(";
int js = 0;
foreach (string item in Value.ToString().Split('，'))
{
ColumnNameValue += (js == 0 ? "" : ",") + "@" + _KeyColumnName + js;
listDara.Add(new DataParameter("@" + _KeyColumnName + js, item));
js++;
}
ColumnNameValue += ")";
break;
case DBTermType.LIKE: ColumnNameValue = "@" + _KeyColumnName;
listDara.Add(new DataParameter("@" + _KeyColumnName, "%" + Value + "%"));
break;
case DBTermType.NOLIKE: ColumnNameValue = "@" + _KeyColumnName;
listDara.Add(new DataParameter("@" + _KeyColumnName, "%" + Value + "%"));
break;
default: ColumnNameValue = "@" + _KeyColumnName;
listDara.Add(new DataParameter("@" + _KeyColumnName, Value));
break;
}
WhereStr = string.Format(WhereStr, ColumnNameValue);
}
}
else
{
if (Value != null && Value + "" != "")
{
WhereStr += " " + whereType + " ( {0} )";
string InLogo = "InValue";
string ColumnNameValue = "";
foreach (object _ColumnName in ColumnName)
{
switch (termType)
{
case DBTermType.Equal:
ColumnNameValue += (ColumnNameValue == "" ? "" : " or ") + _ColumnName + GetOperatorStr(termType) + "@" + _ColumnName + InLogo;
listDara.Add(new DataParameter("@" + _ColumnName + InLogo, Value));
break;
case DBTermType.NOEqual:
ColumnNameValue += (ColumnNameValue == "" ? "" : " or ") + _ColumnName + GetOperatorStr(termType) + "@" + _ColumnName + InLogo;
listDara.Add(new DataParameter("@" + _ColumnName + InLogo, Value));
break;
case DBTermType.IN:
ColumnNameValue = (ColumnNameValue == "" ? "" : " or ") + _ColumnName + GetOperatorStr(termType) + "(";
int js = 0;
foreach (string item in Value.ToString().Split('，'))
{
listDara.Add(new DataParameter("@" + _ColumnName + js, item));
js++;
}
ColumnNameValue += ")";
break;
case DBTermType.LIKE:
ColumnNameValue += (ColumnNameValue == "" ? "" : " or ") + _ColumnName + GetOperatorStr(termType) + "@" + _ColumnName + InLogo;
listDara.Add(new DataParameter("@" + _ColumnName + InLogo, "%" + Value + "%"));
break;
case DBTermType.NOLIKE:
ColumnNameValue += (ColumnNameValue == "" ? "" : " or ") + "ISNULL(" + _ColumnName + ",'')" + GetOperatorStr(termType) + "@" + _ColumnName + InLogo;
listDara.Add(new DataParameter("@" + _ColumnName + InLogo, "%" + Value + "%"));
break;
default:
ColumnNameValue += (ColumnNameValue == "" ? "" : " or ") + _ColumnName + GetOperatorStr(termType) + "@" + _ColumnName + InLogo;
listDara.Add(new DataParameter("@" + _ColumnName + InLogo, Value));
break;
}
}
WhereStr = string.Format(WhereStr, ColumnNameValue);
}
}
}
public void Where(DBWhereType whereType, object ColumnName, DBTermType termType, object Value)
{
Where(whereType, ColumnName, termType, Value, "");
}
