PowerShell uses WinForm to execute TSQL statements in bulk

WinForm module for #加载. NET [Reflection.assembly]::loadwithpartialname ("System.Windows.Forms")

$app =[system.windows.forms.application]
$myForm =new-object System.Windows.Forms.Form
$myForm. text= "T_sql excute window"

#执行程序的按钮

$button 1 = new-object System.Windows.Forms.Button
$button 1. Size = New-object System.drawing.size-argumentlist 75, 23
$button 1. Text = "Executing program"
$button 1. Location = New-object System.drawing.point-argumentlist 200, 200

#获取第一个参数

$label 1 = new-object System.Windows.Forms.Label;
$label 1. Location = New-object System.drawing.point-argumentlist 1, 10
$label 1. Size = New-object System.drawing.size-argumentlist 80, 15
$label 1. Text = "Database address:"
$textbox 1=new-object System.Windows.Forms.TextBox
$textBox 1.Multiline = $true;
$textBox 1.Text = ""
$textBox 1.Size = New-object system.drawing.size-argumentlist 150, 15
$textBox 1.Location = New-object system.drawing.point-argumentlist 90, 10

#获取第二个参数
$label 2 = New-object System.Windows.Forms.Label;
$label 2. Location = New-object System.drawing.point-argumentlist 1, 50
$label 2. Size = New-object System.drawing.size-argumentlist 80, 15
$label 2. Text = "User name:"
$textbox 2=new-object System.Windows.Forms.TextBox
$textBox 2.Multiline = $true;
$textBox 2.Text = ""
$textBox 2.Size = New-object system.drawing.size-argumentlist 150, 15
$textBox 2.Location = New-object system.drawing.point-argumentlist 90, 50

#获取第三个参数
$label 3 = New-object System.Windows.Forms.Label;
$label 3. Location = New-object System.drawing.point-argumentlist 1, 90
$label 3. Size = New-object System.drawing.size-argumentlist 80, 15
$label 3. Text = "Password:"
$textbox 3=new-object System.Windows.Forms.TextBox
$textBox 3.Multiline = $true;
$textBox 3.Text = ""
$textBox 3.Size = New-object system.drawing.size-argumentlist 150, 15
$textBox 3.Location = New-object system.drawing.point-argumentlist 90, 90

#获取第四个参数
$label 4 = New-object System.Windows.Forms.Label;
$label 4. Location = New-object System.drawing.point-argumentlist 1, 130
$label 4. Size = New-object System.drawing.size-argumentlist 80, 15
$label 4. Text = "TSQL Path:"
$textbox 4=new-object System.Windows.Forms.TextBox
$textBox 4.Multiline = $true;
$textBox 4.Text = ""
$textBox 4.Size = New-object system.drawing.size-argumentlist 150, 15
$textBox 4.Location = New-object system.drawing.point-argumentlist 90, 130
# $textbox 2=new-object System.Windows.Forms.TextBox
# $textBox 2.Multiline = $true;
# $textBox 2.Text = "Test"
# $textBox 2.Size = new-object system.drawing.size-argumentlist 281, 113
# $flowLayoutPanel 1 = new-object System.Windows.Forms.FlowLayoutPanel
$myForm. Controls.Add ($label 1)
$myForm. Controls.Add ($textbox 1)
$myForm. Controls.Add ($label 2)
$myForm. Controls.Add ($textbox 2)
$myForm. Controls.Add ($label 3)
$myForm. Controls.Add ($textbox 3)
$myForm. Controls.Add ($label 4)
$myForm. Controls.Add ($textbox 4)
$myForm. Controls.Add ($button 1)
# $myForm. Controls.Add ($textbox 2)
# $myForm. Controls.Add ($label 1)
# $flowLayoutPanel 1.controls.add ($label 1);
# $flowLayoutPanel 1.controls.add ($textBox 1);
# $flowLayoutPanel 1.controls.add ($textBox 2);
# $flowLayoutPanel 1.controls.add ($button 1);
# $flowLayoutPanel 1.Dock = "Fill"
# $flowLayoutPanel 1.FlowDirection = "Topdown"

#下面是批量执行tsql语句的函数
function exec_tsql{
Param ([string] $serverInstance,
[String] $userName,
[String] $password,
[String] $ScriptPath)

$ScriptList = ""
[System.IO.DirectoryInfo] $DirectoryInfo =new-object System.IO.DirectoryInfo $ScriptPath | Sort-object
foreach ($f in ($DirectoryInfo. GetFiles ("*.sql")))
{
$ScriptList = $ScriptList + ";" + $f. Name
}


Try
{
#[void][system.reflection.assembly]::loadwithpartialname (' Microsoft.SqlServer.ConnectionInfo ') |out-null
# $ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance, $userName, $ Password
Import-module "Sqlps"-disablenamechecking
#try
#{
# $ServerConnection. BeginTransaction ()
#Write-host "BeginTransaction."

[System.Text.StringBuilder] $Sql = ""
Foreach ($File in $ScriptList. Split (";"))
{
if ($File-ne "")
{
# $SQL = $Sql. Appendline (([System.io.file]::opentext ($ScriptPath + $File)). ReadToEnd ())
# $ServerConnection. ExecuteNonQuery ($SQL) |out-null
$fileurl = $ScriptPath + $File
Invoke-sqlcmd-inputfile $fileurl-serverinstance $serverInstance-database "Master"-username $userName-password $pass Word
$SQL = ""

Write-host $ScriptPath $file "... ok! "
}
}
# $ServerConnection. CommitTransaction ()

#Write-host "CommitTransaction."
#}
#Catch
#{
# If ($ServerConnection. TRANSACTIONDEPTH-GT 0)
#        {
# $ServerConnection. RollbackTransaction ()
# write-host "RollbackTransaction."
#        }
#
# Write-error $_
#}
}
Catch
{
Write-error $_
}
}
#这个函数到这里结束

#这里是执行按钮的事件, is to execute the function written above
$button 1ClickEventHandler = [System.EventHandler] {
$serverInstance = $textBox 1.Text
$userName = $textBox 2.Text
$password = $textBox 3.Text
$ScriptPath = $textBox 4.Text
Exec_tsql $serverInstance $userName $password $ScriptPath
[System.windows.forms.messagebox]::show ("Successful Execution")
}
$button 1. Add_click ($button 1ClickEventHandler)
$app:: EnableVisualStyles ()
$app:: Run ($myForm)

PowerShell uses WinForm to execute TSQL statements in bulk