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)); }