PowerShell: Accessing SQL Data

Using the .Net Framework and ADO.NET

PS C:\> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlClient")

PS C:\>

PS C:\> $cn = New-Object System.Data.SqlClient.Sqlconnection

PS C:\> $cn.ConnectionString = "server=10.10.0.50;database=adventureworks;integrated security=sspi"

PS C:\> $cn.open()

PS C:\>

PS C:\> $cmd = new-object system.data.sqlclient.sqlcommand

PS C:\> $cmd.CommandText = "select top 10 * from person.contacttype"

PS C:\> $cmd.Connection = $cn

PS C:\>

PS C:\> $da = New-Object system.data.sqlclient.sqldataadapter

PS C:\> $da.SelectCommand = $cmd

PS C:\> $dt = New-Object system.data.datatable

PS C:\> $da.Fill($dt)

PS C:\> $cn.close()

PS C:\>

PS C:\> $dt ft * -AutoSize

ContactTypeID Name ModifiedDate

------------- ---- ------------

1 Accounting Manager 6/1/1998 12:00:00 AM
2 Assistant Sales Agent 6/1/1998 12:00:00 AM
3 Assistant Sales Representative 6/1/1998 12:00:00 AM
4 Coordinator Foreign Markets 6/1/1998 12:00:00 AM
5 Export Administrator 6/1/1998 12:00:00 AM
6 International Marketing Manager 6/1/1998 12:00:00 AM
7 Marketing Assistant 6/1/1998 12:00:00 AM
8 Marketing Manager 6/1/1998 12:00:00 AM
9 Marketing Representative 6/1/1998 12:00:00 AM
10 Order Administrator 6/1/1998 12:00:00 AM

Using a "sample" SQL Provider

http://blogs.msdn.com/powershell/archive/2007/06/21/demo-sql-provider-code.aspx

PS C:\> . $env:Windir\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe .\sqlprovider\SQLProvider.dll

PS C:\> Add-PSSnapin sqlprototypeprovider

PS C:\> cd sql:

PS SQL:\> cd miami

PS SQL:\miami>

PS SQL:\miami> dir master tempdb model msdb ReportServer ReportServerTempDB AdventureWorksDW AdventureWorks

PS SQL:\miami> cd adventureworks

PS SQL:\miami\adventureworks> dir name ---- Address AddressType AWBuildVersion BillOfMaterials Contact ContactCreditCard

PS SQL:\miami\adventureworks> cd person.contacttype

PS SQL:\miami\adventureworks\person.contacttype> dir

PS SQL:\miami\adventureworks> cd person.contacttype

PS SQL:\miami\adventureworks\person.contacttype> dir ft –AutoSize

ContactTypeID Name ModifiedDate ------------- ---- ------------

1 Accounting Manager 6/1/1998 12:00:00 AM
2 Assistant Sales Agent 6/1/1998 12:00:00 AM
3 Assistant Sales Representative 6/1/1998 12:00:00 AM
4 Coordinator Foreign Markets 6/1/1998 12:00:00 AM
5 Export Administrator 6/1/1998 12:00:00 AM
6 International Marketing Manager 6/1/1998 12:00:00 AM
7 Marketing Assistant 6/1/1998 12:00:00 AM
8 Marketing Manager 6/1/1998 12:00:00 AM
9 Marketing Representative 6/1/1998 12:00:00 AM
10 Order Administrator 6/1/1998 12:00:00 AM
11 Owner 6/1/1998 12:00:00 AM
12 Owner/Marketing Assistant 6/1/1998 12:00:00 AM
13 Product Manager 6/1/1998 12:00:00 AM
14 Purchasing Agent 6/1/1998 12:00:00 AM
15 Purchasing Manager 6/1/1998 12:00:00 AM
16 Regional Account Representative 6/1/1998 12:00:00 AM
17 Sales Agent 6/1/1998 12:00:00 AM
18 Sales Associate 6/1/1998 12:00:00 AM
19 Sales Manager 6/1/1998 12:00:00 AM
20 Sales Representative 6/1/1998 12:00:00 AM