Exemplo n.º 1
0
        public override void Dispose()
        {
            WhereStatement = null;
            Columns        = null;

            GC.SuppressFinalize(this);
        }
Exemplo n.º 2
0
 /// <summary>
 /// Creates a where clause to add to the query's where statement
 /// </summary>
 /// <param name="field">The column name</param>
 /// <returns></returns>
 public SqlExpression <SelectWhereStatement> Where(string field)
 {
     if (WhereStatement.InnerClauseOperator == LogicOperator.And)
     {
         return(WhereStatement.And(field));
     }
     else
     {
         return(WhereStatement.Or(field));
     }
 }
Exemplo n.º 3
0
 /// <summary>
 /// Creates a where clause to add to the query's where statement
 /// </summary>
 /// <param name="field">The column name</param>
 /// <param name="operator">The Comaparison Operator to use</param>
 /// <param name="compareValue">The value, for the column name and comparison operator</param>
 /// <returns></returns>
 public SelectWhereStatement Where(string field, Comparison @operator, object compareValue)
 {
     if (WhereStatement.InnerClauseOperator == LogicOperator.And)
     {
         return(WhereStatement.And(field, @operator, compareValue));
     }
     else
     {
         return(WhereStatement.Or(field, @operator, compareValue));
     }
 }
Exemplo n.º 4
0
        /// <summary>
        /// Builds the query string or SQLiteCommand
        /// </summary>
        /// <param name="buildCommand"></param>
        /// <returns></returns>
        protected object BuildQuery(bool buildCommand)
        {
            // Define local variables
            int tableIndex = 0;
            int tableCount = Tables.Count;

            // Make sure we have a table name
            if (Tables.Count == 0 || String.IsNullOrWhiteSpace(Tables[0].Name))
            {
                throw new Exception("No tables were specified for this query.");
            }

            // Start Query
            StringBuilder query = new StringBuilder("SELECT ", 256);

            query.AppendIf(Distinct, "DISTINCT ");

            // Append columns from each table
            foreach (var table in Tables)
            {
                // Define local variables
                int colCount = table.Columns.Count;
                tableIndex++;
                tableCount--;

                // Create alias for this table if there is none
                if (String.IsNullOrWhiteSpace(table.Alias))
                {
                    table.Alias = $"t{tableIndex}";
                }

                // Check if the user wants to select all columns
                if (colCount == 0)
                {
                    query.AppendFormat("{0}.*", Context.QuoteIdentifier(table.Alias));
                    query.AppendIf(tableCount > 0, ", ");
                }
                else
                {
                    // Add each result selector to the query
                    foreach (ColumnIdentifier column in table.Columns.Values)
                    {
                        // Use the internal method to append the column string to our query
                        column.AppendToQuery(query, Context, table.Alias);

                        // If we have more results to select, append Comma
                        query.AppendIf(--colCount > 0 || tableCount > 0, ", ");
                    }
                }
            }

            // === Append main Table === //
            var fromTbl = Tables[0];

            query.Append($" FROM {Context.QuoteIdentifier(fromTbl.Name)} AS {Context.QuoteIdentifier(fromTbl.Alias)}");

            // Append Joined tables
            if (Joins.Count > 0)
            {
                foreach (JoinClause clause in Joins)
                {
                    // Convert join type to string
                    switch (clause.JoinType)
                    {
                    default:
                    case JoinType.InnerJoin:
                        query.Append(" JOIN ");
                        break;

                    case JoinType.OuterJoin:
                        query.Append(" OUTER JOIN ");
                        break;

                    case JoinType.CrossJoin:
                        query.Append(" CROSS JOIN ");
                        break;

                    case JoinType.LeftJoin:
                        query.Append(" LEFT JOIN ");
                        break;
                    }

                    // Append the join statement
                    string alias = Context.QuoteIdentifier(clause.JoiningTable.Alias);
                    query.Append($"{Context.QuoteIdentifier(clause.JoiningTable.Name)} AS {alias}");

                    // Do we have an expression?
                    if (clause.ExpressionType == JoinExpressionType.On)
                    {
                        // Try and grab the table
                        var    tbl   = Tables.Where(x => x.Name.Equals(clause.FromTable, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                        string fromT = tbl?.Alias ?? clause.FromTable;
                        query.Append(" ON ");
                        query.Append(
                            SqlExpression <WhereStatement> .CreateExpressionString(
                                $"{alias}.{Context.QuoteIdentifier(clause.JoiningColumn)}",
                                clause.ComparisonOperator,
                                new SqlLiteral(Context.QuoteIdentifier($"{fromT}.{clause.FromColumn}"))
                                )
                            );
                    }
                    else if (clause.ExpressionType == JoinExpressionType.Using)
                    {
                        var parts = clause.JoiningColumn.Split(',');
                        query.AppendFormat(" USING({0})", String.Join(", ", parts.Select(x => Context.QuoteIdentifier(x))));
                    }
                }
            }

            // Append Where Statement
            List <SQLiteParameter> parameters = new List <SQLiteParameter>();

            if (WhereStatement.HasClause)
            {
                if (buildCommand)
                {
                    query.Append(" WHERE " + WhereStatement.BuildStatement(parameters));
                }
                else
                {
                    query.Append(" WHERE " + WhereStatement.BuildStatement());
                }
            }

            // Append GroupBy
            if (GroupByColumns.Count > 0)
            {
                query.Append(" GROUP BY " + String.Join(", ", GroupByColumns.Select(x => Context.QuoteIdentifier(x))));
            }

            // Append Having
            if (HavingStatement.HasClause)
            {
                if (GroupByColumns.Count == 0)
                {
                    throw new Exception("Having statement was set without Group By");
                }

                query.Append(" HAVING " + HavingStatement.BuildStatement(parameters));
            }

            // Append OrderBy
            if (OrderByStatements.Count > 0)
            {
                int count = OrderByStatements.Count;
                query.Append(" ORDER BY");
                foreach (OrderByClause clause in OrderByStatements)
                {
                    query.Append($" {Context.QuoteIdentifier(clause.ColumnName)}");

                    // Add sorting if not default
                    query.AppendIf(clause.SortOrder == Sorting.Descending, " DESC");

                    // Append seperator if we have more orderby statements
                    query.AppendIf(--count > 0, ",");
                }
            }

            // Append Limit
            query.AppendIf(Limit > 0, " LIMIT " + Limit);
            query.AppendIf(Offset > 0, " OFFSET " + Offset);

            // Create Command
            SQLiteCommand command = null;

            if (buildCommand)
            {
                command = Context.CreateCommand(query.ToString());
                command.Parameters.AddRange(parameters.ToArray());
            }

            // Return Result
            return((buildCommand) ? command as object : query.ToString());
        }
Exemplo n.º 5
0
 /// <summary>
 /// Creates a new instance of <see cref="DeleteQueryBuilder"/> using the
 /// specified <see cref="SQLiteContext"/>
 /// </summary>
 /// <param name="context"></param>
 public DeleteQueryBuilder(SQLiteContext context)
 {
     this.Context        = context;
     this.WhereStatement = new WhereStatement(context);
 }
Exemplo n.º 6
0
 /// <summary>
 /// Creates a new instance of UpdateQueryBuilder with the provided SQLite connection.
 /// </summary>
 /// <param name="table">The table name we are updating data in</param>
 /// <param name="context">The SQLiteContext that will be used to build and query this SQL statement</param>
 public UpdateQueryBuilder(string table, SQLiteContext context) : base(context)
 {
     this.Table          = table;
     this.Context        = context;
     this.WhereStatement = new WhereStatement(context);
 }