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

Remarks