private string stringValueToSqlString(object dataValue, MB.Util.DataFilterConditions queryCondition)
{
if (queryCondition == MB.Util.DataFilterConditions.Special)
{
return(dataValue.ToString());
}
//有必要在这里增加一些处理SQL特殊情况的代码，避免SQL漏洞而产生错误
if (queryCondition == MB.Util.DataFilterConditions.Include || queryCondition == MB.Util.DataFilterConditions.NotInclude)
{
return(string.Format("'%{0}%'", formatString(dataValue.ToString())));
}
else if (queryCondition == MB.Util.DataFilterConditions.In || queryCondition == MB.Util.DataFilterConditions.NotIn)
{
string inStr = BuildQueryInSql <string>(dataValue.ToString().Split(','));
return("(" + inStr + ")");
}
else if (queryCondition == MB.Util.DataFilterConditions.BenginsWith)
{
return(string.Format("'{0}%'", formatString(dataValue.ToString())));
}
else if (queryCondition == MB.Util.DataFilterConditions.EndsWith)
{
return(string.Format("'%{0}'", formatString(dataValue.ToString())));
}
else
{
return(string.Format("'{0}'", formatString(dataValue.ToString())));
}
}
/// <summary>
/// 根据查询的条件转换为SQL 查询的字符窜。
/// </summary>
/// <param name="queryCondition"></param>
/// <returns></returns>
public string ConvertConditionToSqlStr(MB.Util.DataFilterConditions queryCondition)
{
switch (queryCondition)
{
case MB.Util.DataFilterConditions.Special:
case MB.Util.DataFilterConditions.Equal:
return(" = ");
case MB.Util.DataFilterConditions.NotEqual:
return(" <> ");
case MB.Util.DataFilterConditions.GreaterOrEqual:
return(" >= ");
case MB.Util.DataFilterConditions.Greater:
return(" > ");
case MB.Util.DataFilterConditions.EndsWith:
case MB.Util.DataFilterConditions.BenginsWith:
case MB.Util.DataFilterConditions.Include:
case MB.Util.DataFilterConditions.Like:
return(" LIKE ");
case MB.Util.DataFilterConditions.NotInclude:
case MB.Util.DataFilterConditions.NotLike:
return(" NOT LIKE ");
case MB.Util.DataFilterConditions.LessOrEqual:
return(" <= ");
case MB.Util.DataFilterConditions.Less:
return(" < ");
case MB.Util.DataFilterConditions.Between:
return(" BETWEEN ");
case MB.Util.DataFilterConditions.NotBetween:
return(" NOT BETWEEN ");
case MB.Util.DataFilterConditions.IsNull:
return(" IS NULL ");
case MB.Util.DataFilterConditions.IsNotNull:
return(" IS NOT NULL ");
case MB.Util.DataFilterConditions.In:
return(" IN ");
case MB.Util.DataFilterConditions.NotIn:
return(" NOT IN ");
default:
return(string.Empty);
}
}
private string valueToSQL(MB.Orm.Enums.DatabaseType dbaseType, string typeName, object dataValue, MB.Util.DataFilterConditions queryCondition, bool isBetweenEnd)
{
if (dataValue == null || dataValue == System.DBNull.Value || dataValue.ToString().Length == 0)
{
return("NULL");
}
if (string.IsNullOrEmpty(typeName))
{
return(stringValueToSqlString(dataValue, queryCondition));
}
//判断是否为FullName 如： System.Int32
int index = typeName.IndexOf('.');
if (index > 0)
{
typeName = typeName.Substring(index + 1, typeName.Length - index - 1);
}
//判断如果是In 查询的话要特殊处理
if (queryCondition == MB.Util.DataFilterConditions.In ||
queryCondition == MB.Util.DataFilterConditions.NotIn ||
queryCondition == MB.Util.DataFilterConditions.Special)
{
return(stringValueToSqlString(dataValue, queryCondition));
}
switch (typeName)
{
case "Int16":
case "Int32":
case "Decimal":
decimal dre = 0;
bool b = decimal.TryParse(dataValue.ToString(), out dre);
return(dre.ToString());
case "Boolean":
if (dataValue != null && (string.Compare(dataValue.ToString(), "TRUE", true) == 0 || dataValue.ToString() == "1"))
{
return("1");
}
else
{
return("0");
}
case "String":
return(stringValueToSqlString(dataValue, queryCondition));
case "DateTime":
case "DateTime?":
if (string.Compare(typeName, "DateTime?", true) == 0)
{
if (dataValue == null)
{
throw new MB.Util.APPException("valueToSQL 传入的时间类型DateTime?的条件是空", Util.APPMessageType.DisplayToUser);
}
}
//临时解决时间查询的问题， 所有时间的查询都把小时、分秒去掉，只保留日期。
//modify by aifang 增加按照配置日期函数查询，支持时分秒 begin 2012-4-6
DateTime tempDate = DateTime.Parse(Convert.ToDateTime(dataValue).ToString()); //DateTime tempDate = DateTime.Parse(Convert.ToDateTime(dataValue).ToShortDateString());//;//Convert.ToDateTime(dataValue);//
//modify by aifang 增加按照配置日期函数查询，支持时分秒 end
//需要完善 //性能影响
var dbType = dbaseType;
bool withTimeFormate = (tempDate.Hour > 0 || tempDate.Minute > 0 || tempDate.Second > 0);
string sysFormate = withTimeFormate ? MB.BaseFrame.SOD.DATE_TIME_FORMATE : MB.BaseFrame.SOD.DATE_WITHOUT_TIME_FORMATE;
string dbFormate = withTimeFormate ? MB.BaseFrame.SOD.DATABASE_DATE_TIME_FORMATE : MB.BaseFrame.SOD.DATABASE_WITHOUT_DATE_TIME_FORMATE;
if (dbType == MB.Orm.Enums.DatabaseType.Oracle)
{
if (isBetweenEnd)
{
string f = "(to_Date('" + tempDate.ToString(sysFormate) + "','" + dbFormate + "'){0})";
f = withTimeFormate ? string.Format(f, "") : string.Format(f, "+ 0.99999"); //变态的方法解决时间格式的问题 (到选择日期的11点59分)
return(f);
}
else
{
return("to_Date('" + tempDate.ToString(sysFormate) + "','" + dbFormate + "')");
}
}
else
{
return("'" + tempDate.ToString(sysFormate) + "'");
}
default:
return(stringValueToSqlString(dataValue, queryCondition));
}
}
//把值转换为SQL 存储的格式
private string valueToSQL(MB.Orm.Enums.DatabaseType dbaseType, string typeName, object dataValue, MB.Util.DataFilterConditions queryCondition)
{
return(valueToSQL(dbaseType, typeName, dataValue, queryCondition, false));
}
