WX5.DB.DBManager Class Reference

Database manager used to automate some ADO.NET routines DBManager allows special syntax inside SQL queries similar to L# to describe query parameters:

{? parameterName [parameterType [parameterDirection [columnMapping]]]}

where parameterType and/or parameterDirection and/or columnMapping and even parameterName are optional. More...

List of all members.

Public Member Functions
	DBManager (DBKind kind)
	Initializes DBManager instance, ConnectionString still need to be set.
	DBManager (DBKind kind, string connectionString)
	Initializes DBManager instance.
	DBManager (DBProvider customProvider, SQLEngine customEngine)
	This constructor allows using of DBManager with external DBProvider/SQLEngine objects, say to use native SQLite, Oracle or MySQL data providers instead of ODBC/OLEDB.
virtual DBManager	OpenConnection ()
	In manual mode opens a connection.
virtual DBManager	CloseConnection ()
	In manual mode closes the connection.
virtual DBManager	ResetEngineEnvironment ()
	Resets L# environment.
virtual int	ExecSQL (string exprSQL)
	Executes a query.
virtual int	ExecSQL (string exprLISPSQL, params object[] args)
	Executes a query with parameters.
virtual int	ExecSQL (BlockX< IDataParameterCollection > processor, string exprLISPSQL, params object[] args)
	Executes a SQL query with parameters including post-processing of parameters.
virtual int	ExecSQL (string exprLISPSQL, out List< object > retValues, params object[] args)
	Executes a SQL query with parameters.
virtual int	ExecSQL (BlockX< IDataParameterCollection > processor, string exprLISPSQL, out List< object > retValues, params object[] args)
	Executes a SQL query with parameters including post-processing of parameters.
virtual object	GetValue (string exprSQL)
virtual object	GetValue (string exprLISPSQL, params object[] args)
virtual object	GetValue (BlockX< IDataParameterCollection > processor, string exprLISPSQL, params object[] args)
virtual object	GetValue (string exprLISPSQL, out List< object > retValues, params object[] args)
virtual object	GetValue (BlockX< IDataParameterCollection > processor, string exprLISPSQL, out List< object > retValues, params object[] args)
virtual A	GetValue< A > (string exprSQL)
virtual A	GetValue< A > (string exprLISPSQL, params object[] args)
virtual A	GetValue< A > (string exprLISPSQL, out List< object > retValues, params object[] args)
virtual List< object >	GetValueList (string exprSQL)
virtual List< object >	GetValueList (string exprLISPSQL, params object[] args)
virtual List< object >	GetValueList (string exprLISPSQL, out List< object > retValues, params object[] args)
virtual DataTable	LoadTable (DataTable target, string exprSQL)
	Loads a DataTable object from database.
virtual DataTable	LoadTable (DataTable target, string exprLISPSQL, params object[] args)
	Loads a DataTable object from database.
virtual DataTable	StoreTable (DataTable target, string updLISPSQL, string insLISPSQL, string delLISPSQL)
	Stores a datatable into database.
virtual List< object >	GetArgs (DataRow source, List< string > argSequence, bool isExcludeSequence)
virtual List< object >	ExecScript (bool includeAll, params Tuple3< string, bool, object[]>[] script)
	Sequentially executes a list uf queries with its parameters.
virtual List< object >	ExecScript (params Tuple3< string, bool, object[]>[] script)
	Sequentially executes a list uf queries with its parameters.
Protected Member Functions
virtual DBManager	AutoOpenConnection ()
virtual DBManager	AutoCloseConnection ()
virtual DBManager	HandleException (Exception e)
Protected Attributes
DBProvider	_provider
	ADO.NET database objects provider.
SQLEngine	_engine
	SQL Engine.
DBControl	_control
	Current way of connection control.
ParameterDirection	_direction
	Default parameter direction.
bool	_useErrorDialogs
	If set to True, then forces displaying of error message instead of exception throwing.
Properties
DBProvider	Provider `[get]`
	Allows access to the internal DBProvider object.
SQLEngine	Engine `[get]`
	Allows access to the internal SQLEngine object.
virtual bool	UseErrorDialogs `[get, set]`
virtual DBControl	ConnectionManagement `[get, set]`
virtual string	ConnectionString `[get, set]`
virtual int	CommandTimeout `[get, set]`

Detailed Description

Database manager used to automate some ADO.NET routines DBManager allows special syntax inside SQL queries similar to L# to describe query parameters:

{? parameterName [parameterType [parameterDirection [columnMapping]]]}

where parameterType and/or parameterDirection and/or columnMapping and even parameterName are optional.

Here the list of possible parameterType values:

Value	Description
$bool	Boolean type
$datetime or $dt	Date/Time value
$blob or $bin	BLOB value, System.Byte[] array
$int	32-bit integer
$uint	64-bit integer
$dbl	System.Double value
$str	String value
$guid	GUID value

Here the list of possible parameterDirection values:

Value	Description
$$in or $$i	Input direction
$$out or $$o	Output direction
$$inout or $$io	Input/output direction
$$return or $$r	Return value

So, "{? ...}" expression is replaced using rules for concrete data provider used by DBManager. Thus, for example, for SQL Server the SQL query

SELECT * FROM t WHERE uid = {? myId $int}

will be transformed to

SELECT * FROM t WHERE uid = @myId

, and, in addition, the SqlParameter object will be created with name "@myId" and type SqlDbType.Int.
Default behaviours:
1. Default ParameterDirection (if ommited in parameter description), will be given from _direcion field (default value = ParameterDirection.Input)
2. Default type - Variant
3. Default column mapping is the same as parameter name
The next example for SQL Server:

INSERT INTO T(id, name) VALUES( {? _id $int $io colId}, {? _name $str} );

