Revision history [back]

To execute SQL transactions using PowerShell, you can follow the steps below:

1. Install the SQL Server module for PowerShell by running the following command:

Install-Module -Name SqlServer

1. Create a SQL Server connection by using the New-SqlConnection cmdlet. For example:

$connectionString = "Server=<serverName>;Database=<databaseName>;Integrated Security=True"

$connection = New-SqlConnection -ConnectionString $connectionString

1. Open the connection by using the Open() method of the connection object. For example:

$connection.Open()

1. Create a SQL command object by using the New-SqlCommand cmdlet. For example:

$command = New-SqlCommand -Query "SELECT * FROM <tableName>" -Connection $connection

1. Execute the SQL command by using the ExecuteReader() method. For example:

$reader = $command.ExecuteReader()

1. Iterate through the results by using the Read() method of the reader object. For example:

while($reader.Read())
{
    # Process each row
}

1. Close the reader and connection objects by using the Close() method. For example:

$reader.Close()
$connection.Close()

1. To execute SQL transactions such as inserting data, updating data or deleting data, you can use the ExecuteNonQuery() method of the SqlCommand object. For example:

$command = New-SqlCommand -Query "INSERT INTO <tableName> VALUES (<value1>, <value2>, ...)" -Connection $connection

$command.ExecuteNonQuery()

Note: It is recommended to use parameterized queries to prevent SQL injection attacks.