Powershell & SQL
一、使用Powershell插入数据到SQL中
1、方法一
1 $ServerName=xxx
2 $Database=xxx
3 $UserID=xxx
4 $Pwd=xxx
5 $conn=new-object System.Data.SqlClient.SQLConnection
6 $ConnectionString = "Server=$ServerName;Database=$Database;User ID=$UserID;Password=$pwd;Trusted_Connection=False;Connect Timeout=6000"
7 $conn.ConnectionString=$ConnectionString
8 $conn.Open()
9
10 $Query="insert into 表名称(字段名, EventTime, EventType, EventId, Message) values(数值,N'$EventTime',N'$EventType',N'$EventId',N'$Message')"
11 $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
12 $cmd.CommandTimeout=6000
13 $cmd.ExecuteNonQuery()
14 $conn.close()
2、方法二
1 $Database = "xxx"
2 $Server = "xxx"
3 $Connection = New-Object System.Data.SQLClient.SQLConnection
4 $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
5 $Connection.Open()
6 $Command = New-Object System.Data.SQLClient.SQLCommand
7 $Command.Connection = $Connection
8
9
10 $insert="insert into 表名称(字段名,EventTime, EventType,EventSource, EventId, Message,RecordId) values(数值,N'$EventTime',N'$EventType',N'$TaskDisplayName',N'$EventId',N'$Message',N'$RecordId')"
11 $cmd1=new-object system.Data.SqlClient.SqlCommand($insert,$Connection)
12 $cmd1.CommandTimeout=6000
13 $cmd1.ExecuteNonQuery()
14 $Connection.Close()
二、使用Powershell从数据库中获取数据
1 $Database = "xxx"
2 $Server = "xxx"
3 $ServerName="xxx"
4 $Connection = New-Object System.Data.SQLClient.SQLConnection
5 $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
6 $Connection.Open()
7 $Command = New-Object System.Data.SQLClient.SQLCommand
8 $Command.Connection = $Connection
9
10 Add-PSSnapin SqlServerCmdletSnapin100
11 Add-PSSnapin SqlServerProviderSnapin100
12
13 $cmd=Invoke-Sqlcmd -ServerInstance $server -Database $Database -Query "select MAX(recordid) from [$Database].dbo.[$table] where [ServerName]='$serverName'"
14 $max=$cmd.column1
