Functions Class
Contains the public callable DB functions and helper functions
Inheritance Hierarchy
- System.Object
- DBaddin.Functions
Namespace: DBaddinAssembly: DBaddin in (DBaddin.dll)
Syntax
public sealed class Functions
The Functions type exposes the following members.
Constructors
| Class | Summary | |
|---|---|---|
 | Functions() |
Methods
| Name | Summary | |
|---|---|---|
 | chainCells(Object[]) | chains values contained in chainPart together with commas, mainly used for creating select header |
 | checkParamsAndCache(ref Object, Object, Object) | checks calculation mode, query and cached status message. |
| checkQueryAndTarget(Object, Object) | checks Query and targetRange parameters for existence and return error message. |
| concatCells(Object[]) | concatenates values contained in concatPart together (using .value attribute for cells) |
| concatCellsSep(String, Object[]) | concatenates values contained in concatPart (using .value for cells) using a separator |
| currentWorkbook(Boolean) | get current Workbook path + filename or Workbook path only, if onlyPath is set |
| DBAddinEnvironment() | Get the current selected Environment for DB Functions |
| DBAddinSetting(Object) | Get the settings as given in keyword (e.g. SERVER=) for the currently selected Environment for DB Functions |
| DBDate(Object, Int32) | Create database compliant date, time or datetime string from excel date type value |
| DBinClause(Object[]) | Create an in clause from cell values, strings are created with quotation marks, dates are created with DBDate |
| DBinClauseDate(Object[]) | Create an in clause from cell values, strings are created with quotation marks, dates are created with DBDate |
| DBinClauseStr(Object[]) | Create an in clause from cell values, strings are created with quotation marks, dates are created with DBDate |
| DBListFetch(Object, Object, Object, Object, Int32, Object, Object, Object, Object) | Fetches a list of data defined by query into TargetRange. Optionally copy formulas contained in FormulaRange, extend list depending on ExtendDataArea (0(default) = overwrite, 1=insert Cells, 2=insert Rows) and add field headers if HeaderInfo = TRUE |
| DBListFetchAction(String, String, Range, Range, String, Object, Int32, Boolean, Boolean, Boolean, Boolean, String, String) | Actually do the work for DBListFetch: Query list of data delimited by maxRows and maxCols, write it into targetCells additionally copy formulas contained in formulaRange and extend list depending on extendArea |
| DBRowFetch(Object, Object, Object[]) | Fetches a row (single record) queried (defined in query) from DB (defined in ConnString) into targetArray |
| DBRowFetchAction(String, String, Range, Object, String, Boolean) | Actually do the work for DBRowFetch: Query (assumed) one row of data, write it into targetCells |
| DBSetPowerQuery(Object, Object) | Stores a query into an powerquery defined by queryName |
| DBSetPowerQueryAction(String, String, Range, String) | set Query parameters (query text and connection string) of Query List or pivot table (incl. chart) |
| DBSetQuery(Object, Object, Object) | Stores a query into an Object defined in targetRange (an embedded MS Query/List object, Pivot table, etc.) |
| DBSetQueryAction(String, String, Range, String, Range, String) | set Query parameters (query text and connection string) of Query List or pivot table (incl. chart) |
| DBString(Object[]) | Create a database compliant string from cell values, potentially concatenating with other parts for easy inclusion of wildcards (%,_) |
| DoConcatCellsSep(String, Boolean, Boolean, Boolean, Object[]) | private function that actually concatenates values contained in Object array concatParts together (either using .text or .value for cells in concatParts) using a separator |
| finishAction(XlCalculation, String, String) | common for DBListFetch and DBRowFetch Action procedures to finish, resetting anything (Cursor, calc mode, status bar, screen updating) that was set otherwise... |
| formatPQDate(Double, Boolean) | takes an OADate and formats it as a powerquery compliant #date, #time or #datetime function |
| PQDate(Object, Boolean) | Creates a powerquery compliant #date function from excel date type value |
| PQString(Object[]) | Create a powerquery compliant string from cell values, potentially concatenating with other parts for easy inclusion of wildcards (%,_) |
| preventRefresh(Boolean) | set preventing of refreshing DB Functions |
| setCalcModeBack(XlCalculation) | |
| setExtents(Range, ref String, ref String, ref String) | common for DBListFetch, DBRowFetch and DBSetQuery Action procedures, setting the Extent Names at the beginning |
| ToRange(Object) | converts ExcelDna (C API) reference to excel (COM Based) Range |
Fields
| Name | Description | |
|---|---|---|
 | avoidRequeryDuringEdit | |
| conn | connection object |
| CurrConnString | connection string can be changed for calls with different connection strings |
| dontCalcWhileClearing | avoid entering dblistfetch/dbrowfetch functions during clearing of list-fetch areas (before saving) |
| preventRefreshFlag | avoid refreshing of DB Functions |
| queryBackupColl | |
| queryCache | query cache for avoiding unnecessary recalculations/data retrievals by volatile inputs to DB Functions (now(), etc.) |
| StatusCollection | global collection of information transport containers between action function and user-defined function resp. calc event procedure |