Show / Hide Table of Contents

Class Functions

Contains the public callable DB functions and helper functions

Inheritance
Functions
Namespace: DBaddin
Assembly: DBaddin.dll
Syntax
public class Functions

Fields

| Improve this Doc View Source

avoidRequeryDuringEdit

Declaration
public static bool avoidRequeryDuringEdit
Field Value
Description
| Improve this Doc View Source

conn

connection object

Declaration
public static IDbConnection conn
Field Value
Description
| Improve this Doc View Source

CurrConnString

connection string can be changed for calls with different connection strings

Declaration
public static string CurrConnString
Field Value
Description
| Improve this Doc View Source

dontCalcWhileClearing

avoid entering dblistfetch/dbrowfetch functions during clearing of list-fetch areas (before saving)

Declaration
public static bool dontCalcWhileClearing
Field Value
Description
| Improve this Doc View Source

queryBackupColl

Declaration
public static Dictionary<string, string> queryBackupColl
Field Value
Description
| Improve this Doc View Source

queryCache

query cache for avoiding unnecessary recalculations/data retrievals by volatile inputs to DB Functions (now(), etc.)

Declaration
public static Dictionary<string, string> queryCache
Field Value
Description
| Improve this Doc View Source

StatusCollection

global collection of information transport containers between action function and user-defined function resp. calc event procedure

Declaration
public static Dictionary<string, ContainedStatusMsg> StatusCollection
Field Value
Description

Methods

| Improve this Doc View Source

chainCells(Object[])

chains values contained in chainPart together with commas, mainly used for creating select header

Declaration
[ExcelFunction(Description = "chains values contained in chainPart together with commas, mainly used for creating select header")]
public static string chainCells([ExcelArgument(AllowReference = true, Description = "range where values should be chained")] params object[] chainPart)
Parameters
Name Description
chainPart

range where values should be chained

Returns
Description

chained String

| Improve this Doc View Source

concatCells(Object[])

concatenates values contained in concatPart together (using .value attribute for cells)

Declaration
[ExcelFunction(Description = "concatenates values contained in concatPart together (using .value attribute for cells)")]
public static string concatCells([ExcelArgument(AllowReference = true, Description = "all cells/values which should be concatenated")] params object[] concatPart)
Parameters
Name Description
concatPart

all cells/values which should be concatenated

Returns
Description

concatenated String

| Improve this Doc View Source

concatCellsSep(String, Object[])

concatenates values contained in concatPart (using .value for cells) using a separator

Declaration
[ExcelFunction(Description = "concatenates values contained in concatPart (using .value for cells) using a separator")]
public static string concatCellsSep([ExcelArgument(AllowReference = true, Description = "the separator")] string separator, [ExcelArgument(AllowReference = true, Description = "all cells/values which should be concatenated")] params object[] concatPart)
Parameters
Name Description
separator

the separator

concatPart

all cells/values which should be concatenated

Returns
Description

concatenated String

| Improve this Doc View Source

currentWorkbook(Boolean)

get current Workbook path + filename or Workbook path only, if onlyPath is set

Declaration
[ExcelFunction(Description = "get current Workbook path + filename or Workbook path only, if onlyPath is set")]
public static string currentWorkbook([ExcelArgument(Description = "only path of file location?")] bool onlyPath = false)
Parameters
Name Description
onlyPath

only path of file location?

Returns
Description

current Workbook path + filename or Workbook path only

| Improve this Doc View Source

DBAddinEnvironment()

Get the current selected Environment for DB Functions

Declaration
[ExcelFunction(Description = "Get the current selected Environment for DB Functions")]
public static string DBAddinEnvironment()
Returns
Description

ConfigName of environment

| Improve this Doc View Source

DBAddinSetting(Object)

Get the settings as given in keyword (e.g. SERVER=) for the currently selected Environment for DB Functions

Declaration
[ExcelFunction(Description = "Get the settings as given in keyword (e.g. SERVER=) for the currently selected Environment for DB Functions")]
public static string DBAddinSetting([ExcelArgument(Description = "keyword for setting to get")] object keyword)
Parameters
Name Description
keyword
Returns
Description

Server part from connection string of environment

| Improve this Doc View Source

DBDate(Object, Int32)

Create database compliant date, time or datetime string from excel date type value

Declaration
[ExcelFunction(Description = "Create database compliant date, time or datetime string from excel date type value")]
public static string DBDate([ExcelArgument(Description = "date/time/datetime")] object DatePart, [ExcelArgument(Description = "formatting option, 0:'YYYYMMDD', 1: DATE 'YYYY-MM-DD'), 2:{d 'YYYY-MM-DD'},3:Access/JetDB #DD/MM/YYYY#, add 10 to formatting to include fractions of a second (1000)")] int formatting = 99)
Parameters
Name Description
DatePart

date/time/datetime single parameter or range reference

formatting

formatting instruction for Date format, see remarks

Returns
Description

