/// <summary> /// 把行中的值替换SQL语句中的参数满足执行的要求 /// </summary> /// <param name="sqlStr"></param> /// <param name="parsValue"></param> /// <returns></returns> public string ReplaceCanExecSqlStringEx(string sqlStr, params object[] parsValue) { MB.Orm.Enums.DatabaseType dbaseType = MB.Orm.Persistence.DatabaseHelper.CreateDatabaseType(); IList <string> parsName = GetSqlStringParamsName(sqlStr); string sql = sqlStr; if (parsName.Count != parsValue.Length) { throw new MB.Util.APPException("在执行SQL语句" + sqlStr + "时获取的参数和传入的参数不一致,请检查。", MB.Util.APPMessageType.SysErrInfo); } for (int i = 0; i < parsName.Count; i++) { object val = parsValue[i]; if (val != null && string.Compare(parsName[i].ToString(), "WHERE", true) != 0) { val = valueToSQL(dbaseType, val.GetType().Name, val, MB.Util.DataFilterConditions.Equal); } else if (val == null) { val = "NULL"; } sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + parsName[i].ToString() + " ", val.ToString() + " "); sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + parsName[i].ToString() + ",", val.ToString() + ","); sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + parsName[i].ToString() + ";", val.ToString() + ";"); sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + parsName[i].ToString() + ")", val.ToString() + ")"); } return(sql); }
//单个参数转换为SQL 语句 private string singleParameterToSql(MB.Orm.Enums.DatabaseType dbaseType, MB.Orm.Mapping.QueryParameterMappings parsMapping, MB.Util.Model.QueryParameterInfo parInfo) { StringBuilder sqlFilter = new StringBuilder(); string defaultTableAlias = (parsMapping == null || string.IsNullOrEmpty(parsMapping.DefaultTableAlias)) ? "" : parsMapping.DefaultTableAlias + "."; // QueryParameterMappingInfo mappingInfo = (parsMapping != null && parsMapping.ContainsKey(parInfo.PropertyName)) ? parsMapping[parInfo.PropertyName] : null; string dbFieldName = mappingInfo == null ? (defaultTableAlias + parInfo.PropertyName) : mappingInfo.FieldName; if (parInfo.Condition == MB.Util.DataFilterConditions.Special) { dbFieldName = parInfo.PropertyName; } //判断是否为多个值的输入形式 if (parInfo.MultiValue && (parInfo.Condition == Util.DataFilterConditions.In || parInfo.Condition == Util.DataFilterConditions.Equal)) { string inStr = BuildQueryInSql <string>(parInfo.Value.ToString().Split(',')); if (inStr.Length > MB.Orm.DbSql.SqlShareHelper.SQL_MAX_LENGTH) { throw new MB.Util.APPException(string.Format("构造字段{0} 的IN 查询语句时 超长", dbFieldName)); } return(string.Format("{0} IN ({1})", dbFieldName, inStr)); } if (string.Compare(parInfo.DataType, "DateTime", true) == 0) { sqlFilter.Append(string.Format("{0}", dbFieldName)); } else { sqlFilter.Append(dbFieldName); } sqlFilter.Append(ConvertConditionToSqlStr(parInfo.Condition)); if (parInfo.Condition != MB.Util.DataFilterConditions.IsNotNull && parInfo.Condition != MB.Util.DataFilterConditions.IsNull) { if (parInfo.Condition == MB.Util.DataFilterConditions.Between) { sqlFilter.Append(valueToSQL(dbaseType, parInfo.DataType, parInfo.Value, parInfo.Condition)); sqlFilter.Append(SQL_AND); sqlFilter.Append(valueToSQL(dbaseType, parInfo.DataType, parInfo.Value2, parInfo.Condition, true)); } else { sqlFilter.Append(valueToSQL(dbaseType, parInfo.DataType, parInfo.Value, parInfo.Condition)); } } return(sqlFilter.ToString()); }
/// <summary> /// 获取配置的最大命中数SQL 过滤条件。 /// </summary> /// <param name="dbaseType"></param> /// <returns></returns> public static string GetTargetRowCountSqlFilter(MB.Orm.Enums.DatabaseType dbaseType) { if (TargetRowCount > 0 && dbaseType == Enums.DatabaseType.Oracle) { return(string.Format("ROWNUM <= {0}", TargetRowCount)); } else { return(string.Empty); } }
/// <summary> /// 创建动态聚组SQL生成对象 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static DynamicGroupBuilder CreateQueryBuilder(MB.Util.Model.DynamicGroupSetting setting) { var db = DatabaseHelper.CreateDatabase(); MB.Orm.Enums.DatabaseType dbType = DatabaseHelper.GetDatabaseType(db); if (dbType == Enums.DatabaseType.Oracle) { return(new DynamicGroupOracleBuilder(setting)); } return(null); }
/// <summary> /// 把行中的值替换SQL语句中的参数满足执行的要求 /// </summary> /// <param name="sqlStr"></param> /// <param name="paramsName"></param> /// <param name="drData"></param> /// <returns></returns> public string ReplaceCanExecSqlString(string sqlStr, IList <string> paramsName, DataRow drData) { MB.Orm.Enums.DatabaseType dbaseType = MB.Orm.Persistence.DatabaseHelper.CreateDatabaseType(); string sql = sqlStr; DataColumnCollection cols = drData.Table.Columns; foreach (string par in paramsName) { MB.Util.TraceEx.Assert(cols.Contains(par), "SQL语句中的参数" + par + "在数据集中不存在."); string val = valueToSQL(dbaseType, cols[par].DataType.Name, drData[par], MB.Util.DataFilterConditions.Equal); sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + par + ",", val + ","); sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + par + " ", val + " "); sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + par + ";", val + "; "); sql = sql.Replace(SQL_XML_CFG_PARAM_PREFIX + par + ")", val + ")"); } return(sql); }
/// <summary> /// 根据查询的参数数组转换为可以进行查询的SQL 字符窜。 /// </summary> /// <param name="queryParams"></param> /// <returns></returns> public string QueryParametersToSqlString(MB.Orm.Mapping.QueryParameterMappings parsMapping, MB.Util.Model.QueryParameterInfo[] queryParams) { MB.Orm.Enums.DatabaseType dbaseType = MB.Orm.Persistence.DatabaseHelper.CreateDatabaseType(); StringBuilder sqlFilter = new StringBuilder(); string targetRowCount = MB.Orm.Persistence.DbQueryTargetRowCountScope.GetTargetRowCountSqlFilter(dbaseType); if (queryParams == null || queryParams.Length == 0) { if (string.IsNullOrEmpty(targetRowCount)) { return("0=0"); } else { return(targetRowCount); } } var pars = Array.FindAll <MB.Util.Model.QueryParameterInfo>(queryParams, o => o.Limited != true); if (pars.Length == 0) { if (string.IsNullOrEmpty(targetRowCount)) { return("0=0"); } else { return(targetRowCount); } } builderSqlStringByParameters(dbaseType, sqlFilter, MB.Util.Model.QueryGroupLinkType.AND, parsMapping, pars); string sql = "(" + sqlFilter.ToString() + ")"; if (!string.IsNullOrEmpty(targetRowCount)) { sql += " AND " + targetRowCount; } return(sql); }
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)); }
//通过参数 创建SQL 查询语句 private void builderSqlStringByParameters(MB.Orm.Enums.DatabaseType dbaseType, StringBuilder sqlFilter, MB.Util.Model.QueryGroupLinkType linkType, MB.Orm.Mapping.QueryParameterMappings parsMapping, MB.Util.Model.QueryParameterInfo[] queryParams) { foreach (MB.Util.Model.QueryParameterInfo parInfo in queryParams) { List <MB.Util.Model.QueryParameterInfo> childParams = null; if (parInfo.IsGroupNode) { if (parInfo.Childs == null || parInfo.Childs.Count == 0) { continue; } childParams = parInfo.Childs.FindAll(o => o.Limited != true); if (childParams.Count == 0) { continue; } } string sql = sqlFilter.ToString().Trim(); if (sql.Length > 0 && sql.LastIndexOf(SQL_LEFT_BRACKET) != sql.Length - 1) { if (linkType == MB.Util.Model.QueryGroupLinkType.AND) { sqlFilter.Append(SQL_AND); } else if (linkType == MB.Util.Model.QueryGroupLinkType.AndNot) { sqlFilter.Append(SQL_AND_NOT); } else if (linkType == MB.Util.Model.QueryGroupLinkType.OrNot) { sqlFilter.Append(SQL_OR_NOT); } else { sqlFilter.Append(SQL_OR); } } sqlFilter.Append(SQL_LEFT_BRACKET); if (parInfo.IsGroupNode) { builderSqlStringByParameters(dbaseType, sqlFilter, parInfo.GroupNodeLinkType, parsMapping, childParams.ToArray()); } else { //edit chendc 2010-07-09 增加对特殊条件的处理 string singleSql = string.Empty; if (parInfo.Condition == Util.DataFilterConditions.SqlAppend) { if (parInfo.Value == null) { throw new MB.Util.APPException(string.Format("参数{0} 的DataFilterConditions 设置为 sqlAppend,value 不能为空 ", parInfo.PropertyName), Util.APPMessageType.SysErrInfo); } singleSql = parInfo.Value.ToString(); } else { singleSql = singleParameterToSql(dbaseType, parsMapping, parInfo); } sqlFilter.Append(singleSql); } sqlFilter.Append(SQL_RIGHT_BRACKET); } }