Class Functions
Contains the public callable DB functions and helper functions
Inheritance
Namespace: DBaddin
Assembly: DBaddin.dll
Syntax
public class Functions
Fields
| Improve this Doc View SourceavoidRequeryDuringEdit
Declaration
public static bool avoidRequeryDuringEdit
Field Value
Description |
---|
conn
connection object
Declaration
public static IDbConnection conn
Field Value
Description |
---|
CurrConnString
connection string can be changed for calls with different connection strings
Declaration
public static string CurrConnString
Field Value
Description |
---|
dontCalcWhileClearing
avoid entering dblistfetch/dbrowfetch functions during clearing of list-fetch areas (before saving)
Declaration
public static bool dontCalcWhileClearing
Field Value
Description |
---|
queryBackupColl
Declaration
public static Dictionary<string, string> queryBackupColl
Field Value
Description |
---|
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 |
---|
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 SourcechainCells(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 |
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 |
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 |
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 |
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 |
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 |
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)
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 |
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 |
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 |
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) |
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 |
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) |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |