C# Class Serenity.Data.SqlQuery

Inheritance: QueryWithParams, IFilterableQuery, IGetExpressionByName

显示文件 Open project: volkanceylan/Serenity Class Usage Examples

Private Properties

Property	Type	Description
AppendFromWhereOrderByGroupByHaving	void
EnsureJoin	void
IFilterableQuery	void
IGetExpressionByName	string
ISqlQueryExtensible	int
ISqlQueryExtensible	void
JoinToString	void

Public Methods

Method	Description
Clone ( ) : SqlQuery	Creates a clone of the query. Clones states like TrackAssignments, AssignedFields etc, creates a copy of Params dictionary
Dialect ( ) : ISqlDialect	Gets the dialect (SQL server type / version) for query.
Dialect ( ISqlDialect dialect ) : SqlQuery	Sets the dialect (SQL server type / version) for query. TODO: SqlDialect system should be improved.
Distinct ( bool distinct ) : SqlQuery	Sets DISTINCT flag.
EnsureJoin ( Serenity.Data.Join join ) : SqlQuery
EnsureJoinsInExpression ( string expression ) : SqlQuery
ForXml ( string forXml ) : SqlQuery
From ( IAlias alias ) : SqlQuery	Adds a table to the FROM statement, with given short name. This overload requires that alias has a table name.
From ( ISqlQuery subQuery, IAlias alias ) : SqlQuery	Adds a subquery to the FROM statement, with given short name. This overload requires that alias has a table name.
From ( string table ) : SqlQuery	Adds a table to the FROM statement. When it is called more than once, puts a comma between table names (cross join)
From ( string table, IAlias alias ) : SqlQuery	Adds a table to the FROM statement with an alias. When it is called more than once, puts a comma between table names (cross join)
GroupBy ( IAlias alias, string fieldName ) : SqlQuery	Adds a field of a given table alias to the GROUP BY clause.
GroupBy ( string expression ) : SqlQuery	Adds a field name or an SQL expression to the GROUP BY clause.
Having ( string expression ) : SqlQuery	Adds an SQL expression to the GROUP BY clause.
InnerJoin ( IAlias alias, ICriteria onCriteria ) : SqlQuery
Join ( Serenity.Data.Join join ) : SqlQuery
LeftJoin ( IAlias alias, ICriteria onCriteria ) : SqlQuery
LeftJoin ( string toTable, IAlias alias, ICriteria onCriteria ) : SqlQuery
OrderBy ( IAlias alias, string fieldName, bool desc = false ) : SqlQuery	Adds a field of a given table alias to the ORDER BY clause.
OrderBy ( string expression, bool desc = false ) : SqlQuery	Adds a field name or an SQL expression to the ORDER BY clause.
OrderByFirst ( string expression, bool desc = false ) : SqlQuery	Inserts a field name or an SQL expression to the start of ORDER BY clause. This method is designed to help apply user defined orders (for example by clicking headers on a grid) to a query with existing order. SQL server throws an error if a field is used more than once in ORDER BY expression, so this function first removes normal and DESC versions of the expression from the ORDER BY statement.
RightJoin ( IAlias alias, ICriteria onCriteria ) : SqlQuery
RightJoin ( string toTable, IAlias alias, ICriteria onCriteria ) : SqlQuery
Select ( IAlias alias, string fieldName ) : SqlQuery	Adds a field of a given table alias to the SELECT statement. No column name is used for the field or expression.
Select ( IAlias alias, string fieldName, string columnName ) : SqlQuery	Adds a field of a given table alias to the SELECT statement with a column name.
Select ( ISqlQuery expression ) : SqlQuery	Adds a subquery to the SELECT statement.
Select ( ISqlQuery expression, string columnName ) : SqlQuery	Adds a subquery to the SELECT statement.
Select ( string expression ) : SqlQuery	Adds a field name or an SQL expression to the SELECT statement. No column name is used for the field or expression.
Select ( string expression, string columnName ) : SqlQuery	Adds a field name or expression to the SELECT statement with a column name
SelectMany ( ) : SqlQuery	Adds field names or SQL expressions to the SELECT statement. No aliases are used for the fields or expressions.
Skip ( int skipRows ) : SqlQuery	Sets SKIP value. Used for paging.
Skip ( ) : int	Gets current SKIP value.
SqlQuery ( ) : System	Creates a new SqlQuery instance.
SubQuery ( ) : SqlQuery	Creates a new query that shares parameter dictionary with this query.
Take ( int rowCount ) : SqlQuery	Sets TAKE/TOP value. Used for paging.
Take ( ) : int	Gets TAKE/TOP value.
ToString ( ) : string	SqlSelect sorgusunu formatlayıp bir SELECT sorgusuna çevirir. Sayfalama sözkonusuysa (atlanan kayıt varsa) birden fazla sorgu arka arkaya oluşturulur.
Where ( ) : SqlQuery	Adds expressions to WHERE clause, inserting AND between them.
Where ( string expression ) : SqlQuery	Adds an expression to WHERE clause. If query already has a WHERE clause, inserts AND between existing one and new one.

