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()

View Code

 

　　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()

View Code

 

二、使用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

View Code

 

转载于:https://www.cnblogs.com/thescentedpath/p/PowershellSQL.html