PowerShell 通过ADO.NET连接SQL Server数据库，并执行SQL脚本。工作中整理的一小段脚本，后来没有用上，先记录在这里：
建立数据库连接
查询返回一个DataTatble对象
执行一条SQL语句
通过事物执行多条SQL语句
#
# 建立数据库连接.
#
function New-SqlConnection([string]$connectionStr)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connectionStr
try{
$SqlConnection.Open()
Write-Host 'Connected to sql server.'
return $SqlConnection
}
catch [exception] {
Write-Warning ('Connect to database failed with error message:{0}' -f ,$_)
$SqlConnection.Dispose()
return $null
}
}
#
# 查询返回一个DataTable对象
#
function Get-SqlDataTable
{
param
(
[System.Data.SqlClient.SqlConnection]$SqlConnection,
[string]$query
)
$dataSet = new-object "System.Data.DataSet" "WrestlersDataset"
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query,$SqlConnection)
$dataAdapter.Fill($dataSet) | Out-Null
return $dataSet.Tables | select -First 1
}
#
# 执行一条SQL命令
#
function Execute-SqlCommandNonQuery
{
param
(
[System.Data.SqlClient.SqlConnection]$SqlConnection,
[string]$Command
)
$cmd = $SqlConnection.CreateCommand()
try
{
$cmd.CommandText = $Command
$cmd.ExecuteNonQuery() | Out-Null
return $true
}
catch [Exception] {
Write-Warning ('Execute Sql command failed with error message:{0}' -f $_)
return $false
}
finally{
$SqlConnection.Close()
}
}
#
# 通过事物处理执行多条SQL命令
#
function Execute-SqlCommandsNonQuery
{
param
(
[System.Data.SqlClient.SqlConnection]$SqlConnection,
[string[]]$Commands
)
$transaction = $SqlConnection.BeginTransaction()
$command = $SqlConnection.CreateCommand()
$command.Transaction = $transaction
try
{
foreach($cmd in $Commands) {
#Write-Host $cmd -ForegroundColor Blue
$command.CommandText = $cmd
$command.ExecuteNonQuery()
}
$transaction.Commit()
return $true
}
catch [Exception] {
$transaction.Rollback()
Write-Warning ('Execute Sql commands failed with error message:{0}' -f $_)
return $false
}
finally{
$SqlConnection.Close()
}
}
×用微信扫描并分享
