【转】PowerShell 连接SQL Server 数据库 - ADO.NET
2024-08-26 12:12:44
转至:http://www.pstips.net/connect-sql-database.html
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
|
# # 建立数据库连接. # 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() } } |
最新文章
- 如何开发一个简单的HTML5 Canvas 小游戏
- Atitit 图像处理的摩西五经attilax总结
- 如何通过JS调用某段SQL语句
- Linux安装JDK1.7
- 1025基础REDIS
- (原)win8下编译GLUT
- 转>;>;在同一个sql语句中如何写不同条件的count数量
- .Net程序员安卓学习之路5:使用xutils注入View和事件以及图片的显示
- 前端开发自动化工作流工具,JavaScript自动化构建工具grunt、gulp、webpack介绍
- 记 移动端页面中莫名其妙的渲染BUG
- [JQuery]选择器详解
- 又爱又恨的BOOTSTRAP
- 管道(Pipe)/createPipe
- [Swift]LeetCode349. 两个数组的交集 | Intersection of Two Arrays
- python+turtle 笔记
- Jenkins Installing and migration
- [IR] Graph Compression
- CentOS7 使用firewalld打开关闭防火墙以及端口
- 【java】一些零碎的知识点
- 各种蕴含算法思想的DP - 2