PowerShell 连接SQL Server 数据库 2


PowerShell 通过ADO.NET连接SQL Server数据库,并执行SQL脚本。工作中整理的一小段脚本,后来没有用上,先记录在这里:

  1. 建立数据库连接
  2. 查询返回一个DataTatble对象
  3. 执行一条SQL语句
  4. 通过事物执行多条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()
    }
}
×用微信扫描并分享
本文链接: https://www.pstips.net/connect-sql-database.html
请尊重原作者和编辑的辛勤劳动,欢迎转载,并注明出处!

关于 Mooser Lee

我是一个Powershell的爱好者,创建了PowerShell中文博客,热衷于Powershell技术的搜集和分享。本站部分内容来源于互联网,不足之处敬请谅解,并欢迎您批评指正。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

2 条评论 “PowerShell 连接SQL Server 数据库