//单个参数转换为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="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); }
//通过参数 创建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); } }