public virtual string GetLoadPageSqlString(Criteria criteria, SqlParameterCollection paras)
{
throw new ObjectMappingException("current database engine not support GetLoadPageSqlString method!");
}
public virtual string GetLoadSqlString(Criteria criteria, SqlParameterCollection paras)
{
string tableName = criteria.TableName;
if (string.IsNullOrEmpty(tableName))
{
tableName = this.GetTableName(criteria.TableMapping.Name);
}
string strSQL = "select [TOPCOUNT] [COLUMNS] from [TABLE] [CONDITION] [ORDER]";
strSQL = strSQL.Replace(ConstSql.Table, tableName);
if (criteria.TakeCount > 0)
{
strSQL = strSQL.Replace(ConstSql.SelectTop, " top " + criteria.TakeCount);
}
else
{
strSQL = strSQL.Replace(ConstSql.SelectTop, " ");
}
string condition = this.GetConditionsSqlString(criteria.Conditions, paras);
if (condition == string.Empty)
{
strSQL = strSQL.Replace(ConstSql.Condition, " ");
}
else
{
strSQL = strSQL.Replace(ConstSql.Condition, " where " + condition);
}
string pkColumn = criteria.PkColumn;
if (!String.IsNullOrEmpty(pkColumn) && criteria.TableMapping != null)
{
pkColumn = criteria.TableMapping.ColumnPK.Name;
}
string columns = this.GetColumnsSqlString(pkColumn, criteria.Columns);
if (columns == string.Empty)
{
strSQL = strSQL.Replace(ConstSql.Columns, " * ");
}
else
{
strSQL = strSQL.Replace(ConstSql.Columns, columns);
}
string order = this.GetOrdersSqlString(criteria.Orders);
if (order == string.Empty)
{
strSQL = strSQL.Replace(ConstSql.Order, " ");
}
else
{
strSQL = strSQL.Replace(ConstSql.Order, " order by " + order);
}
return(strSQL);
}
public virtual string GetConditionSqlString(SqlCondition condition, SqlParameterCollection paras)
{
string strSQL = string.Empty;
switch (condition.Type)
{
case SqlConditionType.EqualTo:
if (condition.Value == null)
{
strSQL = condition.Column + " is null ";
}
else
{
strSQL = string.Format("{0} = {1}", condition.Column, this.BuildParameterName(paras.GenerateParameter(condition.Value)));
}
break;
case SqlConditionType.NotEqualTo:
if (condition.Value == null)
{
strSQL = condition.Column + " is not null ";
}
else
{
strSQL = string.Format("{0} <> {1}", condition.Column, this.BuildParameterName(paras.GenerateParameter(condition.Value)));
}
break;
case SqlConditionType.GreaterThan:
strSQL = string.Format("{0} > {1}", condition.Column, this.BuildParameterName(paras.GenerateParameter(condition.Value)));
break;
case SqlConditionType.GreaterThanAndEqualTo:
strSQL = string.Format("{0} >= {1}", condition.Column, this.BuildParameterName(paras.GenerateParameter(condition.Value)));
break;
case SqlConditionType.LessThan:
strSQL = string.Format("{0} < {1}", condition.Column, this.BuildParameterName(paras.GenerateParameter(condition.Value)));
break;
case SqlConditionType.LessThanAndEqualTo:
strSQL = string.Format("{0} <= {1}", condition.Column, this.BuildParameterName(paras.GenerateParameter(condition.Value)));
break;
case SqlConditionType.Match:
strSQL = string.Format("{0} like {1} ESCAPE N'~'", condition.Column,
this.BuildParameterName(paras.GenerateParameter(CorrectLikeConditionValue(condition.Value, "%", "%"))));
break;
case SqlConditionType.MatchFullText:
strSQL = string.Format("CONTAINS({0},'\"{1}\"')", condition.Column, CorrectFullTextConditionValue(condition.Value));
break;
case SqlConditionType.MatchPrefix:
strSQL = string.Format("{0} like {1} ESCAPE N'~'", condition.Column,
this.BuildParameterName(paras.GenerateParameter(CorrectLikeConditionValue(condition.Value, "", "%"))));
break;
case SqlConditionType.MatchSuffix:
strSQL = string.Format("{0} like {1} ESCAPE N'~'", condition.Column,
this.BuildParameterName(paras.GenerateParameter(CorrectLikeConditionValue(condition.Value, "%", ""))));
break;
case SqlConditionType.NotMatch:
strSQL = string.Format("{0} not like {1} ESCAPE N'~'", condition.Column,
this.BuildParameterName(paras.GenerateParameter(CorrectLikeConditionValue(condition.Value, "%", "%"))));
break;
case SqlConditionType.NotMatchPrefix:
strSQL = string.Format("{0} not like {1} ESCAPE N'~'", condition.Column,
this.BuildParameterName(paras.GenerateParameter(CorrectLikeConditionValue(condition.Value, "", "%"))));
break;
case SqlConditionType.NotMatchSuffix:
strSQL = string.Format("{0} not like {1} ESCAPE N'~'", condition.Column,
this.BuildParameterName(paras.GenerateParameter(CorrectLikeConditionValue(condition.Value, "%", ""))));
break;
case SqlConditionType.In:
if (condition.Values.Count > 0)
{
string[] vals = this.GetValuesSqlString(condition.Values);
if (vals.Length > 0)
{
strSQL = condition.Column + " in (" + string.Join(" , ", vals) + ") ";
}
if (condition.Values.Contains(null))
{
if (string.IsNullOrEmpty(strSQL))
{
strSQL = condition.Column + " is null";
}
else
{
strSQL = "(" + strSQL + " or " + condition.Column + " is null)";
}
}
}
break;
case SqlConditionType.NotIn:
strSQL = condition.Column + " not in (" + string.Join(" , ", this.GetValuesSqlString(condition.Values)) + ") ";
break;
case SqlConditionType.Custom:
strSQL = (string)condition.Value;
if ((condition.Parameters != null) && (condition.Parameters.Count > 0))
{
foreach (var pitem in condition.Parameters)
{
paras.Add(pitem);
}
}
break;
default:
break;
}
return(strSQL);
}
