예제 #1
0
        private static string _CreateSQLFromFindOptions(IDatabaseService db, 
            string objectFullName, 
            ObjectFindOptions options, 
            ObjectIndex[] indexes, 
            string templateSql, 
            IList<IDataParameter> parameters)
        {
            var logic = options.Logic;
            var oper = options.Operator;
            var limit = options.Limit;

            bool useOr = ObjectFindLogic.Or == logic;
            bool useLike = ObjectFindOperator.Like == oper;
            bool isNull = ObjectFindOperator.IsNull == oper;

            var limitSql = (0 == limit) ? SQLStatements.NoLimit : string.Format(SQLStatements.Limit, limit);

            var tableName = _CreateTableName(db, objectFullName);

            var sqlConstraint = new StringBuilder();
            if (null != indexes && 0 < indexes.Length)
            {
                // initialize the where clause
                sqlConstraint.Append("WHERE ");
                for (int i = 0; indexes.Length > i; i++)
                {
                    var idx = indexes[i];

                    // skip over null objects
                    if (null == idx)
                    {
                        continue;
                    }

                    if (0 < i)
                    {
                        if (useOr)
                        {
                            sqlConstraint.Append(" OR ");
                        }
                        else
                        {
                            sqlConstraint.Append(" AND ");
                        }
                    }

                    var paramName = "p" + i + idx.Name;
                    var value = idx.GetObjectValue();
                    sqlConstraint.Append(db.MakeQuotedName(idx.Name));

                    if (useLike)
                    {
                        sqlConstraint.Append(' ');
                        sqlConstraint.Append(db.MakeLikeParamReference(paramName));
                        parameters.Add(ObjectIndexProvider.MakeLikeParameter(db, paramName, value));
                    }
                    else if (isNull)
                    {
                        sqlConstraint.Append(" IS NULL");
                    }
                    else
                    {
                        sqlConstraint.Append(" = ");
                        sqlConstraint.Append(db.MakeParamReference(paramName));
                        parameters.Add(db.MakeParam(paramName, value));
                    }
                }
            }
            var orderBySql = _CreateOrderBySQL(db, options.Order);
            return string.Format(templateSql, tableName, sqlConstraint.ToString(), orderBySql, limitSql);
        }