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. |
| checkPreventRefreshFlag() | check the preventRefreshFlag, either global or workbook level |
| 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 |
| getConfigDocCollection(String) | gets the documentation dictionary for the config dropdown (used by ConfigFiles.createConfigTreeMenu()). This is the basis for the documentation provided when clicking the config entries with Ctrl or Shift |
| 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, Boolean) | set preventing of refreshing DB Functions |
| setCalcModeBack(XlCalculation) | set calculation mode back to calcMode |
| 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 | during rewriting the query as a sequence of cells, avoid refreshing of power query with this flag |
| 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 | globally avoid refreshing of DB Functions |
| preventRefreshFlagColl | avoid refreshing of DB Functions on Workbook level (has precedence on global preventRefreshFlag) |
| queryBackupColl | for power queries the previously set query is being stored here to retrieve it back in case the new set query fails (excel doesn't allow to enter the power query editor in this case) |
| 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 |