How to Connect SQL with PowerShell

How to connect SQL with PowerShell. PowerShell has built in functionality to connect to SQL databases, which makes it very powerful. Here is how you do it.

Connect SQL with PowerShell

# SQL Server and database
$SQLServer = "localhost" #use Server\Instance for named SQL instances! 
$SQLDBName = "Database"

# SQL Query
$SqlQuery = "select * from table WHERE field = 'data'"

# Connection string 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$Result= New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

#Close the connection 
$SqlConnection.Close()

#Display result 
$Result.Tables[0]