Private Methods

Method	Description
AppendFromWhereOrderByGroupByHaving ( StringBuilder sb, string extraWhere, bool includeOrderBy ) : void	Verilen StringBuilder nesnesine SqlSelect'in FROM, WHERE, ORDER BY, GROUP BY, HAVING kısımlarını, belirtilirse bir ek filtre de gözönüne alınarak ekler. Sayfalama için üretilen sorgularda SqlSelect'in bu kısımları iki ayrı yerde (birinde ek bir koşulla birlikte) kullanıldığından, bu şekilde yapılarak, kod tekrarının önüne geçilmiştir.
EnsureJoin ( string joinAlias ) : void
IFilterableQuery ( string expression ) : void	Implements IDBFilterable.Where, by calling original Where method.
IGetExpressionByName ( string columnName ) : string	Gets the source expression for a column name in the query. This function uses a linear search in column list, so use with caution.
ISqlQueryExtensible ( IField field ) : int
ISqlQueryExtensible ( object row ) : void
JoinToString ( Serenity.Data.Join join, StringBuilder sb, bool modifySelf ) : void

Method Details

Clone() public method

Creates a clone of the query.

Clones states like TrackAssignments, AssignedFields etc, creates a copy of Params dictionary

public Clone ( ) : SqlQuery
return	SqlQuery

Dialect() public method

Gets the dialect (SQL server type / version) for query.

public Dialect ( ) : ISqlDialect
return	ISqlDialect

Dialect() public method

Sets the dialect (SQL server type / version) for query.

TODO: SqlDialect system should be improved.

public Dialect ( ISqlDialect dialect ) : SqlQuery
dialect	ISqlDialect
return	SqlQuery

Distinct() public method

Sets DISTINCT flag.

public Distinct ( bool distinct ) : SqlQuery
distinct	bool	Distinct flag.
return	SqlQuery

EnsureJoin() public method

public EnsureJoin ( Serenity.Data.Join join ) : SqlQuery
join	Serenity.Data.Join
return	SqlQuery

EnsureJoinsInExpression() public method

public EnsureJoinsInExpression ( string expression ) : SqlQuery
expression	string
return	SqlQuery

ForXml() public method

public ForXml ( string forXml ) : SqlQuery
forXml	string
return	SqlQuery

From() public method

Adds a table to the FROM statement, with given short name.

This overload requires that alias has a table name.

public From ( IAlias alias ) : SqlQuery
alias	IAlias	Alias that contains table name and short name.
return	SqlQuery

From() public method

Adds a subquery to the FROM statement, with given short name.

This overload requires that alias has a table name.

public From ( ISqlQuery subQuery, IAlias alias ) : SqlQuery
subQuery	ISqlQuery	A subquery
alias	IAlias	Alias that contains the short name.
return	SqlQuery

From() public method

Adds a table to the FROM statement. When it is called more than once, puts a comma between table names (cross join)

public From ( string table ) : SqlQuery
table	string	Table name
return	SqlQuery

From() public method

Adds a table to the FROM statement with an alias. When it is called more than once, puts a comma between table names (cross join)

public From ( string table, IAlias alias ) : SqlQuery
table	string	Table
alias	IAlias	Alias for the table
return	SqlQuery

GroupBy() public method

Adds a field of a given table alias to the GROUP BY clause.

public GroupBy ( IAlias alias, string fieldName ) : SqlQuery
alias	IAlias	A table alias that will be prepended to the field name with "." between
fieldName	string	A field name of the aliased table.
return	SqlQuery

GroupBy() public method

Adds a field name or an SQL expression to the GROUP BY clause.

public GroupBy ( string expression ) : SqlQuery
expression	string	Array of fields or expressions.
return	SqlQuery

Having() public method

Adds an SQL expression to the GROUP BY clause.

public Having ( string expression ) : SqlQuery
expression	string	Array of fields or expressions.
return	SqlQuery

InnerJoin() public method

public InnerJoin ( IAlias alias, ICriteria onCriteria ) : SqlQuery
alias	IAlias
onCriteria	ICriteria
return	SqlQuery

Join() public method

public Join ( Serenity.Data.Join join ) : SqlQuery
join	Serenity.Data.Join
return	SqlQuery

LeftJoin() public method

public LeftJoin ( IAlias alias, ICriteria onCriteria ) : SqlQuery
alias	IAlias
onCriteria	ICriteria
return	SqlQuery

LeftJoin() public method

