Powershell Programming: sql server sqlclient

Powershell Version

SQL Server PowerShell (SQLPS)

SQL Server Management Objects (SMO) smo 

.NET (System.Data.SqlClient) sqlclient 

Invoke-SqlCmd invoke_sqlcmd 

$PSVersionTable
$PSVersionTable.PSVersion

Get-Module -ListAvailable
Import-Module -Name SQLPS
Import-Module -Name SqlServer

%ProgramFiles%\WindowsPowerShell\Modules\SqlServer

Get-Command -Module SQLPS
Get-Command -Module SQLSERVER

-- Benefit: One open connection, multiple (insert) operation possible
-- sqlclient:
-- Data.SqlClient.SqlCommand.
-- System.Data.SqlClient.

$sqlConn = New-Object System.Data.SQLClient.SQLConnection
$sqlConn.ConnectionString = "server='myservername';database='mydbname';trusted_connection=true;"
-- $sqlConn.ConnectionString = "Server=myservername\myinstancename;Integrated Security=true;Initial Catalog=master"

$sqlConn.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $sqlConn

$sql = "INSERT INTO [mydbname].[dbo|myschema].[mytablename] ([col1],[col2]) VALUES ('" + $value1 + "','" + $value2  + "')"
$Command.CommandText = $sql 
$Result = $Command.ExecuteNonQuery()

$sqlConn.Close()

# -- -----------
$sqlcmd = $sqlConn.CreateCommand()
<# or #>
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn
$query = "SELECT name, database_id FROM sys.databases"
$sqlcmd.CommandText = $query

sqlclient 
$reason = Read-Host 'Reason are you logging on'
$reason.replace("'","''")
$os = Get-WmiInfo Win32_OperatingSystem
[assembly.reflection]::loadwithpartialname('System.Data')
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=SQLSERVER;Initial Catalog=SYSINFO;Integrated Security=SSPI;"
$conn.open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$cmd.commandtext = "INSERT INTO servers (servername,username,spversion,reason) VALUES('{0}','{1}','{2}','{3}')" -f $os.__SERVER,$env.username,$os.servicepackmajorversion,$reason
$cmd.executenonquery()
$conn.close()

Reference

best sqlclient 
best sqlclient1 
access sql data 
msdn Powershell