Powershell Programming: sql server sqlclient

I am in the process of putting my notes into web pages, please keep checking in later for more contents

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