Я использую PowerShell версии 5 и выполняю несколько команд SQL в сценарии PowerShell. В отличие от SQL Server, где мы выполняем команды в BEGIN TRANSACTION и COMMIT TRANSACTION, так что в случае сбоя одной команды все откатывается, я хотел добиться подобного в моем сценарии Powershell.

В моем сценарии есть 3 команды SQL, если какая-либо из них не сработает, то таблица не должна быть сброшена:

  • 1-я DROP TABLE IF EXISTS
  • 2-й Создать таблицу
  • 3-я Вставка записей

При вставке записей я пытаюсь вставить несколько строк, используя массив, и для проверки работы scope я намеренно добавляю дублирующее значение Id, поэтому ожидаемый результат - если Id, т.е. первый элемент массива, действителен, то переходим к следующему элементу, но если второй элемент массива не действителен, то происходит откат изменений.

Но поскольку у меня ограниченные знания Powershell, я не уверен, как этого добиться, в результате сессия не фиксируется, даже если идентификаторы не дублируются или дублируются.

Ниже представлен мой сценарий на данный момент:

$connString = "Data Source=;Database=;User ID=;Password="
 
 
  $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  -Database  -Query $Drop_Command
        
        $Create_Command = "Create table dbo.test1 (Id int, CONSTRAINT PK_test1_Id PRIMARY KEY (Id))"
        Invoke-Sqlcmd -ServerInstance  -Database  -Query $Create_Command
        
        
        $Insert_Command = "INSERT INTO dbo.test1(Id) Values ($Id)"
        Write-Host "$Insert_Command->" $Insert_Command
        Invoke-Sqlcmd -ServerInstance  -Database  -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()
 }

Ответы (3)

Invoke-SqlCmd - это удобный метод. Либо создайте отдельный скрипт со всей обработкой транзакций и передайте его в Invoke-SqlCmd, либо используйте объекты SqlConnection, SqlCommand, SqlTransaction напрямую.

И я не уверен, что TransactionScope работает в PowerShell.

У меня нет знаний по PowerShell, но, похоже, вы пытаетесь решить проблему неправильным способом.

В SQL существует 2 набора команд: DDL - язык определения данных и DML - язык манипулирования данными.

Транзакции, если они поддерживаются движком db, корректно работают с DML. На поддержку транзакций с DDL полагаться не стоит. Это немного теории.

Что вы можете сделать - немного изменить порядок и изменить, чтобы гарантировать то, что вы хотите:

  1. Создайте таблицу с другим именем
  2. Попробуйте вставить данные. Если это не удается, отбросьте таблицу.
  3. Создайте новую таблицу с другим именем.
  4. Если вставка прошла успешно, отбросьте исходную таблицу и переименуйте новую таблицу в исходную.

Предыдущее решение имеет смысл только в том случае, если структура таблиц различна. Если таблицы одинаковые, просто:

  1. Начать транзакцию
  2. Удалить все.
  3. Insert
  4. Коммит

Если вставка прошла успешно, исходные данные в первой таблице удаляются. Если нет, фиксация не применяется. Нет причин удалять таблицу как есть.

Добавляя к ответу Дэвида, вы можете использовать объекты SqlTransaction и SqlCommand вместо Invoke-SqlCmd. Вот пример кода, основанного на коде из вашего вопроса. Я не пытался исправить ошибки в исходном коде (например, создание таблицы dbo.test1 завершится неудачей на второй итерации).

$connString = "Data Source=;Database=;User ID=;Password="
 
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
$arr_values = @('1','1') 
$tbl_name = "dbo.test1"
 
$conn.Open()
$tran = $conn.BeginTransaction()

try {

    foreach ($Id in $arr_values) {

        $script = @"
            DROP Table IF EXISTS $tbl_name;
            Create table dbo.test1 (Id int, CONSTRAINT PK_test1_Id PRIMARY KEY (Id));
            INSERT INTO dbo.test1(Id) Values ($Id);
"@

        $cmd = New-Object System.Data.SqlClient.SqlCommand($script, $conn)
        $cmd.Transaction = $tran
        [void]$cmd.ExecuteNonQuery()
        Write-Host "Record Inserted with Id->" $Id
        }
    $tran.Commit()

}    

catch {
    $tran.Rollback()
    Write-Host "Record not Inserted ->" $Id
    $_.exception.message
}
finally {
    $conn.Close()
}

2022 WebDevInsider