Query an SQL server

Description

Execute commands and retrieve data from an SQL server using PowerShell.

Solution

The .NET classes provided by the System.Data.SqlClient namespace can be used to connect to an SQL server and execute commands:

Open and close a connection to an SQL server:

$DataSource = 'SQLServer'
$DbName = 'TestDb'
  
#Create a connection object
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DataSource; Initial Catalog=$DbName; Integrated Security=SSPI")

#Open a database connection
$conn.Open()

#Perform actions
#...
  
#Close the connection
$conn.Close()
  
#Cleanup
$conn.Dispose()

Define the data source and database name
Create a new connection object with the connection details
Open the connection to the SQL server
Perform all required database actions
Close the connection
Dispose the used connection object

Execute queries:

#Create a command object
$cmd = $conn.CreateCommand()
 
#Define the SQL query
$sqlQuery = "SELECT * FROM Table1"

#Assign SQL query to the command 
$cmd.CommandText = $sqlQuery

#Create SqlDataAdapter
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd

#Create DataSet
$data = New-Object System.Data.DataSet 

#Fill the dataset
$adapter.Fill($data) | Out-Null

#Retreived data
$data.Tables

#Cleanup
$adapter.Dispose()
$data.Dispose()
$cmd.Dispose()

Create a command object
Define the SQL query
Assign the SQL query to the command
Create a new System.Data.SqlClient.SqlDataAdapter and assign the command to the SqlDataAdapter
Create a new System.Data.DataSet
Fill the DataSet
Dispose the used objects

Execute commands:

#Create a command object
$cmd = $conn.CreateCommand()
  
#Define the SQL command
$cmd.CommandText ="INSERT INTO Table1 VALUES ('testtext 1', 'testtext 2', 123)"
  
#Execute the command
$cmd.ExecuteNonQuery() | Out-Null
  
#Cleanup
$cmd.Dispose()

Create a command object
Define the SQL command
Execute the SQL command
Dispose the used command object