コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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);
        }
コード例 #5
0
        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
        }
コード例 #6
0
        /// <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 ");
                    }
                }
            }
        }
コード例 #7
0
        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);
        }
コード例 #8
0
        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);
        }
コード例 #9
0
        //生成包含关联表的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);
            }
        }
コード例 #10
0
        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);
        }
コード例 #11
0
        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);
        }
コード例 #12
0
        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);
                }
            }
        }
コード例 #13
0
        //生成包含关联表的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);
            }
        }