Home - Blogs - Orchestrator (Opalis) - Executing SQL Statements with PowerShell
Samstag, den 28. Februar 2015 um 21:29 Uhr

Executing SQL Statements with PowerShell

Artikel bewerten
(0 Bewertungen)

Erstellt am Samstag, 28. Februar 2015 Geschrieben von Stefan Horz

Working with Orchestrator has the challenge to log the execution results so that you can have a log to analyze. One of the best ways is to design a custom database for your logging.

In Orchestrator there are two Standard Activities to work with databases: "Query Database" and "Write to Database". With "Query Database" Activity you can execute insert and update SQL statements. If you subscribe Publish Data which could contain a single quote you will get problems with your SQL statements while the single quote must be escaped.

In PowerShell you can escape a whole string. So if you don't want the string manipulation in an extra Activity before you can use a PowerShell Script in a "Run .Net Activity" for logging in a SQL database table:

$newIndex = powershell{

$PublishedData = @'
{Your Published Data which may contain a ' or other special chars}
'@
$quote = @'
'
'@
$escquote = @'
''
'@

$query=$PublishedData.Replace($quote,$escquote)

$dataSource = "SQL01.LAB.VASERV.EU\Custom"
$database = "VASP"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.ConnectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$connection.Close()
return $table.index}

Read more

Kommentar hinterlassen