MISC.‎ > ‎

Get-SQLData (Powershell)

This Script will query an SQL server for data.

The script uses a Config file that is configured in the first time you run the script.

You can Assign easy and simple to remember aliases for long table names.


The script outputs the result directly to powershell as an object that you can later sort and format as you like.


Download The Script

 


# ==========================================================

# Get-SQLData.ps1

# Made By : Assaf Miron

# http://assaf.miron.googlepages.com

# Description : Connects to an SQL Server and retrieves the requested Query.

# You can allso define aliases for table names.

# ==========================================================


param( [String] $SQLTable="",

[String] $SQLRange="",

[String] $SQLQuery="",

[Bool] $Config = $False )

$Tables = @{}

[string]$ConnectionString = " "


$ScriptPath = $MyInvocation.MyCommand.Path

# Get Script Location (For Config File)

$ScriptLocation = Split-Path -Path $ScriptPath -Parent

# Define a Config File Name

$ConfigFileName = $ScriptLocation+"\SQLApp.config"


function IsHelpRequest($argument)

{

return ($argument -eq "-?" -or $argument -eq "-help");

}


Function Usage

{

# Function that displays the help related to this script following

# the same format provided by get-help or <cmdletcall> -?

@"

NAME:

`tGet-SQLData.ps1 [-SQLTable][-SQLRange][-SQLQuery][-Config]

SYNOPSIS:

`tGets the Requsted data from an SQL Table to an Object.

`tSupports only Select Frases.

SYNTAX:

`tGet-SQLData.ps1

`t`t[-SQLTable <Table Name>]

`t`t[-SQLRange <Query Range>]

`t`t[-SQLQuery <Free Text Select Query>]

`t`t[-Config <Bool>]

PARAMETERS:

`t-SQLTable (Required)

`t`tA Table Name to look into, can use Table Alias names (configurred in the Config File).

`t-SQLRange (Required only with the SQLTable Parameter)

`t`tA Table Range for Example : *, (Any Column Name from the Table

`t-SQLQuery (Not Required)

`t`tFree Text select query, can replace the table parmeter (SQLTable),

`t`tcan use table Aliases names (configurred in the Config File).

`t-Config (Not Required)

`t`tSet to True to Edit the Config File.

`t-------------------------- EXAMPLE 1 --------------------------

C:\PS> .\Get-SQLData.ps1 -SQLQuery "Select * from MyTable where Name='Assaf'"


`t-------------------------- EXAMPLE 2 --------------------------

C:\PS> .\Get-SQLData.ps1 -SQLTable "Master.MyTable" -SQLRange *

REMARKS:

"@

Write-Host "Table Alias Avialable:"

$Tables

break;

}


function Join-Data

# Function Joins arrays and Strings to a Single Object with Members

# I Used the same principle of the Out-DataTable and converted it to Join Objects into one

# Using the Add-Member cmdlet. the Function writes to a predefiend object named $DataObject

{

param($objName="") # This parameter is used for objects that don't have member other than Length like Strings

foreach ($item in $Input)

{

$Item.PsObject.get_properties() | foreach{

if ($_.value -eq $null)

{

$DataObject | Add-Member noteproperty $_.Name "[empty]"

}

elseif ($_.IsArray) {

$DataObject | Add-Member noteproperty $_.Name [string]::Join($_.value, ";")

}

elseif ($objName -ne "") {

$DataObject | Add-Member noteproperty $objName $Item -Force

}

else

{

$DataObject | Add-Member noteproperty $_.Name $_.value -Force

}

}

}

return @(,$DataObject)

}


function Check-ConfigFile()

{

If (!(Test-Path $ConfigFileName)){

$ConString = Read-Host "Enter a Connection String for your Regular DB"

$Res = Read-Host "Would you Like to create Aliases for your Tables? [Yes(Y)][No(N)]"

If ($Res -ieq "Y" -or $Res -ieq "Yes"){

Write-Host "Enter Table Names in the Format:`n`t TableAlias,TableRealName"

Write-Host "To end inserting just Enter end"

$TableAlias = @()

$InputLine = ""

While ($InputLine -ne "end"){

$InputLine = Read-Host "TableAlias,TableName"

$TableAlias += $InputLine

}

"ConnectionString : $ConString" | Out-File $ConfigFileName

$ID = 1

ForEach($tbl in $TableAlias){

"Table$ID : $tbl" | Out-File $ConfigFileName -Append

$ID++

}

}

}


#region Read Configuration File

$ConfigData = Get-Content $ConfigFileName

# Define Connection String

$ConnectionString = ($($ConfigData -match "ConnectionString").split(":")[1].Trim()).ToString()

$arrTables = $($ConfigData -match "Table")

ForEach ($tbl in $arrTables)

{

$tmpLine = $tbl.Split(":")[1].Trim()

If ($tmpLine -ne ""){

$Tables.add($tmpLine.Split(",")[0],$tmpLine.Split(",")[1])

}

}

#endregion

return $ConnectionString

}


$ConnectionString = Check-ConfigFile


# Check for Usage Statement Request

$args | foreach { if (IsHelpRequest $_) { Usage; exit; } }


# Show or Edit the Config File if True

if ($Config -and $SQLQuery -eq "" -and $SQLTable -eq "")

{

$Res = Read-Host "Would you Like to create Aliases for your Tables? [Yes(Y)][No(N)]"

If ($Res -ieq "Y" -or $Res -ieq "Yes")

{

Write-Host "Enter Table Names in the Format:`n`t TableAlias,TableRealName"

Write-Host "To end inserting just Enter - end\n"

$TableAlias = @()

$InputLine = ""

$InputLine = Read-Host "TableAlias,TableName"

While ($InputLine -ne "end")

{

$TableAlias += $InputLine

$InputLine = Read-Host "TableAlias,TableName"

}

$ID = $Tables.Count + 1

ForEach($tbl in $TableAlias)

{

"Table$ID : $tbl" | Out-File $ConfigFileName -Append

$ID++

}

}

else { $Tables }

break;

}


#region Define SQL Query Vars

# Check if there is an Alias for the inserted table

if($SQLQuery -ine ""){

foreach($tbl in $Tables.Keys)

{

If($SQLQuery -match "from $tbl")

{

$tmpTbl = $tbl

$SQLQuery = $SQLQuery.ToLower().Replace("from $($tmpTbl.ToLower())","from $($Tables.get_Item($tmpTbl))")

}

}

}

# Create the SQL Query

if($SQLQuery -eq "" -or $SQLQuery -eq $Null)

{

if($SQLTable -eq "" -or $SQLTable -eq $null) {

#throw "Enter a Table Name or an SQL Query"

$SQLTable = Read-Host "SQL Table Requierd"

if ( $SQLTable -eq "" -or $SQLTable -eq $null) { break }

}

# Replace The Table Alias with the real Table Name

if($Tables.Contains($SQLTable)){

$SQLTable = $Tables.get_Item($SQLTable)

}

if($SQLRange -eq "") { 

#throw "Enter a Range to Query in or an SQL Query" 

$SQLRange = Read-Host "SQL Range Requierd"

if ( $SQLRange -eq "" -or $SQLRange -eq $null) { break }

}

$SQLQuery = "select "+$SQLRange+" from "+$SQLTable

}

#endregion



#region Connect to DB

# Open a new Connection and Create a new Command Type

# Connect To DB

$SQLconn = New-Object System.Data.SqlClient.SqlConnection("$ConnectionString")

$SQLcmd = New-Object System.Data.SqlClient.SqlCommand

$SQLcmd.CommandText = $SQLQuery

$SQLcmd.Connection = $SQLconn

# Setup .NET SQLAdapter to execute and fill .Net Dataset

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SQLAdapter.SelectCommand = $SQLcmd

# Create a new DataSet

$DataSet = New-Object System.Data.DataSet

# Fill the Dataset with the SQL Data Recieved

$nRecs = $SQLAdapter.Fill($DataSet)

$SQLconn.Close()

#endregion



# Assign the SQLData object with all the Dataset Table Items

$SQLData = $DataSet.Tables[0]


# Create an Empty Array to Contain all The Data of all Scaned Computers

$AllComputerInfo = @()



#region Insert all the SQL Data to the DataObject Object

# For Each Record in the DataSet 

For($Rec=0; $Rec -lt $nRecs; $Rec++)

{

# Create an Empty psObject, $DataObjcet - Used by this Name in the Join-Data Function

$DataObject = New-Object psobject


# Go Over every object in the SQL Data and assing it with a name and value in the DataObject

For( $I=0; $I -lt $SQLData.Columns.Count;$I++)

{

$SQLData.Rows[$Rec][$i].ToString().Trim() | Join-Data -objName $SQLData.Columns[$i] | Out-Null

}

$AllComputerInfo += $DataObject

}

#endregion


return $AllComputerInfo 

Comments