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.

Inheritance

System.Object

Query

Query

Query

Query

Query

Query

Namespace: DataTables

Assembly: DataTables-Editor-Server.dll

Syntax

public abstract class Query : object

Constructors

Query(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

_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 as alias added automatically (true) or not

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 (true) or closing bracket
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 null for an IS NULL or IS NOT NULL condition, depending on the value of op which should be = or !=
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 null for an IS NULL or IS NOT NULL condition, depending on the value of op which should be = or !=
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 (true) or not (false)

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()

Columns to get

Declaration

public List<string> Get()

Returns

Type	Description
List<System.String>	List of column names

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

LeftJoin(List<LeftJoin>)

Add a collection of left joins to the query

Declaration

public Query LeftJoin(List<LeftJoin> leftJoin)

Parameters

Type	Name	Description
List<LeftJoin>	leftJoin	Left join list

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 null for an IS NULL or IS NOT NULL condition, depending on the value of op which should be = or !=
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 null for an IS NULL or IS NOT NULL condition, depending on the value of op which should be = or !=
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 null for an IS NULL or IS NOT NULL condition, depending on the value of op which should be = or !=
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 null for an IS NULL or IS NOT NULL condition, depending on the value of op which should be = or !=
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	true to open brackets, false to close
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