//单个参数转换为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);
}
}