Table of Contents

Class DataConnector

Namespace
PolarStudio
Assembly
PolarStudio.dll

This class provides a standardized interface with a database.

public class DataConnector
Inheritance
DataConnector
Inherited Members
Extension Methods

Constructors

DataConnector(PolarConnection)

Constructor for a specific connection or empty constructor.

public DataConnector(PolarConnection pInitialConnection = null)

Parameters

pInitialConnection PolarConnection

The connection to interface with.

Fields

DatabaseType_MsJet

This const is used for the property DatabaseType, indicating Microsoft Access.

public const string DatabaseType_MsJet = "MSJET"

Field Value

string

DatabaseType_SqlServer

This const is used for the property DatabaseType, indicating Microsoft SQL Server.

public const string DatabaseType_SqlServer = "SQLSERVER"

Field Value

string

DatabaseType_Unknown

This const is used for the property DatabaseType, indicating an unknown database type.

public const string DatabaseType_Unknown = "UNKNOWN"

Field Value

string

Properties

BudaEngine

This method returns the Engine this connection is tied to.

public BudaEngine BudaEngine { get; }

Property Value

BudaEngine

CommandTimeoutSeconds

This property gets/sets the timeout for queries and SQL statements in seconds. Must be a value of 0-3600. 0 means wait infinitely.

public int CommandTimeoutSeconds { get; set; }

Property Value

int

ConnectString

This property (write-only) can be used to set a new custom connection string.

public string ConnectString { set; }

Property Value

string

Connection

Returns the underlying connection that is used for the core communication with the database.

public PolarConnection Connection { get; }

Property Value

PolarConnection

ConnectionTimeoutSeconds

This property gets/sets the timeout to (re)connect with the database in seconds. Must be a value of 0-3600. 0 means wait infinitely.

public int ConnectionTimeoutSeconds { get; set; }

Property Value

int

ConnectionType

Returns the type of connection.

public DataConnector.dcConnectionType ConnectionType { get; }

Property Value

DataConnector.dcConnectionType

DataBase

Returns the name of the database

public string DataBase { get; }

Property Value

string

DatabaseType

Returns the coded name of the database type, 'SQLSERVER', 'MSJET' or 'UNKNOWN'

public string DatabaseType { get; }

Property Value

string

DatabaseVersion

This property tries to return the database version of SQL Server

public int DatabaseVersion { get; }

Property Value

int

InTransaction

Returns True if a transaction is open on the connection with the database.

public bool InTransaction { get; }

Property Value

bool

ObjectGUID

This method returns a GUID for this object.

public string ObjectGUID { get; }

Property Value

string

Path

This method returns the path in the file-system for Microsoft Access databases.

public string Path { get; }

Property Value

string

TransactionLevelCounter

This method returns the level of the transaction. 0 means 'not in transaction', >= 1 means 'in transaction'

public int TransactionLevelCounter { get; }

Property Value

int

TransactionWillBeRolledBack

This method returns True if a transaction is open and a RollBack has been received on this transaction.

public bool TransactionWillBeRolledBack { get; }

Property Value

bool

Methods

Abandon()

This method fires an event to all Polar Studio dataset and will let these datasets close the recordset. Is not really used anymore.

public void Abandon()

AcceptFileOnCommit(string)

This method is for putting files in the file-system that are tied to a transaction

public void AcceptFileOnCommit(string pFileName)

Parameters

pFileName string

The full filename for the file in the file-system to keep. This base filename MUST start with 'NEW_' (like: 'C:\Temp\NEW_MyFile.txt'). On Commit, this file is copied to the same file without the 'NEW_' prefix.

AddCommitAction(Context, string, string)

This method registers a script-expression that is to be executed when the current transaction is commited. A UniqueKey can be added to easily prevent duplicate actions.

public void AddCommitAction(Context pContext, string pScriptExpression, string pUniqueKey = "")

Parameters

pContext Context

The context the script should use.

pScriptExpression string

The script-expression to execute on Commit.

pUniqueKey string

An optional key to prevent duplicate actions.

AddRollbackAction(Context, string, string)

This method registers a script-expression that is to be executed when the current transaction is rolled back. A UniqueKey can be added to easily prevent duplicate actions.

public void AddRollbackAction(Context pContext, string pScriptExpression, string pUniqueKey = "")

Parameters

pContext Context

The context the script should use.

pScriptExpression string

The script-expression to execute on RollBack.

pUniqueKey string

An optional key to prevent duplicate actions.

BooleanToSQL(bool)