For this query 2 parameters will be created:
@_id and @_name,
first will have the type SqlDbType.Int, second - SqlDbType.NVarChar,
first will have the direction InputOutput, second - Input,
first will have the column mapping "colId", second - "_name"

The L# is used to process "{? ...}" expressions so you can use any valid L# expressions instead of paramName, paramType, paramDirection and columnMapping:

        // Example
        DBManager m = new DBManager(DBKind.dbkMSSQL, "data source=localhost;initial catalog=test");
        int x = m.GetValue<int>("SELECT 1");
        DataTable t = m.LoadTable(new DataTable(), "SELECT * FROM SomeTable WHERE SomeTablePK = {? param $int}", 7);

Constructor & Destructor Documentation

WX5.DB.DBManager.DBManager ( DBKind kind )

Initializes DBManager instance, ConnectionString still need to be set.

Parameters:

kind

built-in data provider

WX5.DB.DBManager.DBManager	(	DBKind	kind,
		string	connectionString
	)

Initializes DBManager instance.

Parameters:

	kind	built-in data provider
	connectionString	connection string specific to the selected data provider

WX5.DB.DBManager.DBManager	(	DBProvider	customProvider,
		SQLEngine	customEngine
	)

This constructor allows using of DBManager with external DBProvider/SQLEngine objects, say to use native SQLite, Oracle or MySQL data providers instead of ODBC/OLEDB.

Parameters:

	customProvider	custom provider
	customEngine	custom engine

Member Function Documentation

virtual DBManager WX5.DB.DBManager.CloseConnection ( ) [virtual]

In manual mode closes the connection.

Returns:: current DBManager instance

virtual List<object> WX5.DB.DBManager.ExecScript ( params Tuple3< string, bool, object[]>[] script ) [virtual]

Sequentially executes a list uf queries with its parameters.

Parameters:

script

a list of 3-element tuples where Value1 - SQL string, Value2 - True if query returns a data, Value3 - list of input parameter values

Returns:: a sequence of execution results for queries that returns values

virtual List<object> WX5.DB.DBManager.ExecScript	(	bool	includeAll,
		params Tuple3< string, bool, object[]>[]	script
	)			`[virtual]`

Sequentially executes a list uf queries with its parameters.

Parameters:

	includeAll	when True - indicates that all queries must return result (null for queries that does not return any data)
	script	a list of 3-element tuples where Value1 - SQL string, Value2 - True if query returns a data, Value3 - list of input parameter values

Returns:: a sequence of execution results for queries that returns values, in case if includeAll is True - for all queries in the script

virtual int WX5.DB.DBManager.ExecSQL	(	BlockX< IDataParameterCollection >	processor,
		string	exprLISPSQL,
		out List< object >	retValues,
		params object[]	args
	)			`[virtual]`

Executes a SQL query with parameters including post-processing of parameters.

Parameters:

	processor	Block of code to process pearmeters after execution
	exprLISPSQL	SQL query with special syntax
	retValues	a list of output parameters
	args	paramater values

Returns:: the number of records affected for last sql statement

virtual int WX5.DB.DBManager.ExecSQL	(	string	exprLISPSQL,
		out List< object >	retValues,
		params object[]	args
	)			`[virtual]`

Executes a SQL query with parameters.

Parameters:

	exprLISPSQL	SQL query with special syntax
	retValues	a list of output parameters
	args	paramater values

Returns:: the number of records affected for last sql statement

virtual int WX5.DB.DBManager.ExecSQL	(	BlockX< IDataParameterCollection >	processor,
		string	exprLISPSQL,
		params object[]	args
	)			`[virtual]`

Executes a SQL query with parameters including post-processing of parameters.

Parameters:

	processor	Block of code to process pearmeters after execution
	exprLISPSQL	SQL query with special syntax
	args	paramater values

Returns:: the number of records affected for last sql statement

virtual int WX5.DB.DBManager.ExecSQL	(	string	exprLISPSQL,
		params object[]	args
	)			`[virtual]`

Executes a query with parameters.

Parameters:

	exprLISPSQL	SQL query with special syntax
	args	paramater values

Returns:: the number of records affected for last sql statement

virtual int WX5.DB.DBManager.ExecSQL ( string exprSQL ) [virtual]

Executes a query.

Parameters:

exprSQL

plain SQL query

Returns:: the number of records affected for last sql statement

virtual DataTable WX5.DB.DBManager.LoadTable	(	DataTable	target,
		string	exprLISPSQL,
		params object[]	args
	)			`[virtual]`

Loads a DataTable object from database.

Parameters:

	target	a table to be loaded
	exprLISPSQL	SQL query with special syntax
	args	parameter values

Returns:: target table

virtual DataTable WX5.DB.DBManager.LoadTable	(	DataTable	target,
		string	exprSQL
	)			`[virtual]`

Loads a DataTable object from database.

Parameters:

	target	a table to be loaded
	exprSQL	plain SQL query

Returns:: target table

virtual DBManager WX5.DB.DBManager.OpenConnection ( ) [virtual]

In manual mode opens a connection.

Returns:: current DBManager instance

virtual DBManager WX5.DB.DBManager.ResetEngineEnvironment ( ) [virtual]

Resets L# environment.

Returns:: current DBManager instance

virtual DataTable WX5.DB.DBManager.StoreTable	(	DataTable	target,
		string	updLISPSQL,
		string	insLISPSQL,
		string	delLISPSQL
	)			`[virtual]`

Stores a datatable into database.

Parameters:

	target	a table to be saved
	updLISPSQL	SQL UPDATE query with special syntax
	insLISPSQL	SQL INSERT query with special syntax
	delLISPSQL	SQL DELETE query with special syntax