SqlQueryBuilder Class

.NET Standard 2.x

Represents an object used to build a SQL SELECT statement.

The SQL SELECT statements can be used to retrieve rows from the database.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Implements

SnapObjects.Data.SqlBuilderBase

SnapObjects.Data.ISqlQueryBuilder

Syntax

public class SqlQueryBuilder : SqlBuilderBase, ISqlQueryBuilder;

Constructor

Name	Description
SqlQueryBuilder(string name = "")	Initializes a new instance of the SqlQueryBuilder class.

Properties

Name	Return Type	Description
HasJoin	bool	Gets whether a joined table has been created.
HasOn	bool	Gets whether a joined table has been created and the condition on which the join is based has been specified.
HasTable	bool	Gets whether any table source has been added to the current object using the From or FromRaw method.
HasWhere	bool	Determines whether any search condition has been added to the WHERE clause of the current object using a Where or WhereRaw method and so on.
Name	string	Gets the name of the current object.
SqlType	SqlStatementType	Gets the SQL statement type of the current object.
UsedParameters	IReadOnlyDictionary<string, ISqlParameter>	Gets an IReadOnlyDictionary<string, ISqlParameter> object that contains all parameters used in the current object.

Methods

Name	Return Type	Description
AddParameters(params ISqlParameter[] parameters)	bool	Adds the parameters which will be used in the SQL statement to the current object.
AsGroupBy()	ISqlGroupBuilder	Returns an ISqlGroupBuilder object which represents an object used to build the GROUP BY clause in a SQL statement.
AsHaving()	ISqlHavingAndOr	Returns an ISqlHavingAndOr object that can be used to add a search condition to a group or an aggregate in the HAVING clause.
AsJoin()	ISqlJoinOnAndOr	Returns an ISqlJoinOnAndOr object which represents an object used to build the JOIN clause of a SQL statement.
AsOrderBy()	ISqlOrderThenBuilder	Returns an ISqlOrderThenBuilder object which can be used to add sorting criteria to the ORDER clause.
AsWhere()	ISqlWhereAndOr	Returns an ISqlWhereAndOr object which represents an object used to build the WHERE clause in a SQL statement.
Clone()	ISqlBuilder	Makes a copy of the current ISqlBuilder object.
Distinct()	ISqlQueryBuilder	Adds the DISTINCT keyword to the SELECT clause of the current object. It specifies that only unique rows can appear in the result set.
From(string table, string alias)	ISqlFromBuilder	Adds a derived table (specified by another ISqlBuilder object and an alias) to the FROM clause of the current object.
From(string table)	ISqlFromBuilder	Adds a table or view (with no alias specified) to the FROM clause of the current object.
From(string table, string alias, string schema)	ISqlFromBuilder	Adds a table or view (with the alias specified) to the FROM clause of the current object.
From(ISqlBuilder sqlBuilder, string alias)	ISqlFromBuilder	Adds a table or view (with the alias and the schema specified) to the FROM clause of the current object.
FromRaw(string fromClause, string builderAlias)	ISqlFromBuilder	Adds a table source (table, view, or derived table), specified by a raw SQL, to the FROM clause of the current object.
FromRaw(string fromClause)	ISqlFromBuilder	Adds a table source (table, view, or derived table), specified by a raw SQL, to the FROM clause of the current object, and specifies an alias for this table source.
FullJoin(ISqlBuilder sqlBuilder, string alias)	ISqlJoinOnBuilder	Uses the FULL OUTER JOIN operator to join the original table source and the new table source (specified by a subquery generated by an ISqlBuilder object) to create a joined table. You need to specify an alias for the new table source.
FullJoin(string table, string alias, string schema)	ISqlJoinOnBuilder	Uses the FULL OUTER JOIN operator to join the original table source and the new table source to create a joined table. You need to specify a schema and an alias for the new table source.
FullJoin(string table, string alias)	ISqlJoinOnBuilder	Uses the FULL OUTER JOIN operator to join the original table source and the new table source to create a joined table. You need to specify an alias for the new table source.
FullJoin(string table)	ISqlJoinOnBuilder	Uses the FULL OUTER JOIN operator to join the original table source and the new table source to create a joined table.
FullJoinRaw(string joinClause, string builderAlias)	ISqlJoinOnBuilder	Uses the FULL OUTER JOIN operator to join the original table source directly with a raw SQL to create a joined table. You need to specify an alias for the raw SQL.
FullJoinRaw(string joinClaus)	ISqlJoinOnBuilder	Uses the FULL OUTER JOIN operator to join the original table source directly with a raw SQL to create a joined table.
GetJoinType(string tableName)	SqlJoinType?	Gets the type of the JOIN operation for the specified table source.
GetParameterPlacehold(string name)	ISqlParameter[]	Gets the placeholder string for a SQL parameter.
GetTableAlias(string tableName)	string	Gets the alias of a table source that has been added to the current object using the From or FromRaw method.
GroupBy(string groupClause)	ISqlGroupBuilder	Creates a GROUP BY clause, and adds a raw SQL string containing one or more column expressions (each expression specifies a column or a non-aggregate calculation on a column).
GroupBy(params string[] groupExpressions)	ISqlGroupBuilder	Creates a GROUP BY clause and adds an array of SQL expressions (each expression specifies a column or a non-aggregate calculation on a column).
Having(ISqlHavingCondition condition)	ISqlHavingAndOr	Creates a HAVING clause, and adds a search condition to the HAVING clause by an ISqlHavingCondition object.
Having(string left, string right)	ISqlHavingAndOr	Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify SQL expressions on both the left and right of the operator. The operator is '='.
Having(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)	ISqlHavingAndOr	Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify a SQL expression on the left of the operator and a SQL parameter on the right of the operator.
Having(string left, ISqlParameter parameter)	ISqlHavingAndOr	Creates a HAVING clause, and adds a search condition to the HAVING clause. You can specify a SQL expression on the left of the operator and a SQL parameter on the right of the operator. The operator is '='.
Having(ISqlHavingCondition condition, string havingName)	ISqlHavingAndOr	Creates a HAVING clause, and adds a search condition to the HAVING clause by an ISqlHavingCondition object and specifies the name for the search condition.
Having(string left, SqlBinaryOperator sqlOperator, string right)	ISqlHavingAndOr	Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify a SQL expression on the left of the operator and a SQL subquery on the right side. The operator is '='.
Having(string left, ISqlBuilder sqlBuilder)	ISqlHavingAndOr	Creates a HAVING clause and adds a search condition to the HAVING clause. Specifies a SQL expression on the left of the operator and specifies a SQL subquery on the right side. The operator is '='.
Having(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)	ISqlHavingAndOr	Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify a SQL expression on the left of the operator and a SQL subquery on the right side.
HavingRaw(string havingClause, string havingName, params ISqlParameter[] parameters)	ISqlHavingAndOr	Creates a HAVING clause, and adds a raw SQL to the HAVING clause and specifies a name for the raw SQL.
HavingRaw(string havingClause, params ISqlParameter[] parameters)	ISqlHavingAndOr	Creates a HAVING clause and adds a raw SQL to the HAVING clause.
HavingValue(string left, SqlBinaryOperator sqlOperator, object value)	ISqlHavingAndOr	Creates a HAVING clause, and adds a search condition to the HAVING clause. You can specify the SQL expression on the left the operator and a specific value on the right of the operator.
HavingValue(string left, object value)	ISqlHavingAndOr	Creates a HAVING clause, and adds a search condition to the HAVING clause. You can specify a SQL expression on the left the operator and a specific value on the right of the operator. The operator is '='.
InnerJoin(string table)	ISqlJoinOnBuilder	Uses the INNER JOIN operator to join the original table source and the new table source to create a joined table.
InnerJoin(string table, string alias)	ISqlJoinOnBuilder	Uses the INNER JOIN operator to join the original table source and the new table source to create a joined table. You need to specify an alias for the new table source.
InnerJoin(string table, string alias, string schema)	ISqlJoinOnBuilder	Uses the INNER JOIN operator to join the original table source and the new table source to create a joined table. You need to specify a schema and an alias for the new table source.
InnerJoin(ISqlBuilder sqlBuilder, string alias)	ISqlJoinOnBuilder	Uses the INNER JOIN operator to join the original table source and the new table source (specified by a subquery generated by an ISqlBuilder object) to create a joined table. You need to specify an alias for the new table source.
InnerJoinRaw(string joinClause)	ISqlJoinOnBuilder	Uses the INNER JOIN operator to join the original table source directly with a raw SQL to create a joined table.
InnerJoinRaw(string joinClause, string builderAlias)	ISqlJoinOnBuilder	Uses the INNER JOIN operator to join the original table source directly with a raw SQL to create a joined table. You need to specify an alias for the raw SQL.
Join(string table)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source, without specifying the type of the JOIN operator.
Join(string table, string alias)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source, without specifying the type of the JOIN operator. You need to specify an alias for the new table source.
Join(string table, string alias, string schema)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source, without specifying the type of the JOIN operator. You need to specify a schema and an alias for the new table source.
Join(ISqlBuilder sqlBuilder, string alias)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source which is specified by a subquery generated by an ISqlBuilder object. You need to specify an alias for the new table source.
JoinRaw(string joinClause)	ISqlJoinOnBuilder	Creates a table by joining the original table source directly with a raw SQL, without specifying the type of the JOIN operator.
JoinRaw(string joinClause, string builderAlias)	ISqlJoinOnBuilder	Creates a table by joining the original table source directly with a raw SQL, without specifying the type of the JOIN operator. You need to specify an alias for the raw SQL.
LeftJoin(string table, string alia)	ISqlJoinOnBuilder	Uses the LEFT OUTER JOIN operator to join the original table source and the new table source to create a joined table. You need to specify an alias for the new table source.
LeftJoin(string table)	ISqlJoinOnBuilder	Uses the LEFT OUTER JOIN operator to join the original table source and the new table source to create a joined table.
LeftJoin(ISqlBuilder sqlBuilder, string alias)	ISqlJoinOnBuilder	Uses the LEFT OUTER JOIN operator to join the original table source and the new table source (specified by a subquery generated by an ISqlBuilder object) to create a joined table. You need to specify an alias for the new table source.
LeftJoin(string table, string alias, string schema)	ISqlJoinOnBuilder	Uses the LEFT OUTER JOIN operator to join the original table source and the new table source to create a joined table. You need to specify a schema and an alias for the new table source.
LeftJoinRaw(string joinClause, string builderAlias)	ISqlJoinOnBuilder	Uses the LEFT OUTER JOIN operator to join the original table source directly with a raw SQL to create a joined table. You need to specify an alias for the raw SQL.
LeftJoinRaw(string joinClause)	ISqlJoinOnBuilder	Uses the LEFT OUTER JOIN operator to join the original table source directly with a raw SQL to create a joined table.
OrderByAscending(params string[] orderExpressions)	ISqlOrderThenBuilder	Creates an ORDER BY clause and specifies one or more sort criteria in ascending order.
OrderByDescending(params string[] orderExpression)	ISqlOrderThenBuilder	Creates an ORDER BY clause and specifies one or more sort criteria in descending order.
OrderByRaw(string orderClause, string alias)	ISqlOrderThenBuilder	Creates an ORDER BY clause; and specifies one or more sort criteria using a raw SQL and specifies an alias for the raw SQL.
OrderByRaw(string orderClause)	ISqlOrderThenBuilder	Creates an ORDER BY clause and specifies one or more sort criteria using a raw SQL.
Page(int page, int count)	ISqlBuilder	Specifies that the SQL SELECT statement of the current object only returns the data on the specified page of the paginated result set.
RemoveSelect(string alias)	ISqlQueryBuilder	Removes a column (specified by alias) from a selected list.
RemoveWhere(string whereName)	bool	Removes the search condition (specified by name) from the WHERE clause.
RemoveWhere()	bool	Removes all of the search conditions that have been added to the WHERE clause.
ReplaceSelect(string selectRaw)	ISqlQueryBuilder	Replaces the select list in the SELECT clause with the specified raw SQL.
Reset()	ISqlBuilder	Clears the SQL statement and all of the SQL parameters defined in the current object.
RightJoin(string table, string alias, string schema)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source using the RIGHT OUTER JOIN operator. You need to specify a schema and an alias for the new table source.
RightJoin(string table, string alias)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source using the RIGHT OUTER JOIN operator. You need to specify an alias for the new table source.
RightJoin(string table)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source using the RIGHT OUTER JOIN operator.
RightJoin(ISqlBuilder sqlBuilder, string alias)	ISqlJoinOnBuilder	Creates a table by joining the original table source with a new table source (specified by a subquery generated by an ISqlBuilder object) using the RIGHT OUTER JOIN operator. You need to specify an alias for the new table source.
RightJoinRaw(string joinClause)	ISqlJoinOnBuilder	Uses the RIGHT OUTER JOIN operator to join the original table source directly with a raw SQL to create a joined table.
RightJoinRaw(string joinClause, string builderAlias)	ISqlJoinOnBuilder	Uses the RIGHT OUTER JOIN operator to join the original table source directly with a string of raw SQL to create a joined table. You need to specify an alias for the raw SQL.
Select(ISqlBuilder sqlBuilder, string alias)	ISqlQueryBuilder	Adds a column (which is returned by a subquery specified by an ISqlBuilder object) to the select list of a SELECT clause and specifies an alias for this column.
Select(string[] columns)	ISqlQueryBuilder	Adds multiple columns to the select list of a SELECT clause.
Select(string column, string alias)	ISqlQueryBuilder	Adds a column to the select list of a SELECT clause, and specifies an alias for this column.
Select(string column)	ISqlQueryBuilder	Adds a column to the select list of a SELECT clause.
Select(ISqlBuilder sqlBuilder)	ISqlQueryBuilder	Adds a column (which is returned by a subquery specified by an ISqlBuilder object) to the select list of a SELECT clause. It uses the name property of the ISqlBuilder object as the alias for this column by default.
SelectRaw(string selectRaw, string builderAlias, params ISqlParameter[] parameters)	ISqlQueryBuilder	Adds a raw SQL as a column to the select list of a SELECT clause, and specifies an alias for the raw SQL.
SelectRaw(string selectRaw, params ISqlParameter[] parameters)	ISqlQueryBuilder	Adds a raw SQL to the select list of a SELECT clause.
Top(int count)	ISqlBuilder	Adds the TOP keyword to the SELECT clause of the current object. It specifies that only a certain number of rows will be returned from the result set.
ToReadOnly()	ISqlReadOnlyBuilder	Gets a read-only ISqlReadOnlyBuilder object generated by the clone of the current object.
ToSqlString(DataContext context)	string	Returns the raw SQL string for the corresponding database by the current object.
Union(string sqlRaw, string builderAlias)	ISqlQueryBuilder	Uses UNION to combine the result set of the current SQL SELECT statement with the result set of another raw SQL SELECT statement; and specifies an alias for the raw SQL SELECT statement.
Union(string sqlRaw)	ISqlQueryBuilder	Uses UNION to combine the result set of the current SQL SELECT statement with the result set of another raw SQL SELECT statement.
Union(ISqlQueryBuilder queryBuilder, string builderAlias)	ISqlQueryBuilder	Uses UNION to combine the result set of the current SQL SELECT statement with the result set of another SQL SELECT statement generated by the ISqlQueryBuilder object; and specifies an alias for the ISqlQueryBuilder object.
Union(ISqlQueryBuilder queryBuilder)	ISqlQueryBuilder	Uses UNION to combine the result set of the current SQL SELECT statement with the result set of another SQL SELECT statement generated by the ISqlQueryBuilder object.
UnionAll(string sqlRaw, string builderAlias)	ISqlQueryBuilder	Uses UNION ALL to combine the result set of the current SQL SELECT statement with the result set of another raw SQL SELECT statement (the combined result set includes duplicate records); and specifies an alias for the raw SQL SELECT statement.
UnionAll(string sqlRaw)	ISqlQueryBuilder	Uses UNION ALL to combine the result set of the current SQL SELECT statement with the result set of another raw SQL SELECT statement. The combined result set includes duplicate records.
UnionAll(ISqlQueryBuilder queryBuilder, string builderAlias)	ISqlQueryBuilder	Uses UNION ALL to combine the result set of the current SQL SELECT statement and the result set of another SQL SELECT statement generated by the ISqlQueryBuilder object (the combined result set includes duplicate records); and specifies an alias for the ISqlQueryBuilder object.
UnionAll(ISqlQueryBuilder queryBuilder)	ISqlQueryBuilder	Uses UNION ALL to combine the result set of the current SQL SELECT statement and the result set of another SQL SELECT statement generated by the ISqlQueryBuilder object. The combined result set includes duplicate records.
Validate(DataContext context, bool throwError = true)	bool	Validates the raw SQL string for the specified database generated by the current SqlBuilder.
Where(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
Where(ISqlWhereCondition condition, string whereName)	ISqlWhereAndOr	Creates a WHERE clause, adds a search condition to the WHERE clause by an ISqlWhereCondition object, and specifies a name for the search condition.
Where(ISqlWhereCondition condition)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause by an ISqlWhereCondition object.
Where(string left, ISqlBuilder sqlBuilder)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right. The operator is '='.
Where(string left, SqlBinaryOperator sqlOperator, string righ)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies SQL expressions on both the left and right of the operator.
Where(string left, SqlBinaryOperator sqlOperator, ISqlParameter paramete)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the right of the operator.
Where(string left, ISqlParameter parameter)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the right of the operator. The operator is '='.
Where(string left, string right)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies SQL expressions on both the left and right of the operator. The operator is '='.
WhereExists(ISqlBuilder sqlBuilder)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses EXISTS to test for the existence of the record returned from a subquery.
WhereIsNotNull(string left)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses IS NOT NULL operator to search for values that are not null.
WhereIsNull(string left)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses IS NULL operator to search for values that are null.
WhereNotExists(ISqlBuilder sqlBuilder)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses the NOT EXISTS operator to test if the record returned from a subquery does not exist.
WhereRaw(string whereClause, string whereName, params ISqlParameter[] parameters)	ISqlWhereAndOr	Creates a WHERE clause, adds a raw SQL to the WHERE clause, and specifies a name for the raw SQL.
WhereRaw(string whereClause, params ISqlParameter[] parameters)	ISqlWhereAndOr	Creates a WHERE clause, and adds a raw SQL to the WHERE clause.
WhereValue(string left, SqlBinaryOperator sqlOperator, object value)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left of the operator and a specific value on the right of the operator.
WhereValue(string left, object value)	ISqlWhereAndOr	Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left of the operator and a specific value on the right of the operator. The operator is '='.