≡ Menu

Gettting and Setting SQL Data

Many of the Powershell scripts I write either retrieve data from SQL Server or execute a nonquery i.e. delete, insert, update against SQL Server. I find myself reusing a couple of simple functions often enough that posted Get-SqlData and Set-SqlData functions to poshcode as LibrarySqlData.ps1:
#######################
function Get-SqlData
{
param([string]$serverName=$(throw ‘serverName is required.’), [string]$databaseName=$(throw ‘databaseName is required.’),
[string]$query=$(throw ‘query is required.’))

Write-Verbose “Get-SqlData serverName:$serverName databaseName:$databaseName query:$query”

$connString = “Server=$serverName;Database=$databaseName;Integrated Security=SSPI;”
$da = New-Object “System.Data.SqlClient.SqlDataAdapter” ($query,$connString)
$dt = New-Object “System.Data.DataTable”
[void]$da.fill($dt)
$dt

} #Get-SqlData

#######################
function Set-SqlData
{

param([string]$serverName=$(throw ‘serverName is required.’), [string]$databaseName=$(throw ‘databaseName is required.’),
[string]$query=$(throw ‘query is required.’))

$connString = “Server=$serverName;Database=$databaseName;Integrated Security=SSPI;”
$conn = new-object System.Data.SqlClient.SqlConnection $connString
$conn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand(“$query”, $conn)
[void]$cmd.ExecuteNonQuery()
$conn.Close()

} #Set-SqlData

To use soure the  . ./LibrarySqlData.ps1 file. And here are a couple examples:
Get-SqlData ‘Z002SQLEXPRESS’ ‘master’ ‘SELECT @@servername’
Set-SqlData ‘Z002SQLEXPRESS’ ‘pubs’ “update authors set au_lname = ‘White’ WHERE au_lname = ‘White'”

Comments on this entry are closed.

%d bloggers like this: