通过ADO查询Access
$adOpenStatic = 3
$adLockOptimistic = 3
$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $pwd\checksav.mdb")
$strQuery="Select * from CheckSAV"
$objRecordset.Open($strQuery, $objConnection,$adOpenStatic,$adLockOptimistic)
#$objRecordSet=$objConnection.Execute($strQuery)
$objRecordset.MoveFirst()
do {$objRecordset.Fields.Item("ComputerName").Value; $objRecordset.MoveNext()} until
($objRecordset.EOF -eq $True)
$objRecordset.Close()
$objConnection.Close()
通过ADO更新Access
$adOpenStatic = 3
$adLockOptimistic = 3
$objConnection = New-Object -com "ADODB.Connection"
$objRecordSet = New-Object -com "ADODB.Recordset"
$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $pwd\checksav.mdb")
$strQuery="Select * from CheckSAV"
$objRecordset.Open($strQuery, $objConnection,$adOpenStatic,$adLockOptimistic)
$objRecordSet.AddNew()
$objRecordSet.Fields.Item("ComputerName").Value = "atl-ws-001"
$objRecordSet.Update()
$objRecordSet.Close()
$objConnection.Close()
通过ADO删除Access
$objConnection = New-Object -com "ADODB.Connection"
$objRecordSet = New-Object -com "ADODB.Recordset"
$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $pwd\checksav.mdb")
$strQuery="delete from checksav where computername='atl-ws-001'"
$objRecordSet=$objConnection.Execute($strQuery)
$objConnection.Close()
通过ADO查询Excel
$objConnection= New-Object -com "ADODB.Connection"
$file="c:\ATT1741595.xls"
$strQuery="Select * from [Sheet1$]"
$users=@()
$objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;`
Data Source=$file;Extended Properties=Excel 8.0;")
$objRecordSet=$objConnection.Execute($strQuery)
do {
$users+=$objRecordSet.Fields.Item(9).Value
$objRecordSet.MoveNext()
} Until ($objRecordSet.EOF)
$objConnection.Close()
查询SqlServer
$conn = new-object system.data.oledb.oledbconnection
$connstring = "provider=sqloledb;data source=corp-alt-66;initial catalog=tracer2_dev_dfm;integrated security=SSPI"
$conn.connectionstring = $connstring
$conn.open()
$sqlquery = "select ServerId, ServerName from Servers"
$cmd = New-Object system.data.oledb.oledbcommand
$cmd.connection = $conn
$cmd.commandtext = $sqlquery
$reader = $cmd.executereader()#将 CommandText 发送到 Connection 并生成一个 OleDbDataReader。OleDbCommand.ExecuteScalar 方法，执行查询，并返回查询所返回的结果集中第一行的第一列，忽略其他列或行。
while($reader.read())
{
$servId = $reader.getvalue(0)
$computer = $reader.getvalue(1)
}
$reader.close()
$conn.close()
更新Sqlserver
$conn = new-object System.Data.SqlClient.SqlConnection
$connstring = "provider=sqloledb;data source=corp-alt-66;initial catalog=tracer2_dev_dfm;integrated security=SSPI"
$conn.connectionstring = $connstring
$conn.open()
$query = "Insert into TestTable (ServerId, OSVersion) values ('$servId','$version')"
$cmd = New-Object system.data.oledb.oledbcommand
$cmd.connection = $conn
$cmd.commandtext = $query
$cmd.executenonquery()#针对 Connection 执行 SQL 语句并返回受影响的行数。
$conn.close()
$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "server=localhost;database=Northwind;integrated security=SSPI"
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT CategoryID, CategoryName, Description,Picture FROm Categories"
$cmd.Connection = $conn
$adapter = new-object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $cmd
$ds = new-object System.Data.DataSet
$adapter.Fill($ds)
$conn.close()
$ds.Tables[0]
- ##############################################################################
- ##
- ## Invoke-SqlCommand.ps1
- ##
- ## From Windows PowerShell Cookbook (O'Reilly)
- ## by Lee Holmes (http://www.leeholmes.com/guide)
- ##
- ## Return the results of a SQL query or operation
- ##
- ## ie:
- ##
- ## ## Use Windows authentication
- ## Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders"
- ##
- ## ## Use SQL Authentication
- ## $cred = Get-Credential
- ## Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred
- ##
- ## ## Perform an update
- ## $server = "MYSERVER"
- ## $database = "Master"
- ## $sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248"
- ## Invoke-SqlCommand $server $database $sql
- ##
- ## $sql = "EXEC SalesByCategory 'Beverages'"
- ## Invoke-SqlCommand -Sql $sql
- ##
- ## ## Access an access database
- ## Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users"
- ##
- ## ## Access an excel file
- ## Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$]'
- ##
- ##############################################################################
-
- param(
- [string] $dataSource = ".\SQLEXPRESS",
- [string] $database = "Northwind",
- [string] $sqlCommand = $(throw "Please specify a query."),
- [System.Management.Automation.PsCredential] $credential
- )
-
-
- ## Prepare the authentication information. By default, we pick
- ## Windows authentication
- $authentication = "Integrated Security=SSPI;"
-
- ## If the user supplies a credential, then they want SQL
- ## authentication
- if($credential)
- {
- $plainCred = $credential.GetNetworkCredential()
- $authentication =
- ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
- }
-
- ## Prepare the connection string out of the information they
- ## provide
- $connectionString = "Provider=sqloledb; " +
- "Data Source=$dataSource; " +
- "Initial Catalog=$database; " +
- "$authentication; "
-
- ## If they specify an Access database or Excel file as the connection
- ## source, modify the connection string to connect to that data source
- if($dataSource -match '\.xls$|\.mdb$')
- {
- $connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; "
-
- if($dataSource -match '\.xls$')
- {
- $connectionString += 'Extended Properties="Excel 8.0;"; '
-
- ## Generate an error if they didn't specify the sheet name properly
- if($sqlCommand -notmatch '\[.+\$\]')
- {
- $error = 'Sheet names should be surrounded by square brackets, and ' +
- 'have a dollar sign at the end: [Sheet1$]'
- Write-Error $error
- return
- }
- }
- }
-
- ## Connect to the data source and open it
- $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
- $command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
- $connection.Open()
-
- ## Fetch the results, and close the connection
- $adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
- $dataset = New-Object System.Data.DataSet
- [void] $adapter.Fill($dataSet)
- $connection.Close()
-
- ## Return all of the rows from their query
- $dataSet.Tables | Select-Object -Expand Rows