SQL via Powershell

[Hikedaya]

Делать было решительно нечего, а идея витала в воздухе давно. Небольшой скрипт с шаблонами для работы с SQL сервером (в частности, MS SQL). Возможно, пригодится в самом ближайшем будущем. Ну а если не только мне - буду рад.

<#Usage example:

$srv = "server-name"
$db = "database-name"
$query = "SQL Query (Select/Insert/Update/Delete)"
$login = "your-username"
$pswd = "your-passord"

Get/Insert/Update/Delete-SQLData -SQLServerName $srv -SQLDatabasename $db -SQLQuery $query -Login $login -Password $pswd

or 

Get/Insert/Update/Delete-SQLData -SQLServerName $srv -SQLDatabasename $db -SQLQuery $query -isTrustedConnection
#>

Function Get-SQLData {
param(
    [String]$SQLServerName,
    [string]$SQLDatabaseName,
    [string]$SQLQuery,
    [switch]$isTrustedConnection,
    [string]$Login,
    [string]$Password
)

$Connection = new-object system.data.sqlclient.sqlconnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlCmd     = New-Object System.Data.SqlClient.SqlCommand 
$DataSet    = New-Object System.Data.DataSet

if ($isTrustedConnection) {
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;trusted_connection=True"
}
else
{
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;User Id=$Login;Password=$Password"   
}

$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $Connection

$Connection.open()
$SqlAdapter.SelectCommand = $SqlCmd
[void]$SqlAdapter.Fill($DataSet)
$Connection.Close()

return $DataSet.tables[0]
}

Function Insert-SQLData {
param(
    [String]$SQLServerName,
    [string]$SQLDatabaseName,
    [string]$SQLQuery,
    [switch]$isTrustedConnection,
    [string]$Login,
    [string]$Password
)

$Connection = new-object system.data.sqlclient.sqlconnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlCmd     = New-Object System.Data.SqlClient.SqlCommand 

if ($isTrustedConnection) {
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;trusted_connection=True"
}
else
{
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;User Id=$Login;Password=$Password"   
}

$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $Connection

$Connection.open()
$sqlCmd.ExecuteNonQuery()
$Connection.Close()
}

Function Update-SQLData {
param(
    [String]$SQLServerName,
    [string]$SQLDatabaseName,
    [string]$SQLQuery,
    [switch]$isTrustedConnection,
    [string]$Login,
    [string]$Password
)

$Connection = new-object system.data.sqlclient.sqlconnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlCmd     = New-Object System.Data.SqlClient.SqlCommand 

if ($isTrustedConnection) {
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;trusted_connection=True"
}
else
{
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;User Id=$Login;Password=$Password"   
}

$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $Connection

$Connection.open()
$sqlCmd.ExecuteNonQuery()
$Connection.Close()
}

Function Delete-SQLData {
param(
    [String]$SQLServerName,
    [string]$SQLDatabaseName,
    [string]$SQLQuery,
    [switch]$isTrustedConnection,
    [string]$Login,
    [string]$Password
)

$Connection = new-object system.data.sqlclient.sqlconnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlCmd     = New-Object System.Data.SqlClient.SqlCommand 

if ($isTrustedConnection) {
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;trusted_connection=True"
}
else
{
    $Connection.ConnectionString = "server=$SQLServerName;database=$SQLDatabaseName;User Id=$Login;Password=$Password"   
}

$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $Connection

$Connection.open()
$sqlCmd.ExecuteNonQuery()
$Connection.Close()
}

@музыка: Bryan El - Solaris

@темы: PowerShell, Scripting