This method converts a Boolean value to the syntax as used in the current database.

public string BooleanToSQL(bool pValue)

Parameters

pValue bool

The boolean value to convert.

Returns

string

A SQL expression that represents the value according the current database type.

CommitTransaction()

This method is to register a Commit. Only when the TransactionLevelCounter is back to zero and no RollBacks were fired in this transaction, the transaction is acutally commited. Else, when the TransactionLevelCounter is back to zero and a RollBack was received previously in this transaction, this RollBack will now take place. See StartTransaction for more info.

public bool CommitTransaction()

Returns

bool

True if the transaction is actually commited.

Exceptions

PolarException

Raises an error if not in a transaction.

ConvertToAnsiSQL(string)

This method takes a SQL string/query and converts it to the SQL dialect of the current database. This method is normally called internally from Polar Studio.

public string ConvertToAnsiSQL(string pSQLstring)

Parameters

pSQLstring string

The SQL to convert.

Returns

string

The SQL statement converted to the current dialect.

CreateTransaction()

This method returns a new Transaction-object for easier programming.

public Transaction CreateTransaction()

Returns

Transaction

A new Transaction object

DateToSQL(DateTime)

This method converts a Date value to the syntax as used in the current database.

public string DateToSQL(DateTime pDate)

Parameters

pDate DateTime

The date to convert.

Returns

string

A SQL expression that represents the date according the current database type.

DetermineNewName(string, string, string, string, string)

This method creates a new unique Name for an item. It tries the suggested name on the table with the given additional where. If this name already exists, a _1 suffix is tested, then _2, etc.

public string DetermineNewName(string pTableName, string pAdditionalWhere, string pSuggestedName, string pNameField = "", string pAllowedNameCharacters = "")

Parameters

pTableName string

The name of the table to create the new name for.

pAdditionalWhere string

An optional additional where. Leave empty if not needed.

pSuggestedName string

The suggested new name. Required.

pNameField string

The name of the 'Name' field. If not specified, 'Name' is assumed.

pAllowedNameCharacters string

By default the name will be sanitized to '[a-z][0-9]_', but if you want another set of characters for this sanitation, these characters can be specified here.

Returns

string

EasyConnect(dcConnectionType, string, string, string, string)

Use this method for 'easy'-connecting to a database.

public void EasyConnect(DataConnector.dcConnectionType pConnectionType, string pDataBase, string pUserName, string pPassword, string pWorkgroupFile = "")

Parameters

pConnectionType DataConnector.dcConnectionType

The type of connection. Use 2:Microsoft Jet, 4:Microsoft SQL Server, 6:Microsoft OLEDB for Access

pDataBase string

Access MDB-databases: The name and location of the database-file; SqlServer databases: HostName;DatabaseName

pUserName string

The UserName to connect with. Leave empty for SQL Server integrated security.

pPassword string

The password for the specified user.

pWorkgroupFile string

In case Microsoft Access uses a workgroup-file, this file can be specified here.

Exceptions

Exception

Execute(string)

This method executes a SQL statetment on the current database. Exceptions will be thrown and logged.

public void Execute(string pSQLstring)

Parameters

pSQLstring string

The SQL statement to execute. The statement can start with an exclamation mark to send the statement 'raw' to the connection without any translation to the current dialect. If the statement starts with a tilde (~), this conversion is also suppressed and logging the query in Polar Studio will be suppressed.

Execute(string, ref int, ref string)

This method executes a SQL statetment on the current database. The by-ref parameter pErrorMessage will be filled with an errormessage if applicable.

public void Execute(string pSQLstring, ref int pRecordsAffected, ref string pErrorMessage)

Parameters

pSQLstring string

The SQL statement to execute. The statement can start with an exclamation mark to send the statement 'raw' to the connection without any translation to the current dialect. If the statement starts with a tilde (~), this conversion is also suppressed and logging the query in Polar Studio will be suppressed.

pRecordsAffected int

This by-ref int parameter will return the number of affected records as reported by the database.

pErrorMessage string

A by-ref parameter to receive the errormessage.

Execute(string, ref string)

This method executes a SQL statetment on the current database. The by-ref parameter pErrorMessage will be filled with an errormessage if applicable.

public void Execute(string pSQLstring, ref string pErrorMessage)

Parameters

pSQLstring string

The SQL statement to execute. The statement can start with an exclamation mark to send the statement 'raw' to the connection without any translation to the current dialect. If the statement starts with a tilde (~), this conversion is also suppressed and logging the query in Polar Studio will be suppressed.

pErrorMessage string

