豆子对SQL Server自带的sqlps模块并不熟悉。昨天发现Don Jones提供的一个SQL模块，理论上支持任何兼容ODBC Driver的数据库（MySQL, MSSQL, Oracle 等等），原理就是调用.Net框架的一些底层函数。使用起来很方便顺手，也不需要学习新的技能。

下载以后，发现这个模块其实就只包括了两个函数，一个用来查询，另外一个用来修改删除。两个函数都只有3个参数，分别是connectionString, 需要执行的SQL语句，以及是否为SQLServer。 第一个的格式根据数据库有所不同，可以去 看对应的例子。 第二个就是真正执行的SQL语句，最后一个其实是个布尔值，如果输入了这个参数就是真，没输入就是假。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
function 
Get-DatabaseData 
{
    
[
CmdletBinding
()]
    
param 
(
        
[string]
$connectionString
,
        
[string]
$query
,
        
[switch]
$isSQLServer
    
)
    
if 
(
$isSQLServer
) {
        
Write-Verbose 
'in SQL Server mode'
        
$connection 
= 
New-Object 
-TypeName `
            
System.Data.SqlClient.SqlConnection
    
} 
else 
{
        
Write-Verbose 
'in OleDB mode'
        
$connection 
= 
New-Object 
-TypeName `
            
System.Data.OleDb.OleDbConnection
    
}
    
$connection
.ConnectionString = 
$connectionString
    
$command 
= 
$connection
.CreateCommand()
    
$command
.CommandText = 
$query
    
if 
(
$isSQLServer
) {
        
$adapter 
= 
New-Object 
-TypeName `
        
System.Data.SqlClient.SqlDataAdapter 
$command
    
} 
else 
{
        
$adapter 
= 
New-Object 
-TypeName `
        
System.Data.OleDb.OleDbDataAdapter 
$command
    
}
    
$dataset 
= 
New-Object 
-TypeName System.Data.DataSet
    
$adapter
.Fill(
$dataset
)
    
$dataset
.Tables[0]
    
$connection
.close()
}
 
 
function 
Invoke-DatabaseQuery 
{
    
[
CmdletBinding
(SupportsShouldProcess=
$True
,
                   
ConfirmImpact=
'Low'
)]
    
param 
(
        
[string]
$connectionString
,
        
[string]
$query
,
        
[switch]
$isSQLServer
    
)
    
if 
(
$isSQLServer
) {
        
Write-Verbose 
'in SQL Server mode'
        
$connection 
= 
New-Object 
-TypeName `
            
System.Data.SqlClient.SqlConnection
    
} 
else 
{
        
Write-Verbose 
'in OleDB mode'
        
$connection 
= 
New-Object 
-TypeName `
            
System.Data.OleDb.OleDbConnection
    
}
    
$connection
.ConnectionString = 
$connectionString
    
$command 
= 
$connection
.CreateCommand()
    
$command
.CommandText = 
$query
    
if 
(
$pscmdlet
.shouldprocess(
$query
)) {
        
$connection
.Open()
        
$command
.ExecuteNonQuery()
        
$connection
.close()
    
}
}

下载脚本之后，重命名然后拷贝到$env:psmodulepath对应的路径上就可以使用了

现在看看怎么使用。

例如：豆子想查询一下某个数据库的备份记录。首先登陆MSSQL2012 Express的管理界面，利用T-SQL语句查询看看应该的效果。

把T-SQL的语句拷贝下来，作为传入的字符串参数，如下所示，调用第一个查询的函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Import-Module 
db
$ConnectionString 
= 
"server=sydav01\Sophos;database=sophos521;trusted_connection=True;"
$query
=
"
SELECT TOP 100
s.database_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
where s.database_name='SOPHOS521'
"
Get-DatabaseData  
-connectionString 
$ConnectionString 
-isSQLServer -query 
$query 
| ft

结果如下所示

成功获取，可以看见和在图形界面下看见的结果是一样的。