Class Database

DataTables Database connection object.

Create a database connection which may then have queries performed upon it.

This is a database abstraction class that can be used on multiple different databases. As a result of this, it might not be suitable to perform complex queries through this interface or vendor specific queries, but everything required for basic database interaction is provided through the abstracted methods.

Inheritance

System.Object

Database

Namespace: DataTables

Assembly: DataTables-Editor-Server.dll

Syntax

public class Database : IDisposable

Constructors

Database(String, DbConnection)

Create a database connection

Declaration

public Database(string dbType, DbConnection conn)

Parameters

Type	Name	Description
System.String	dbType	Database type - this should be "sqlserver" or "mysql"
DbConnection	conn	Database connection that has already been established to the SQL server

Database(String, DbConnectionStringBuilder)

Create a database connection

Declaration

public Database(string dbType, DbConnectionStringBuilder builder)

Parameters

Type	Name	Description
System.String	dbType	Database type - this should be "sqlserver" or "mysql"
DbConnectionStringBuilder	builder	Connection string builder instance to connect to the SQL server

Database(String, String, String)

Create a database connection

Declaration

public Database(string dbType, string str, string adapter = null)

Parameters

Type	Name	Description
System.String	dbType	Database type - this should be "sqlserver" or "mysql"
System.String	str	Connection string to connect to the SQL server
System.String	adapter	Set the database provider factory

Fields

CommandTimeout

Command timeout (seconds). The next command executed will take the timeout of this value. -1 indicates that it will not be applied (i.e. the system default applied), 0 is no limit. See also: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout

Declaration

public int CommandTimeout

Field Value

Type	Description
System.Int32

DbTransaction

Declaration

public DbTransaction DbTransaction

Field Value

Type	Description
DbTransaction

Methods

Adapter()

Get the database provider factory

Declaration

public string Adapter()

Returns

Type	Description
System.String	Provider factory name

Adapter(String)

Set the database provider factory

Declaration

public Database Adapter(string set)

Parameters

Type	Name	Description
System.String	set	Provider factory name

Returns

Type	Description
Database	Self for chaining

Any(String, Action<Query>)

Determine if there is any data in the table that matches the query condition

Declaration

public bool Any(string table, Action<Query> where = null)

Parameters

Type	Name	Description
System.String	table	Table name to act upon
Action<Query>	where	Where condition for what to select

Returns

Type	Description
System.Boolean	Boolean flag - true if there were rows

Commit()

Commit the current transaction

Declaration

public Database Commit()

Returns

Type	Description
Database	Self for chaining

Conn()

Get the database connection

Declaration

public DbConnection Conn()

Returns

Type	Description
DbConnection	Database connection

DbType()

Get the database type

Declaration

public string DbType()

Returns

Type	Description
System.String	Database type

Debug()

Get the current debug state for this Database instance

Declaration

public bool Debug()

Returns

Type	Description
System.Boolean	true if debug recording is enabled, false otherwise

Debug(Action<Object>)

Set the debug callback function

Declaration

public Database Debug(Action<object> callback)

Parameters

Type	Name	Description
Action<System.Object>	callback	Function that will be used to log debug messages

Returns

Type	Description
Database	Self for chaining

Debug(Boolean)

Set the debug state. true will enable recording of SQL statements

Declaration

public Database Debug(bool debug)

Parameters

Type	Name	Description
System.Boolean	debug	Debug flag

Returns

Type	Description
Database	Self for chaining

DebugInfo(String, List<Binding>)

Record debug information

Declaration

public Database DebugInfo(string sql, List<Binding> bindings = null)

Parameters

Type	Name	Description
System.String	sql	Query
List<Binding>	bindings	Bindings

Returns

Type	Description
Database	Self for chaining

Delete(String, Dictionary<String, Object>)

Perform a delete query on a table.

This is a short cut method that creates and update query and then uses the

query('delete')

, table, where and exec methods of the query.

Declaration

public Result Delete(string table, Dictionary<string, dynamic> where)

Parameters

Type	Name	Description
System.String	table	Table to operate the delete on
Dictionary<System.String, System.Object>	where	Collection of conditions to apply to the delete to

Returns

Type	Description
Result	Result instance

Dispose()

Dispose of this database instance

Declaration

public void Dispose()

GetTransaction()

Get the current database transaction. Will return null if not in a transaction

Declaration

public DbTransaction GetTransaction()

Returns

Type	Description
DbTransaction

Insert(String, Dictionary<String, Object>, String[])

Insert data into a table.

This is a short cut method that creates an update query and then uses the

query('insert')

, table, set and exec methods of the query.

Declaration

public Result Insert(string table, Dictionary<string, dynamic> set, string[] pkey = null)

Parameters

Type	Name	Description
System.String	table	Table to perform the insert on
Dictionary<System.String, System.Object>	set	Dictionary of field names and values to set
System.String[]	pkey	Primary key column names (this is an array for forwards compt, although only the first item in the array is actually used). This doesn't need to be set, but it must be if you want to use the Result.InsertId() method.

Returns

Type	Description
Result	Result instance

Push(String, Dictionary<String, Object>, Dictionary<String, Object>, String[])

Update or Insert data. When doing an insert, the where condition is added as a set field

Declaration

public Result Push(string table, Dictionary<string, dynamic> set, Dictionary<string, dynamic> where, string[] pkey = null)

Parameters

