Accessing SQL Server with Powershell

The more I use Powershell the more I realize how useful it can be in certain scenarios. Here I will demonstrate how to access SQL Server with a Powershell script. This can be used for almost any purpose where integration with SQL Server is required.

First you'll need to create a simple sql table named "test" with 2 columns "column1" and "column2" both of type varchar(10). For simplicity I suggest a value of "1" in column1 and a value of "2" in column2.

create table test
(
column1 varchar(10),
column2 varchar(10)
)

Populate the table with 1 row.

insert into test
(column1,column2)
values
('1','2')

Let's start out with the whole script and break it down.



$ScriptName = $myInvocation.MyCommand.Name
$Server = "yoursqlservername"

#variable that will be a parameter
#whatever value you populated into column1 in test table.

$column1 = 1

function New-SQLconnection {
Param ([string]$server,
[string]$database = "yourdatabasename",
[string]$connectionName = $ScriptName)
if (test-path variable:\conn) {
$conn.close()
} else {
$conn = new-object ('System.Data.SqlClient.SqlConnection')
}
$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
$conn.ConnectionString = $connString
$conn.StatisticsEnabled = $true
$conn.Open()
$conn
}

function Query-SQL {
Param ($query, $conn, [int]$CommandTimeout = 30)
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandTimeout = $CommandTimeout
$sqlCmd.CommandText = $query
$sqlCmd.Connection = $conn

#add parameters here
$sqlCmd.Parameters.AddWithValue("@column1",$column1) | Out-Null

$Rset = $sqlCmd.ExecuteReader()
if ($? -eq $false) {
Close-SQLquery $rset
[int]$rset = -1
}
if ($Rset.hasrows -eq $false) {
Close-SQLquery $rset
[string]$rset = ""
}
,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

function Close-SQLquery {
Param ($query)
$query.close()
$query = $null
}

function Remove-SQLconnection {
Param ($connection)
$connection.close()
$connection = $null
}

$myConn = New-SQLconnection $Server "yourdbnamehere"
if ($x.state -eq "closed") {"Failed to establish a connection";EXIT}
$query = "SELECT column2 from test where column1 = @column1"
$data = Query-SQL $query $myConn
while($data.Read())
{
Write-Host $data.GetValue(0)
}

Close-SQLquery $data

Remove-SQLconnection $data


Lets begin.

$ScriptName = $myInvocation.MyCommand.Name
$Server = "yoursqlservername"

#variable that will be a parameter
#whatever value you populated into column1 in test table.

$column1 = 1

Here I am declaring variables and populating them with values that are pretty straight forward. $column1 is going to be hardcoded for the purposes of this exaple. This is why generic create table and insert statements above are recommended.

function New-SQLconnection {
Param ([string]$server,
[string]$database = "yourdatabasename",
[string]$connectionName = $ScriptName)
if (test-path variable:\conn) {
$conn.close()
} else {
$conn = new-object ('System.Data.SqlClient.SqlConnection')
}
$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
$conn.ConnectionString = $connString
$conn.StatisticsEnabled = $true
$conn.Open()
$conn
}

Here I create a function that allows me create a SQL connection and open that connection.

function Query-SQL {
Param ($query, $conn, [int]$CommandTimeout = 30)
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandTimeout = $CommandTimeout
$sqlCmd.CommandText = $query
$sqlCmd.Connection = $conn

#add parameters here
$sqlCmd.Parameters.AddWithValue("@column1",$column1) | Out-Null

$Rset = $sqlCmd.ExecuteReader()
if ($? -eq $false) {
Close-SQLquery $rset
[int]$rset = -1
}
if ($Rset.hasrows -eq $false) {
Close-SQLquery $rset
[string]$rset = ""
}
,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

Here I am creating a function that assigns necessary values and executes a executereader command. Note the use of parameter creation and mapping. This can be tailored for execute scalar and others. This also drops the result set into a variable ($Rset).

function Close-SQLquery {
Param ($query)
$query.close()
$query = $null
}

function Remove-SQLconnection {
Param ($connection)
$connection.close()
$connection = $null
}

Close and remove connections are pretty self explainatory.

$myConn = New-SQLconnection $Server "yourdbnamehere"
if ($x.state -eq "closed") {"Failed to establish a connection";EXIT}
$query = "SELECT column2 from test where column1 = @column1"
$data = Query-SQL $query $myConn
while($data.Read())
{
Write-Host $data.GetValue(0)
}

Close-SQLquery $data

Remove-SQLconnection $data

Here I put it all in motion. Here I call the function with inputs and pass in the variables. Next I drop the results to a dataset variable ($data). To complete the test I write the result to the code window. Finally, I close and remove.

Reading data from SQL server from a powershell script isn't so bad. Extending this for reasons such as data insertions and lookups for applications will be next in the series of Powershell scripts. You'll see this next. Until them Happy Coding!