# 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"
