# encoding: ascii
# api: powershell
# title: Start-SQL
# description: A series of functions to handle getting data from SQL Servers. Think of these more as tutorials for using SQL Server from powershell than as a finished set of scripts.
# version: 1.0
# type: script
# license: CC0
# x-poshcode-id: 115
# x-archived: 2009-11-12T15:05:44
#
#
# Start-Sql.ps1
###################################################################################################
# This is a SCRIPT which emits functions and variables into the global scope
# Most importantly, it uses a variable $SqlConnection which is expected to exist....
#
# On my computer, I set default values for the server and database, but not for the query,
# nor for authentication (I usually use Integrated Security)
###################################################################################################
# By default when you run this script it:
# * creates the functions
# * initializes the connection
# But we don't automatically do a query -- unless you pass one in!
#
# Thus, calling the script with no parameters results in an initialized connection,
# but it doesn't return anything, so it's basically silent if there are no errors.
#
# the default server and database
param(
$Server = $(Read-Host "SQL Server Name"),
$Database = $(Read-Host "Default Database"),
$UserName, $Password, $Query )
## Uncomment the next line to start the SqlServer (or fail miserably but silently)
# Get-Service -include "MSSQLSERVER" | where {$_.Status -like "Stopped"} | Start-Service
#
# change the SqlConnection (it's set to a default when the script it run)
#
function global:Set-SqlConnection( $Server = $(Read-Host "SQL Server Name"), $Database = $(Read-Host "Default Database"), $UserName , $Password ) {
if( ($UserName -gt $null) -and ($Password -gt $null)) {
$login = "User Id = $UserName; Password = $Password"
} else {
$login = "Integrated Security = True"
}
$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; $login"
}
#
# A function to perform a query that returns a table full of data
#
function global:Get-SqlDataTable( $Query = $(Read-Host "Enter SQL Query")) {
if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand $Query, $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$SqlConnection.Close()
return $DataSet.Tables[0]
}
#
# A function to perform a single-value (series) query. That is, a query that
# returns only a single value per row, which we just output strongly typed.
#
function global:Get-SQLQuery($Query = $(Read-Host "Enter SQL Query"), $type = "string")
{
if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand $Query, $SqlConnection
# $results = @();
# trap [SqlException]
# {
#LogSqlErrors(e.Errors, sqlQuery);
# throw new DataException("Unable to ExecuteReader(), check EventLog for details.", e);
# }
$dr = $SqlCmd.ExecuteReader()
while($dr.Read())
{
#$results += $dr.GetValue(0) -as $type
$dr.GetValue(0) -as $type # emit the value directly, isn't that more PowerShelly?
}
$dr.Close()
$dr.Dispose()
# return $results
}
# Initialize the SqlConnection variable
Set-Variable SqlConnection (New-Object System.Data.SqlClient.SqlConnection) -Scope Global -Option AllScope -Description "Personal variable for Sql Query functions"
# Initially create the SqlConnection
Set-SqlConnection $Server $Database
# go ahead and run the initial query if we have one...
if( $query -gt $null ) {
Get-SqlDataTable $Query
}
# Some aliases to let you use the functions with less typing
Set-Alias gdt Get-SqlDataTable -Option AllScope -scope Global -Description "Personal Function alias from Get-Sql.ps1"
Set-Alias sql Set-SqlConnection -Option AllScope -scope Global -Description "Personal Function alias from Get-Sql.ps1"
Set-Alias gq Get-SqlQuery -Option AllScope -scope Global -Description "Personal Function alias from Get-Sql.ps1"