Introduction
Powershell is a powerful scripting language for performing various day-to-day operations on Windows and other operating systems. One of the common requirements is to perform various CRUD operations on SQL Server. These could be operations such as cleaning up the database or adding new values to the database.
Since PowerShell uses .NET objects the steps to connect to the SQL Server database and execute commands are similar to those we use in C#. First, we need to connect to the database using the ADO.NET connection class and then use the various commands to perform CRUD operations.
In general, for performing any CRUD operation in an SQL server database, we need to follow the below steps:
- Create a connection to the database from your code
- Execute commands on the database.
- Declare the command object which represents the action we want to perform.
- Close the connection
For performing CRUD operations using PowerShell, we need to follow the above common steps. Please note that variables in Powershell are declared using the "$" prefix.
Step 1. First, we can define the connection string to the database
$connectionString= "Data Source=DESKTOP-0V9BC8L;User Id=micky;Integrated Security=yes"
Step 2. Then we can create a connection object using PowerShell.
$connection=New-Object System.Data.SqlClient.SqlConnection $connectionString
Step 3. We need to define the command we want to execute on the DB. This means defining the command text as well as the command object. Here we are just fetching the values from the database so we are using a simple select statement. But you can also use stored procedure or any other valid SQL statement depending on the requirement.
$connection.open()
$queryString ="select getdate()"
$command=$connection.CreateCommand()
$command.CommandText=$queryString
#$command = New-Object System.Data.SqlClient.SqlCommand $connection
Step 4. Finally, we can execute the command and close the connection. This will print the current date and time on the console since we have specified that in the select statement.
$reader=$command.ExecuteReader()
$reader.Read()
Write-Host $reader.GetDateTime(0)
$connection.Close()
This is a simple example to understand how to connect to an SQL Server database and perform any CRUD operation. But for more complex updates we need to also use transactions. We can declare our SQL statements as a part of a transaction using the BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN commands.
$command = "INSERT INTO Products (id, name) VALUES (1, 'product1')"
BEGIN TRY
#execute command
BEGIN TRAN
$command.ExecuteNonQuery()
COMMIT TRAN
END TRY
BEGIN CATCH
#catch logic
ROLLBACK TRAN
END CATCH