Profitbase.Data.SqlScript functions
These functions use SQL Scripts stored in InVision. Scripts can be addressed by Name or Id.
$result = [Profitbase.Data.SqlScript]::ExecuteNonQueryByName($scriptName [, $sqlParameters])
$result = [Profitbase.Data.SqlScript]::ExecuteNonQueryById($scriptId [, $sqlParameters])
Executes the defined SQL Script against the solution database connection and returns the number of rows affected.
$objectResult = [Profitbase.Data.SqlScript]::ExecuteScalarByName($scriptName [, $sqlParameters])
$objectResult = [Profitbase.Data.SqlScript]::ExecuteScalarById($scriptId [, $sqlParameters])
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 [, $sqlParameters])
$dataTable = [Profitbase.Data.SqlScript]::LoadTableById($scriptId [, $sqlParameters])
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 [, $sqlParameters])
$sqlDataReader = [Profitbase.Data.SqlScript]::ExecuteReaderById($scriptId [, $sqlParameters])
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.
Example: ExecuteNonQueryByName
$param1 = New-Object System.Data.SqlClient.SqlParameter('@logEntryType',
[System.Data.SqlDbType]::NVarChar)
$param1.Value = 'Warning'
[Profitbase.Data.SqlScript]::ExecuteNonQueryByName('NonQuerySql', $param1)
Example: ExecuteScalarByName
$param1 = New-Object Microsoft.Data.SqlClient.SqlParameter('@logEntryType',
[System.Data.SqlDbType]::int)
$param1.Value = 3
Write-Output([Profitbase.Data.SqlScript]::ExecuteScalarByName('ScalarScript', $param1))
Example: LoadTableByName
$param1 = New-Object System.Data.SqlClient.SqlParameter('@logEntryType',
[System.Data.SqlDbType]::NVarChar);
$param1.Value = 'Error'
[Profitbase.Data.SqlScript]::LoadTableByName( 'LoadTableSql', $param1)
Example: ExecuteReaderByName
$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