How to connect to / read from / write to SQL Server from Powershell?

First create the connection object, set the connection string and open the connection:

$objSqlConnection = New-Object System.Data.SqlClient.SqlConnection
$objSqlConnection.ConnectionString = "Server=YourSqlServer; Database=YourDataBase; User Id=YourUsername; password=YourPassword"
$objSqlConnection.Open()

You can also use “Integrated Security=true;” instead of “User Id” and “password” to login with the user executing the script.
Now create a SqlCommand, set its query and execute it:

$objSqlCommand = $objSqlConnection.CreateCommand()
$objSqlCommand.CommandText = "INSERT INTO northwind.dbo.testtable (YourColumnName) VALUES (YourColumnData)"
$objSqlCommand.ExecuteNonQuery()

This will execute inserts and updates, commands that will not return any data. To query your tables you can use following code.

Create a reader, set a query again, execute it and read its results:

$objSqlCommand.CommandText = "SELECT column_str, column_int, column_flt,  FROM northwind.dbo.testtable"
$objSqlReader = $objSqlCommand.ExecuteReader()
while($objSqlReader.Read())
{
  $column_str = $objSqlReader.GetString(0)
  $column_int = $objSqlReader.GetInt32(1)
  $column_flt = $objSqlReader.GetFloat(2)
}

Finally close the sql connection.

$objSqlConnection.Close()

Documentation for functions, methods and properties used in this post:

• System.Data.SqlClient.SqlConnection
• $objSqlConnection.ConnectionString
• $objSqlConnection.Open()
• $objSqlConnection.CreateCommand()
• $objSqlCommand.CommandText
• $objSqlCommand.ExecuteNonQuery()
• $objSqlCommand.ExecuteReader()
• $objSqlReader.Read()
• $objSqlReader.GetString()
• $objSqlReader.GetInt32()
• $objSqlReader.GetFloat()
• SqlDataReader Methods
• $objSqlConnection.Close()