I'm using a couple of long SQL queries to run validation on a staging table prior to synchronising with the Person table. Originally I was using stored procedures but this makes managing change more complex and so have been advised to run the queries from a process step via a script.
I've looked at two approaches for running SQL in scripts:
1. Using SqlExecutor
ConnectionString = Connection.GetSingleProperty("DialogDatabase", "ConnectionString", "")
sqlStatement = "UPDATE Test SET Test2 = 1 WHERE Test1 = 'test'"
Dim conData As ConnectData = DbApp.Instance.Connect(ConnectionString)
Using conData.Connection
Dim cSQL As SqlExecutor = conData.Connection.CreateSqlExecutor(conData.PublicKey)
Result = cSQL.SqlExecuteNonQuery(sqlStatement)
End Using
2. Using the Invoke-Sqlcmd in Powershell
$Query = "
UPDATE Test
SET Test2 = 1
WHERE Test1 = 'test'
"
$Tables = Invoke-Sqlcmd -ErrorAction Stop -ConnectionString "${ConnString}" -Query "${Query}" -As DataTables
I have been advised to use the Powershell option over option 1.
I have also read in other posts that using embedded SQL in scripts is not best practice and it is better to use stored procedures when using an application server (which we will eventually be using)?
