• PowerShell应用之批量执行SQL脚本


    一篇,我们来实现PowerShell 2.0在SQL Server中的一个应用,是批量执行SQL脚本。在接下来的内容,将使用到下面的命令或类库。

    • Sort-Object
    • out-null
    • Write-Error
    • $_
    • System.IO.DirectoryInfo
    • Microsoft.SqlServer.Management.Common.ServerConnection

    创建测试环境


    为了更能说明PowerShell脚本的应用,我们这里创建个测试环境,模拟一个要升级的SQL脚本文件;首先,要创建两个数据库『TestingDB01』和『TestingDB02』:

    View Code
    use master
    Go
    if db_id('TestingDB01'Is Not null
        Drop Database TestingDB01
    Go
    Create Database TestingDB01
    Go
    if db_id('TestingDB02'Is Not null
        Drop Database TestingDB02
    Go
    Create Database TestingDB02
    Go

    在Microsoft SQL Server Management Studio(MSSMS)中执行上面的创建数据库SQL语句。接下来我们创建三个SQL脚本文件:

    1. 01_ TestingDB_CreateTB&InitializeData.sql

    2. 02_ TestingDB_Procedures_0001.sql

    3. 03_ TestingDB_Procedures_0002.sql

    第1个脚本,应用于创建数据表和初始化数据使用,第2、3个脚本,只要是存储过程的脚本文件,其中有1个存储过程包含有动态的SQL语句,每一个脚本都包含有对数据库『TestingDB01』和『TestingDB02』的脚本。这些脚本制作是模拟真实环境中的升级脚本,列举常见的脚本内容样本。下面是描述这三个SQL脚本文件的具体内容。

    [01_ TestingDB_CreateTB&InitializeData.sql]文件內容:


    View Code
    use TestingDB01
    go
    if object_id('TestingTB1'Is Not Null
        Drop Table TestingTB1
    Go     
    Create Table TestingTB1(ID int identity(1,1),Data nvarchar(200))
    go
    Set identity_insert TestingTB1 On 
    Insert into TestingTB1(ID,Data) Values(1,N'Data1')
    Insert into TestingTB1(ID,Data) Values(1,N'Data2')
    Insert into TestingTB1(ID,Data) Values(1,N'Data3')
    Insert into TestingTB1(ID,Data) Values(1,N'Data4')
    Set identity_insert TestingTB1 Off
    GO
    --TestingDB02
    use TestingDB02
    go
    if object_id('TestingTB2'Is Not Null
        Drop Table TestingTB2
    Go     
    Create Table TestingTB2(ID int identity(1,1),Data nvarchar(200))
    go
    Set identity_insert TestingTB2 On 
    Insert into TestingTB2(ID,Data) Values(1,N'DB2Data1')
    Insert into TestingTB2(ID,Data) Values(1,N'DB2Data2')
    Insert into TestingTB2(ID,Data) Values(1,N'DB2Data3')

    Set identity_insert TestingTB2 Off
    GO

    [02_ TestingDB_Procedures_0001.sql]文件內容:


    View Code
    use TestingDB01
    GO
    if object_id('rTestingTB1'Is Not Null
        Drop proc rTestingTB1
    Go     
    Create Proc rTestingTB1
    As
    Select ID,Data From TestingTB1
    Go



    use TestingDB02
    GO
    if object_id('rTestingTB2'Is Not Null
        Drop proc rTestingTB2
    Go     
    Create Proc rTestingTB2
    (
        @Columns nvarchar(max)=null,
        @Where nvarchar(max)=null
    )
    As
    If Isnull(@Columns,'')=''
        Set @Columns='ID,Data'

    If Isnull(@Where,'')>''
        Set @Where=' Where '+@Where
    Else 
        Set @Where=''
        
    Exec(N'Select '+@Columns+' From TestingTB1'+@Where)
    Go

    [03_ TestingDB_Procedures_0002.sql]文件內容:


    View Code
    use TestingDB01
    GO
    if object_id('rTestingTB'Is Not Null
        Drop proc rTestingTB
    Go     
    Create Proc rTestingTB
    As
    Select ID,Data From TestingTB1 Where Not Exists(Select 1 From TestingDB02 Where id=a.id)
    Go
    我们把上面的三个SQL脚本文件存储在本机的某一文件路径下面,如“E:\ExecuteSQLScript”

    image

    这里提示下,对于这三个脚本,需根据实际场景,按执行先后顺序,对SQL脚本文件名作编号,如格式“01_…”,”02_…”,”03_…”.

    编写PowerShell脚本


    在前边我们创建好了测试环境,接下来就开始编写PowerShell来实现执行SQL脚本文件功能。编写PowerShell脚本的时候,我们会考虑这几点问题:

    • 如何连接到SQL Server实例,如何执行SQL脚本.
    • 如何读取SQL脚本文件内容,如何按SQL脚本编号读取.
    • 如何处理错误

    如何连接到SQL Server实例,如何执行SQL脚本

    PowerShell基于.NET Framework上构建,我们可以根据.NET Framework提供的丰富的类库实现我们需要的功能。这里我们引用到Microsoft.SqlServer.Management.Common命名空間下的ServerConnection类,它可以让我们连接到某一SQL Server实例,而且在ServerConnection类中提供有方法ExecuteNonQuery(),可执行T-SQL语句功能。

    e.g.

    $serverInstance="WINSERVER01\SQL2008DE01" 
    $userName="sa"
    $password="sql20081"

    [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') |out-null
    $ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance,$userName, $password
    $ServerConnection.ExecuteNonQuery(“use test Update myTable Set Data='test' Where ID=3”)

    image

    上面的例子,我们更新数据库test中的表myTable数据,从PowerShell控制台返回的信息”1”可以判断更新脚本已经在实例”WINSERVER01\SQL2008DE01”上执行。当然我们可以在MSSMS上查询验证所更新的数据。

    如何读取SQL脚本文件内容,如何按SQL脚本编号读取.

    读SQL脚本文件,需要获得文件路径下的文件名,把脚本文件内容按文件编号读取出来,把数据转换成字符串类型String。这样我们才能调用应用到上边的ServerConnection类的ExecuteNonQuery()方法中。这里我们System.IO.DirectoryInfo类来实现。

    e.g.

    $ScriptPath="E:\ExecuteSQLScript\"
    [System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object
    foreach( $f In ($DirectoryInfo.GetFiles("*.sql")))
    {
    $f.Name
    }

    image

    我们下面定义1个类型为[System.Text.StringBuilder]的变量$Sql,调用类System.Io.File中的方法OpenText(),获取上面三个SQL脚本文件的内容,

    e.g.

    $ScriptPath="E:\ExecuteSQLScript\"
    [System.Text.StringBuilder]$Sql=""
    [System.IO.DirectoryInfo]$DirectoryInfo=New-Object System.IO.DirectoryInfo $ScriptPath | Sort-Object
    foreach( $f In ($DirectoryInfo.GetFiles("*.sql")))
    {
    $Sql=$Sql.AppendLine(([System.Io.File]::OpenText($ScriptPath+$f.Name)).ReadToEnd())
    }

    $Sql.ToString()

    image

    如何处理错误

    我们无法保证我们所写的PowerShell脚本完全能正常运行,或在运行中发生错误,我们需要作一些特殊的错误处理,如自定义错误提示等。在PowerShell脚本为我们提供类似C#或SQL Server 2005\SQL Server 2008的”Try …Catch”用法。

    e.g.

    Try
    {
    $0=0
    $value=1/$0
    }
    Catch
    {
    Write-Error $_
    }

    image

    主要考虑的几个问题,我们已经一一针对它们去解决了,下面我们写成完整的PowerShell脚本,实现批量执行SQL脚本功能:

    <#批量执行SQL脚本文件 Andy 2011-10-25 #>
    <#===========================================#>
    $serverInstance="WINSERVER01\SQL2008DE01"
    $userName="sa"
    $password="sql20081"
    $ScriptPath="E:\ExecuteSQLScript\"
    $ScriptList="

    "
    <#===========================================#>
    $n="`n"
    $r="`r"
    While ($ScriptList.IndexOf($n) -gt 0)
    {$ScriptList=$ScriptList.Replace($n,";")}
    While ($ScriptList.IndexOf($r) -gt 0)
    {$ScriptList=$ScriptList.Replace($r,";")}
    While ($ScriptList.IndexOf(" ") -gt 0)
    {$ScriptList=$ScriptList.Replace(" ","")}
    While ($ScriptList.IndexOf(",") -gt 0)
    {$ScriptList=$ScriptList.Replace(",","")}
    If ($ScriptList.IndexOf(".sql") –le 0)
    {
    $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
    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
    $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 $_

    }

    运行脚本结果如图:

    image

    提示:

    以上脚本测试,使用的SQL Server & Windows环境是:

    Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
        Sep 16 2010 20:09:22
        Copyright (c) 1988-2008 Microsoft Corporation
        Enterprise Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)

    另外在PowerShell 2.0+SQL Server 2005(sp4) + Windows 20003(Windows XP)上测试通过。

  • 相关阅读:
    计算素数。
    两个div在一行中显示
    html文本太长显示为省略号的方法
    .net remoting实例
    C#核心基础--静态类&部分类
    C#核心基础--类的继承
    c#核心基础--类的构造方法
    C#核心基础--类的声明
    C#核心基础--浅谈类和对象的概念
    c#学习之旅------01
  • 原文地址:https://www.cnblogs.com/wghao/p/2224626.html
Copyright © 2020-2023  润新知