powershell 连接数据库

来源：互联网发布：中国十大公知言论编辑：程序博客网时间：2024/05/01 20:48

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

建立数据库连接
查询返回一个DataTatble对象
执行一条SQL语句
通过事物执行多条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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
# 
# 建立数据库连接.
#
functionNew-SqlConnection([string]$connectionStr)
{
    $SqlConnection= New-ObjectSystem.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对象
#
functionGet-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命令
#
functionExecute-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命令
#
functionExecute-SqlCommandsNonQuery
{
    param
    (
    [System.Data.SqlClient.SqlConnection]$SqlConnection,
    [string[]]$Commands
    )
    $transaction= $SqlConnection.BeginTransaction()
    $command= $SqlConnection.CreateCommand()
    $command.Transaction =$transaction
    try
    {
        foreach($cmdin $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()
    }
}

本文链接： http://www.pstips.net/connect-sql-database.html
请尊重原作者和编辑的辛勤劳动,欢迎转载,并注明出处!

阅读全文

0 0