Type	Name	Description
System.String	table	Table name to act upon
Dictionary<System.String, System.Object>	set	Dictionary of field names and values to update / set
Dictionary<System.String, System.Object>	where	Where condition for what to update
System.String[]	pkey	Primary key column names (this is an array for forwards compt, although only the first item in the array is actually used). This doesn't need to be set, but it must be if you want to use the Result.InsertId() method.

Returns

Type	Description
Result	Result instance

Query(String)

Create a query object to build a database query.

Declaration

public Query Query(string type)

Parameters

Type	Name	Description
System.String	type	Database type - this can be 'mysql', 'oracle', 'sqlite' or 'sqlserver'

Returns

Type	Description
Query	Query for the database type given

Query(String, String)

Create a query object to build a database query.

Declaration

public Query Query(string type, string table)

Parameters

Type	Name	Description
System.String	type	Database type - this can be 'mysql', 'oracle', 'sqlite' or 'sqlserver'
System.String	table	Table to setup this query to execute against

Returns

Type	Description
Query	Query for the database type given

Rollback()

Rollback the database state to the start of the transaction.

Declaration

public Database Rollback()

Returns

Type	Description
Database	Self for chaining

Select(String, IEnumerable<String>, Action<Query>, IEnumerable<String>)

Select data from a table.

This is a short cut method that creates an update query and then uses the

query('select')

, table, get, where and exec methods of the query.

Declaration

public Result Select(string table, IEnumerable<string> field = null, Action<Query> where = null, IEnumerable<string> orderBy = null)

Parameters

Type	Name	Description
System.String	table	Table name to act upon
IEnumerable<System.String>	field	Collection of field names to get. If null all fields are returned
Action<Query>	where	Where condition for what to select
IEnumerable<System.String>	orderBy	Order by condition

Returns

Type	Description
Result	Result instance

Select(String, IEnumerable<String>, Dictionary<String, Object>, IEnumerable<String>)

Select data from a table.

This is a short cut method that creates an update query and then uses the

query('select')

, table, get, where and exec methods of the query.

Declaration

public Result Select(string table, IEnumerable<string> field = null, Dictionary<string, dynamic> where = null, IEnumerable<string> orderBy = null)

Parameters

Type	Name	Description
System.String	table	Table name to act upon
IEnumerable<System.String>	field	Collection of field names to get. If null all fields are returned
Dictionary<System.String, System.Object>	where	Where condition for what to select
IEnumerable<System.String>	orderBy	Order by condition

Returns

Type	Description
Result	Result instance

SelectDistinct(String, IEnumerable<String>, Action<Query>, IEnumerable<String>)

Select distinct data from a table.

This is a short cut method that creates an update query and then uses the

query('select')

, distinct ,table, get, where and exec methods of the

query.

Declaration

public Result SelectDistinct(string table, IEnumerable<string> field = null, Action<Query> where = null, IEnumerable<string> orderBy = null)

Parameters

Type	Name	Description
System.String	table	Table name to act upon
IEnumerable<System.String>	field	Collection of field names to get. If null all fields are returned
Action<Query>	where	Where condition for what to select
IEnumerable<System.String>	orderBy	Order by condition

Returns

Type	Description
Result	Result instance

SelectDistinct(String, IEnumerable<String>, Action<Query>, String)

Select distinct data from a table.

This is a short cut method that creates an update query and then uses the

query('select')

, distinct ,table, get, where and exec methods of the

query.

Declaration

public Result SelectDistinct(string table, IEnumerable<string> field = null, Action<Query> where = null, string orderBy = null)

Parameters

Type	Name	Description
System.String	table	Table name to act upon
IEnumerable<System.String>	field	Collection of field names to get. If null all fields are returned
Action<Query>	where	Where condition for what to select
System.String	orderBy	Order by condition

Returns

Type	Description
Result	Result instance

SelectDistinct(String, IEnumerable<String>, Dictionary<String, Object>, IEnumerable<String>)

Select distinct data from a table.

This is a short cut method that creates an update query and then uses the

query('select')

, distinct ,table, get, where and exec methods of the

query.

Declaration

public Result SelectDistinct(string table, IEnumerable<string> field = null, Dictionary<string, dynamic> where = null, IEnumerable<string> orderBy = null)

Parameters

Type	Name	Description
System.String	table	Table name to act upon
IEnumerable<System.String>	field	Collection of field names to get. If null all fields are returned
Dictionary<System.String, System.Object>	where	Where condition for what to select
IEnumerable<System.String>	orderBy	Order by condition

Returns

Type	Description
Result	Result instance

Sql(String)

Execute an raw SQL query - i.e. give the method your own SQL, rather than having the Database classes building it for you.

Declaration

public Result Sql(string sql)

Parameters

Type	Name	Description
System.String	sql	SQL to execute

Returns

Type	Description
Result	Result instance

Transaction()

Start a new database transaction.

Declaration

public Database Transaction()

Returns

Type	Description
Database	Self for chaining

Update(String, Dictionary<String, Object>, Dictionary<String, Object>)

Update data.

This is a short cut method that creates an update query and then uses the

query('update')

, table, set, where and exec methods of the query.

Declaration

public Result Update(string table, Dictionary<string, dynamic> set, Dictionary<string, dynamic> where)

Parameters

Type	Name	Description
System.String	table	Table name to operate on
Dictionary<System.String, System.Object>	set	Field names and values to set
Dictionary<System.String, System.Object>	where	Where condition for what to update

Returns

Type	Description
Result	Self for chaining