public override int SelectCount(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition)
{
StringBuilder sb = new StringBuilder();
SqlCommand command = (SqlCommand)db.Connection.CreateCommand();
int parameterCounter = 1;
//创建一个QueryExpression
IQueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
queryExpression.Wheres.Add(condition);
var 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 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);
var sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
StringBuilder sb = new StringBuilder();
SqlCommand command = (SqlCommand)db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("select top 1 1 ");
GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping);
//sb.Append(conditionCollection.Count > 0 ? " and " : " where ");
//sb.Append(" rownum<2");
//执行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 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 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);
}
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);
}
private void BtReload_Click(object sender, EventArgs e)
{
lstBloc.DataSource = null;
lstDatabaseObject.DataSource = null;
lstResume.DataSource = null;
var analyzer = SqlAnalyzer.Analyse(myScript.ScriptId, myScript.ScriptText);
lstBloc.DataSource = analyzer.Blocs;
lstDatabaseObject.DataSource = analyzer.SqlObjets;
lstResume.DataSource = analyzer.Resumes;
using var cnn = new DatabaseConnection();
analyzer.Save(cnn);
lstBloc.SelectedItem = null;
lstDatabaseObject.SelectedItem = null;
lstResume.SelectedItem = null;
EnableButtons();
}
private void AddScriptToObjectAction(Object currentObjectEdited, int action)
{
// Ajouter un script à la version
var script = new ScriptObject(currentObjectEdited, new SqlAction()
{
SqlActionId = action
});
var crudScript = new Script()
{
ScriptText = script.ToString(),
VersionId = currentObjectEdited.VersionId,
};
var scriptId = cnn.ExecuteScalar(Script.SQLInsert, crudScript);
// Ajouter l'anlyse pour ce script
var analyzer = SqlAnalyzer.Analyse(scriptId, crudScript.ScriptText);
analyzer.Valide = EValidation.Valide;
analyzer.Save(cnn);
}
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);
}
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);
}
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);
}
}
}