A by-ref parameter to receive the errormessage.

FinalizeExcelWorksheetAsXML(string)

This method is to be used to create a final XML file after RecordSetAsExcelWorksheetXML has been called.

public string FinalizeExcelWorksheetAsXML(string pExcelDataXML)

Parameters

pExcelDataXML string

The result of the called RecordSetAsExcelWorksheetXML.

Returns

string

The unicode encoded XML to save as Excel.

KillFileOnCommit(string)

This method is for deleting files in the file-system when a transaction is commited.

public void KillFileOnCommit(string pFileName)

Parameters

pFileName string

The full name of the file to delete when the transaction is commited.

KillFileOnRollBack(string)

This method is for deleting files in the file-system when a transaction is rolled back.

public void KillFileOnRollBack(string pFileName)

Parameters

pFileName string

The full name of the file to delete when the transaction is rolled back.

KillTransaction()

This method completely kills the current transaction by RollingBack an open transaction in the database and resetting the TransactionLevelCounter. Is used internally in PolarStudio.

public void KillTransaction()

Lookup(string)

This method will execute the given query and return the first field of the first record. If no data is found or an exception was raised, NULL will be returned.

public object Lookup(string pQuery)

Parameters

pQuery string

The query to execute, like 'SELECT COUNT(*) FROM Customer'

Returns

object

The value of the first field of the first record returned by the query or NULL in all other cases.

OpenDynamicRecordSet(string, bool)

This method returns a dynamic recordset. Dynamic recordsets use a server-side cursor. They are slower in processing but offer more updating features and multiple dynamic recordsets can be opened while in a transaction.

public Recordset OpenDynamicRecordSet(string pQuery, bool pRemoveBpTags = false)

Parameters

pQuery string

The SQL statement to execute. The statement can start with an exclamation mark to send the statement 'raw' to the connection without any translation to the current dialect. If the statement starts with a tilde (~), this conversion is also suppressed and logging the query in Polar Studio will be suppressed.

pRemoveBpTags bool

When this parameter is set to True, the 'Blue Polar tags' are removed. These tags are sometimes within Polar Studio added to queries to easily identify parts and are like ^<#>'

Returns

Recordset

A dynamic recordset.

OpenStaticRecordSet(string, bool)

This method returns a static recordset. Static recordsets are fast for reading, but use a forward-only cursor. Also, they do not return the recordcount. Exceptions will be thrown.

public Recordset OpenStaticRecordSet(string pQuery, bool pRemoveBpTags = false)

Parameters

pQuery string

The SQL statement to execute. The statement can start with an exclamation mark to send the statement 'raw' to the connection without any translation to the current dialect. If the statement starts with a tilde (~), this conversion is also suppressed and logging the query in Polar Studio will be suppressed.

pRemoveBpTags bool

When this parameter is set to True, the 'Blue Polar tags' are removed. These tags are sometimes within Polar Studio added to queries to easily identify parts and are like ^<#>'

Returns

Recordset

A static recordset.

OpenStaticRecordSet(string, bool, ref int)

This method returns a static recordset. Static recordsets are fast for reading, but use a forward-only cursor. Also, they do not return the recordcount. Exceptions will be thrown.

public Recordset OpenStaticRecordSet(string pQuery, bool pRemoveBpTags, ref int pRecordsAffected)

Parameters

pQuery string

The SQL statement to execute. The statement can start with an exclamation mark to send the statement 'raw' to the connection without any translation to the current dialect. If the statement starts with a tilde (~), this conversion is also suppressed and logging the query in Polar Studio will be suppressed.

pRemoveBpTags bool

When this parameter is set to True, the 'Blue Polar tags' are removed. These tags are sometimes within Polar Studio added to queries to easily identify parts and are like ^<#>'

pRecordsAffected int

This by-ref int parameter will return the number of affected records as reported by the database.

Returns

Recordset

A static recordset.

OpenStaticRecordSet(string, bool, ref int, bool, ref PolarConnection)

This method returns a static recordset. Static recordsets are fast for reading, but use a forward-only cursor. Also, they do not return the recordcount. Exceptions will be thrown.

public Recordset OpenStaticRecordSet(string pQuery, bool pRemoveBpTags, ref int pRecordsAffected, bool pOpenInNewConnection, ref PolarConnection pReturnedConnection)

Parameters

pQuery string

The SQL statement to execute. The statement can start with an exclamation mark to send the statement 'raw' to the connection without any translation to the current dialect. If the statement starts with a tilde (~), this conversion is also suppressed and logging the query in Polar Studio will be suppressed.

