Loading...

Profitbase.Data.SqlScript functions

These functions use SQL Scripts stored in InVision. Scripts can be addressed by their Name or their Id (contentId).

The following functions are obsolete and must be changed before upgrading to platform version 2022.4:

[Profitbase.Data.SqlScript]::ExecuteNonQuery

[Profitbase.Data.SqlScript]::ExecuteScalar

[Profitbase.Data.SqlScript]::LoadTable

[Profitbase.Data.SqlScript]::ExecuteReader


Following functions is available from platform version 2022.3:

$result = [Profitbase.Data.SqlScript]::ExecuteNonQueryByName($scriptName [, $sqlParamaters])

$result = [Profitbase.Data.SqlScript]::ExecuteNonQueryById($scriptId [, $sqlParamaters])

Executes the defined SQL Script against the solution database connection and returns the number of rows affected.


$objectResult = [Profitbase.Data.SqlScript]::ExecuteScalarByName($scriptName [, $sqlParamaters])

$objectResult = [Profitbase.Data.SqlScript]::ExecuteScalarById($scriptId [, $sqlParamaters])

Executes the defined SQL Script, and returns a single value of first column of the first row in the result set returned by the query.


$dataTable = [Profitbase.Data.SqlScript]::LoadTableByName($scriptName [, $sqlParamaters])

$dataTable = [Profitbase.Data.SqlScript]::LoadTableById($scriptId [, $sqlParamaters])

Executes the defined SQL Script against the solution database connection and returns a DataTable object that contain the resulting columns and rows.


$sqlDataReader = [Profitbase.Data.SqlScript]::ExecuteReaderByName($scriptName [, $sqlParamaters])

$sqlDataReader = [Profitbase.Data.SqlScript]::ExecuteReaderById($scriptId [, $sqlParamaters])

Executes the defined SQL Script against the solution database connection and returns a SqlDataReader object that can be used to read field and row data.



Examples

$param1 = New-Object System.Data.SqlClient.SqlParameter('@logEntryType',
[System.Data.SqlDbType]::NVarChar)
$param1.Value = 'Warning'
[Profitbase.Data.SqlScript]::ExecuteNonQueryByName('NonQuerySql', $param1)

$param1 = New-Object System.Data.SqlClient.SqlParameter('@logEntryType',
[System.Data.SqlDbType]::NVarChar)
$param1.Value = 'Info'
[Profitbase.Data.SqlScript]::ExecuteScalarByName('ScalarSql', $param1)

$param1 = New-Object System.Data.SqlClient.SqlParameter('@logEntryType',
[System.Data.SqlDbType]::NVarChar);
$param1.Value = 'Error'
[Profitbase.Data.SqlScript]::LoadTableByName( 'LoadTableSql', $param1)

$param1 = New-Object System.Data.SqlClient.SqlParameter('@logEntryType', [System.Data.SqlDbType]::NVarChar);
$param1.Value = 'Error'
[Profitbase.Data.SqlScript]::ExecuteReaderByName( 'ReaderSql', $param1)
$readerHadRows = $reader.HasRows
$reader.Close() # Closes reader and connection