/// <summary> /// Builds the query string or DbCommand /// </summary> /// <param name="BuildCommand"></param> /// <returns></returns> protected object BuildQuery(bool BuildCommand) { // Make sure we have a valid DB driver if (BuildCommand && Driver == null) { throw new Exception("Cannot build a command when the Db Drvier hasn't been specified. Call SetDbDriver first."); } // Make sure we have a table name if (selectedTables.Count == 0) { throw new Exception("No tables were specified for this query."); } // Create Command DbCommand Command = (BuildCommand) ? Driver.CreateCommand(null) : null; // Start Query StringBuilder Query = new StringBuilder("SELECT "); Query.AppendIf(Distinct, "DISTINCT "); // Append columns Query.Append(String.Join(", ", SelectedColumns)); // Append Tables Query.Append(" FROM " + String.Join(", ", SelectedTables)); // Append Joins if (Joins.Count > 0) { foreach (JoinClause Clause in Joins) { // Convert join type to string switch (Clause.JoinType) { case JoinType.InnerJoin: Query.Append(" JOIN "); break; case JoinType.OuterJoin: Query.Append(" FULL OUTER JOIN "); break; case JoinType.LeftJoin: Query.Append(" LEFT JOIN "); break; case JoinType.RightJoin: Query.Append(" RIGHT JOIN "); break; } // Append the join statement Query.Append($"{Clause.JoiningTable} ON "); Query.Append( WhereStatement.CreateComparisonClause( $"{Clause.JoiningTable}.{Clause.JoiningColumn}", Clause.ComparisonOperator, new SqlLiteral($"{Clause.FromTable}.{Clause.FromColumn}") ) ); } } // Append Where if (this.WhereStatement.Count != 0) { Query.Append(" WHERE " + WhereStatement.BuildStatement(Command)); } // Append GroupBy if (GroupByColumns.Count > 0) { Query.Append(" GROUP BY " + String.Join(", ", GroupByColumns)); } // Append Having if (HavingStatement.Count > 0) { if (GroupByColumns.Count == 0) { throw new Exception("Having statement was set without Group By"); } Query.Append(" HAVING " + this.HavingStatement.BuildStatement(Command)); } // Append OrderBy if (OrderByStatements.Count > 0) { int count = OrderByStatements.Count; Query.Append(" ORDER BY"); foreach (OrderByClause Clause in OrderByStatements) { Query.Append($" {Clause.FieldName}"); // 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 if (LimitRecords is Array) { if (LimitRecords.Length == 1) { Query.Append(" LIMIT " + LimitRecords[0].ToString()); } else { Query.Append($" LIMIT {LimitRecords[1]}, {LimitRecords[0]}"); } } // Set the command text if (BuildCommand) { Command.CommandText = Query.ToString(); } // Return Result return((BuildCommand) ? Command as object : Query.ToString()); }
/// <summary> /// Builds the query string or DbCommand /// </summary> /// <param name="BuildCommand"></param> /// <returns></returns> protected object BuildQuery(bool BuildCommand) { // Make sure we have a valid DB driver if (BuildCommand && Driver == null) { throw new Exception("Cannot build a command when the Db Drvier hasn't been specified. Call SetDbDriver first."); } // Make sure we have a table name if (String.IsNullOrWhiteSpace(Table)) { throw new Exception("Table to insert into was not set."); } // Make sure we have at least 1 field to update if (Fields.Count == 0) { throw new Exception("No fields to insert"); } // Create Command DbCommand Command = (BuildCommand) ? Driver.CreateCommand(null) : null; // Start Query StringBuilder Query = new StringBuilder("INSERT INTO " + Table + " ("); StringBuilder Values = new StringBuilder(); bool First = true; // Add fields and values foreach (KeyValuePair <string, object> Item in Fields) { // Append comma if (!First) { Query.Append(", "); Values.Append(", "); } else { First = false; } // If using a command, Convert values to Parameters if (BuildCommand && Item.Value != null && Item.Value != DBNull.Value && !(Item.Value is SqlLiteral)) { // Create param for value DbParameter Param = Command.CreateParameter(); Param.ParameterName = "@P" + Command.Parameters.Count; Param.Value = Item.Value; // Add Params to command Command.Parameters.Add(Param); // Append query's Query.Append(Item.Key); Values.Append(Param.ParameterName); } else { Query.Append(Item.Key); Values.Append(WhereStatement.FormatSQLValue(Item.Value)); } } // Finish the query string, and return the proper object Query.Append(") VALUES (" + Values.ToString() + ")"); // Set the command text if (BuildCommand) { Command.CommandText = Query.ToString(); } return((BuildCommand) ? Command as object : Query.ToString()); }