SQL Console на PowerShell

В качестве ещё одного небольшого приложения на PowerShell я написал небольшой скрипт, который позволит работать с вашими базами данных. Это небольшое самостоятельное Windows-приложение с визуальными формами, которое позволяет писать запросы, а результат запроса можно не долько просматривать в виде удобного GridView, но и применять различные фильтры

#Блок Try/Catch/Finally, для обработки ошибок
function Try
{
    param
    (
        [ScriptBlock]$Command = $(throw "The parameter -Command is required."),
        [ScriptBlock]$Catch   = { throw $_ },
        [ScriptBlock]$Finally = {}
    )
    
    & {
        $local:ErrorActionPreference = "SilentlyContinue"
        
        trap
        {
            trap
            {
                & {
                    trap { throw $_ }
                    &$Finally
                }
                
                throw $_
            }
            
            $_ | & { &$Catch }
        }
        
        &$Command
    }

    & {
        trap { throw $_ }
        &$Finally
    }
}


# Создание формы 
[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
[void][reflection.assembly]::LoadWithPartialName("System.Data")
[void][reflection.assembly]::LoadWithPartialName("System.Data.Sql")
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")

$form = new-object Windows.Forms.Form
$form.Text = "SQL Console (Power Shell)"
$form.WindowState="Maximized"
$form.startposition = "CenterScreen"
$form.autosize = 0 

# add a labelServer
$labelServer = new-object Windows.Forms.label
$labelServer.Location = New-Object System.Drawing.Size(5,10)
$labelServer.autosize = 1
$labelServer.Text="Server Name:"
# add a TextBoxServer
$TextBoxServer = new-object Windows.Forms.TextBox
$TextBoxServer.Location = New-Object System.Drawing.Size(100,10)
$TextBoxServer.autosize = 1
$TextBoxServer.Width=180
$TextBoxServer.Text="."

# add a labelAuth
$labelAuth = new-object Windows.Forms.label
$labelAuth.Location = New-Object System.Drawing.Size(5,35)
$labelAuth.autosize = 1
$labelAuth.Text="Authentication:"
# add a TextBoxAuth
$TextBoxAuth = new-object Windows.Forms.ComboBox
$TextBoxAuth.Location = New-Object System.Drawing.Size(100,35)
$TextBoxAuth.autosize = 1
$TextBoxAuth.Width=180
$TextBoxAuth.Items.Add("Windows Authentication")
$TextBoxAuth.Items.Add("SQL Server Authentication")
$TextBoxAuth.SelectedIndex=0;

# add a labelLogin
$labelLogin = new-object Windows.Forms.label
$labelLogin.Location = New-Object System.Drawing.Size(5,60)
$labelLogin.autosize = 1
$labelLogin.Text="Login:"

# add a TextBoxLogin
$TextBoxLogin = new-object Windows.Forms.TextBox
$TextBoxLogin.Location = New-Object System.Drawing.Size(100,60)
#$TextBoxLogin.autosize = 1
$TextBoxLogin.Width=180

# add a labelPassword
$labelPassword = new-object Windows.Forms.label
$labelPassword.Location = New-Object System.Drawing.Size(5,85)
$labelPassword.autosize = 1
$labelPassword.Text="Password:"

# add a TextBoxLogin
$TextBoxPassword = new-object Windows.Forms.TextBox
$TextBoxPassword.Location = New-Object System.Drawing.Size(100,85)
$TextBoxPassword.autosize = 1
$TextBoxPassword.Width=180
$TextBoxPassword.UseSystemPasswordChar = "true"

# add a PanelConnect
$PanelConnect = new-object Windows.Forms.Panel
$PanelConnect.Location = New-Object System.Drawing.Size(0,0)
$PanelConnect.autosize = 1
$PanelConnect.Height=270
$PanelConnect.Dock="Top"

# add a PanelQuery
$PanelQuery = new-object Windows.Forms.Panel
$PanelQuery.Location = New-Object System.Drawing.Size(10,180)
$PanelQuery.autosize = 1
$PanelQuery.Dock="Fill"

# add a PanelButton
$PanelButton = new-object Windows.Forms.Panel
$PanelButton.Location = New-Object System.Drawing.Size(10,200)
$PanelButton.autosize = 1
$PanelButton.Dock="Bottom"

# add a Scroll1
$Scroll1 = new-object Windows.Forms.Splitter
$Scroll1.Location = New-Object System.Drawing.Size(10,191)
$Scroll1.Dock="Bottom"

# add a TextBoxResult
$TextBoxResult = new-object Windows.Forms.TextBox
$TextBoxResult.Location = New-Object System.Drawing.Size(0,0)
$TextBoxResult.autosize = 1
$TextBoxResult.Multiline="true"
$TextBoxResult.Dock="Fill"
$TextBoxResult.ScrollBars="Vertical"

# add a button 
$button = new-object Windows.Forms.button
$button.Location = New-Object System.Drawing.Size(5,5)
$button.text = "Execute SQL"
$button.width = 100

$button.add_click(
{
#Вот тут кусок для работы с БД
$Query = $TextBoxResult.text
$Connect = $TextBoxServer.text
$Login = $TextBoxLogin.text
$Password = $TextBoxPassword.text
$Aut = $TextBoxAuth.text

if ($Aut -eq 'SQL Server Authentication')
{
$SQLConnection = new-object System.Data.SqlClient.SqlConnection("Data Source='$Connect';User='$Login';password='$Password';")
}
else
{
$SQLConnection = new-object System.Data.SqlClient.SqlConnection("Data Source='$Connect';Integrated Security=SSPI")
}

$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $SQLConnection)

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)

$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]


Try {
    $SQLCommand.ExecuteReader()
} Catch {
    $SQLCommand.EndExecuteNonQuery()
} Finally {
    { Write-Warning "Ошибка!" }; 
}

$Grid.DataSource=$DataTable.DefaultView | Out-GridView;
$SQLConnection.Close()
}
) 

# Вывод формы на экран
$PanelConnect.controls.add($labelServer)
$PanelConnect.controls.add($labelAuth)
$PanelConnect.controls.add($labelLogin)
$PanelConnect.controls.add($labelPassword)

$PanelConnect.controls.add($TextBoxServer)
$PanelConnect.controls.add($TextBoxAuth)
$PanelConnect.controls.add($TextBoxLogin)
$PanelConnect.controls.add($TextBoxPassword)

$form.controls.add($Scroll2)
$PanelQuery.controls.add($TextBoxResult)
$form.controls.add($PanelQuery)

$form.controls.add($PanelConnect)

$PanelButton.controls.add($button)
$form.controls.add($PanelButton)

$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Скрипт для загрузки: sqlposh.ps1 (5,71 kb)