pRemoveBpTags bool

When this parameter is set to True, the 'Blue Polar tags' are removed. These tags are sometimes within Polar Studio added to queries to easily identify parts and are like ^<#>'

pRecordsAffected int

This by-ref int parameter will return the number of affected records as reported by the database.

pOpenInNewConnection bool

This boolean can be set to True, to make sure the recordset is opened in a new (cloned) connection.

pReturnedConnection PolarConnection

This by-ref parameter returns the connection used to open the query in.

Returns

Recordset

A static recordset.

RecordExists(string)

This method runs a query and returns True if at least one record was returned. If an exception was raised, this method returns False.

public bool RecordExists(string pQuery)

Parameters

pQuery string

The query to execute, like 'SELECT TOP 1 * FROM Customer WHERE Status = 9'

Returns

bool

True if the given query returned at least one record.

RecordSetAsExcelWorksheetXML(Recordset, string, string)

This method can be used to create the XML for one worksheet. After all recordsets are converted to worksheets, the method 'FinalizeExcelWorksheetAsXML' must be called to get a valid Excel XML file.

public string RecordSetAsExcelWorksheetXML(Recordset pRecordSet, string pWorkSheetName, string pFieldNames = "")

Parameters

pRecordSet Recordset

The recordset to convert to an Excel sheet.

pWorkSheetName string

The name of the worksheet.

pFieldNames string

The field names to include (in square brackets). Leave empty for all fields.

Returns

string

RecordSetAsText(Recordset, int, bool, string, Context)

This method converts a recordset to text.

public string RecordSetAsText(Recordset pRecordSet, int pMaxRowCount, bool pIncludeColumnHeaders = true, string pFieldNames = "", Context pFormattingContext = null)

Parameters

pRecordSet Recordset

The recordset to convert.

pMaxRowCount int

The max number of rows to return. Use 0 to get all rows.

pIncludeColumnHeaders bool

A boolean to include the field names as column headers

pFieldNames string

The field names to include in square brackets. Leave empty to return all fields.

pFormattingContext Context

A context to be used for formatting dates and numbers.

Returns

string

RecordSetAsXML(Recordset, string, string, bool, string)

This method returns the given recordset as XML

public string RecordSetAsXML(Recordset pRecordSet, string pBaseElementName, string pRecordElementName, bool pReturnDatesInYYYYMMDDTHHMMSS = false, string pFieldNames = "")

Parameters

pRecordSet Recordset

The recordset to convert

pBaseElementName string

The name of the XML base element.

pRecordElementName string

The name of each record element.

pReturnDatesInYYYYMMDDTHHMMSS bool

A boolean indicating to return dates in technical format.

pFieldNames string

An optional string containing the field names to include in square brackets. Leave empty for all fields.

Returns

string

RecordsetAsHTML(Context, clsString, Recordset, int, int, int, string, string, object)

This function Exports a recordset as HTML Special features in the FieldNames: FieldName* : Group on this field FieldName> : Align this field to the right (especially for Text-fields, because number and dates are right-aligned by default) FieldName< : Align this field to the left FieldName;200 : To set the width of this field in pixels FieldName:3 : show this field as a number with 3 decimals (FieldName:0 for 0 decimals, etc) FieldName- : do not export this field (this feature is used to include a hidden field for graph-captions) FieldName@ : to sum this field FieldName! : Escape this field to HTML

public void RecordsetAsHTML(Context pContext, clsString pResultString, Recordset pRecordSet, int pNumberOfFirstColumns = 0, int pMaxNumberOfRows = 0, int pMaxLinesPerPage = 0, string pEnvelopMain = "", string pEnvelopSub = "", object pHTMLdesignSpecs = null)

Parameters

pContext Context

The Context to use for value-to-text conversions and for script expressions.

pResultString clsString

The clsString object that will contain the result.

pRecordSet Recordset

The recordset that contains the data to convert.

pNumberOfFirstColumns int

The number of columns that are to be formatted as 'header'

pMaxNumberOfRows int

The max number of rows to include in the result.

pMaxLinesPerPage int

Setting this value will paginate the result.

pEnvelopMain string

Contains the HTML envelop to show the first page (or everything then pMaxLinesPerPage = 0). Must contain a <DETAILS> placeholder to put the data in.

pEnvelopSub string

Can contain a HTML envelop for page 2 and further. Must contain a <DETAILS> placeholder to put the data in.

pHTMLdesignSpecs object

