public static string Format(string str, FunType funType, FunFormat funFormat)
{
if (funType != FunType.Null)
{
string formatstring = "YYYY-MM-DD";
if (funFormat == FunFormat.YYYYMM)
{
formatstring = "YYYY-MM";
}
else if (funFormat == FunFormat.YYYYMMDDHH24MI)
{
formatstring = "YYYY-MM-DD HH24:MI";
}
else if (funFormat == FunFormat.YYYYMMDDHH24MISS)
{
formatstring = "YYYY-MM-DD HH24:MI:SS";
}
return(string.Format(" {0}({1},'{2}')", funType.ToString(), str, formatstring));
}
return(str);
}
public static void BetweenAnd <T>(T value1, T value2, string fieldName, FunType funType, FunFormat funFormat, IDbCommand cmd)
{
Add <T>(value1, value2, fieldName, funType, funFormat, cmd);
}
public static void AddMax <T>(T value, string fieldName, FunType funType, FunFormat funFormat, IDbCommand cmd)
{
Add <T>(value, fieldName, funType, funFormat, cmd, OperateType.Max);
}
//for between...and
public static void Add <T>(T value1, T value2, string fieldName, FunType funType, FunFormat funFormat, IDbCommand cmd)
{
if (value1 != null && value2 != null)
{
IDbDataParameter parameter1;
IDbDataParameter parameter2;
string str1 = fieldName.Replace('.', '_') + "_1";
string str2 = string.Format(":{0}", str1);
string str3 = fieldName.Replace('.', '_') + "_2";
string str4 = string.Format(":{0}", str3);
string str = string.Format(" AND {0} BETWEEN {1} AND {2}", fieldName, str2, str4);
if (funType != FunType.Null)
{
string formatstring = "YYYY-MM-DD";
if (funFormat == FunFormat.YYYYMM)
{
formatstring = "YYYY-MM";
}
else if (funFormat == FunFormat.YYYYMMDDHH24MI)
{
formatstring = "YYYY-MM-DD HH24:MI";
}
else if (funFormat == FunFormat.YYYYMMDDHH24MISS)
{
formatstring = "YYYY-MM-DD HH24:MI:SS";
}
str = string.Format(" AND {0} BETWEEN {1}({2},'{3}') AND {1}({4},'{3}')", fieldName, funType.ToString(), str2, formatstring, str4);
}
if (value1.GetType() == typeof(string) && value2.GetType() == typeof(string))
{
object obj1 = value1;
object obj2 = value2;
if (string.IsNullOrEmpty((string)obj1) || string.IsNullOrEmpty((string)obj2))
{
Conditions.Add("");
return;
}
parameter1 = Driver.GenerateParameter(str2, (DbType)13);
parameter2 = Driver.GenerateParameter(str4, (DbType)13);
}
else if (value1.GetType() == typeof(int) && value2.GetType() == typeof(int))
{
parameter1 = Driver.GenerateParameter(str2, (DbType)2);
parameter2 = Driver.GenerateParameter(str4, (DbType)2);
}
else if (value1.GetType() == typeof(float) && value2.GetType() == typeof(float))
{
parameter1 = Driver.GenerateParameter(str2, (DbType)2);
parameter2 = Driver.GenerateParameter(str4, (DbType)2);
}
else if (value1.GetType() == typeof(DateTime) && value2.GetType() == typeof(DateTime))
{
parameter1 = Driver.GenerateParameter(str2, (DbType)11);
parameter2 = Driver.GenerateParameter(str4, (DbType)11);
}
else
{
if (value1.GetType() != typeof(decimal) || value2.GetType() != typeof(decimal))
{
throw new Exception("不支持的参数类型");
}
parameter1 = Driver.GenerateParameter(str2, (DbType)7);
parameter2 = Driver.GenerateParameter(str4, (DbType)7);
}
parameter1.Value = value1;
parameter2.Value = value2;
cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);
//sqltext.Replace("{0}", str.ToString());
Conditions.Add(str);
}
}
public static void Add <T>(T value, string fieldName, FunType funType, FunFormat funFormat, IDbCommand cmd, OperateType operateType)
{
if (value != null)
{
string str;
IDbDataParameter parameter;
string str2;
string str3 = Regex.Replace(fieldName, "[.,\\(\\)\\s]", "", RegexOptions.IgnoreCase);
switch (operateType)
{
case OperateType.NotEqual:
str2 = string.Format(":{0}", str3);
str = string.Format(" AND {0}<>{1}", fieldName, Format(str2, funType, funFormat));
break;
case OperateType.Min:
str2 = string.Format(":Min{0}", str3);
str = string.Format(" AND {0}>={1}", fieldName, Format(str2, funType, funFormat));
break;
case OperateType.Max:
str2 = string.Format(":Max{0}", str3);
str = string.Format(" AND {0}<={1}", fieldName, Format(str2, funType, funFormat));
break;
case OperateType.Like:
str2 = string.Format(":{0}", str3);
str = string.Format(" AND {0} like '%'||{1}||'%'", fieldName, Format(str2, funType, funFormat));
break;
case OperateType.Or:
str2 = string.Format(":{0}", str3);
str = string.Format(" Or {0}={1}", fieldName, Format(str2, funType, funFormat));
break;
case OperateType.IsNull:
str2 = string.Format(":{0}", str3);
str = string.Format(" AND {0} IS NULL", fieldName);
break;
default:
str2 = string.Format(":{0}", str3);
str = string.Format(" AND {0}={1}", fieldName, Format(str2, funType, funFormat));
break;
}
if (operateType != OperateType.IsNull)
{
if (value.GetType() == typeof(string))
{
object obj2 = value;
if (string.IsNullOrEmpty((string)obj2))
{
Conditions.Add("");
return;
}
parameter = Driver.GenerateParameter(str2, (DbType)13);
}
else if (value.GetType() == typeof(int))
{
parameter = Driver.GenerateParameter(str2, (DbType)2);
}
else if (value.GetType() == typeof(float))
{
parameter = Driver.GenerateParameter(str2, (DbType)2);
}
else if (value.GetType() == typeof(DateTime))
{
parameter = Driver.GenerateParameter(str2, (DbType)11);
}
else
{
if (value.GetType() != typeof(decimal))
{
throw new Exception("不支持的参数类型");
}
parameter = Driver.GenerateParameter(str2, (DbType)7);
}
parameter.Value = value;
cmd.Parameters.Add(parameter);
}
//sqltext.Replace("{0}", str.ToString());
Conditions.Add(str);
}
}
public static void BetweenAnd <T>(T value1, T value2, string fieldName, FunType funType, FunFormat funFormat, List <IDbDataParameter> paramList)
{
Add(value1, value2, fieldName, funType, funFormat, paramList);
}