public LeftJoin ( string toTable, IAlias alias, ICriteria onCriteria ) : SqlQuery
toTable	string
alias	IAlias
onCriteria	ICriteria
return	SqlQuery

OrderBy() public method

Adds a field of a given table alias to the ORDER BY clause.

public OrderBy ( IAlias alias, string fieldName, bool desc = false ) : SqlQuery
alias	IAlias	A table alias that will be prepended to the field name with "." between
fieldName	string	A field name of the aliased table.
desc	bool	True to add " DESC" keyword to the expression.
return	SqlQuery

OrderBy() public method

Adds a field name or an SQL expression to the ORDER BY clause.

public OrderBy ( string expression, bool desc = false ) : SqlQuery
expression	string	A field or an SQL expression.
desc	bool	True to add " DESC" keyword to the expression.
return	SqlQuery

OrderByFirst() public method

Inserts a field name or an SQL expression to the start of ORDER BY clause.

This method is designed to help apply user defined orders (for example by clicking headers on a grid) to a query with existing order. SQL server throws an error if a field is used more than once in ORDER BY expression, so this function first removes normal and DESC versions of the expression from the ORDER BY statement.

public OrderByFirst ( string expression, bool desc = false ) : SqlQuery
expression	string	A field or an SQL expression.
desc	bool	True to add a " DESC" suffix.
return	SqlQuery

RightJoin() public method

public RightJoin ( IAlias alias, ICriteria onCriteria ) : SqlQuery
alias	IAlias
onCriteria	ICriteria
return	SqlQuery

RightJoin() public method

public RightJoin ( string toTable, IAlias alias, ICriteria onCriteria ) : SqlQuery
toTable	string
alias	IAlias
onCriteria	ICriteria
return	SqlQuery

Select() public method

Adds a field of a given table alias to the SELECT statement.

No column name is used for the field or expression.

public Select ( IAlias alias, string fieldName ) : SqlQuery
alias	IAlias	A table alias that will be prepended to the field name with "." between
fieldName	string	A field name of the aliased table.
return	SqlQuery

Select() public method

Adds a field of a given table alias to the SELECT statement with a column name.

public Select ( IAlias alias, string fieldName, string columnName ) : SqlQuery
alias	IAlias	A table alias that will be prepended to the field name with "." between
fieldName	string	A field name of the aliased table.
columnName	string	A column name
return	SqlQuery

Select() public method

Adds a subquery to the SELECT statement.

public Select ( ISqlQuery expression ) : SqlQuery
expression	ISqlQuery	A subquery.
return	SqlQuery

Select() public method

Adds a subquery to the SELECT statement.

public Select ( ISqlQuery expression, string columnName ) : SqlQuery
expression	ISqlQuery	A subquery.
columnName	string	A column name
return	SqlQuery

Select() public method

Adds a field name or an SQL expression to the SELECT statement.

No column name is used for the field or expression.

public Select ( string expression ) : SqlQuery
expression	string	A field or an SQL expression.
return	SqlQuery

Select() public method

Adds a field name or expression to the SELECT statement with a column name

public Select ( string expression, string columnName ) : SqlQuery
expression	string	A field name or SQL expression.
columnName	string	A column name.
return	SqlQuery

SelectMany() public method

Adds field names or SQL expressions to the SELECT statement.

No aliases are used for the fields or expressions.

public SelectMany ( ) : SqlQuery
return	SqlQuery

Skip() public method

Sets SKIP value. Used for paging.

public Skip ( int skipRows ) : SqlQuery
skipRows	int	Number of rows to skip (server dependant implementation)
return	SqlQuery

Skip() public method

Gets current SKIP value.

public Skip ( ) : int
return	int

SqlQuery() public method

Creates a new SqlQuery instance.

public SqlQuery ( ) : System
return	System

SubQuery() public method

Creates a new query that shares parameter dictionary with this query.

public SubQuery ( ) : SqlQuery
return	SqlQuery

Take() public method

Sets TAKE/TOP value. Used for paging.

public Take ( int rowCount ) : SqlQuery
rowCount	int	Number of rows to take.
return	SqlQuery

Take() public method

Gets TAKE/TOP value.

public Take ( ) : int
return	int

ToString() public method

SqlSelect sorgusunu formatlayıp bir SELECT sorgusuna çevirir. Sayfalama sözkonusuysa (atlanan kayıt varsa) birden fazla sorgu arka arkaya oluşturulur.

public ToString ( ) : string
return	string

Where() public method

Adds expressions to WHERE clause, inserting AND between them.

public Where ( ) : SqlQuery
return	SqlQuery

Where() public method

Adds an expression to WHERE clause. If query already has a WHERE clause, inserts AND between existing one and new one.

public Where ( string expression ) : SqlQuery
expression	string	An expression
return	SqlQuery