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

最新推荐文章于 2024-07-28 18:38:22 发布

weixin_34075268

最新推荐文章于 2024-07-28 18:38:22 发布

阅读量361

点赞数

文章标签：数据库 c#

这一篇，我们来实现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”

这里提示下，对于这三个脚本，需根据实际场景，按执行先后顺序，对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”)

上面的例子，我们更新数据库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
    }

我们下面定义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()

如何处理错误

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

e.g.

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

主要考虑的几个问题，我们已经一一针对它们去解决了，下面我们写成完整的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 $_

}

运行脚本结果如图：

提示：

以上脚本测试，使用的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)上测试通过。

weixin_34075268

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
PowerShell应用之-批量执行SQL脚本

这一篇，我们来实现PowerShell 2.0在SQL Server中的一个应用,是批量执行SQL脚本。在接下来的内容，将使用到下面的命令或类库。 Sort-Object out-null Write-Error $_ System.IO.DirectoryInfo Microsoft.SqlServer.Management.Common.ServerCo...
复制链接

扫一扫