powershell利用winform批量执行tsql语句
$app=[System.Windows.Forms.Application]
$myForm=new-object System.Windows.Forms.Form
$myForm.Text="T_sql Excute window"
$button1 = new-object System.Windows.Forms.Button
$button1.Size = new-object System.Drawing.Size -argumentlist 75, 23
$button1.Text = "执行程序"
$button1.Location = new-object System.Drawing.Point -argumentlist 200, 200
$label1 = new-object System.Windows.Forms.Label;
$label1.Location = new-object System.Drawing.Point -argumentlist 1, 10
$label1.Size = new-object System.Drawing.Size -argumentlist 80, 15
$label1.Text = "数据库地址 : "
$textbox1=new-object System.Windows.Forms.TextBox
$textBox1.Multiline = $true;
$textBox1.Text = ""
$textBox1.Size = new-object System.Drawing.Size -argumentlist 150, 15
$textBox1.Location = new-object System.Drawing.Point -argumentlist 90, 10
#获取第二个参数
$label2 = new-object System.Windows.Forms.Label;
$label2.Location = new-object System.Drawing.Point -argumentlist 1, 50
$label2.Size = new-object System.Drawing.Size -argumentlist 80, 15
$label2.Text = "用户名 : "
$textbox2=new-object System.Windows.Forms.TextBox
$textBox2.Multiline = $true;
$textBox2.Text = ""
$textBox2.Size = new-object System.Drawing.Size -argumentlist 150, 15
$textBox2.Location = new-object System.Drawing.Point -argumentlist 90, 50
#获取第三个参数
$label3 = new-object System.Windows.Forms.Label;
$label3.Location = new-object System.Drawing.Point -argumentlist 1, 90
$label3.Size = new-object System.Drawing.Size -argumentlist 80, 15
$label3.Text = "密码 : "
$textbox3=new-object System.Windows.Forms.TextBox
$textBox3.Multiline = $true;
$textBox3.Text = ""
$textBox3.Size = new-object System.Drawing.Size -argumentlist 150, 15
$textBox3.Location = new-object System.Drawing.Point -argumentlist 90, 90
#获取第四个参数
$label4 = new-object System.Windows.Forms.Label;
$label4.Location = new-object System.Drawing.Point -argumentlist 1, 130
$label4.Size = new-object System.Drawing.Size -argumentlist 80, 15
$label4.Text = "tsql路径 : "
$textbox4=new-object System.Windows.Forms.TextBox
$textBox4.Multiline = $true;
$textBox4.Text = ""
$textBox4.Size = new-object System.Drawing.Size -argumentlist 150, 15
$textBox4.Location = new-object System.Drawing.Point -argumentlist 90, 130
# $textbox2=new-object System.Windows.Forms.TextBox
# $textBox2.Multiline = $true;
# $textBox2.Text = "test"
# $textBox2.Size = new-object System.Drawing.Size -argumentlist 281, 113
#$flowLayoutPanel1 = new-object System.Windows.Forms.FlowLayoutPanel
$myForm.Controls.Add($label1)
$myForm.Controls.Add($textbox1)
$myForm.Controls.Add($label2)
$myForm.Controls.Add($textbox2)
$myForm.Controls.Add($label3)
$myForm.Controls.Add($textbox3)
$myForm.Controls.Add($label4)
$myForm.Controls.Add($textbox4)
$myForm.Controls.Add($button1)
#$myForm.Controls.Add($textbox2)
#$myForm.Controls.Add($label1)
#$flowLayoutPanel1.Controls.Add($label1);
#$flowLayoutPanel1.Controls.Add($textBox1);
#$flowLayoutPanel1.Controls.Add($textBox2);
#$flowLayoutPanel1.Controls.Add($button1);
#$flowLayoutPanel1.Dock = "Fill"
#$flowLayoutPanel1.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 $password
$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 $_
}
}
#这个函数到这里结束
#这里是执行按钮的事件,就是执行上面写的函数
$button1ClickEventHandler = [System.EventHandler] {
$serverInstance = $textBox1.Text
$userName = $textBox2.Text
$password = $textBox3.Text
$ScriptPath = $textBox4.Text
exec_tsql $serverInstance $userName $password $ScriptPath
[System.Windows.Forms.MessageBox]::Show("执行成功")
}
$button1.Add_Click($button1ClickEventHandler)
$app::EnableVisualStyles()
$app::Run($myForm)