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