the DB compliant formatted date/time/datetime

Remarks

formatting = 0: A simple date string (format 'YYYYMMDD'), datetime values are converted to 'YYYYMMDD HH:MM:SS' and time values are converted to 'HH:MM:SS'. formatting = 1: An ANSI compliant Date string (format date 'YYYY-MM-DD'), datetime values are converted to 'YYYY-MM-DD HH:MM:SS' and time values are converted to 'HH:MM:SS'. formatting = 2: An ODBC compliant Date string (format {d 'YYYY-MM-DD'}), datetime values are converted to {ts 'YYYY-MM-DD HH:MM:SS'} and time values are converted to {t 'HH:MM:SS'}. formatting = 3: An Access/JetDB compliant Date string (format #YYYY-MM-DD#), datetime values are converted to #YYYY-MM-DD HH:MM:SS# and time values are converted to #HH:MM:SS#. add 10 to formatting to include fractions of a second (1000) formatting >13 or empty (99=default value): take the formatting option from setting DefaultDBDateFormatting (0 if not given)

| Improve this Doc View Source

DBinClause(Object[])

Create an in clause from cell values, strings are created with quotation marks, dates are created with DBDate

Declaration
[ExcelFunction(Description = "Create an in clause from cell values, strings are created with quotation marks")]
public static string DBinClause([ExcelArgument(AllowReference = true, Description = "array of values or ranges containing values")] params object[] inClausePart)
Parameters
Name Description
inClausePart

array of values or ranges containing values

Returns
Description

database compliant in-clause string

| Improve this Doc View Source

DBinClauseDate(Object[])

Create an in clause from cell values, strings are created with quotation marks, dates are created with DBDate

Declaration
[ExcelFunction(Description = "Create an in clause from cell values, numbers are always treated as dates (formatted using default) and created with quotation marks")]
public static string DBinClauseDate([ExcelArgument(AllowReference = true, Description = "array of values or ranges containing values")] params object[] inClausePart)
Parameters
Name Description
inClausePart

array of values or ranges containing values

Returns
Description

database compliant in-clause string

| Improve this Doc View Source

DBinClauseStr(Object[])

Create an in clause from cell values, strings are created with quotation marks, dates are created with DBDate

Declaration
[ExcelFunction(Description = "Create an in clause from cell values, all arguments are treated as strings (and will be created with quotation marks)")]
public static string DBinClauseStr([ExcelArgument(AllowReference = true, Description = "array of values or ranges containing values")] params object[] inClausePart)
Parameters
Name Description
inClausePart

array of values or ranges containing values

Returns
Description

database compliant in-clause string

| Improve this Doc View Source

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

Declaration
[ExcelFunction(Description = "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")]
public static string DBListFetch([ExcelArgument(Description = "query for getting data")] object Query, [ExcelArgument(Description = "connection string defining DB, user, etc...")] object ConnString, [ExcelArgument(Description = "Range to put the data into", AllowReference = true)] object targetRange, [ExcelArgument(Description = "Range to copy formulas down from", AllowReference = true)] object formulaRange = null, [ExcelArgument(Description = "how to deal with extending List Area")] int extendDataArea = 0, [ExcelArgument(Description = "should headers be included in list")] object HeaderInfo = null, [ExcelArgument(Description = "should columns be auto-fitted ?")] object AutoFit = null, [ExcelArgument(Description = "should 1st row formats be auto-filled down?")] object autoformat = null, [ExcelArgument(Description = "should row numbers be displayed in 1st column?")] object ShowRowNums = null)
Parameters
Name Description
Query

query for getting data

ConnString

connection string defining DB, user, etc...

targetRange

Range to put the data into

formulaRange

Range to copy formulas down from

extendDataArea

how to deal with extending List Area

HeaderInfo

should headers be included in list

AutoFit

should columns be auto fitted ?

autoformat

should 1st row formats be auto filled down?

ShowRowNums

should row numbers be displayed in 1st column?

Returns
Description

Status Message, data values are returned outside of function cell (@see DBFuncEventHandler)

| Improve this Doc View Source

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

Declaration
public static void DBListFetchAction(string callID, string Query, Range caller, Range targetRange, string ConnString, object formulaRange, int extendArea, bool HeaderInfo, bool AutoFit, bool autoformat, bool ShowRowNumbers, string targetRangeName, string formulaRangeName)
Parameters
Name Description
callID
Query
caller
targetRange
ConnString
formulaRange
extendArea
HeaderInfo
AutoFit
autoformat
ShowRowNumbers
targetRangeName
formulaRangeName
| Improve this Doc View Source

DBRowFetch(Object, Object, Object[])

Fetches a row (single record) queried (defined in query) from DB (defined in ConnString) into targetArray

Declaration
[ExcelFunction(Description = "Fetches a row (single record) queried (defined in query) from DB (defined in ConnString) into targetArray")]
public static string DBRowFetch([ExcelArgument(Description = "query for getting data")] object Query, [ExcelArgument(Description = "connection string defining DB, user, etc...")] object ConnString, [ExcelArgument(Description = "Range to put the data into", AllowReference = true)] params object[] targetArray)
Parameters
Name Description
Query

query for getting data

ConnString

connection string defining DB, user, etc...

targetArray

Range to put the data into

Returns
Description

Status Message, data values are returned outside of function cell (@see DBFuncEventHandler)

| Improve this Doc View Source

DBRowFetchAction(String, String, Range, Object, String, Boolean)

Actually do the work for DBRowFetch: Query (assumed) one row of data, write it into targetCells

Declaration
public static void DBRowFetchAction(string callID, string Query, Range caller, object targetArray, string ConnString, bool HeaderInfo)
Parameters
Name Description
callID
Query
caller
targetArray
ConnString
HeaderInfo
| Improve this Doc View Source

DBSetPowerQuery(Object, Object)

Stores a query into an powerquery defined by queryName

Declaration
[ExcelFunction(Description = "Stores a query into a power query object defined in queryName")]
public static string DBSetPowerQuery([ExcelArgument(Description = "query for getting data")] object Query, [ExcelArgument(Description = "Name of Powerquery where query should be set")] object queryName)
Parameters
Name Description
Query

(power) query for getting data

queryName

powerquery name

Returns
Description

Status Message

| Improve this Doc View Source

DBSetPowerQueryAction(String, String, Range, String)

set Query parameters (query text and connection string) of Query List or pivot table (incl. chart)

Declaration
public static void DBSetPowerQueryAction(string callID, string Query, Range caller, string queryName)
Parameters
Name Description
callID

the key for the statusMsg container

Query

(power) query for getting data

caller

calling range passed by Action procedure

queryName

Name of Powerquery where query should be set

| Improve this Doc View Source

DBSetQuery(Object, Object, Object)

Stores a query into an Object defined in targetRange (an embedded MS Query/List object, Pivot table, etc.)

Declaration
[ExcelFunction(Description = "Stores a query into an Object (embedded List object or Pivot table) defined in targetRange")]
public static string DBSetQuery([ExcelArgument(Description = "query for getting data")] object Query, [ExcelArgument(Description = "connection string defining DB, user, etc...")] object ConnString, [ExcelArgument(Description = "Range with embedded Object to put the Query/ConnString into", AllowReference = true)] object targetRange)
Parameters
Name Description
Query

query for getting data

ConnString

connection string defining DB, user, etc...

targetRange

Range with Object beneath to put the Query/ConnString into

Returns
Description

Status Message

| Improve this Doc View Source

DBSetQueryAction(String, String, Range, String, Range, String)

set Query parameters (query text and connection string) of Query List or pivot table (incl. chart)

Declaration
public static void DBSetQueryAction(string callID, string Query, Range targetRange, string ConnString, Range caller, string targetRangeName)
Parameters
Name Description
callID

the key for the statusMsg container

Query

query for getting data

targetRange

Range with Object beneath to put the Query/ConnString into

ConnString

connection string defining DB, user, etc...

caller

calling range passed by Action procedure

targetRangeName
| Improve this Doc View Source

DBString(Object[])

Create a database compliant string from cell values, potentially concatenating with other parts for easy inclusion of wildcards (%,_)

Declaration
[ExcelFunction(Description = "Create a database compliant string from cell values, potentially concatenating with other parts for easy inclusion of wildcards (%,_)")]
public static string DBString([ExcelArgument(Description = "array of strings/wildcards or ranges containing strings/wildcards")] params object[] StringPart)
Parameters
Name Description
StringPart

array of strings/wildcards or ranges containing strings/wildcards

Returns
Description

database compliant string

| Improve this Doc View Source

PQDate(Object, Boolean)

Creates a powerquery compliant #date function from excel date type value

Declaration
[ExcelFunction(Description = "Create powerquery compliant #date, #time or #datetime function from excel date type value")]
public static string PQDate([ExcelArgument(Description = "date/time/datetime")] object DatePart, [ExcelArgument(Description = "enforce datetime for date only values (without fractional part)")] bool forceDateTime = false)
Parameters
Name Description
DatePart

date/time/datetime single parameter or range reference

forceDateTime
Returns
Description

the powerquery #date function

| Improve this Doc View Source

PQString(Object[])

Create a powerquery compliant string from cell values, potentially concatenating with other parts for easy inclusion of wildcards (%,_)

Declaration
[ExcelFunction(Description = "Create a powerquery compliant string from cell values, potentially concatenating with other parts for easy inclusion of wildcards (%,_)")]
public static string PQString([ExcelArgument(Description = "array of strings/wildcards or ranges containing strings/wildcards")] params object[] StringPart)
Parameters
Name Description
StringPart

array of strings/wildcards or ranges containing strings/wildcards

Returns
Description

powerquery compliant string

  • Improve this Doc
  • View Source
Back to top Generated by DocFX