I am using PowerShell version 5, And I am executing a couple of SQL commands in PowerShell script. Unlike SQL Server where we execute commands in BEGIN TRANSACTION and COMMIT TRANSACTION so that if a single command fails everything is rollbacked I wanted to achieve a similar thing in my Powershell script.
So in my scenario, I have 3 SQL commands if any fails then the table should not be dropped:
- 1st DROP TABLE IF EXISTS
- 2nd Create table
- 3rd Insert Records
While inserting records I am trying to insert multiple rows using an array and to test the working of scope so I am intentionally adding duplicate Id value, so the expected result is if Id i.e. 1st array element is valid then proceed for next element, but if 2nd array element is not valid then rollback changes.
But since I have limited knowledge of Powershell so not sure how to achieve this as a result session is not getting committed even if Id's are not duplicate or duplicate.
Below is my script so far:
$connString = "Data Source=<Server-Name>;Database=<DB-Name>;User ID=<Login>;Password=<Pass>"
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
$arr_values = @('1','1')
$tbl_name = "dbo.test1"
$conn.Open()
try
{
if($conn.State -eq "Open")
{
foreach ($Id in $arr_values)
{
$scope = New-Object -TypeName System.Transactions.TransactionScope
$Drop_Command= "DROP Table IF EXISTS $tbl_name"
Invoke-Sqlcmd -ServerInstance <Server-Name> -Database <DB-Name> -Query $Drop_Command
$Create_Command = "Create table dbo.test1 (Id int, CONSTRAINT PK_test1_Id PRIMARY KEY (Id))"
Invoke-Sqlcmd -ServerInstance <Server-Name> -Database <DB-Name> -Query $Create_Command
$Insert_Command = "INSERT INTO dbo.test1(Id) Values ($Id)"
Write-Host "$Insert_Command->" $Insert_Command
Invoke-Sqlcmd -ServerInstance <Server-Name> -Database <DB-Name> -Query $Insert_Command
# Start-Sleep -Seconds 10
Write-Host "Record Inserted with Id->" $Id
}
$scope.Complete()
# $scope.Dispose()
}
}
catch
{
Write-Host "Record not Inserted ->" $Id
$_.exception.message
}
finally
{
$scope.Dispose()
$conn.Close()
}