public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
//ITable table=getTables().getTableByClass(clazz);
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
//执行关联删除
var sb = new StringBuilder();
var command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("delete ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
public override bool Exists(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
StringBuilder sb = new StringBuilder();
IDbCommand command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("select 0 ");
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
sb.Append(" limit 1");
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
IDataReader dataReader = db.ExecuteReader(command);
bool exists = dataReader.Read();
dataReader.Close();
return(exists);
}
public override int SelectCount(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
StringBuilder sb = new StringBuilder();
IDbCommand command = db.Connection.CreateCommand();
int parameterCounter = 1;
//创建一个QueryExpression
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
sb.Append("select count(0) ");
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
IDataReader dataReader = db.ExecuteReader(command);
int count = 0;
if (dataReader.Read())
{
count = Convert.ToInt32(dataReader[0]);
}
dataReader.Close();
return(count);
}
public override SelectionResult SelectCollection(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, IQueryExpression queryExpression)
{
//检查pageSize和pageIndex是否合法
Util.CheckPageSizeAndPageIndex(queryExpression.PageSize, queryExpression.PageIndex);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
StringBuilder sb = new StringBuilder();
IDbCommand cmd = db.Connection.CreateCommand();
int counter = 1;
GenerateSelectSql_SelectFromWhereOrder(sb, cmd.Parameters, ref counter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//执行sql
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
if (queryExpression.PageIndex != null && queryExpression.PageSize != null)
{
//分页的时候需要包装分页语句
WrapPaging(db, cmd, queryExpression.PageSize.Value, queryExpression.PageIndex.Value);
}
SelectionResult result = new SelectionResult();
result.ObjectFiller = sqlAnalyseResult.ObjectFiller;
//先关闭datarader
if (queryExpression.ReturnMatchedCount)
{
if (sqlAnalyseResult.HasGroup)
{
result.TotalMatchedCount = SelectCountGroup(db, mappingProvider, tableMapping, queryExpression.EntityType, queryExpression, sqlAnalyseResult);
}
else
{
result.TotalMatchedCount = SelectCount(db, mappingProvider, tableMapping, queryExpression.EntityType, queryExpression.Wheres);
}
}
result.DataReader = db.ExecuteReader(cmd);
return(result);
}
private void Oracle_CreateSqlExists(
StringBuilder sb, IDataParameterCollection parameters, ref int parameterCounter,
ICondition condition, SqlAnalyseResult sqlAnalyseResult, IMappingProvider mappingProvider,
IDictionary <object, string> tableMapping
)
{
sb.Append("select 1 from ");
int tableIndex = 0;
if (sqlAnalyseResult.ForeignTables.Count > 0)
{
foreach (SqlTable sqlTable in sqlAnalyseResult.ForeignTables)
{
if (tableIndex > 0)
{
sb.Append(",");
}
tableIndex++;
sb.Append(GetTableName(sqlTable.ForeignEntityType, mappingProvider, tableMapping));
sb.Append(" ");
sb.Append(sqlTable.ForeignTableNameAlias);
}
tableIndex = 0;
foreach (SqlTable sqlTable in sqlAnalyseResult.ForeignTables)
{
foreach (SqlRelationColumn sqlRelationColumn in sqlTable.RelationColumns)
{
sb.Append(tableIndex > 0 ? " and " : " where ");
tableIndex++;
sb.Append(sqlRelationColumn.Expression);
}
}
}
else
{
sb.Append("dual");
}
//condition
sb.Append(tableIndex > 0 ? " and " : " where ");
//tableIndex++; 这句不能有
GenerateSelectSql_Where(sb, parameters, ref parameterCounter, condition, sqlAnalyseResult, true, mappingProvider);
//order 不需要order
}
/// <summary>
/// 生成一句完整的包含多表关联的 select from where order by 语句
/// </summary>
/// <param name="sb">拼接sql语句时会调用sb.Append方法</param>
/// <param name="parameters">需要参数时，会生成创建参数并添加至本参数</param>
/// <param name="parameterCounter">生成sql过程中参数计数器,添加parameters集合的参数的名称格式为{parameterNamePrefix}{parameterCounter}</param>
/// <param name="queryExpression">查询表达式</param>
/// <param name="sqlAnalyseResult">查询表达式解析后的对象</param>
/// <param name="mappingProvider"></param>
/// <param name="tableNameMapping">动态表名称时使用</param>
protected virtual void GenerateSelectSql_SelectFromWhereOrder(
StringBuilder sb,
IDataParameterCollection parameters, ref int parameterCounter,
IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult, IMappingProvider mappingProvider, IDictionary <object, string> tableNameMapping
)
{
//select
sb.Append("select ");
int index = 0;
foreach (string outputSqlColumn in sqlAnalyseResult.SortedOutputColumns)
{
if (index > 0)
{
sb.Append(",");
}
index++;
sb.Append(outputSqlColumn);
}
//from where
GenerateSelectSql_FromWhere(sb, parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableNameMapping);
//order
if (queryExpression.OrderBys.Count > 0)
{
sb.Append(" order by ");
index = 0;
foreach (IOrderExpression orderExpression in queryExpression.OrderBys)
{
if (index > 0)
{
sb.Append(",");
}
index++;
SqlColumn sqlColumn = sqlAnalyseResult.SqlColumns[orderExpression.Property];
sb.Append(sqlColumn.TableAliasAndColumn);
if (orderExpression.OrderType == OrderType.Desc)
{
sb.Append(" desc ");
}
}
}
}
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
//ITable table=getTables().getTableByClass(clazz);
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
//执行关联删除
var sb = new StringBuilder();
var command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("delete ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" from ");
sb.Append(GetTableName(entityType, mappingProvider, tableMapping));
sb.Append(" ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
if (sqlAnalyseResult.ForeignTables.Count == 0)
{
//说明是无关联表的条件，只是本表的条件
sb.Append(" where ");
GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, true, mappingProvider);
}
else
{
//说明有关联表的条件，需要多表关联
sb.Append(" where exists (");
SqlServerCreateSqlExists(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, mappingProvider, tableMapping);
sb.Append(")");
}
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
//ITable table=getTables().getTableByClass(clazz);
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
if (sqlAnalyseResult.ForeignTables.Count > 0)
{
//不支持
throw ExceptionFactory.NotSupported("SQLite不支持多表关联更新");
}
//执行关联删除
StringBuilder sb = new StringBuilder();
IDbCommand command = db.Connection.CreateCommand();
int parameterCounter = 1;
string tableName = GetTableName(entityType, mappingProvider, tableMapping);
sb.Append("delete from ");
sb.Append(tableName);
//说明是无关联表的条件，只是本表的条件
sb.Append(" where ");
GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, false, mappingProvider);
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
//生成包含关联表的select语句的 where 部分
protected virtual void GenerateSelectSql_Where(
StringBuilder sb,
IDataParameterCollection parameters, ref int parameterCounter,
ICondition condition, SqlAnalyseResult sqlAnalyseResult, bool includeTableNameAlias, IMappingProvider mappingProvider
)
{
if (condition is IConditionExpressionCollection)
{
IConditionExpressionCollection conditions = (IConditionExpressionCollection)condition;
string andOr = conditions.ConditionRelation == ConditionRelation.And ? " and " : " or ";
if (conditions.Count > 1)
{
sb.Append("(");
}
int index = 0;
foreach (ICondition conditionItem in conditions)
{
if (index > 0)
{
sb.Append(andOr);
}
index++;
GenerateSelectSql_Where(sb, parameters, ref parameterCounter, conditionItem, sqlAnalyseResult, includeTableNameAlias, mappingProvider);
}
if (conditions.Count > 1)
{
sb.Append(") ");
}
}
else
{
IConditionExpression conditionItem = (IConditionExpression)condition;
SqlColumn sqlColumn = sqlAnalyseResult.SqlColumns[conditionItem.Property];
GenerateSelectSql_Where_Item(sb, parameters, ref parameterCounter, conditionItem, sqlColumn, includeTableNameAlias, mappingProvider);
}
}
public override int Update(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition, IDictionary <string, object> updates)
{
ITable table = mappingProvider.GetTable(entityType);
QueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
foreach (string propertyName in updates.Keys)
{
queryExpression.Selects.Add(propertyName);
}
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
var sb = new StringBuilder();
var command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("update ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" set ");
var index = 0;
foreach (KeyValuePair <string, object> kv in updates)
{
if (index > 0)
{
sb.Append(",");
}
index++;
IColumn column = table.Columns[kv.Key];
sb.Append(column.ColumnName);
sb.Append("=");
sb.Append(CreateParameterNameInStatement(parameterCounter));
//object propertyValue = column.PropertyAdapter.GetValue(obj);
//Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size);
}
sb.Append(" from ");
sb.Append(GetTableName(entityType, mappingProvider, tableMapping));
sb.Append(" ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" ");
if (sqlAnalyseResult.ForeignTables.Count == 0)
{
//说明是无关联表的条件，只是本表的条件
sb.Append(" where ");
GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, true, mappingProvider);
}
else
{
//说明有关联表的条件，需要多表关联
sb.Append(" where exists (");
SqlServerCreateSqlExists(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, mappingProvider, tableMapping);
sb.Append(")");
}
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
public int SelectCountGroup(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult)
{
StringBuilder sb = new StringBuilder();
IDbCommand command = db.Connection.CreateCommand();
int parameterCounter = 1;
//创建一个QueryExpression
sb.Append("select count(0) from (");
//select
sb.Append("select 1 as A_A_A ");
//from where
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
sb.Append(") a");
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
IDataReader dataReader = db.ExecuteReader(command);
int count = 0;
if (dataReader.Read())
{
count = Convert.ToInt32(dataReader[0]);
}
dataReader.Close();
return(count);
}
protected virtual void GenerateSelectSql_Where_Item(
StringBuilder sb,
IDataParameterCollection parameters, ref int parameterCounter,
IConditionExpression conditionExpression, SqlColumn sqlColumn, bool includeTableNameAlias, IMappingProvider mappingProvider
)
{
//为处理Like自动增加%
object objValue = conditionExpression.ConditionValue;
switch (conditionExpression.ConditionOperator)
{
case ConditionOperator.Like:
case ConditionOperator.NotLike:
objValue = "%" + objValue + "%";
break;
case ConditionOperator.RightLike:
case ConditionOperator.NotRightLike:
objValue = "%" + objValue;
break;
case ConditionOperator.LeftLike:
case ConditionOperator.NotLeftLike:
objValue = objValue + "%";
break;
}
bool hasParameter = false;
//添加字段名
sb.Append(includeTableNameAlias ? sqlColumn.TableAliasAndColumn : sqlColumn.Column.ColumnName);
//添加操作符
switch (conditionExpression.ConditionOperator)
{
case ConditionOperator.Equal:
if (conditionExpression.ConditionValue == null)
{
sb.Append(" is null");
}
else
{
sb.Append("=");
hasParameter = true;
}
break;
case ConditionOperator.GreaterThan:
sb.Append(">");
hasParameter = true;
break;
case ConditionOperator.GreaterThanOrEqual:
sb.Append(">=");
hasParameter = true;
break;
case ConditionOperator.LessThan:
sb.Append("<");
hasParameter = true;
break;
case ConditionOperator.LessThanOrEqual:
sb.Append("<=");
hasParameter = true;
break;
case ConditionOperator.NotEqual:
if (conditionExpression.ConditionValue == null)
{
sb.Append(" is not null");
}
else
{
sb.Append(" <>");
hasParameter = true;
}
break;
case ConditionOperator.In:
case ConditionOperator.NotIn:
if (conditionExpression.ConditionOperator == ConditionOperator.NotIn)
{
sb.Append(" not");
}
sb.Append(" in ");
if (objValue is IQueryExpression)
{
hasParameter = true;
}
else
{
string v = conditionExpression.ConditionValue + "";
v = v.Trim();
if (!v.StartsWith("("))
{
sb.Append("(");
}
sb.Append(v);
if (!v.EndsWith(")"))
{
sb.Append(")");
}
}
break;
case ConditionOperator.Like:
case ConditionOperator.LeftLike:
case ConditionOperator.RightLike:
sb.Append(" like ");
hasParameter = true;
break;
case ConditionOperator.NotLike:
case ConditionOperator.NotLeftLike:
case ConditionOperator.NotRightLike:
sb.Append(" not like ");
hasParameter = true;
break;
case ConditionOperator.Custom:
sb.Append(conditionExpression.ConditionValue);
break;
}
if (hasParameter)
{
if (objValue is IQueryExpression)
{
IQueryExpression query = objValue as IQueryExpression;
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, query);
sb.Append(" (");
GenerateSelectSql_SelectFromWhereOrder(sb, parameters, ref parameterCounter, query, sqlAnalyseResult, mappingProvider, null);
sb.Append(")");
}
else
{
sb.Append(CreateParameterNameInStatement(parameterCounter));
Type propertyType = sqlColumn.FullPropertyName.GroupFunction == GroupFunction.Count ? typeof(int) : sqlColumn.Column.PropertyType;
int size = sqlColumn.FullPropertyName.GroupFunction == GroupFunction.Count ? 4 : sqlColumn.Column.Size;
AddParameter(parameters, ref parameterCounter, objValue, propertyType, size);
}
}
}
//生成包含关联表的select语句的 from where 部分
protected virtual void GenerateSelectSql_FromWhere(
StringBuilder sb,
IDataParameterCollection parameters, ref int parameterCounter,
IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult, IMappingProvider mappingProvider, IDictionary <object, string> tableNameMapping
)
{
int index;
//from
sb.Append(" from ");
sb.Append(GetTableName(sqlAnalyseResult.MasterEntityType, mappingProvider, tableNameMapping));
sb.Append(" ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
//join
foreach (SqlTable sqlTable in sqlAnalyseResult.ForeignTables)
{
sb.Append(" left outer join ");
sb.Append(GetTableName(sqlTable.ForeignEntityType, mappingProvider, tableNameMapping));
sb.Append(" ");
sb.Append(sqlTable.ForeignTableNameAlias);
sb.Append(" on ");
index = 0;
foreach (SqlRelationColumn sqlRelationColumn in sqlTable.RelationColumns)
{
if (index > 0)
{
sb.Append(" and ");
}
index++;
sb.Append(sqlRelationColumn.Expression);
}
}
//where
if (queryExpression.Wheres.Count > 0)
{
sb.Append(" where ");
GenerateSelectSql_Where(sb, parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, true, mappingProvider);
}
//group by
if (queryExpression.GroupBys.Count > 0)
{
sb.Append(" group by ");
index = 0;
foreach (IGroupExpression groupExpression in queryExpression.GroupBys)
{
if (index > 0)
{
sb.Append(",");
}
index++;
SqlColumn sqlColumn = sqlAnalyseResult.SqlColumns[new PropertyExpression(groupExpression.PropertyName, GroupFunction.None)];
sb.Append(sqlColumn.TableAliasAndColumn);
}
}
//having
if (queryExpression.Havings.Count > 0)
{
sb.Append(" having ");
GenerateSelectSql_Where(sb, parameters, ref parameterCounter, queryExpression.Havings, sqlAnalyseResult, true, mappingProvider);
}
}