Class Query
The Query class provides methods to craft an individual query against the database.
The typical pattern for using this class is through the 'Database'. Typically it would not be initialised directly.
Note that this is a stub class that a driver will extend and complete as required for individual database types. Individual drivers could add additional methods, but this is discouraged to ensure that the API is the same for all database types.
Namespace: DataTables
Assembly: DataTables-Editor-Server.dll
Syntax
public abstract class Query : object
Constructors
| Improve this Doc View SourceQuery(Database, String)
Query instance construtor. Should be called by the Database class methods rather than direction initialisation.
Declaration
protected Query(Database db, string type)
Parameters
Type | Name | Description |
---|---|---|
Database | db | Database host |
System.String | type | Query type |
Methods
| Improve this Doc View Source_BuildField(Boolean)
Create a comma separated field list
Declaration
protected virtual string _BuildField(Boolean addAlias = null)
Parameters
Type | Name | Description |
---|---|---|
Boolean | addAlias | Indicate if the fields should have an |
Returns
Type | Description |
---|---|
System.String | SQL list of fields |
_BuildGroupBy()
Create a GROUP BY satement
Declaration
protected virtual string _BuildGroupBy()
Returns
Type | Description |
---|---|
System.String | SQL GROUP BY statement |
_BuildJoin()
Create a JOIN satement list
Declaration
protected virtual string _BuildJoin()
Returns
Type | Description |
---|---|
System.String | SQL list of joins |
_BuildLimit()
Create the LIMIT / OFFSET string.
Default is to create a MySQL and Postgres style statement. Drivers can override
Declaration
protected virtual string _BuildLimit()
Returns
Type | Description |
---|---|
System.String | SQL limit and offset statement |
_BuildOrder()
Create the ORDER BY statement
Declaration
protected virtual string _BuildOrder()
Returns
Type | Description |
---|---|
System.String | SQL order statement |
_BuildSet()
Create a set list
Declaration
protected virtual string _BuildSet()
Returns
Type | Description |
---|---|
System.String | SQL for update |
_BuildTable()
Create the table list
Declaration
protected virtual string _BuildTable()
Returns
Type | Description |
---|---|
System.String | SQL table statement |
_BuildValue()
Create a bind field balue list
Declaration
protected virtual string _BuildValue()
Returns
Type | Description |
---|---|
System.String | SQL value list for inserts |
_BuildWhere()
Create the WHERE statement
Declaration
protected virtual string _BuildWhere()
Returns
Type | Description |
---|---|
System.String | SQL WHERE statement |
_Delete()
Execute a DELETE statement from the current configuration
Declaration
protected virtual Result _Delete()
Returns
Type | Description |
---|---|
Result | Query result |
_Exec()
Execute the query. Provided by the driver
Declaration
protected virtual Result _Exec()
Returns
Type | Description |
---|---|
Result | Query result |
_Insert()
Execute an INSERT statement from the current configuration
Declaration
protected virtual Result _Insert()
Returns
Type | Description |
---|---|
Result | Query result |
_Prepare(String)
Prepare the SQL query by populating the bound variables. Provided by the driver
Declaration
protected virtual void _Prepare(string sql)
Parameters
Type | Name | Description |
---|---|---|
System.String | sql | SQL to run |
_ProtectIdentifiers(String)
Protect field names
Declaration
protected virtual string _ProtectIdentifiers(string identifier)
Parameters
Type | Name | Description |
---|---|---|
System.String | identifier | Field name |
Returns
Type | Description |
---|---|
System.String | Quoted field name |
_Raw(String)
Execute a given statement
Declaration
protected virtual Result _Raw(string sql)
Parameters
Type | Name | Description |
---|---|---|
System.String | sql | SQL to execute |
Returns
Type | Description |
---|---|
Result | Query result |
_SafeBind(String)
The characters that can be used to bind a value are quite limited. We need to abstract this out to allow slightly more complex expressions including dots for easy aliasing
Declaration
protected virtual string _SafeBind(string name)
Parameters
Type | Name | Description |
---|---|---|
System.String | name | Field name |
Returns
Type | Description |
---|---|
System.String | Modify field name |
_Select()
Execute an SELECT statement from the current configuration
Declaration
protected virtual Result _Select()
Returns
Type | Description |
---|---|
Result | Query result |
_Update()
Execute a UPDATE statement from the current configuration
Declaration
protected virtual Result _Update()
Returns
Type | Description |
---|---|
Result | Query result |
_Where(String, Object, String, String, Boolean)
Add an individual where condition to the query
Declaration
protected virtual void _Where(string key, dynamic value, string type = "AND ", string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | key | Wkere key |
System.Object | value | Value to use |
System.String | type | Combination operator |
System.String | op | Conditional operator |
System.Boolean | bind | Bind flag |
_WhereGroup(Boolean, String)
Add parentheses to a where condition
Declaration
protected virtual void _WhereGroup(bool inOut, string op)
Parameters
Type | Name | Description |
---|---|---|
System.Boolean | inOut | Opening ( |
System.String | op | Operator |
AndWhere(Action<Query>)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query AndWhere(Action<Query> fn)
Parameters
Type | Name | Description |
---|---|---|
Action<Query> | fn | Function that can be used to construct a contained set of options. The Query instance is passed in so Where, AndWhere and OrWhere can all be used |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
AndWhere(Dictionary<String, Object>, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query AndWhere(Dictionary<string, dynamic> set, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
Dictionary<System.String, System.Object> | set | Dictionary of key (column name) / value pairs to use for the conditions |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
AndWhere(String, IEnumerable<Object>, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query AndWhere(string key, IEnumerable<dynamic> values, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | key | Column name to perform the condition on |
IEnumerable<System.Object> | values | Values to check. This can be |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
AndWhere(String, Object, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query AndWhere(string key, dynamic value, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | key | Column name to perform the condition on |
System.Object | value | Value to check. This can be |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Bind(String, Object, Object)
Bind a value for safe SQL execution
Declaration
public Query Bind(string name, dynamic value, dynamic type = null)
Parameters
Type | Name | Description |
---|---|---|
System.String | name | Parameter name - should include the leading escape character (typically a colon or @) |
System.Object | value | Value to bind |
System.Object | type | Data type |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Commit(Database)
Commit a transaction
Declaration
public static void Commit(Database dbh)
Parameters
Type | Name | Description |
---|---|---|
Database | dbh | The Db instance to use |
Distinct(Boolean)
Set a distinct flag for a select
query. Note that this has no
effect on any other query type.
Declaration
public Query Distinct(bool dis)
Parameters
Type | Name | Description |
---|---|---|
System.Boolean | dis | Distinct select ( |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Exec(String)
Execute the setup query
Declaration
public Result Exec(string sql = null)
Parameters
Type | Name | Description |
---|---|---|
System.String | sql | SQL string to execute (only if type is 'raw') |
Returns
Type | Description |
---|---|
Result | Query result |
Get(IEnumerable<String>)
Add one or more get (select) field
Declaration
public Query Get(IEnumerable<string> fields)
Parameters
Type | Name | Description |
---|---|---|
IEnumerable<System.String> | fields | List of column names to get |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Get(String)
A column name to get
Declaration
public Query Get(string field)
Parameters
Type | Name | Description |
---|---|---|
System.String | field | Column name to get |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
GroupBy(String)
Add string representing the field to group by
Declaration
public Query GroupBy(string groupBy)
Parameters
Type | Name | Description |
---|---|---|
System.String | groupBy | The string for the group by |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Init(Database)
Method that can be used by the database driver to run commands on first connect
Declaration
public static void Init(Database dbh)
Parameters
Type | Name | Description |
---|---|---|
Database | dbh | Database instance |
Join(String, String, String, Boolean)
Perform a JOIN operation
Declaration
public Query Join(string table, string condition, string type = "", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | table | Table name to do the JOIN on |
System.String | condition | JOIN condition |
System.String | type | JOIN type |
System.Boolean | bind |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Limit(Int32)
Limit the result set to a certain size
Declaration
public Query Limit(int lim)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | lim | The number of records to limit the result to |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Offset(Int32)
Offset the return set by a given number of records (useful for paging).
Declaration
public Query Offset(int off)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | off | The number of records to offset the result by |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Order(IEnumerable<String>)
Order by
Declaration
public Query Order(IEnumerable<string> orders)
Parameters
Type | Name | Description |
---|---|---|
IEnumerable<System.String> | orders | List of columns and direction to order by. Can be specified as individual names or a string of comma separated names. The 'asc' and 'desc' for each column (as in SQL) is optional. |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Order(String)
Order by
Declaration
public Query Order(string order)
Parameters
Type | Name | Description |
---|---|---|
System.String | order | Columns and direction to order by. Can be specified as individual names or a string of comma separated names. The 'asc' and 'desc' for each column (as in SQL) is optional. |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
OrWhere(Action<Query>)
Where query - Bound to the previous condition (if there is one) as an OR statement
Declaration
public Query OrWhere(Action<Query> fn)
Parameters
Type | Name | Description |
---|---|---|
Action<Query> | fn | Function that can be used to construct a contained set of options. The Query instance is passed in so Where, AndWhere and OrWhere can all be used |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
OrWhere(Dictionary<String, Object>, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an OR statement
Declaration
public Query OrWhere(Dictionary<string, dynamic> set, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
Dictionary<System.String, System.Object> | set | Dictionary of key (column name) / value pairs to use for the conditions |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
OrWhere(String, IEnumerable<Object>, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an OR statement
Declaration
public Query OrWhere(string key, IEnumerable<dynamic> values, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | key | Column name to perform the condition on |
IEnumerable<System.Object> | values | Values to check. This can be |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
OrWhere(String, Object, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an OR statement
Declaration
public Query OrWhere(string key, dynamic value, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | key | Column name to perform the condition on |
System.Object | value | Value to check. This can be |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Pkey()
Get the primary key column name(s) that have been set for an insert
Declaration
public string[] Pkey()
Returns
Type | Description |
---|---|
System.String[] | Primary key names |
Pkey(String[])
Set the primary key column names for an insert, so the inserted value can be retrieved in the result.
Declaration
public Query Pkey(string[] pkey)
Parameters
Type | Name | Description |
---|---|---|
System.String[] | pkey | Primary key column names |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Rollback(Database)
Rollback the database state to the start of the transaction
Declaration
public static void Rollback(Database dbh)
Parameters
Type | Name | Description |
---|---|---|
Database | dbh | The Db instance to use |
Set(Dictionary<String, Object>, Boolean)
Set one or more fields to their given values
Declaration
public Query Set(Dictionary<string, dynamic> fields, Boolean bind = null)
Parameters
Type | Name | Description |
---|---|---|
Dictionary<System.String, System.Object> | fields | Key value pairs where the key is the column name |
Boolean | bind | Bind (i.e. escape) the value, or not. Set to false if you want to use a field reference or function as the value |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Set(String, Object, Boolean)
Set a single field to a given value
Declaration
public Query Set(string field, dynamic val, Boolean bind = null)
Parameters
Type | Name | Description |
---|---|---|
System.String | field | Field name to set |
System.Object | val | Value to set |
Boolean | bind | Bind (i.e. escape) the value, or not. Set to false if you want to use a field reference or function as the value |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Set(String, Object, Boolean, Nullable<DbType>)
Set a single field to a given value
Declaration
public Query Set(string field, dynamic val, Boolean bind, DbType? type)
Parameters
Type | Name | Description |
---|---|---|
System.String | field | Field name to set |
System.Object | val | Value to set |
Boolean | bind | Bind (i.e. escape) the value, or not. Set to false if you want to use a field reference or function as the value |
System.Nullable<DbType> | type | Db type |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Table(List<String>)
Set table(s) to perform the query on
Declaration
public Query Table(List<string> tables)
Parameters
Type | Name | Description |
---|---|---|
List<System.String> | tables | Collection of table names |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Table(String)
Set table(s) to perform the query on
Declaration
public Query Table(string table)
Parameters
Type | Name | Description |
---|---|---|
System.String | table | Comma separated list of table names |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Transaction(Database)
Start a new transaction
Declaration
public static void Transaction(Database dbh)
Parameters
Type | Name | Description |
---|---|---|
Database | dbh | The Db instance to use |
Where(Action<Query>)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query Where(Action<Query> fn)
Parameters
Type | Name | Description |
---|---|---|
Action<Query> | fn | Function that can be used to construct a contained set of options. The Query instance is passed in so Where, AndWhere and OrWhere can all be used |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Where(Dictionary<String, Object>, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query Where(Dictionary<string, dynamic> set, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
Dictionary<System.String, System.Object> | set | Dictionary of key (column name) / value pairs to use for the conditions |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Where(String, IEnumerable<Object>, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query Where(string key, IEnumerable<dynamic> values, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | key | Column name to perform the condition on |
IEnumerable<System.Object> | values | Values to check. This can be |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
Where(String, Object, String, Boolean)
Where query - Bound to the previous condition (if there is one) as an AND statement
Declaration
public Query Where(string key, dynamic value, string op = "=", bool bind = true)
Parameters
Type | Name | Description |
---|---|---|
System.String | key | Column name to perform the condition on |
System.Object | value | Value to check. This can be |
System.String | op | Conditional operation to perform |
System.Boolean | bind | Bind the value or not. Binding will cause the parameter to effectively be escaped, which you might not want for some cases, such as passing in an SQL function as the condition |
Returns
Type | Description |
---|---|
Query | Query instance for chaining |
WhereGroup(Action<Query>, String)
Provide grouping for WHERE conditions.
Declaration
public Query WhereGroup(Action<Query> fn, string op = "AND")
Parameters
Type | Name | Description |
---|---|---|
Action<Query> | fn | Callback function which will have any conditions it assigns to the query automatically grouped. |
System.String | op | Conditional operator to use to join to the preceding condition. |
Returns
Type | Description |
---|---|
Query | Self for chaining |
WhereGroup(Boolean, String)
Provide grouping for WHERE conditions.
Declaration
public Query WhereGroup(bool inOut, string op = "AND")
Parameters
Type | Name | Description |
---|---|---|
System.Boolean | inOut |
|
System.String | op | Conditional operator to use to join to the preceding condition. |
Returns
Type | Description |
---|---|
Query | Self for chaining |
WhereIn(String, ICollection<Object>, String)
Provide a method that can be used to perform a WHERE ... IN (...)
query with bound values and parameters.
Declaration
public Query WhereIn(string field, ICollection<object> values, string op = "AND")
Parameters
Type | Name | Description |
---|---|---|
System.String | field | Field name to condition on |
ICollection<System.Object> | values | Values to bind |
System.String | op | Conditional operator to use to join to the preceding condition. |
Returns
Type | Description |
---|---|
Query |