A string contains specs to set all classes for all elements, like: TableClasses=table table-striped HeadingClassGroup= HeadingClassColumn= HeadingClassRow= HeadingClassColumnRow= CellClass=

RemoveSQLcomments(string)

This method takes a SQL statement and removes the parts that in SQL Server syntax are to be considered as comment (line-parts after -- and /.../ parts). This is used to allow comments to be used in queries that are fired on JET databases (that do not support comments).

public string RemoveSQLcomments(string pSQLstring)

Parameters

pSQLstring string

The SQL to clear from comments.

Returns

string

The SQL without comments.

RollBackTransaction()

This method is to register a RollBack. Only when the TransactionLevelCounter is back to zero this RollBack will take place. See StartTransaction for more info.

public bool RollBackTransaction()

Returns

bool

True if the RollBack has taken place.

Exceptions

PolarException

Raises an error if not in a transaction.

SendMessage(string, bool, string, string, string, string, string, string, string, object, string, Context)

This method sends a message to a user that is shown in a popup in the browser. If a transaction is active, this message will be discarded in case of a RollBack. This way, you can send a message to the user like 'We have sent you the invoice', but let this be discarded if the transaction fails and the invoice was not created.

public void SendMessage(string pMessage, bool pMessageIsHtmlFormatted = false, string pCaption = "", string pIconURL = "", string pOnOkScript = "", string pOnCancelScript = "", string pOkButtonCaption = "", string pCancelButtonCaption = "", string pStyle = "", object pReverseButtons = null, string pOnCloseScript = "", Context pContext = null)

Parameters

pMessage string

The message to send to the user.

pMessageIsHtmlFormatted bool

A boolean parameter indicating the passed message contains HTML. Only pass as true if your own code makes sure al variable data is converted to HTML.

pCaption string

An optional caption for the caption of the popup dialog

pIconURL string

The URL to use for the icon that is shown in the popup.

pOnOkScript string

The script-expression to execute server side when the users clicks the OK button. The expression can be preceeded by an exclamation-mark to run the given script-expression 'interactively'. When an expression is given that starts with a @, this expression is fired as javascript in the browser.

pOnCancelScript string

The Cancel button will be hidden if this parameter is empty. Use only a minus character to show the cancel button but to take no action. Else, this paramater contains the script-expression to execute server side when the users clicks the OK button. The expression can be preceeded by an exclamation-mark to run the given script-expression 'interactively'. When an expression is given that starts with a @, this expression is fired as javascript in the browser.

pOkButtonCaption string

The caption of the OK button if it should be differen than OK. Specify as '---' (3x minus) to completely hide the OK button.

pCancelButtonCaption string

The caption of the Cancel button if it should be differen than Cancel.

pStyle string

A css-class that is applied to the elements in the Popup and that can be used to style popups using your custom CSS.

pReverseButtons object

A boolean parameter to indicate that the OK and Cancel buttons must trade places (thus Cancel / OK)

pOnCloseScript string

The Close-cross button in the top-right corner of the dialog will be hidden if this parameter is empty. Use only a minus character to show the close button but to take no action. Else, this paramater contains the script-expression to execute server side when the users clicks the OK button. The expression can be preceeded by an exclamation-mark to run the given script-expression 'interactively'. When an expression is given that starts with a @, this expression is fired as javascript in the browser.

pContext Context

The context to use for the action-scripts. In particular in a multiple document environment this can be important.

SetConnection(PolarConnection)

This method sets the connection of the DataConnector to a specific connection.

public void SetConnection(PolarConnection pConnection)

Parameters

pConnection PolarConnection

The connection to use for the DataConnector.

StartTransaction()

This method starts a transaction on the current connection. Please note that this works with a 'level'. The first time StartTransaction is called, a transaction is actually started. Each subsequent time StartTransaction is fired, only the level is increased. When Commits or RollBacks are fired, the level is decreased. When the level reaches 0, the transaction is actually commited (if no RollBacks were fired) or rolled back (if at least one RollBack was received).

public bool StartTransaction()

Returns

bool

True if the transaction was successfully started.

StringToSQL(string)

This method converts a string value to the syntax as used in the current database.

public string StringToSQL(string pString)

Parameters

pString string

The string to convert.

Returns

string

A SQL expression that represents the string according the current database type. This included the outer quotes and a N-prefix if the string contains unicode characters.

ValueToSQL(object)

This method converts the given pValue to the correct syntax for the current database based on the type of the value.

public string ValueToSQL(object pValue)

Parameters

pValue object

A value

Returns

string

The string representing the value.